SQL ET POSTGRE 5.28 Challenge 1: Selection queries

Time for a challenge. You will practise SELECT, the WHERE clause and comparison operators on our film database. Open the Query Tool and tackle the four queries below one after the other.

The four queries to write

First, write a selection query that displays the title and the release date of every film. Then write a second query to fetch the first and last names of every director whose nationality is English. The third query selects all female actors born after 1 January 1970. Finally, write a query that returns the titles of every film with a running time longer than 90 minutes and whose country of origin is the United States. 
-- 1. Title + release date
SELECT titre, date_de_sortie FROM film;

-- 2. English directors
SELECT prenom, nom FROM realisateur WHERE nationalite = 'Anglais';

-- 3. Female actors after 1970
SELECT * FROM acteur
WHERE civilite = 'F' AND date_naissance > '1970-01-01';

-- 4. US films > 90 minutes
SELECT titre FROM film WHERE duree > 90 AND pays = 'Etats-Unis';
  • Reuse SELECT <columns> FROM <table>.
  • Add a WHERE clause when filtering is needed.
  • Combine conditions with AND for queries 3 and 4.
  • Use comparison operators > for dates and durations.
Pause the video and write the four queries yourself. The solution will be presented in the next lesson — good luck.

Summary

This lesson presents four SQL SELECT query challenges designed to strengthen practical data retrieval skills. Students practice writing queries to display film metadata, filter directors by nationality, retrieve actresses by birth date criteria, and identify films by duration and country of origin. The challenges combine individual WHERE conditions and AND operators to progressively build SQL proficiency.

Key points

  • SELECT queries are the foundation for retrieving data with specific filtering criteria from database tables
  • WHERE clauses enable single-condition filtering (nationality, gender, birth date, duration)
  • AND operators combine multiple conditions to refine queries (e.g., duration > 90 AND country = 'United States')
  • Each challenge targets a different table entity (films, directors, actresses) with distinct column selection strategies
  • Date-based and numeric comparisons (after 1970, greater than 90 minutes) demonstrate temporal and quantitative filtering in SQL

FAQ

What is the purpose of these SQL SELECT challenges?

These challenges develop practical skills in writing SELECT queries to answer specific data retrieval questions, emphasizing WHERE clauses and combining multiple filtering conditions to extract meaningful data from a PostgreSQL database.

What filtering techniques are covered in these exercises?

The challenges cover simple WHERE conditions (nationality filtering, gender-based selection) and complex multi-condition filters using AND operators to combine duration thresholds with geographic origin.

Which tables and fields are used in these query challenges?

The exercises involve films (with title, release_date, duration, country), directors (with name, nationality), and actresses (with name, birth_date) tables, requiring students to select appropriate columns for each query objective.