SQL Cheatsheet

The SQL SELECT Statement

• The SELECT statement is used to select data from a database. The data returned is stored in a result table, called the result-set.

SELECT Syntax:

SELECT column1, column2, ...
FROM table_name; 

The SQL SELECT DISTINCT Statement

• The SELECT DISTINCT statement is used to return only distinct (different) values. Inside a table, a column often contains many duplicate values; and sometimes you only want to list the different (distinct) values.

SELECT DISTINCT Syntax:

SELECT DISTINCT column1, column2, ...
FROM table_name; 

The SQL WHERE Clause

• The WHERE clause is used to filter records. It is used to extract only those records that fulfill a specified condition.

WHERE Syntax:

SELECT column1, column2, ...
FROM table_name
WHERE condition;

The SQL AND, OR and NOT Operators

The WHERE clause can be combined with AND, OR, and NOT operators. The AND and OR operators are used to filter records based on more than one condition:

• The AND operator displays a record if all the conditions separated by AND are TRUE.

• The OR operator displays a record if any of the conditions separated by OR is TRUE.

• The NOT operator displays a record if the condition(s) is NOT TRUE.

AND Syntax:

SELECT column1, column2, ...
FROM table_name
WHERE condition1 AND condition2 AND condition3 ...;

OR Syntax:

SELECT column1, column2, ...
FROM table_name
WHERE condition1 OR condition2 OR condition3 ...;

NOT Syntax:

SELECT column1, column2, ...
FROM table_name
WHERE NOT condition; 

The SQL ORDER BY Keyword

• The ORDER BY keyword is used to sort the result-set in ascending or descending order. The ORDER BY keyword sorts the records in ascending order by default. To sort the records in descending order, use the DESC keyword.

ORDER BY Syntax:

SELECT column1, column2, ...
FROM table_name
ORDER BY column1, column2, ... ASC|DESC; 

The SQL INSERT INTO Statement

• The INSERT INTO statement is used to insert new records in a table. INSERT INTO Syntax

It is possible to write the INSERT INTO statement in two ways:

1. Specify both the column names and the values to be inserted:

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

1. If you are adding values for all the columns of the table, you do not need to specify the column names in the SQL query. However, make sure the order of the values is in the same order as the columns in the table. Here, the INSERT INTO syntax would be as follows:

INSERT INTO table_name
VALUES (value1, value2, value3, ...); 

How to Test for NULL Values?

• It is not possible to test for NULL values with comparison operators, such as =, <, or <>.

We will have to use the IS NULL and IS NOT NULL operators instead.

IS NULL Syntax:

SELECT column_names
FROM table_name
WHERE column_name IS NULL;

IS NOT NULL Syntax:

SELECT column_names
FROM table_name
WHERE column_name IS NOT NULL; 

The SQL UPDATE Statement

The UPDATE statement is used to modify the existing records in a table.

UPDATE Syntax:

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition; 

The SQL DELETE Statement

• The DELETE statement is used to delete existing records in a table.

DELETE Syntax:

DELETE FROM table_name WHERE condition;

The SQL MIN() and MAX() Functions

• The MIN() function returns the smallest value of the selected column. The MAX() function returns the largest value of the selected column.

MIN() Syntax:

SELECT MIN(column_name)
FROM table_name
WHERE condition;

MAX() Syntax:

SELECT MAX(column_name)
FROM table_name
WHERE condition; 

The SQL COUNT(), AVG() and SUM() Functions

• The COUNT() function returns the number of rows that matches a specified criterion. COUNT() Syntax:

SELECT COUNT(column_name)
FROM table_name
WHERE condition;

The AVG() function returns the average value of a numeric column. AVG() Syntax:

SELECT AVG(column_name)
FROM table_name
WHERE condition;

The SUM() function returns the total sum of a numeric column. SUM() Syntax:

SELECT SUM(column_name)
FROM table_name
WHERE condition; 

The SQL LIKE Operator

The LIKE operator is used in a WHERE clause to search for a specified pattern in a column. There are two wildcards often used in conjunction with the LIKE operator:

• The percent sign (%) represents zero, one, or multiple characters

• The underscore sign (_) represents one, single character

Note: MS Access uses an asterisk (*) instead of the percent sign (%), and a question mark (?) instead of the underscore (_). The percent sign and the underscore can also be used in combinations!

LIKE Syntax:

SELECT column1, column2, ...
FROM table_name
WHERE columnN LIKE pattern; 

The SQL IN Operator

• The IN operator allows you to specify multiple values in a WHERE clause. The IN operator is a shorthand for multiple OR conditions. IN Syntax:

SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, ...);

or:

SELECT column_name(s)
FROM table_name
WHERE column_name IN (SELECT STATEMENT); 

The SQL BETWEEN Operator

• The BETWEEN operator selects values within a given range. The values can be numbers, text, or dates. The BETWEEN operator is inclusive: begin and end values are included.

BETWEEN Syntax:

SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;