Views
5. Views in complicated queries
Revision

Instruction

Good! As you can see, the view worked like any other table. In fact, views can be used in complicated queries such as joins too. Let's give it a go.

Exercise

Use a join to select course_name, lecturer_first_name and lecturer_last_name from the view course_lecturer together with the column title from the table position. Select only the current position (i.e. a position whose end_date is null). Use the ERD diagram to help you navigate around tables:

Database with view course_lecturer

You will need to use the table job_history and two joins.

Stuck? Here's a hint!

Type

SELECT course_name, lecturer_first_name, lecturer_last_name, position.title 
FROM course_lecturer 
JOIN job_history 
ON job_history.lecturer_id = course_lecturer.lecturer_id  
JOIN position 
ON position.id = job_history.position_id 
WHERE job_history.end_date IS NULL;

Console

Code editor

Result

TableConsole