SQL exercises with solutions: SELECT, aliases and DISTINCT

Topics to use: SELECT, aliases, DISTINCT.

Query structure:

SELECT *
FROM …

SELECT *
FROM employees;

Expected result (4 rows):

Topics to use: SELECT, aliases, DISTINCT.

Query structure:

SELECT …, …
FROM …

SELECT name, salary
FROM employees;

Expected result (4 rows):

Topics to use: SELECT, aliases, DISTINCT.

Query structure:

SELECT … AS …, … AS …
FROM …

SELECT name AS employee, salary AS annual_salary
FROM employees;

Expected result (4 rows):

Topics to use: SELECT, aliases, DISTINCT.

Query structure:

SELECT DISTINCT …
FROM …

SELECT DISTINCT category
FROM products;

Expected result (3 rows):

Hint: The star * replaces the list of all the columns.

SELECT *
FROM movies;

Expected result (10 rows):

SELECT * is the quickest way to discover what a table contains.

Hint 1 · lead: List the columns you want right after SELECT.

Hint 2 · method: Separate the two columns with a comma: SELECT title, genre …

SELECT title, genre
FROM movies;

Expected result (10 rows):

Columns appear in the order you write them.

Hint 1 · lead: An alias goes right after the column it renames.

Hint 2 · method: Add AS film after title, and AS minutes after duration.

SELECT title AS film, duration AS minutes
FROM movies;

Expected result (10 rows):

An alias only changes the displayed name: the table itself is not modified.

Hint 1 · lead: A calculation can be written directly in the SELECT list, like a column.

Hint 2 · method: A computed column: price * 2 AS price_for_two.

SELECT name, price, price * 2 AS price_for_two
FROM dishes;

Expected result (8 rows):

The calculation is done again for every row of the table.

Hint 1 · lead: First work out the number of empty seats, then multiply it by the price. Mind the order of operations.

Hint 2 · method: Empty seats are capacity - seats_sold: put this calculation in parentheses before multiplying it by price.

SELECT airline,
       price * seats_sold AS revenue,
       price * (capacity - seats_sold) AS lost_revenue
FROM flights;

Expected result (12 rows):

Without parentheses, price * capacity - seats_sold computes (price × capacity) − seats_sold: multiplication comes before subtraction.