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 = ___;This exercise is part of the course
Introduction to Relational Databases in SQL
Learn how to create one of the most efficient ways of storing data - relational databases!
In the final chapter, you'll leverage foreign keys to connect tables and establish relationships that will greatly benefit your data quality. And you'll run ad hoc analyses on your new database.
Exercise 1: Model 1:N relationships with foreign keysExercise 2: REFERENCE a table with a FOREIGN KEYExercise 3: Explore foreign key constraintsExercise 4: JOIN tables linked by a foreign keyExercise 5: Model more complex relationshipsExercise 6: Add foreign keys to the "affiliations" tableExercise 7: Populate the "professor_id" columnExercise 8: Drop "firstname" and "lastname"Exercise 9: Referential integrityExercise 10: Referential integrity violationsExercise 11: Change the referential integrity behavior of a keyExercise 12: RoundupExercise 13: Count affiliations per universityExercise 14: Join all the tables togetherWhat is DataCamp?
Learn the data skills you need online at your own pace—from non-coding essentials to data science and machine learning.