Summer Deals - hours only!Up to 80% off on all courses and bundles.-Close
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:

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;