SELECT and column aliases

You rarely ask a librarian to “bring me every book”. You ask for something specific: “just the titles and authors, please”. Databases work the same way — we seldom need a whole table. The SELECT statement lets you say exactly which data you want, and even compute new values that are not stored anywhere.

SELECT and FROM

The simplest query has two parts: after SELECT we list the columns we need, separated by commas, and after FROM we write the table name. The result is a table too — with only the columns we chose, in the order we chose. The final ORDER BY id keeps the rows sorted by student number.

SELECT first_name, last_name, city
FROM students
ORDER BY id;

first_name | last_name | city
Aysel | Məmmədova | Bakı
Murad | Əliyev | Gəncə
Leyla | Hüseynova | Bakı
Elvin | Quliyev | Sumqayıt
Nigar | Rzayeva | Şəki
Rəşad | Kərimov | Bakı
Günay | Abbasova | Lənkəran
Tural | İsmayılov | Gəncə
Səbinə | Nəsirova | Quba
Kamran | Həsənov | Bakı
Fidan | Cəfərova | Naxçıvan
Orxan | Babayev | Sumqayıt

Calculations in SELECT

SELECT can contain not only column names but also expressions: +, -, *, / and % for the remainder. For example, to find the total value of each product in stock we multiply the price by the stock. The new column exists only in the result — the table itself does not change.

SELECT name, price, stock,
       price * stock AS stock_value
FROM products
ORDER BY id;

name | price | stock | stock_value
Laptop | 1450 | 8 | 11600
Smartphone | 899.99 | 15 | 13499.85
Headphones | 120.5 | 40 | 4820
Notebook | 3.2 | 500 | 1600
Pen set | 7.9 | 300 | 2370
Backpack | 55 | 60 | 3300
Desk lamp | 34.99 | 25 | 874.75
Water bottle | 12 | 120 | 1440
Monitor | 310 | 0 | 0
Chess set | 42 | 18 | 756

Prices are often decimals. The ROUND(x, 2) function rounds a number to two decimal places. Let's calculate each price including an 18% tax by multiplying it by 1.18.

SELECT name,
       price,
       ROUND(price * 1.18, 2) AS price_with_tax  -- price + 18%
FROM products
ORDER BY id;

name | price | price_with_tax
Laptop | 1450 | 1711
Smartphone | 899.99 | 1061.99
Headphones | 120.5 | 142.19
Notebook | 3.2 | 3.78
Pen set | 7.9 | 9.32
Backpack | 55 | 64.9
Desk lamp | 34.99 | 41.29
Water bottle | 12 | 14.16
Monitor | 310 | 365.8
Chess set | 42 | 49.56

SELECT 7 / 2   AS int_div,
       7 / 2.0 AS real_div,
       7 % 2   AS remainder;

int_div | real_div | remainder
3 | 3.5 | 1

Aliases: AS

A calculated column has no proper name — in the result it shows up as something clumsy like price * stock. An alias gives a column in the result a new name: expression AS name. The word AS is optional (price * stock stock_value also works), but writing it makes the query easier to read. An alias exists only in the result; the column in the table keeps its name.

To join pieces of text, SQLite uses the || operator. Below we glue the first name, a space and the last name into one column and call it full_name.

SELECT first_name || ' ' || last_name AS full_name,
       age
FROM students
ORDER BY id;

full_name | age
Aysel Məmmədova | 15
Murad Əliyev | 16
Leyla Hüseynova | 14
Elvin Quliyev | 17
Nigar Rzayeva | 15
Rəşad Kərimov | 16
Günay Abbasova | 14
Tural İsmayılov | 17
Səbinə Nəsirova | 16
Kamran Həsənov | 15
Fidan Cəfərova | 17
Orxan Babayev | 14

If an alias contains spaces, put it in double quotes: AS "Course title". Remember: in SQL, double quotes are for names (columns, tables, aliases), while text values always go in single quotes: 'Bakı'.

SELECT title   AS "Course title",
       teacher AS "Teacher"
FROM courses
ORDER BY id;

Course title | Teacher
Algebra | Ramin Səfərov
Geometry | Ramin Səfərov
Mechanics | Ülviyyə Axundova
Organic Chemistry | Samir Hacıyev
World History | Nərmin Vəliyeva
Python Basics | Elnur Qasımov
English B1 | Sara Mitchell

Removing duplicates: DISTINCT

Which cities do our students come from? SELECT city would return 12 rows, with Baku repeated four times. The word DISTINCT removes identical rows from the result and keeps each value once.

SELECT DISTINCT city
FROM students
ORDER BY city;

city
Bakı
Gəncə
Lənkəran
Naxçıvan
Quba
Sumqayıt
Şəki

DISTINCT applies to the whole row: with several columns we get unique combinations. Let's look at the customers' countries and cities: two customers live in Baku, but the pair Azerbaijan | Bakı appears only once.

SELECT DISTINCT country, city
FROM customers
ORDER BY country, city;

country | city
Azerbaijan | Bakı
Azerbaijan | Gəncə
Russia | Moscow
Türkiye | Ankara
Türkiye | İstanbul
United Kingdom | London

SELECT name
       -- add the discounted price here
FROM products
ORDER BY id;

name | sale_price
Laptop | 1305
Smartphone | 809.99
Headphones | 108.45
Notebook | 2.88
Pen set | 7.11
Backpack | 49.5
Desk lamp | 31.49
Water bottle | 10.8
Monitor | 279
Chess set | 37.8

SELECT subject
FROM courses
ORDER BY subject;

subject
Chemistry
English
History
Informatics
Math
Physics