Introduction
Foreign keys
Multicolumn foreign keys
Updates and deletes
Revision

Instruction

Cool! That's it. Now that we have the tables ready, let's try to query our database.

Exercise

Select first_name and last_name of each employee together with the title of their current position. Use the ERD below to help you.

The employee, job_history and position tables

Hints:

  • (a) you will need to use JOIN twice to join the three tables
  • (b) if you only want to retrieve the current position of each employee, remember that the filed end_data for that particular row in job_history will be NULL.

 

Stuck? Here's a hint!

Type

SELECT e.first_name, e.last_name, p.title
FROM employee e INNER JOIN job_history j
ON e.id = j.employee_id JOIN position p
ON j.position_id = p.id
WHERE end_date IS NULL;

Console

Code editor

Result

TableConsole