Lesson 1 of 12 · 0 of 2 exercises solved

Tables & SELECT

A relational stores data in tables made of rows and columns. SELECT is how you read from them, and it is the statement you will use most.

Everything in revolves around tables. A table has named columns (each with a type) and any number of rows. SELECT reads rows out of a table. You choose which columns you want, and optionally filter which rows come back.

-- A table called 'students' might look like:
-- id | name    | age | grade
-- 1  | Ada     | 17  | 95
-- 2  | Linus   | 16  | 88
-- 3  | Grace   | 17  | 92

-- Get every column of every row:
SELECT * FROM students;

-- Get only some columns:
SELECT name, grade FROM students;

-- Rename a column in the output with AS:
SELECT name, grade AS score FROM students;

-- Filter rows with WHERE:
SELECT name FROM students WHERE age = 17;

-- Comparison operators: =, <>, <, >, <=, >=
SELECT name FROM students WHERE grade >= 90;

-- Combine conditions with AND / OR / NOT:
SELECT name FROM students
WHERE age = 17 AND grade > 90;

-- Pattern matching with LIKE (% = any characters):
SELECT name FROM students WHERE name LIKE 'A%';

Common question

Are the rows in a table stored in order?

A relational table is an unordered collection of rows, each with the same named, typed columns. SELECT says which columns you want and WHERE says which rows; the database decides how to find them. That declarative style is the point of SQL: you describe the result and the engine plans the work.

Say exactly what you need. Name columns instead of using *, filter with WHERE instead of in code, and add ORDER BY whenever order matters, because without it the order is an accident that will change one day.

Source: Codd's relational model defines a relation as a of , and sets have no order.

Common mix-ups

Each of these sounds reasonable. Think about why it is wrong, then open the explanation.

Rows come back in the order they were inserted, so SELECT returns them in that order.
A query returns the whole table, and filtering happens afterwards in the application.
SELECT * is fine in production code because it saves listing columns.

Practice

Write a query that returns the name and grade of every student who is 17 years old.

Exercise

Filter by age

3 checks

SELECT * FROM students;

Output

Press Run to try your code, then Check answer to grade it.

Return the names of students whose name starts with A or whose grade is 90 or higher.

Exercise

LIKE and OR

3 checks

SELECT name FROM students WHERE ...;

Output

Press Run to try your code, then Check answer to grade it.

Check yourself

Quick check

Which of these is a valid string literal in standard SQL?

Finished reading?2 exercises still open. You can come back to them later.

Tip: use the ← and → keys to move between lessons.