Session 4 · Querying data with SELECT#

The big idea#

SELECT is how you ask the database a question. You say which columns you want and which table to read them from. You can rename columns, calculate new ones, sort the result and remove repeats. The table itself is never changed: SELECT only reads.

Everyday example: a railway enquiry counter. You do not carry the whole timetable home. You ask “trains from Chennai, show name and departure time, earliest first,” and the clerk reads out just that. SELECT name, departure FROM trains ORDER BY departure is the same request in SQL.

The order a query is read in You write SELECT first, but the database works FROM table, then WHERE, then picks columns, then ORDER BY and LIMIT. FROM which table WHERE which rows SELECT which columns ORDER BY what order LIMIT how many SELECT title, year FROM movies WHERE genre = 'Action' ORDER BY year LIMIT 3;
You write SELECT first, but the database works FROM table, then WHERE, then picks columns, then ORDER BY and LIMIT.

The practice queries#

Each query below was run against the movie database from code/00-setup.sql. The \echo lines just print a label so you can match each result to its query.

\echo '-- one column'
SELECT title FROM movies;
\echo '-- several columns, with aliases'
SELECT title AS film, year AS released FROM movies;
\echo '-- computed column'
SELECT title, year, 2026 - year AS age_in_years FROM movies;
\echo '-- ORDER BY, newest first, then title'
SELECT title, year FROM movies ORDER BY year DESC, title ASC;
\echo '-- DISTINCT removes repeats'
SELECT DISTINCT genre FROM movies ORDER BY genre;
\echo '-- DISTINCT on two columns'
SELECT DISTINCT genre, year FROM movies WHERE year = 1995 ORDER BY genre;

Real output#

-- one column
       title       
-------------------
 Nayakan
 Thalapathi
 Baasha
 Muthu
 Sivaji
 Pariyerum Perumal
 Kaithi
 Soorarai Pottru
 Karnan
 Vikram
 Untitled Project
(11 rows)

-- several columns, with aliases
       film        | released 
-------------------+----------
 Nayakan           |     1987
 Thalapathi        |     1991
 Baasha            |     1995
 Muthu             |     1995
 Sivaji            |     2007
 Pariyerum Perumal |     2018
 Kaithi            |     2019
 Soorarai Pottru   |     2020
 Karnan            |     2021
 Vikram            |     2022
 Untitled Project  |     2026
(11 rows)

-- computed column
       title       | year | age_in_years 
-------------------+------+--------------
 Nayakan           | 1987 |           39
 Thalapathi        | 1991 |           35
 Baasha            | 1995 |           31
 Muthu             | 1995 |           31
 Sivaji            | 2007 |           19
 Pariyerum Perumal | 2018 |            8
 Kaithi            | 2019 |            7
 Soorarai Pottru   | 2020 |            6
 Karnan            | 2021 |            5
 Vikram            | 2022 |            4
 Untitled Project  | 2026 |            0
(11 rows)

-- ORDER BY, newest first, then title
       title       | year 
-------------------+------
 Untitled Project  | 2026
 Vikram            | 2022
 Karnan            | 2021
 Soorarai Pottru   | 2020
 Kaithi            | 2019
 Pariyerum Perumal | 2018
 Sivaji            | 2007
 Baasha            | 1995
 Muthu             | 1995
 Thalapathi        | 1991
 Nayakan           | 1987
(11 rows)

-- DISTINCT removes repeats
 genre  
--------
 Action
 Comedy
 Crime
 Drama
 
(5 rows)

-- DISTINCT on two columns
 genre  | year 
--------+------
 Action | 1995
 Comedy | 1995
(2 rows)

What each part did#

  • SELECT title FROM movies returns one column from every row. SELECT * returns all columns; it is handy in a console but in application code list the columns you need, so a new column added later cannot break your program.

  • AS gives a column a nicer name in the result (film, released). The stored column is not renamed.

  • Calculated columns: 2026 - year AS age_in_years is computed for each row on the fly.

  • ORDER BY year DESC, title ASC sorts by year first, then breaks ties by title. Without ORDER BY the order of rows is not guaranteed.

  • SELECT DISTINCT genre shows each genre once. DISTINCT on two columns removes duplicate pairs; the last query shows Comedy and Action for 1995, each once.

Read the diagram above as the order the database thinks in: table first, then rows, then columns, then sorting. That is why an alias defined in SELECT can be used in ORDER BY but not in WHERE.

Common mistakes#

  • Forgetting the semicolon in psql. The prompt changes to course-# and waits for more. Type ; and Enter.

  • Trusting row order without ORDER BY. It looks sorted on your laptop and changes in production.

  • Using SELECT * in application code. You pull unneeded data, and your code breaks when the table changes.

  • Single versus double quotes. 'Action' is a text value; "Action" is an identifier (a column name). Mixing them gives “column does not exist”.

  • Thinking DISTINCT applies to one column. It applies to the whole row you selected.

Hands-on exercises#

Exercise 1. Show every movie’s title and rating, best rated first. What happens to the movie that has no rating?

Solution 
SELECT title, rating FROM movies ORDER BY rating DESC;

In PostgreSQL NULL sorts first in a descending sort, so “Untitled Project” appears at the top, then Nayakan (9.0). Use ORDER BY rating DESC NULLS LAST to push it to the bottom.

Exercise 2. List the different release years once each, oldest first.

Solution 
SELECT DISTINCT year FROM movies ORDER BY year;

1995 appears once even though two movies came out that year.

Exercise 3. Show title as film and a new column decade that is the year rounded down to the decade (1987 gives 1980).

Solution 
SELECT title AS film, (year / 10) * 10 AS decade FROM movies ORDER BY decade, film;

Both numbers are integers, so year / 10 drops the fraction (198.7 becomes 198), and * 10 brings it back to 1980.

Practice#

Take the Session 4 quiz (SELECT, aliases, ORDER BY, DISTINCT). The original DB-TASK-001 on the DVD Rental database is in the reference section of the course.