SELECT

The smallest query

select says which columns come back. from says where the rows come from. Everything else in this course is a clause added to those two.

two clauses, and one of them is optional

select * from doors;          -- every column of every row
select name from doors;       -- one column of every row
select 2 + 2;                 -- no table at all: 4

Never ship select *

select * is for looking around. In an application it is a slow way to fetch data you do not use, and a silent bug waiting for the day someone adds a column: your code suddenly receives a field it has never seen, and the row got twice as big on the wire.

Name the columns. The query then says what the code needs, and a reader can tell at a glance.

the list is the documentation

select name, style, price
from   doors;

Aliases: as

renaming a column for the result

select name        as door,
       lead_days   as days_to_make,
       price * 1.18 as with_vat
from   doors;

-- as is optional, but leaving it out reads like a typo
select name door from doors;

-- a name with a space or a capital needs double quotes
select price as "Price, incl. VAT" from doors;

Double quotes are not string quotes

In SQL, 'text' is a string and "text" is an identifier — a column or table name. select 'name' from doors returns the word "name" ten times, once per row. MySQL is loose about this; everything else is not.

Columns you compute

A select expression is worked out once per row. case is SQL's if: a list of conditions, the first true one wins, else catches the rest.

arithmetic, text, and a condition, per row

select name,
       price,
       price * 1.18            as with_vat,
       round(price * 1.18, 2)  as tidy,
       name || ' (' || style || ')'  as label,        -- || joins text
       upper(material)         as material,
       case when price > 2000 then 'premium' else 'standard' end as tier
from   doors;

Dialect: joining text

|| is the standard and works in SQLite, PostgreSQL and Oracle. MySQL uses concat(a, b) — and reads || as "or" unless it is told otherwise.

distinct

distinct applies to the whole row that comes back, not to one column — which is why the second query returns more rows than the first, not the same rows with an extra column.

one row per distinct combination of the selected columns

select distinct style from doors;              -- 4 rows
select distinct style, material from doors;    -- every pair that exists

Comments, case and layout

Keywords are conventionally written one way and kept consistent — lower case reads better in an application file, upper case in a migration; pick one. Table and column names are case-insensitive in most databases, but a quoted identifier is not, which is a good reason never to quote them.

style that survives a code review

-- a line comment
/* a block comment,
   which is useful for switching a clause off */

select   name,          -- keywords in one case, your names in another
         price
from     doors;         -- keyword case is free; SQL is not case sensitive here