SQL Basics Cheat Sheet
SQL, or Structured Query Language, is a lang uage to talk to
databases . It allows you to select specific dat a and to build
complex repo rts. Today, SQL is a unive rsal language of d ata. It is
used in prac tically all tech nologies that pro cess data.
SQL
SAMPLE DATA
CITY
id name co un tr y_id population rating
1Paris 12243000 5
2Be rlin 23460000 3
... ... ... ... ...
COUNTRY
id name population area
1Fran ce 66600000 640680
2Germany 80700000 357000
... ... ... ...
ALIASES
COLUMNS
SELECT na me AS city_name
FROM cit y;
TABL ES
SELECT c o.nam e, c i.nam e
FROM cit y AS ci
JOIN country A S co
ON ci.country_id = co.id;
QUERYING MULTIPLE TABLES
INNER JOIN
SELECT c it y. na m e, c ou n tr y.n a m e
FROM cit y
[INNER] JOIN country
ON city.country_id = country.id;
CITY
id name co un tr y_id
1Paris 1
2Be rlin 2
3Warsaw 4
COUNTRY
id name
1Fran ce
2Germany
3Icela nd
JOIN (or explicitly INNER JOIN) returns rows t hat have
matching value s in both tables.
LEFT JOIN
SELECT c it y. na m e, c ou n tr y.n a m e
FROM cit y
LEFT JOIN co untry
ON city.country_id = country.id;
CITY
id name co un tr y_id
1Paris 1
2Be rlin 2
3Warsaw 4
COUNTRY
id name
1Fran ce
2Germany
NULL NULL
LEFT JOIN retur ns all rows from the left t able with
correspon ding rows from the rig ht table. If there's no
matching row, NULLs are retur ned as values from t he second
table.
RIGHT JOIN
SELECT c it y. na m e, c ou n tr y.n a m e
FROM cit y
RIGHT J OIN countr y
ON city.country_id = country.id;
CITY
id name co un tr y_id
1Paris 1
2Be rlin 2
NULL NULL NULL
COUNTRY
id name
1Fran ce
2Germany
3Icela nd
RIGHT JOIN r eturns all rows from t he right table with
correspon ding rows from the left t able. If there's no
matching row, NULLs are retur ned as values from t he left
table.
FULL JOIN
SELECT c it y. na m e, c ou n tr y.n a m e
FROM cit y
FULL [OUTER] JOIN country
ON city.country_id = country.id;
CITY
id name co un tr y_id
1Paris 1
2Be rlin 2
3Warsaw 4
NULL NULL NULL
COUNTRY
id name
1Fran ce
2Germany
NULL NULL
3Icela nd
FULL JOIN (or explicitly FULL OUTER JOIN) re turns all rows
from both tab les – if there's no matchin g row in the second
table, NULL s are returned.
CITY
co un try _id id name
6 6 San Ma rino
7 7 Vatica n City
5 9 Greece
10 11 Monaco
COUNTRY
name id
San Ma rino 6
Vatica n City 7
Greece 9
Monaco 10
NATURAL JOIN
SELECT c it y. na m e, c ou n tr y.n a m e
FROM cit y
NATU RAL JOIN c ou ntr y;
NATUR AL JOI N will join table s by all columns with the s ame
name.
NATUR AL JOIN u sed these columns to m atch rows:
cit y.id, cit y.na m e, c ou ntr y.id, co un tr y.na me
NATUR AL JOIN i s very rarely us ed in practice.
CROSS JOIN
SELECT c it y. na m e, c ou n tr y.n a m e
FROM cit y
CROSS JOIN co unt ry;
SELECT c it y. na m e, c ou n tr y.n a m e
FROM city, country;
CROSS JOIN returns al l possible combinati ons of rows from
both tables . There are two sy ntaxes available.
CITY
id name co un tr y_id
1Paris 1
1Paris 1
2Be rlin 2
2Be rlin 2
COUNTRY
id name
1Fran ce
2Germany
1Fran ce
2Germany
QUERYING SINGLE TABLE
Fetch all columns f rom the coun try table:
SELECT *
FROM co un try;
Fetch id and nam e columns from the c ity table:
SELECT i d, na me
FROM cit y;
SELECT nam e
FROM cit y
ORDER BY r ating DESC;
Fetch city na mes sorted by t he ratin g column
in the DESCending order:
SELECT nam e
FROM cit y
ORDER BY r ating [A S C];
Fetch city na mes sorted by t he ratin g column
in the default A SCending order:
SELECT nam e
FROM cit y
WHERE nam e LIKE '_ u bl in ';
Fetch names of ci ties that star t with any lett er followed by
'ublin' (like Dublin in I reland or Lublin in Pol and):
SELECT nam e
FROM cit y
WHERE nam e != 'Berlin'
AND name != 'Madrid';
Fetch names of ci ties that are neit her Berlin nor Ma drid:
SELECT nam e
FROM cit y
WHERE ra ting IS NOT NULL;
Fetch names of ci ties that don't mis s a rating value:
SELECT nam e
FROM cit y
WHERE country_id IN (1, 4, 7, 8);
Fetch names of ci ties that are in coun tries with IDs 1, 4, 7, or 8:
FILTERING THE OUTPUT
SELECT nam e
FROM cit y
WHERE rating > 3;
Fetch names of ci ties that have a rat ing above 3:
COMPARISON OPERATORS
SELECT nam e
FROM cit y
WHERE nam e LIKE 'P %'
OR name LIKE '% s';
Fetch names of ci ties that star t with a 'P' or end w ith an 's':
TEXT OPERATORS
SELECT nam e
FROM cit y
WHERE population BETWEEN 500000 AND 5000000;
Fetch names of ci ties that have a popula tion between
500K and 5M :
OTHER OPERATORS
