How to query more than one table
Creating JOINs
Referencing columns
Let's practice
Instruction
Excellent! We have one more exercise for you before the end of this part. You are going to write your longest SQL query yet, and you'll apply knowledge from both of the parts you've mastered so far. Are you ready?
Exercise
Select the Id, Title, and ProductionYear columns from the Movie table and the Name and BirthYear columns from the Director table such that each movie is shown together with its director. Show the BirthYear column as BornIn and the ProductionYear column as ProducedIn. Select only those movies:
- Whose title contains a letter
'a'and that was filmed after 2000.
or
- Whose director was born between 1945 and 1995.
Stuck? Here's a hint!
Type:
SELECT
Movie.Id,
Movie.Title,
Movie.ProductionYear AS ProducedIn,
Director.Name,
Director.BirthYear AS BornIn
FROM Movie
JOIN Director
ON Director.Id = DirectorId
WHERE (
Movie.Title LIKE N'%a%'
AND Movie.ProductionYear > 2000
)
OR Director.BirthYear BETWEEN 1945 AND 1995;
