SQL Basics Cheat Sheet, Cheat Sheet of Database Management Systems (DBMS)

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