How to query more than one table
Creating JOINs
Referencing columns
Let's practice
Instruction
Nice! To sum up, we discussed the following concepts:
- In SQL, you can select data from multiple tables. To do so, you list comma-separated table names in the
FROMclause. - Unfortunately, this isn't very efficient and often produces many unnecessary rows. To solve that, you should use:
JOIN <table> ON <condition>
- If there are multiple columns with the same name, you should add a table name before the column and separate the two with a dot (
Table.Column). - Columns can be given aliases. The syntax looks like this:
<column> AS <alias>.
Let's put into practice everything we've learned so far. Are you ready? This example is going to be slightly more complicated, so make sure you remember everything from this part of the course.
Exercise
Select the Title and ProductionYear columns from the Movie table and Name and BirthYear columns from the Director table such that each movie is shown together with its director.
Show the BirthYear column as BornIn. Select only those movies that were filmed when their director was younger than 40.
Stuck? Here's a hint!
If we are supposed to find young directors, we may i.e., assume that the difference between ProductionYear and BirthYear must be less than 40.
Take a look at the correct query:
SELECT Movie.Title, Movie.ProductionYear, Director.Name, Director.BirthYear AS BornIn FROM Movie JOIN Director ON Movie.DirectorId = Director.Id WHERE (Movie.ProductionYear - Director.BirthYear) < 40;
