Learning Goals
By the end of this section you will:
understand that joins are used to extract data from multiple tables
know how to combine tables using inner joins and extract data from it
Joins are used in SQL to combine rows from two or more tables based on a related column between them. Instead of writing multiple subqueries, joins let you pull data from different tables into a single result. This is helpful when related information is stored across separate tables, such as customers and their orders. By using joins, you can view and analyse all the connected data in one query result.
Go to the W3schools Tutorial on Joins and complete their exercises (we will only be worried about inner joins)
The JOIN Clause¶
The SQLite JOIN clause is used to combine rows from two or more tables based on a related column they share. Go to the W3schools Tutorial on the INNER JOIN Keyword and complete their exercises. Note, in SQLite we can just use JOIN in place of INNER JOIN.
JOIN Activities
Using Repairs database
List all job numbers and the name of the technian assigned to it.
List all technicians and what they are qualified in.
Create a job listing which lists the job number, technician name, type, owner and whether it is ready. Sort the listing to the ready devices are first.
Create an invoice listing that, for each finished repair shows the job number, the owner, the type, the time the repair took and the cost (time * rate).
Using the Chinook database
List all albums, including artist name, sorted by artist name.
List the name, and only the name, of all the tacks that are in some form of metal genre.
List the details of all Def Leppard tracks (Name, album name, genre name, composer, length in seconds, and unit price).
Using the Schools database
List all students and the subject results (name, subject name and percent)
What is the gender breakdown for each subject?
What is the average percentage of students results for each teacher?