Back to articles list September 25, 2020 - 7 minutes read SQL Basics Cheat Sheet LearnSQL.com Team Tags: cheat sheet sql Download this 2-page SQL Basics Cheat Sheet in PDF or PNG format, print it out, and stick to your desk. The SQL Basics Cheat Sheet provides you with the syntax of all basics clauses, shows you how to write different conditions, and has examples. You can download this cheat sheet as follows: Download 2-page SQL Basics Cheat Sheet in PDF format (A4) Download 2-page SQL Basics Cheat Sheet in PDF format (Letter) Download 1-page SQL Basics Cheat Sheet in PDF format (A3) Download 1-page SQL Basics Cheat Sheet in PDF format (Ledger) You may also read the contents here: SQL Basics Cheat Sheet SQL 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. SAMPLE DATA 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; Fetch city names sorted by the rating column in the default ASCending order: SELECT name FROM city ORDER BY rating [ASC]; Fetch city names sorted by the rating column in the DESCending order: SELECT name FROM city ORDER BY rating DESC; 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; FILTERING THE OUTPUT COMPARISON OPERATORS Fetch names of cities that have a rating above 3: SELECT name FROM city WHERE rating > 3; Fetch names of cities that are neither Berlin nor Madrid: SELECT name FROM city WHERE name != 'Berlin' AND name != 'Madrid'; TEXT OPERATORS Fetch names of cities that start with a 'P' or end with an 's': SELECT name FROM city WHERE name LIKE 'P%' OR name LIKE '%s'; 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 LIKE '_ublin'; OTHER OPERATORS Fetch names of cities that have a population between 500K and 5M: SELECT name FROM city WHERE population BETWEEN 500000 AND 5000000; Fetch names of cities that don't miss a rating value: SELECT name FROM city WHERE rating IS NOT NULL; Fetch names of cities that are in countries with IDs 1, 4, 7, or 8: SELECT name FROM city WHERE country_id IN (1, 4, 7, 8); QUERYING MULTIPLE TABLES INNER JOIN JOIN (or explicitly INNER JOIN) returns rows that have matching values in both tables. SELECT city.name, country.name FROM city [INNER] JOIN country ON city.country_id = country.id; LEFT JOIN 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. SELECT city.name, country.name FROM city LEFT JOIN country ON city.country_id = country.id; RIGHT JOIN 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. SELECT city.name, country.name FROM city RIGHT JOIN country ON city.country_id = country.id; FULL JOIN 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. SELECT city.name, country.name FROM city FULL [OUTER] JOIN country ON city.country_id = country.id; CROSS JOIN CROSS JOIN returns all possible combinations of rows from both tables. There are two syntaxes available. SELECT city.name, country.name FROM city CROSS JOIN country; SELECT city.name, country.name FROM city, country; NATURAL JOIN NATURAL JOIN will join tables by all columns with the same name. SELECT city.name, country.name FROM city NATURAL JOIN country; NATURAL JOIN used these columns to match rows: city.id, city.name, country.id, country.name. NATURAL JOIN is very rarely used in practice. AGGREGATION AND GROUPING GROUP BY groups together rows that have the same values in specified columns. It computes summaries (aggregates) for each unique combination of values. AGGREGATE FUNCTIONS avg(expr) − average value for rows within the group count(expr) − count of values for rows within the group max(expr) − maximum value within the group min(expr) − minimum value within the group sum(expr) − sum of values within the group EXAMPLE QUERIES Find out the number of cities: SELECT COUNT(*) FROM city; Find out the number of cities with non-null ratings: SELECT COUNT(rating) FROM city; Find out the number of distinctive country values: SELECT COUNT(DISTINCT country_id) FROM city; Find out the smallest and the greatest country populations: SELECT MIN(population), MAX(population) FROM country; Find out the total population of cities in respective countries: SELECT country_id, SUM(population) FROM city GROUP BY country_id; Find out the average rating for cities in respective countries if the average is above 3.0: SELECT country_id, AVG(rating) FROM city GROUP BY country_id HAVING AVG(rating) > 3.0; SUBQUERIES A subquery is a query that is nested inside another query, or inside another subquery. There are different types of subqueries. SINGLE VALUE The simplest subquery returns exactly one column and exactly one row. It can be used with comparison operators =, <, <=, >, or >=. This query finds cities with the same rating as Paris: SELECT name FROM city WHERE rating = ( SELECT rating FROM city WHERE name = 'Paris' ); MULTIPLE VALUES A subquery can also return multiple columns or multiple rows. Such subqueries can be used with operators IN, EXISTS, ALL, or ANY. This query finds cities in countries that have a population above 20M: SELECT name FROM city WHERE country_id IN ( SELECT country_id FROM country WHERE population > 20000000 ); CORRELATED A correlated subquery refers to the tables introduced in the outer query. A correlated subquery depends on the outer query. It cannot be run independently from the outer query. This query finds cities with a population greater than the average population in the country: SELECT * FROM city main_city WHERE population > ( SELECT AVG(population) FROM city average_city WHERE average_city.country_id = main_city.country_id ); This query finds countries that have at least one city: SELECT name FROM country WHERE EXISTS ( SELECT * FROM city WHERE country_id = country.id ); SET OPERATIONS Set operations are used to combine the results of two or more queries into a single result. The combined queries must return the same number of columns and compatible data types. The names of the corresponding columns can be different UNION UNION combines the results of two result sets and removes duplicates. UNION ALL doesn't remove duplicate rows. This query displays German cyclists together with German skaters: SELECT name FROM cycling WHERE country = 'DE' UNION / UNION ALL SELECT name FROM skating WHERE country = 'DE'; INTERSECT INTERSECT returns only rows that appear in both result sets. This query displays German cyclists who are also German skaters at the same time: SELECT name FROM cycling WHERE country = 'DE' INTERSECT SELECT name FROM skating WHERE country = 'DE'; EXCEPT EXCEPT returns only the rows that appear in the first result set but do not appear in the second result set. This query displays German cyclists unless they are also German skaters at the same time: SELECT name FROM cycling WHERE country = 'DE' EXCEPT / MINUS SELECT name FROM skating WHERE country = 'DE'; Try out the interactive SQL Basics course at LearnSQL.com, and check out our other SQL courses. Tags: cheat sheet sql You may also like SQL JOIN Cheat Sheet This 2-page SQL JOIN Cheat Sheet covers the syntax of different JOINs (even the rare ones!) Download it in PDF or PNG format. Read more SQL Window Functions Cheat Sheet This 2-page SQL Window Functions Cheat Sheet covers the syntax of window functions and a list of window functions. Download it in PDF or PNG format. Read more What Is Vertabelo’s SQL Cheat Sheet? Rock the SQL! You don’t have to be a programmer to master SQL. Download the SQL Cheat Sheet and find quick answers for the common problems with SQL queries. Read more Can you Join two Tables Without a Common Column? Do you need to combine two tables without a common column? Learn how the CROSS JOIN and the UNION operators can help you with this task. Read more What's the Difference Between Having Multiple Tables in FROM and Using JOIN? What’s your approach to SQL JOINs? Do you use JOIN or simply list tables in FROM? Learn why SQL practitioners prefer the new syntax with the JOIN keyword. Read more Practical Examples of When to Use Non-Equi JOINs in SQL Do you only use equals in your JOIN conditions? Learn how non-equi JOINs can assist with listing duplicates and joining tables based on a range of values. Read more How to Learn SQL JOINs Learn how to use SQL JOINs to effectively combine data across multiple tables and analyze sophisticated data sets. Read more How to Join 3 Tables (or More) in SQL Have you ever wondered how to join three tables in SQL? It's easy when you know the basics. Joining three tables can be as easy as joining two tables. Read more Subquery vs. JOIN What are the differences between a subquery and a JOIN in SQL? How do you choose one over the other? See examples of when to use which. Read more Subscribe to our newsletter Join our weekly newsletter to be notified about the latest posts.