Level 1: Reading a table with SELECT

Lesson

A database stores its information in tables. A table looks like a spreadsheet: each row is an item (a movie, a customer, a sale) and each column is a piece of information about that item (its title, its year, its duration). All the rows of a table have the same columns.

To read a table, you write a query that starts with SELECT. Right after SELECT comes the list of columns you want, separated by commas, in the order you want to see them. Then FROM gives the name of the table. SELECT title, year FROM movies shows the title and the year of each movie. The star * means “all the columns”: SELECT * FROM movies is handy to discover a table.

A SELECT query never changes the table: it builds a result, a new table shown on screen. So you can experiment without any risk.

You can rename a column of the result with AS: this is an alias. title AS film shows the column under the name film. The alias only changes the display, not the table.

You can also calculate a column with + - * /: SQL does the calculation for each row, one by one. price * 2 AS price_for_two gives the price for two people on every row. As in maths, * and / come before + and -: add parentheses to force your own order of calculation.

Watch out for division: dividing two whole numbers gives a whole number, 95 / 60 equals 1. Write 60.0 to keep the decimals.

The final semicolon marks the end of the query. Keywords such as SELECT and FROM can be written in lowercase, but they are often written in uppercase to tell them apart from column names.

Syntax

SELECT column1, column2 AS new_name, column3 * 2 AS double
FROM my_table;

Worked example

SELECT title, year, duration / 60.0 AS hours
FROM movies;

We read three pieces of information about each movie. The third one is computed: the duration in minutes divided by 60.0 gives hours, shown under the name hours. SQL shows every decimal; you will learn to round with ROUND in level 5.

Example result

Key points

• SELECT lists the columns, FROM names the table.
• * shows every column, in the table's order.
• AS gives a name to a result column, especially useful for computed columns.

Common pitfalls

• Forgetting the comma between two columns: SELECT title year reads title and renames it “year”!
• Dividing two whole numbers gives a whole number: 95 / 60 is 1. Write 60.0 to keep the decimals.
• price * capacity - seats_sold is not price * (capacity - seats_sold): multiplication comes before subtraction.

The level’s 5 exercises

1. Guided · Show every column of the movies table. (Table: movies)
2. Practice · Show the title and genre of each movie. (Table: movies)
3. Practice · Show the title of each movie in a column named film, and its duration in a column named minutes. (Table: movies)
4. Practice · Show the name and price of each dish, plus the price for two people in a column named price_for_two. (Table: dishes)
5. Challenge · For each flight, show the airline, the flight's revenue (revenue: price × seats sold) and the revenue lost to empty seats (lost_revenue: price × empty seats). (Table: flights)

In SpeedQL, every query is checked straight away: its result is compared with the expected one, then the query is run again on a hidden control database. Each exercise has written hints, to show only if you get stuck.

See also: the cheat sheet card · the SQL exercises with solutions on this topic