SQL Basics Cheat Sheet (2024)

• SQL
• Sample Data
• Querying Single Table
• Aliases
• Columns
• Tables
• Filtering The Output
• Comparison Operators
• Text Operators
• Other Operators
• Querying Multiple Tables
• INNER JOIN
• LEFT JOIN
• RIGHT JOIN
• FULL JOIN
• CROSS JOIN
• NATURAL JOIN
• Aggregation and Grouping
• Aggregate Functions
• Example Queries
• Subqueries
• Single Value
• Multiple Values
• Correlated
• Set Operations
• UNION
• INTERSECT
• EXCEPT

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, nameFROM city;

Fetch city names sorted by the rating column in the default ASCending order:

SELECT nameFROM cityORDER BY rating [ASC];

Fetch city names sorted by the rating column in the DESCending order:

SELECT nameFROM cityORDER BY rating DESC;

Learn SQL by actually writing SQL code. Complete 129 interactive exercises in our SQL Basics course and gain confidence in your coding skills.

Aliases

Columns

SELECT name AS city_nameFROM city;

Tables

SELECT co.name, ci.nameFROM city AS ciJOIN 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 nameFROM cityWHERE rating > 3;

Fetch names of cities that are neither Berlin nor Madrid:

SELECT nameFROM cityWHERE name != 'Berlin' AND name != 'Madrid';

TEXT OPERATORS

Fetch names of cities that start with a 'P' or end with an 's':

SELECT nameFROM cityWHERE 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 nameFROM cityWHERE name LIKE '_ublin';

OTHER OPERATORS

Fetch names of cities that have a population between 500K and 5M:

SELECT nameFROM cityWHERE population BETWEEN 500000 AND 5000000;

Fetch names of cities that don't miss a rating value:

SELECT nameFROM cityWHERE rating IS NOT NULL;

Fetch names of cities that are in countries with IDs 1, 4, 7, or 8:

SELECT nameFROM cityWHERE 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.nameFROM 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.nameFROM cityLEFT 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.nameFROM cityRIGHT 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.nameFROM cityFULL [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.nameFROM cityCROSS JOIN country;SELECT city.name, country.nameFROM city, country;

NATURAL JOIN

NATURAL JOIN will join tables by all columns with the same name.

SELECT city.name, country.nameFROM cityNATURAL 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

The best way to learn SQL is through practice. Try out our interactive SQL Basics course.

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 cityGROUP 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 cityGROUP BY country_idHAVING 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 nameFROM cityWHERE 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 nameFROM cityWHERE 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_cityWHERE 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 nameFROM countryWHERE 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 nameFROM cyclingWHERE country = 'DE'UNION / UNION ALLSELECT nameFROM skatingWHERE 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 nameFROM cyclingWHERE country = 'DE'INTERSECTSELECT nameFROM skatingWHERE 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 nameFROM cyclingWHERE country = 'DE'EXCEPT / MINUSSELECT nameFROM skatingWHERE country = 'DE';

Try out the interactive SQL Basics course at LearnSQL.com, and check out our other SQL courses.