JOIN tables linked by a foreign key
Let's join these two tables to analyze the data further!
You might already know how SQL joins work from the Intro to SQL for Data Science course (last exercise) or from Joining Data in PostgreSQL.
Here's a quick recap on how joins generally work:
SELECT ...
FROM table_a
JOIN table_b
ON ...
WHERE ...
While foreign keys and primary keys are not strictly necessary for join queries, they greatly help by telling you what to expect. For instance, you can be sure that records referenced from table A will always be present in table B – so a join from table A will always find something in table B. If not, the foreign key constraint would be violated.
This is a part of the course
“Introduction to Relational Databases in SQL”
Exercise instructions
JOINprofessorswithuniversitiesonprofessors.university_id = universities.id, i.e., retain all records where the foreign key ofprofessorsis equal to the primary key ofuniversities.- Filter for
university_city = 'Zurich'.
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
-- Select all professors working for universities in the city of Zurich
SELECT professors.lastname, universities.id, universities.university_city
___ professors
___ ___
ON ___.university_id = universities.___
___ universities.university_city = ___;