Learn/SQL/1. SELECT basics
Lesson 1 of 8
Sign in

SQL, lesson 1

SELECT basics

SQL is the language you use to talk to a database. A database stores data in tables: each table has named columns (like title or price) and holds any number of rows, one per record. In this course you use SQLite, a small database engine that runs your whole file as a script, top to bottom.

The statement you will write most is SELECT. It asks for data and prints the answer.

Every statement ends with a semicolon. SQL keywords are not case-sensitive (select works too), but writing them in capitals makes queries easier to read.

Reading from a table

Name the columns you want after SELECT, and the table after FROM:

Use * to get every column. It is handy while exploring, but naming the columns you need is clearer and keeps your output stable if someone adds a column later.

2 exercises to try what you just read.

Exercise 1

The reading list

Press Ctrl Enter or Check to test your code.

Exercise 2

Café prices with tax

Press Ctrl Enter or Check to test your code.

How results are printed

SQLite prints one line per row. Values in a row are separated by a vertical bar |, and no column names are printed. A missing value (NULL) shows as nothing at all.

Expressions and aliases

A column in a SELECT can be any expression: arithmetic, text, or a function call. AS gives the result a name, which matters later when you sort or reuse it.

Watch out for one SQLite rule: dividing two whole numbers gives a whole number. 7 / 2 is 3, but 7.0 / 2 is 3.5. Text values go in single quotes: 'Dune'.

Comments

Two dashes start a comment that runs to the end of the line. SQLite ignores it.

Your exercises

In each exercise the tables are already created and filled for you. You only write the query. The prompt shows what the table holds so you know which columns exist.

Further reading

  • SQLite: SELECT
  • SQLite: SQL expressions
  • SQLite: core functions such as round()

The table books has the columns title, author, year and pages:

Write a query that prints only the title and author of every book, in the order they are stored. Expected output:

The table menu lists drinks and their prices before tax:

Print each item with its price including 20% tax, rounded to 2 decimal places. Multiply by 1.2 and use round(value, 2). Expected output:

SELECT 'Hello, World!';
SELECT 6 * 7;
SELECT title, author FROM books;
SELECT * FROM books;
Dune|Frank Herbert
Emma|Jane Austen
SELECT title, pages / 30 AS days_to_read FROM books;
SELECT 'Book: ' || title FROM books;   -- || joins two pieces of text
SELECT round(9.99 * 1.2, 2);           -- 11.99
-- List every book
SELECT title FROM books;  -- a comment can follow code too
title                 | author          | year | pages
The Hobbit            | J.R.R. Tolkien  | 1937 | 310
Dune                  | Frank Herbert   | 1965 | 412
Matilda               | Roald Dahl      | 1988 | 240
The Martian           | Andy Weir       | 2011 | 369
The Hobbit|J.R.R. Tolkien
Dune|Frank Herbert
Matilda|Roald Dahl
The Martian|Andy Weir
item         | price
Espresso     | 2.5
Latte        | 3.8
Hot chocolate| 3.2
Tea          | 1.9
Espresso|3.0
Latte|4.56
Hot chocolate|3.84
Tea|2.28