Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close

Wrap course as a gift

Prepare your gift and double-check it looks just right.

Form validation errors

Recipient's Name:
Recipient's Email:
Your Message (Optional):
Proceed to Checkout

SQL Basics in PostgreSQL

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. 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 the id, title, and production_year columns from the movie table, and the name and birth_year columns from the director table 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!

  1. Select proper columns from the movie and director tables.
  2. Start by writing the construction for the WHERE condition, like so:
    WHERE (...)
       OR (...)
  3. Fill in both of the parenthesis:
    • title LIKE '%a%' AND production_year > 2000
    • birth_year between 1945 AND 1995

And when you're really stuck, use the answer:

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);