5. Views in complicated queries


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.


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!


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 = job_history.position_id 
WHERE job_history.end_date IS NULL;