CHEETS OF SQL

SQL Basics Cheat Sheet
SQL FILTERING THE OUTPUT QUERYING MULTIPLE TABLES
SQL, or Structured Query Language, is a language to talk to COMPARISON OPERATORS INNER JOIN FULL JOIN
databases. It allows you to select specific data and to build
Fetch names of cities that have a rating above 3: JOIN (or explicitly INNER JOIN) returns rows that have FULL JOIN (or explicitly FULL OUTER JOIN) returns all rows
complex reports. Today, SQL is a universal language of data. It is
matching values in both tables. from both tables – if there's no matching row in the second
used in practically all technologies that process data. SELECT name table, NULL s are returned.
FROM city
SELECT city.name, country.name
SAMPLE DATA WHERE rating > 3; SELECT city.name, country.name
FROM city FROM city
COUNTRY [INNER] JOIN country FULL [OUTER] JOIN country
id name population area ON city.country_id = country.id; ON city.country_id = country.id;
1 France 66600000 640680 Fetch names of cities that are neither Berlin nor Madrid:
2 Germany 80700000 357000 CITY COUNTRY
SELECT name
... ... ... ... CITY COUNTRY id name country_id id name
FROM city id name country_id id name 1 Paris 1 1 France
CITY WHERE name != 'Berlin' 1 Paris 1 1 France 2 Berlin 2 2 Germany
id name country_id population rating AND name != 'Madrid'; 2 Berlin 2 2 Germany 3 Warsaw 4 NULL NULL
1 Paris 1 2243000 5 3 Warsaw 4 3 Iceland NULL NULL NULL 3 Iceland
2 Berlin 2 3460000 3
... ... ... ... ...
TEXT OPERATORS
Fetch names of cities that start with a 'P' or end with an 's':
QUERYING SINGLE TABLE
SELECT name LEFT JOIN CROSS JOIN
Fetch all columns from the country table:
FROM city LEFT JOIN returns all rows from the left table with CROSS JOIN returns all possible combinations of rows from
SELECT * WHERE name LIKE 'P%' corresponding rows from the right table. If there's no both tables. There are two syntaxes available.
FROM country; OR name LIKE '%s'; matching row, NULL s are returned as values from the second SELECT city.name, country.name
table. FROM city
Fetch id and name columns from the city table: CROSS JOIN country;
SELECT city.name, country.name
SELECT id, name Fetch names of cities that start with any letter followed by FROM city
FROM city; 'ublin' (like Dublin in Ireland or Lublin in Poland): SELECT city.name, country.name
LEFT JOIN country FROM city, country;
Fetch city names sorted by the rating column SELECT name ON city.country_id = country.id; CITY COUNTRY
in the default ASCending order: FROM city CITY COUNTRY id name country_id id name
WHERE name LIKE '_ublin'; id name country_id id name 1 Paris 1 1 France
SELECT name 1 Paris 1 1 France 1 Paris 1 2 Germany
FROM city 2 Berlin 2 2 Germany 2 Berlin 2 1 France
ORDER BY rating [ASC]; 3 Warsaw 4 NULL NULL 2 Berlin 2 2 Germany
OTHER OPERATORS
Fetch city names sorted by the rating column Fetch names of cities that have a population between
in the DESCending order: 500K and 5M:
SELECT name SELECT name
FROM city FROM city RIGHT JOIN NATURAL JOIN
ORDER BY rating DESC; WHERE population BETWEEN 500000 AND 5000000; NATURAL JOIN will join tables by all columns with the same
RIGHT JOIN returns all rows from the right table with
name.
corresponding rows from the left table. If there's no
ALIASES Fetch names of cities that don't miss a rating value:
matching row, NULL s are returned as values from the left
table.
SELECT city.name, country.name
FROM city
COLUMNS SELECT name NATURAL JOIN country;
SELECT city.name, country.name
SELECT name AS city_name FROM city CITY COUNTRY
FROM city
FROM city; WHERE rating IS NOT NULL; country_id id name name id
RIGHT JOIN country
6 6 San Marino San Marino 6
ON city.country_id = country.id;
TABLES 7 7 Vatican City Vatican City 7
5 9 Greece Greece 9
SELECT co.name, ci.name Fetch names of cities that are in countries with IDs 1, 4, 7, or 8: CITY COUNTRY
10 11 Monaco Monaco 10
id name country_id id name
FROM city AS ci SELECT name NATURAL JOIN used these columns to match rows:
1 Paris 1 1 France
JOIN country AS co FROM city 2 Berlin 2 2 Germany city.id, city.name, country.id, country.name
ON ci.country_id = co.id; WHERE country_id IN (1, 4, 7, 8); NULL NULL NULL 3 Iceland NATURAL JOIN is very rarely used in practice.




LearnSQL.com is owned by Vertabelo SA
Try out the interactive SQL Basics course at LearnSQL.com, and check out our other SQL courses. vertabelo.com | CC BY-NC-ND Vertabelo SA