Brainlag
  1. SQL
  2. Foundations
06/39

DISTINCT, each value once

25 min5 exercises
  1. 1A bigger table
  2. 2Distinct means whole rows
  3. 3Run it: every genre once
  4. 4Every age rating once
  5. 5Genres made in 2024
  6. 6The same title twice
  7. 7Genres that keep repeating
  8. 8Who reviewed in 2025

Learn

A bigger table

From here on the tables get bigger. This lesson's database is a small film site: directors, films and reviews. You can't check 146 reviews by eye, which is exactly why you are learning to ask questions instead.

Say you want to know which genres the site has. SELECT genre FROM films; gives you 34 rows, one per film, with drama turning up again and again. Add DISTINCT right after SELECT and every repeat is dropped:

SQL
SELECT DISTINCT genre FROM films;

Seven rows, one per genre. Useful for "which values exist in this column?", which is one of the first things you ask about any new table.

Learn

Distinct means whole rows

DISTINCT works on the whole result row, not on the first column. So this:

SQL
SELECT DISTINCT genre, age_rating FROM films;

gives each combination once: drama | 12A and drama | 15 are different rows, so both stay. The film site has two different films called The Long Shift (2022 and 2025). SELECT DISTINCT title shows that title once, but SELECT DISTINCT title, year shows it twice, because the rows are not the same.

A few more things worth knowing:

  • NULL counts as one value, so a column with gaps shows a single NULL row.
  • DISTINCT goes straight after SELECT, once, not in front of each column.
  • It teams up with everything you know: WHERE filters first, then repeats are removed, then ORDER BY sorts.

Try

Run it: every genre once

The query in the editor runs against this lesson's tables (open Tables above the editor to see them). Run it, change the WHERE or the columns, run it again.

Tables directors, films, reviews
directors 10 rows, 3 columns
idnamecountry
1Maren HoltNorway
2Tomas ReyesMexico
3Priya RamanIndia
4Callum ShawUK
5Yuki AraiJapan
6Ada NwosuNigeria
7Lena FischerGermany
8Sam OkoroUK
9Rosa MarinSpain
10Jonah PikeUSA
films 34 rows, 9 columns
idtitleyeargenreminutesage_ratingdirector_idbudgetgross
1The Last Lighthouse2019drama11812A137.8153.1
2Turbo Hamsters2021animation88U1028.594.5
3Static Hearts2022romance10412A956.3142.7
4Below Zero2018thriller9715744.6151.0
5Paper Moons2020animation92U551.828.5
6The Quiet Floor2023horror10115411.853.0
7Signal Lost2021sci-fi12612A445.366.6
8Kitchen Wars2019comedy9512A845.149.3
9Monsoon Letters2022drama13112A322.950.6
10Iron Orchard2024sci-fi14212A715.314.8
11Glass Garden2023drama10915131.495.9
12The Night Market2020thriller11215520.563.7

First 12 of 34 rows. Query the table to see the rest.

reviews 146 rows, 5 columns
idfilm_idreviewerstarsreviewed_on
11lou_watches32019-03-04
21oscarbait22019-10-31
31critic_cal12019-02-11
42oscarbait32021-05-23
52critic_cal22021-06-07
62nightowl_nia22021-06-15
72jojo_reviews12021-10-16
82popcorn_priya32021-07-15
92zara_frames32021-08-14
102devsees22021-11-23
113filmfinn32022-02-17
123zara_frames52022-10-02

First 12 of 146 rows. Query the table to see the rest.

Show the SQL that built them
-- films: a small film database with reviews. Shared SQL dataset (content/datasets/sql-films.sql),
-- loaded with dataset: sql-films. Budgets and box office (gross) are in millions of pounds.
CREATE TABLE directors (id INTEGER PRIMARY KEY, name TEXT, country TEXT);
CREATE TABLE films (id INTEGER PRIMARY KEY, title TEXT, year INTEGER, genre TEXT, minutes INTEGER, age_rating TEXT, director_id INTEGER REFERENCES directors(id), budget REAL, gross REAL);
CREATE TABLE reviews (id INTEGER PRIMARY KEY, film_id INTEGER REFERENCES films(id), reviewer TEXT, stars INTEGER, reviewed_on TEXT);
INSERT INTO directors VALUES
  (1, 'Maren Holt', 'Norway'),
  (2, 'Tomas Reyes', 'Mexico'),
  (3, 'Priya Raman', 'India'),
  (4, 'Callum Shaw', 'UK'),
  (5, 'Yuki Arai', 'Japan'),
  (6, 'Ada Nwosu', 'Nigeria'),
  (7, 'Lena Fischer', 'Germany'),
  (8, 'Sam Okoro', 'UK'),
  (9, 'Rosa Marin', 'Spain'),
  (10, 'Jonah Pike', 'USA');
INSERT INTO films VALUES
  (1, 'The Last Lighthouse', 2019, 'drama', 118, '12A', 1, 37.8, 153.1),
  (2, 'Turbo Hamsters', 2021, 'animation', 88, 'U', 10, 28.5, 94.5),
  (3, 'Static Hearts', 2022, 'romance', 104, '12A', 9, 56.3, 142.7),
  (4, 'Below Zero', 2018, 'thriller', 97, '15', 7, 44.6, 151.0),
  (5, 'Paper Moons', 2020, 'animation', 92, 'U', 5, 51.8, 28.5),
  (6, 'The Quiet Floor', 2023, 'horror', 101, '15', 4, 11.8, 53.0),
  (7, 'Signal Lost', 2021, 'sci-fi', 126, '12A', 4, 45.3, 66.6),
  (8, 'Kitchen Wars', 2019, 'comedy', 95, '12A', 8, 45.1, 49.3),
  (9, 'Monsoon Letters', 2022, 'drama', 131, '12A', 3, 22.9, 50.6),
  (10, 'Iron Orchard', 2024, 'sci-fi', 142, '12A', 7, 15.3, 14.8),
  (11, 'Glass Garden', 2023, 'drama', 109, '15', 1, 31.4, 95.9),
  (12, 'The Night Market', 2020, 'thriller', 112, '15', 5, 20.5, 63.7),
  (13, 'Puddle Jumpers', 2024, 'animation', 85, 'U', 10, 59.0, 36.9),
  (14, 'Late Checkout', 2021, 'comedy', 99, '15', 8, 18.1, 14.0),
  (15, 'Red Tide', 2019, 'horror', 94, '18', 4, 41.3, 86.4),
  (16, 'Half Light', 2025, 'drama', 115, '12A', 3, 27.5, NULL),
  (17, 'Orbit Kids', 2025, 'sci-fi', 103, 'PG', 10, 16.3, 58.4),
  (18, 'The Long Shift', 2022, 'thriller', 121, '15', 2, 46.0, 102.8),
  (19, 'Salt and Smoke', 2020, 'drama', 127, '15', 2, 49.8, 67.1),
  (20, 'Bad Influence', 2023, 'comedy', 92, '15', 9, 22.0, 95.2),
  (21, 'Second Act', 2018, 'romance', 108, '12A', 9, 15.2, 15.1),
  (22, 'The Burrow', 2024, 'horror', 89, '15', 6, 3.9, 12.3),
  (23, 'Lagos Nights', 2022, 'thriller', 116, '15', 6, 49.6, 30.4),
  (24, 'Snow Day', 2019, 'comedy', 86, 'PG', 8, 29.7, 60.2),
  (25, 'Echo Chamber', 2024, 'sci-fi', 119, '15', 7, 21.0, 28.6),
  (26, 'Mango Season', 2021, 'romance', 111, '12A', 3, 24.3, 23.8),
  (27, 'The Inheritance', 2023, 'drama', 136, '12A', 1, 55.5, 67.5),
  (28, 'Cloud Shepherds', 2022, 'animation', 90, 'U', 5, 33.0, 99.9),
  (29, 'Night Swim', 2025, 'horror', 98, '15', 4, 40.1, 94.0),
  (30, 'Final Draft', 2020, 'comedy', 101, '12A', 8, 32.3, 136.9),
  (31, 'River Road', 2018, 'drama', 122, '12A', 2, 12.1, 14.8),
  (32, 'Overclocked', 2025, 'sci-fi', 109, '12A', 6, 19.5, NULL),
  (33, 'Two Left Feet', 2024, 'romance', 97, 'PG', 9, 44.9, 95.9),
  (34, 'The Long Shift', 2025, 'thriller', 118, '15', 10, 24.6, NULL);
INSERT INTO reviews VALUES
  (1, 1, 'lou_watches', 3, '2019-03-04'),
  (2, 1, 'oscarbait', 2, '2019-10-31'),
  (3, 1, 'critic_cal', 1, '2019-02-11'),
  (4, 2, 'oscarbait', 3, '2021-05-23'),
  (5, 2, 'critic_cal', 2, '2021-06-07'),
  (6, 2, 'nightowl_nia', 2, '2021-06-15'),
  (7, 2, 'jojo_reviews', 1, '2021-10-16'),
  (8, 2, 'popcorn_priya', 3, '2021-07-15'),
  (9, 2, 'zara_frames', 3, '2021-08-14'),
  (10, 2, 'devsees', 2, '2021-11-23'),
  (11, 3, 'filmfinn', 3, '2022-02-17'),
  (12, 3, 'zara_frames', 5, '2022-10-02'),
  (13, 3, 'oscarbait', 4, '2022-10-23'),
  (14, 3, 'moviemaya', 3, '2022-03-26'),
  (15, 3, 'tilly_tv', 3, '2022-02-03'),
  (16, 3, 'critic_cal', 4, '2022-11-26'),
  (17, 4, 'oscarbait', 3, '2019-11-05'),
  (18, 4, 'critic_cal', 2, '2019-03-02'),
  (19, 5, 'critic_cal', 3, '2020-04-02'),
  (20, 5, 'cinemakai', 4, '2020-10-04'),
  (21, 5, 'reelrob', 3, '2020-02-22'),
  (22, 5, 'popcorn_priya', 4, '2020-10-17'),
  (23, 5, 'jojo_reviews', 3, '2020-06-30'),
  (24, 6, 'devsees', 2, '2023-07-06'),
  (25, 6, 'jojo_reviews', 1, '2023-06-06'),
  (26, 6, 'zara_frames', 3, '2023-09-20'),
  (27, 7, 'cinemakai', 2, '2021-08-09'),
  (28, 7, 'sofasam', 3, '2021-10-21'),
  (29, 7, 'reelrob', 2, '2021-04-09'),
  (30, 7, 'hana_h', 2, '2021-04-30'),
  (31, 7, 'zara_frames', 2, '2021-07-24'),
  (32, 8, 'popcorn_priya', 2, '2019-08-26'),
  (33, 8, 'oscarbait', 3, '2019-03-21'),
  (34, 8, 'cinemakai', 2, '2019-03-04'),
  (35, 9, 'benwatches', 4, '2022-04-08'),
  (36, 9, 'moviemaya', 5, '2022-05-15'),
  (37, 10, 'reelrob', 4, '2024-07-05'),
  (38, 10, 'filmfinn', 5, '2024-04-30'),
  (39, 10, 'zara_frames', 5, '2024-03-24'),
  (40, 10, 'nightowl_nia', 3, '2024-03-06'),
  (41, 10, 'devsees', 4, '2024-10-20'),
  (42, 10, 'sofasam', 4, '2024-02-22'),
  (43, 10, 'cinemakai', 4, '2024-11-26'),
  (44, 11, 'tilly_tv', 3, '2023-08-25'),
  (45, 11, 'nightowl_nia', 2, '2023-09-15'),
  (46, 11, 'hana_h', 2, '2023-08-20'),
  (47, 12, 'reelrob', 3, '2020-07-16'),
  (48, 12, 'lou_watches', 3, '2020-04-25'),
  (49, 12, 'sofasam', 3, '2020-04-15'),
  (50, 12, 'moviemaya', 3, '2020-10-11'),
  (51, 12, 'filmfinn', 3, '2020-08-26'),
  (52, 12, 'critic_cal', 2, '2020-02-26'),
  (53, 12, 'jojo_reviews', 2, '2020-08-02'),
  (54, 13, 'lou_watches', 5, '2024-08-12'),
  (55, 13, 'moviemaya', 5, '2024-05-04'),
  (56, 13, 'filmfinn', 5, '2024-05-22'),
  (57, 14, 'cinemakai', 3, '2021-04-04'),
  (58, 14, 'lou_watches', 5, '2021-05-25'),
  (59, 14, 'filmfinn', 3, '2021-02-04'),
  (60, 14, 'popcorn_priya', 4, '2021-07-02'),
  (61, 14, 'hana_h', 3, '2021-08-10'),
  (62, 14, 'moviemaya', 3, '2021-09-21'),
  (63, 15, 'benwatches', 2, '2019-04-12'),
  (64, 15, 'cinemakai', 2, '2019-06-14'),
  (65, 15, 'sofasam', 3, '2019-11-04'),
  (66, 15, 'popcorn_priya', 2, '2019-04-28'),
  (67, 15, 'lou_watches', 2, '2019-03-24'),
  (68, 15, 'nightowl_nia', 2, '2019-05-02'),
  (69, 15, 'hana_h', 3, '2019-04-28'),
  (70, 17, 'cinemakai', 4, '2025-08-28'),
  (71, 17, 'hana_h', 3, '2025-03-03'),
  (72, 17, 'moviemaya', 3, '2025-05-31'),
  (73, 17, 'zara_frames', 4, '2025-04-10'),
  (74, 18, 'reelrob', 3, '2022-05-31'),
  (75, 18, 'oscarbait', 3, '2022-08-17'),
  (76, 18, 'devsees', 2, '2022-03-27'),
  (77, 18, 'nightowl_nia', 1, '2022-11-20'),
  (78, 18, 'tilly_tv', 5, '2022-11-02'),
  (79, 18, 'critic_cal', 3, '2022-05-04'),
  (80, 19, 'benwatches', 2, '2020-08-19'),
  (81, 19, 'hana_h', 3, '2020-08-21'),
  (82, 19, 'tilly_tv', 4, '2020-11-21'),
  (83, 19, 'cinemakai', 4, '2020-07-12'),
  (84, 19, 'nightowl_nia', 3, '2020-02-18'),
  (85, 20, 'tilly_tv', 4, '2023-04-24'),
  (86, 20, 'devsees', 2, '2023-03-28'),
  (87, 20, 'cinemakai', 1, '2023-07-18'),
  (88, 20, 'zara_frames', 2, '2023-02-12'),
  (89, 20, 'popcorn_priya', 2, '2023-10-31'),
  (90, 21, 'reelrob', 5, '2019-05-26'),
  (91, 21, 'nightowl_nia', 5, '2019-10-03'),
  (92, 21, 'popcorn_priya', 4, '2019-04-18'),
  (93, 21, 'oscarbait', 5, '2019-06-13'),
  (94, 21, 'critic_cal', 4, '2019-03-27'),
  (95, 21, 'zara_frames', 4, '2019-07-28'),
  (96, 22, 'filmfinn', 3, '2024-04-09'),
  (97, 22, 'zara_frames', 3, '2024-05-09'),
  (98, 22, 'benwatches', 3, '2024-04-03'),
  (99, 22, 'hana_h', 3, '2024-05-30'),
  (100, 22, 'sofasam', 4, '2024-07-22'),
  (101, 22, 'popcorn_priya', 3, '2024-05-07'),
  (102, 23, 'nightowl_nia', 3, '2022-03-26'),
  (103, 23, 'devsees', 3, '2022-10-10'),
  (104, 23, 'moviemaya', 3, '2022-10-17'),
  (105, 24, 'tilly_tv', 4, '2019-09-16'),
  (106, 24, 'filmfinn', 4, '2019-07-30'),
  (107, 24, 'popcorn_priya', 4, '2019-03-08'),
  (108, 24, 'reelrob', 3, '2019-09-22'),
  (109, 25, 'filmfinn', 4, '2024-07-14'),
  (110, 25, 'popcorn_priya', 3, '2024-11-17'),
  (111, 25, 'jojo_reviews', 4, '2024-07-15'),
  (112, 26, 'filmfinn', 3, '2021-08-01'),
  (113, 26, 'critic_cal', 4, '2021-06-22'),
  (114, 26, 'sofasam', 3, '2021-04-02'),
  (115, 26, 'cinemakai', 2, '2021-05-03'),
  (116, 26, 'reelrob', 3, '2021-10-07'),
  (117, 26, 'lou_watches', 3, '2021-02-23'),
  (118, 26, 'moviemaya', 2, '2021-07-10'),
  (119, 27, 'moviemaya', 4, '2023-05-31'),
  (120, 27, 'reelrob', 3, '2023-03-19'),
  (121, 27, 'oscarbait', 3, '2023-10-21'),
  (122, 27, 'sofasam', 2, '2023-02-07'),
  (123, 27, 'nightowl_nia', 4, '2023-11-03'),
  (124, 27, 'benwatches', 3, '2023-07-23'),
  (125, 28, 'devsees', 4, '2022-06-08'),
  (126, 28, 'nightowl_nia', 3, '2022-02-17'),
  (127, 28, 'filmfinn', 4, '2022-08-13'),
  (128, 28, 'critic_cal', 4, '2022-07-21'),
  (129, 30, 'devsees', 3, '2020-03-10'),
  (130, 30, 'nightowl_nia', 4, '2020-05-19'),
  (131, 31, 'jojo_reviews', 4, '2019-02-28'),
  (132, 31, 'popcorn_priya', 5, '2019-11-20'),
  (133, 31, 'benwatches', 3, '2019-03-08'),
  (134, 31, 'hana_h', 3, '2019-09-25'),
  (135, 31, 'reelrob', 5, '2019-08-11'),
  (136, 31, 'filmfinn', 4, '2019-08-08'),
  (137, 33, 'zara_frames', 5, '2024-11-01'),
  (138, 33, 'lou_watches', 4, '2024-11-08'),
  (139, 33, 'critic_cal', 5, '2024-02-09'),
  (140, 34, 'critic_cal', 3, '2025-06-03'),
  (141, 34, 'filmfinn', 4, '2025-10-25'),
  (142, 34, 'benwatches', 3, '2025-06-23'),
  (143, 34, 'cinemakai', 4, '2025-05-04'),
  (144, 34, 'sofasam', 3, '2025-02-07'),
  (145, 34, 'popcorn_priya', 5, '2025-05-14'),
  (146, 34, 'moviemaya', 3, '2025-08-15');
example.sql to run
-- Each genre once, in alphabetical order
SELECT DISTINCT genre
FROM films
ORDER BY genre;

Practice

Every age rating once

Return each age_rating used on the site, once each.

exercise_1.sql to run
SELECT age_rating
FROM films;

Practice

Genres made in 2024

Return the genres of films released in 2024, each genre once, in alphabetical order.

exercise_2.sql to run
SELECT genre
FROM films;

PracticePredict the output

The same title twice

What does this query return? One row per line, values separated by |.

Read the program, type exactly what it prints, then lock it in. It runs after that.

exercise_3.sql
SELECT DISTINCT title, year
FROM films
WHERE title LIKE 'The L%'
ORDER BY title, year;

Break/fixFix the bug

Genres that keep repeating

A colleague wants each genre once, in alphabetical order. Their list still has repeats. Fix it.

exercise_4.sql to run
SELECT DISTINCT genre, year
FROM films
ORDER BY genre;

Your turn

Who reviewed in 2025

The site wants to thank everyone who wrote a review in 2025. Return each reviewer who did, once each, in alphabetical order. Reviews are in the reviews table, and reviewed_on is stored like 2025-04-17.

On your own. Hints open after your first check.

exercise_5.sql to run
-- Your query here
Step 1 of 8
SQLite