SQL Cheat Sheet

In this guide, you’ll find a useful cheat sheet that documents some of the more commonly used elements of SQL, and even a few of the less common. Hopefully, it will help developers – both beginner and experienced level – become more proficient in their understanding of the SQL language.

Use this as a quick reference during development, a learning aid, or even print it out and bind it if you’d prefer (whatever works!).

But before we get to the cheat sheet itself, for developers who may not be familiar with SQL, let’s start with…

PDF Version of SQL Cheat Sheet

SQL Cheat Sheet (Download PDF)

What is SQL

SQL stands for Structured Query Language. It’s the language of choice on today’s web for storing, manipulating and retrieving data within relational databases. Most, if not all of the websites you visit will use it in some way, including this one.

Here’s what a basic relational database looks like. This example in particular stores e-commerce information, specifically the products on sale, the users who buy them, and records of these orders which link these 2 entities.

Using SQL, you are able to interact with the database by writing queries, which when executed, return any results which meet its criteria.

Here’s an example query:-

SELECT * FROM users;

Using this SELECT statement, the query selects all data from all columns in the user’s table. It would then return data like the below, which is typically called a results set:-

If we were to replace the asterisk wildcard character (*) with specific column names instead,  only the data from these columns would be returned from the query.

SELECT first_name, last_name FROM users;

We can add a bit of complexity to a standard SELECT statement by adding a WHERE clause, which allows you to filter what gets returned.

SELECT * FROM products WHERE stock_count <= 10 ORDER BY stock_count ASC;

This query would return all data from the products table with a stock_count value of less than 10 in its results set.

The use of the ORDER BY keyword means the results will be ordered using the stock_count column, lowest values to highest.

Using the INSERT INTO statement, we can add new data to a table. Here’s a basic example adding a new user to the users table:-

INSERT INTO users (first_name, last_name, address, email)
VALUES ('Tester', 'Jester', '123 Fake Street, Sheffield, United Kingdom', 'test@lukeharrison.dev');

Then if you were to rerun the query to return all data from the user’s table, the results set would look like this:

Of course, these examples demonstrate only a very small selection of what the SQL language is capable of.

SQL vs MySQL

You may have heard of MySQL before. It’s important that you don’t confuse this with SQL itself, as there’s a clear difference.

SQL is the language. It outlines syntax that allows you to write queries that manage relational databases. Nothing more.

MySQL meanwhile is a database system that runs on a server. It implements the SQL language, allowing you to write queries using its syntax to manage MySQL databases.

In addition to MySQL, there are other systems that implement SQL. Some of the more popular ones include:

• PostgreSQL
• SQLite
• Oracle Database
• Microsoft SQL Server

Installing MySQL

Windows

The recommended way to install MySQL on Windows is by using the installer you can download from the MySQL website.

MacOS

On macOS, the recommended way to install MySQL is using native packages, which sounds a lot more complicated than it actually is. Essentially, it also involves just downloading an installer.

Alternatively, If you prefer to use package managers such as Homebrew, you can install MySQL like so:

brew install mysql

Whilst if you need to install the older MySQL version 5.7, which is still widely used today on the web, you can:

brew install mysql@5.7

Using MySQL

With MySQL now installed on your system, to get up and going as quickly as possible writing SQL queries, it’s recommended that you use an SQL management application to make managing your databases a much simpler, easier process.

There are lots of apps to choose from which largely do the same job, so it’s down to your own personal preference on which one to use:

• MySQL Workbench is developed by Oracle, the owner of MySQL.
• HeidiSQL (Recommended Windows) is a free, open-source app for Windows. For macOS and Linux users, Wine is first required as a prerequisite.
• phpMyAdmin is a very popular alternative that operates in the web browser.
• Sequel Pro (Recommended macOS) is a macOS’ only alternative and our favorite thanks to its clear and easy to use interface.

When you’re ready to start writing your own SQL queries, rather than spending time creating your own database, consider importing dummy data instead.

The MySQL website provides a number of dummy databases that you can download free of charge and then import into your SQL app.

Our favorite of these is the world database, which provides some interesting data to practice writing SQL queries for. Here’s a screenshot of its country table within Sequel Pro.

This example query returns all countries with Queen Elizabeth II as their head of state.

Whilst this one returns all European countries with a population of over 50million along with their capital city and its population.

And this final one returns the average percentage of French speakers in countries where the total number of French speakers is higher than 10%.

Cheat Sheet

Keywords

A collection of keywords used in SQL statements, a description, and where appropriate an example. Some of the more advanced keywords have their own dedicated section later in the cheat sheet.

Where MySQL is mentioned next to an example, this means this example is only applicable to MySQL databases (as opposed to any other database system).

ALTER TABLE users
ADD email_address varchar(255);

ALTER TABLE users
ADD CONSTRAINT user PRIMARY KEY (ID, SURNAME);

ALTER TABLE deals
ADD approved boolean;

ALTER TABLE deals
DROP COLUMN approved;

ALTER TABLE users
ALTER COLUMN incept_date datetime;

SELECT first_name, surname, tasks_no
FROM users
WHERE tasks_no > ALL (SELECT tasks FROM user WHERE department_id = 2);

SELECT * FROM events
WHERE host_country='United Kingdom' AND host_city='London';

SELECT name
FROM products
WHERE productId = ANY (SELECT productId FROM orders WHERE quantity > 5);

SELECT north_east_user_subscriptions AS ne_subs
FROM users
WHERE ne_subs > 5;

SELECT * FROM stock
WHERE quantity BETWEEN 100 AND 150;

SELECT * FROM stock
WHERE quantity NOT BETWEEN 100 AND 150;

SELECT first_name, surname, subscriptions
CASE WHEN subscriptions > 10 THEN 'Very active'
WHEN Quantity BETWEEN 3 AND 10 THEN 'Active'
ELSE 'Inactive'
END AS activity_levels
FROM users;

CREATE TABLE users (
  first_name varchar(255),
  age int,
  CHECK (age>=18)
);

ALTER TABLE users
ADD CHECK (age>=18);

CREATE DATABASE websitesetup;

CREATE TABLE users (
    id int,
    first_name varchar(255),
    surname varchar(255),
    address varchar(255),
    contact_number int
);

CREATE TABLE products (
    id int,
    name varchar(255) DEFAULT 'Placeholder Name',
    available_from date DEFAULT GETDATE()
);

ALTER TABLE products
ALTER name SET DEFAULT 'Placeholder Name',
ALTER available_from SET DEFAULT GETDATE();

DELETE FROM users WHERE user_id = 674;

ALTER TABLE users
DROP COLUMN first_name

DROP DATABASE websitesetup;

ALTER TABLE products
ALTER COLUMN name DROP DEFAULT;

DROP TABLE users;

SELECT dealership_name
FROM dealerships
WHERE EXISTS (SELECT deal_name FROM deals WHERE dealership_id = deals.dealership_id AND finance_percentage < 10);

SELECT area_manager
FROM area_managers
WHERE EXISTS (SELECT ProductName FROM Products WHERE area_manager_id = deals.area_manager_id AND Price < 20);

SELECT * FROM users
WHERE country = 'USA' OR country = 'United Kingdom' OR country = 'Russia' OR country = 'Australia';

SELECT * FROM users
WHERE country IN ('USA', 'United Kingdom', 'Russia', 'Australia');

INSERT INTO cars (make, model, mileage, year)
VALUES ('Audi', 'A3', 30000, 2016);

SELECT * FROM users
WHERE contact_number IS NULL;

SELECT * FROM users
WHERE first_name LIKE '%son';

SELECT * FROM users
WHERE first_name NOT LIKE '%son';

SELECT * FROM users
WHERE city = 'Sheffield' OR 'Manchester';

SELECT * FROM countries
ORDER BY name;

SELECT * FROM countries
WHERE ROWNUM <= 10;

SELECT * FROM users;

SELECT first_name, surname FROM users;

SELECT * INTO usersBackup2020
FROM users;

SELECT DISTINCT country from users;

SELECT TOP 3 * FROM cars;

UPDATE orders
SET value = 19.49, quantity = 2
WHERE id = 642;

SELECT TOP 5 * FROM users;

TRUNCATE TABLE sessions;

SELECT city FROM events
UNION
SELECT city from subscribers;

CREATE TABLE users (
    id int NOT NULL,
    name varchar(255) NOT NULL,
    UNIQUE (id)
);

ALTER TABLE users
ADD UNIQUE (id);

UPDATE cars
SET mileage = 23500, serviceDue = 0
WHERE id = 45;

INSERT INTO cars (name, model, year)
VALUES ('Ford', 'Fiesta', 2010);

SELECT * FROM orders
WHERE quantity > 1;

Comments

Comments allow you to explain sections of your SQL statements, or to comment out code and prevent its execution.

In SQL, there are 2 types of comments, single line and multiline.

Single Line Comments

Single line comments start with –. Any text after these 2 characters to the end of the line will be ignored.

-- My Select query
SELECT * FROM users;

Multiline Comments

Multiline comments start with /* and end with */. They stretch across multiple lines until the closing characters have been found.

/*
This is my select query.
It grabs all rows of data from the users table
*/
SELECT * FROM users;

/*
This is another select query, which I don't want to execute yet

SELECT * FROM tasks;
*/

MySQL Data Types

When creating a new table or editing an existing one, you must specify the type of data that each column accepts.

In the below example, data passed to the id column must be an int, whilst the first_name column has a VARCHAR data type with a maximum of 255 characters.

CREATE TABLE users (
    id int,
    first_name varchar(255)
);

String Data Types

Numeric Data Types

Date / Time Data Types

Operators

Arithmetic Operators

Bitwise Operator

Comparison Operators

Compound Operators

Functions

String Functions

Numeric Functions

Date Functions

Misc Functions

SELECT COUNT(user_id), active_orders
FROM users
GROUP BY active_orders;

SELECT COUNT(user_id), active_orders
FROM users
GROUP BY active_orders
HAVING COUNT(user_id) > 3;

Wildcard Characters

In SQL, Wildcards are special characters used with the LIKE and NOT LIKE keywords which allow us to search data with sophisticated patterns much more efficiently

SELECT * FROM users
WHERE surname LIKE '%son';

SELECT * FROM users
WHERE city LIKE '%che%';

SELECT * FROM users
WHERE city LIKE '___chester';

SELECT * FROM users
WHERE first_name LIKE '[jhm]%';

SELECT * FROM users
WHERE first_name LIKE '[a-l]%';

SELECT * FROM users
WHERE first_name LIKE '%[!n-s]';

Keys

In relational databases, there is a concept of primary and foreign keys. In SQL tables, these are included as constraints, where a table can have a primary key, a foreign key, or both.

Primary Key

A primary key allows each record in a table to be uniquely identified. There can only be one primary key per table, and you can assign this constraint to any single or combination of columns. However, this means each value within this column(s) must be unique.

Typically in a table, the primary key is an ID column, and is usually paired with the AUTO_INCREMENT keyword. This means the value increases automatically as new records are created.

Example 1 (MySQL)

Create a new table and set the primary key to the ID column.

CREATE TABLE users (
    id int NOT NULL AUTO_INCREMENT,
    first_name varchar(255),
    last_name varchar(255) NOT NULL,
    address varchar(255),
    email varchar(255),
    PRIMARY KEY (id)
);

Example 2 (MySQL)

Alter an existing table and set the primary key to the first_name column.

ALTER TABLE users
ADD PRIMARY KEY (first_name);

Foreign Key

A foreign key can be applied to one column or many and is used to link 2 tables together in a relational database.

As seen in the diagram below, the table containing the foreign key is called the child key, whilst the table which contains the referenced key, or candidate key, is called the parent table.

This essentially means that the column data is shared between 2 tables, as a foreign key also prevents invalid data from being inserted which isn’t also present in the parent table.

Example 1 (MySQL)

Create a new table and turn any columns that reference IDs in other tables into foreign keys.

CREATE TABLE orders (
    id int NOT NULL,
    user_id int,
    product_id int,
    PRIMARY KEY (id),
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (product_id) REFERENCES products(id)
);

Example 2 (MySQL)

Alter an existing table and create a foreign key.

ALTER TABLE orders
ADD FOREIGN KEY (user_id) REFERENCES users(id);

Indexes

Indexes are attributes that can be assigned to columns that are frequently searched against to make data retrieval a quicker and more efficient process.

This doesn’t mean each column should be made into an index though, as it takes longer for a column with an index to be updated than a column without. This is because when indexed columns are updated, the index itself must also be updated.

CREATE INDEX idx_test
ON users (first_name, surname);

CREATE UNIQUE INDEX idx_test
ON users (first_name, surname);

ALTER TABLE users
DROP INDEX idx_test;

Joins

In SQL, a JOIN clause is used to return a results set which combines data from multiple tables, based on a common column which is featured in both of them

There are a number of different joins available for you to use:-

• Inner Join (Default): Returns any records which have matching values in both tables.

• Left Join: Returns all of the records from the first table, along with any matching records from the second table.

• Right Join: Returns all of the records from the second table, along with any matching records from the first.

• Full Join: Returns all records from both tables when there is a match.

A common way of visualising how joins work is like this:

In the following example, an inner join will be used to create a new unifying view combining the orders table and then 3 different tables

We’ll replace the user_id and product_id with the first_name and surname columns of the user who placed the order, along with the name of the item which was purchased.

SELECT orders.id, users.first_name, users.surname, products.name as 'product name'
FROM orders
INNER JOIN users on orders.user_id = users.id
INNER JOIN products on orders.product_id = products.id;

Would return a results set which looks like:

View

A view is essentially a SQL results set that get stored in the database under a label, so you can return to it later, without having to rerun the query. These are especially useful when you have a costly SQL query which may be needed a number of times, so instead of running it over and over to generate the same results set, you can just do it once and save it as a view.

Creating Views

To create a view, you can do so like this:

CREATE VIEW priority_users AS
SELECT * FROM users
WHERE country = 'United Kingdom';

Then in future, if you need to access the stored result set, you can do so like this:

SELECT * FROM [priority_users];

Replacing Views

With the CREATE OR REPLACE command, a view can be updated.

CREATE OR REPLACE VIEW [priority_users] AS
SELECT * FROM users
WHERE country = 'United Kingdom' OR country='USA';

Deleting Views

To delete a view, simply use the DROP VIEW command.

DROP VIEW priority_users;

Conclusion

The majority of the websites on today’s web use relational databases in some way. This makes SQL a valuable language to know, as it allows you to create more complex, functional websites, and systems.

Make sure to bookmark this page, so in the future, if you’re working with SQL and can’t quite remember a specific operator, how to write a certain query, or are just confused about how joins work, then you’ll have a cheat sheet on hand which is ready, willing and able to help.

If you spot any errors in this cheat sheet, please contact us – info@websitesetup.org