Quick SQL Cheatsheet

A quick reminder of all SQL queries and examples on how to use them.

Table of Contents

1. Finding Data Queries.
2. Data Modification Queries.
3. Reporting Queries.
4. Join Queries.

1. Finding Data Queries

SELECT: used to select data from a database

• SELECT * FROM table_name;

DISTINCT: returns rows that do not have duplicates of specified column

• SELECT DISTINCT column_name;

WHERE: used to filter records/rows

• SELECT column1, column2 FROM table_name WHERE condition;
• SELECT * FROM table_name WHERE condition1 AND condition2;
• SELECT * FROM table_name WHERE condition1 OR condition2;
• SELECT * FROM table_name WHERE NOT condition;
• SELECT * FROM table_name WHERE condition1 AND (condition2 OR condition3);

ORDER BY: used to sort the result-set in ascending or descending order

• SELECT * FROM table_name ORDER BY column;
• SELECT * FROM table_name ORDER BY column DESC;
• SELECT * FROM table_name ORDER BY column1 ASC, column2 DESC;

SELECT TOP: used to specify the number of records to return from top of table

• SELECT TOP number columns_names FROM table_name WHERE condition;
• SELECT TOP percent columns_names FROM table_name WHERE condition;

LIKE: operator used in a WHERE clause to search for a specific pattern in a column

• % (percent sign) is a wildcard character that represents zero, one, or multiple characters
• _ (underscore) is a wildcard character that represents a single character
• SELECT column_names FROM table_name WHERE column_name LIKE pattern;
• LIKE ‘a%’ (find any values that start with “a”)
• LIKE ‘%a’ (find any values that does not end with “a”)
• LIKE ‘%or%’ (find any values that have “or” in any position)
• LIKE ‘_r%’ (find any values that have “r” in the second position)
• LIKE ‘a_%_%’ (find any values that start with “a” and are at least 3 characters in length)
• LIKE ‘[a-c]%’ (find any values starting with “a”, “b”, or “c”

IN: operator that allows you to specify multiple values in a WHERE clause

• essentially the IN operator is shorthand for multiple OR conditions
• SELECT column_names FROM table_name WHERE column_name IN (value1, value2, …);
• SELECT column_names FROM table_name WHERE column_name IN (SELECT STATEMENT);

BETWEEN: operator selects values within a given range inclusive

• SELECT column_names FROM table_name WHERE column_name BETWEEN value1 AND value2;
• SELECT * FROM Products WHERE (column_name BETWEEN value1 AND value2) AND NOT column_name2 IN (value3, value4);
• SELECT * FROM Products WHERE column_name BETWEEN #01/07/1999# AND #03/12/1999#;

NULL: values in a field with no value

• SELECT * FROM table_name WHERE column_name IS NULL;
• SELECT * FROM table_name WHERE column_name IS NOT NULL;

AS: aliases are used to assign a temporary name to a table or column

• SELECT column_name AS alias_name FROM table_name;
• SELECT column_name FROM table_name AS alias_name;
• SELECT column_name AS alias_name1, column_name2 AS alias_name2;
• SELECT column_name1, column_name2 + ‘, ‘ + column_name3 AS alias_name;

2. Data Modification Queries

INSERT INTO: used to insert new records/rows in a table

• INSERT INTO table_name (column1, column2) VALUES (value1, value2);
• INSERT INTO table_name VALUES (value1, value2 …);

UPDATE: used to modify the existing records in a table

• UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;
• UPDATE table_name SET column_name = value;

DELETE: used to delete existing records/rows in a table

• DELETE FROM table_name WHERE condition;
• DELETE * FROM table_name;

3. Reporting Queries

COUNT: returns the # of occurrences

• SELECT COUNT(DISTINCT column_name);

MIN() AND MAX(): returns the smallest/largest value of the selected column

• SELECT MIN(column_names) FROM table_name WHERE condition;
• SELECT MAX(column_names) FROM table_name WHERE condition;

AVG(): returns the average value of a numeric column

• SELECT AVG(column_name) FROM table_name WHERE condition;

SUM(): returns the total sum of a numeric column

• SELECT SUM(column_name) FROM table_name WHERE condition;

4. Join Queries