Skip to main content
HomeCheat sheetsSQL

SQL Basics Cheat Sheet

With this SQL cheat sheet, you'll have a handy reference guide to basic querying tables, filtering data, and aggregating data
Mar 2022  · 5 min read

SQL, also known as Structured Query Language, is a powerful tool to search through large amounts of data and return specific information for analysis. Learning SQL is crucial for anyone aspiring to be a data analyst, data engineer, or data scientist, and helpful in many other fields such as web development or marketing.

In this cheat sheet, you'll find a handy list of functions covering querying data, filtering data, aggregation, and more—all collected from our SQL Fundamentals Skill Track.

To learn more about SQL basics, watch this video from our course. To easily run all the example code in this tutorial yourself, you can create a DataLab workbook for free that has the dataset, SQL, and the code samples preconfigured.

SQL for Data Science

Have this cheat sheet at your fingertips

Download PDF

The Different Dialects of SQL

Although SQL languages all share a basic structure, some of the specific commands and styles can differ slightly. Popular dialects include MySQL, SQLite, SQL Server, Oracle SQL, and more. PostgreSQL is a good place to start —since it’s close to standard SQL syntax and is easily adapted to other dialects. 

Sample Data

Throughout this cheat sheet, we'll be using the sample data airbnb_listings—denoting rental apartments on Airbnb.

id city country number_of_rooms year_listed
1 Paris France 5 2018
2 Tokyo Japan 2 2017
3 New York USA 2 2022

Querying tables

Get all the columns from a table

SELECT * 
FROM airbnb_listings;

Return the city column from the table

SELECT city 
FROM airbnb_listings;

Get the city and year_listed columns from the table

SELECT city, year_listed
FROM airbnb_listings;

Get the listing id, city, ordered by the number_of_rooms in ascending order

SELECT city, year_listed 
FROM airbnb_listings 
ORDER BY number_of_rooms ASC;

Get the listing id, city, ordered by the number_of_rooms in descending order

SELECT city, year_listed 
FROM airbnb_listings 
ORDER BY number_of_rooms DESC;

Get the first 5 rows from airbnb_listings

SELECT * 
FROM airbnb_listings
LIMIT 5;

Get a unique list of cities where there are listings

SELECT DISTINCT city
FROM airbnb_listings;

Filtering on numeric columns

Get all the listings where number_of_rooms is more or equal to 3

SELECT *
FROM airbnb_listings 
WHERE number_of_rooms >= 3;

Get all the listings where number_of_rooms is more than 3

SELECT *
FROM airbnb_listings 
WHERE number_of_rooms > 3;

Get all the listings where number_of_rooms is exactly 3

SELECT *
FROM airbnb_listings 
WHERE number_of_rooms = 3;

Get all the listings where number_of_rooms is lower or equal to 3

SELECT *
FROM airbnb_listings 
WHERE number_of_rooms <= 3;

Get all the listings where number_of_rooms is lower than 3

SELECT *
FROM airbnb_listings 
WHERE number_of_rooms < 3;

Filtering columns within a range—Get all the listings with 3 to 6 rooms

SELECT *
FROM airbnb_listings 
WHERE number_of_rooms BETWEEN 3 AND 6;

Filtering on text columns

Get all the listings that are based in 'Paris'

SELECT * 
FROM airbnb_listings 
WHERE city = 'Paris';

Filter one column on many conditions—Get the listings based in the 'USA' and in ‘France’

SELECT *
FROM airbnb_listings 
WHERE country IN ('USA', 'France');

Get all listings where city starts with "j" and where it does not end with "t"

SELECT * 
FROM airbnb_listings 
WHERE city LIKE 'j%' AND city NOT LIKE '%t';

Filtering on multiple columns

Get all the listings in "Paris" where number_of_rooms is bigger than 3

SELECT *
FROM airbnb_listings 
WHERE city = 'Paris' AND number_of_rooms > 3;

Get all the listings in "Paris" OR the ones that were listed after 2012

SELECT * 
FROM airbnb_listings
WHERE city = 'Paris' OR year_listed > 2012;

Become SQL Certified

Prove your SQL skills are job-ready with a certification.
Boost My Career

Filtering on missing data

Get all the listings where number_of_rooms is missing

SELECT *
FROM airbnb_listings 
WHERE number_of_rooms IS NULL;

Get all the listings where number_of_rooms is not missing

SELECT *
FROM airbnb_listings 
WHERE number_of_rooms IS NOT NULL;

Simple aggregations

Get the total number of rooms available across all listings 

SELECT SUM(number_of_rooms) 
FROM airbnb_listings;

Get the average number of rooms per listing across all listings

SELECT AVG(number_of_rooms) 
FROM airbnb_listings;

Get the listing with the highest number of rooms across all listings

SELECT MAX(number_of_rooms) 
FROM airbnb_listings;

Get the listing with the lowest number of rooms across all listings

SELECT MIN(number_of_rooms) 
FROM airbnb_listings;

Grouping, filtering, and sorting 

Get the total number of rooms for each country

SELECT country, SUM(number_of_rooms)
FROM airbnb_listings
GROUP BY country;

Get the average number of rooms for each country

SELECT country, AVG(number_of_rooms)
FROM airbnb_listings
GROUP BY country;

Get the listing with the maximum number of rooms for each country

SELECT country, MAX(number_of_rooms)
FROM airbnb_listings
GROUP BY country;

Get the listing with the lowest amount of rooms per country

SELECT country, MIN(number_of_rooms)
FROM airbnb_listings
GROUP BY country;

For each country, get the average number of rooms per listing, sorted by ascending order

SELECT country, AVG(number_of_rooms) AS avg_rooms
FROM airbnb_listings
GROUP BY country
ORDER BY avg_rooms ASC;

For Japan and the USA, get the average number of rooms per listing in each country

SELECT country, AVG(number_of_rooms)
FROM airbnb_listings
WHERE country IN ('USA', 'Japan');
GROUP BY country;

Get the number of listings per country

SELECT country, COUNT(id) AS number_of_listings
FROM airbnb_listings
GROUP BY country;

Get all the years where there were more than 100 listings per year

SELECT year_listed
FROM airbnb_listings
GROUP BY year_listed
HAVING COUNT(id) > 100;

SQL Upskilling for Beginners

Gain the SQL skills to interact with and query your data.
Start Learning for Free
Topics
SQL
Data Science
Data Literacy
Related

cheat sheet

MySQL Basics Cheat Sheet

With this MySQL basics cheat sheet, you'll have a handy reference guide to basic querying tables, filtering data, and aggregating data
Richie Cotton's photo

Richie Cotton

6 min

cheat sheet

PostgreSQL Basics Cheat Sheet

With this PostgreSQL cheat sheet, you'll have a handy reference guide to basic querying tables, filtering data, and aggregating data
Richie Cotton's photo

Richie Cotton

6 min

cheat sheet

SQL Joins Cheat Sheet

With this SQL Joins cheat sheet, you'll have a handy reference guide to joining data in SQL.
Richie Cotton's photo

Richie Cotton

6 min

cheat sheet

SQL Window Functions Cheat Sheet

With this SQL Window Functions cheat sheet, you'll have a handy reference guide to the various types of window functions in SQL.
Richie Cotton's photo

Richie Cotton

10 min

code-along

Getting Started in SQL

Learn how to write basic queries in SQL and find answers to business questions.
Kelsey McNeillie's photo

Kelsey McNeillie

code-along

SQL for Absolute Beginners

Start from the very basics of what SQL is and why it's essential, move through key components such as retrieving data from databases, manipulation of data, and basic SQL queries.
Adel Nehme's photo

Adel Nehme

See MoreSee More