How to query more than one table
Creating JOINs
Referencing columns
Let's practice
13. Further practice

Instruction

Excellent! We have one more exercise for you before the end of this part. Are you ready? You are going to write your longest SQL query ever and you'll apply knowledge from both of the parts you've mastered so far. Are you ready?

Exercise

Select columns id, title, production_year from table movie and columns name, birth_year from table director in such a way that a movie is shown together with its director. Show the column birth_year as born_in and the column production_year as produced_in. Select only those movies:

  • whose title contains a letter 'a' and which were filmed after 2000, or
  • whose director was born between 1945 and 1995.

     

Stuck? Here's a hint!

Type:

SELECT
  movie.id, 
  title, 
  production_year AS produced_in, 
  name, 
  birth_year AS born_in 
FROM movie 
JOIN director 
  ON director.id = director_id 
WHERE (title LIKE '%a%' AND  production_year > 2000) 
  OR (birth_year between 1945 AND 1995);