SQL Commands

used to SELECT data FROM tables in a database; will return multiples


SELECT column_name FROM table_name;

used to SELECT data FROM tables in a database without multiples


SELECT DISTINCT column_name FROM table_name;

filters the results based on certain conditions; can include a single comparison clause (simple condition) or multiple comparisons combined with AND or OR operators (compound conditions)


SELECT column_name FROM table_name WHERE condition;

the expression inside the brackets will occur one or more times

connects simple conditions to create compound conditions; [AND|OR] means either AND or OR can be used


SELECT column_name FROM table_name WHERE simple condition {[AND|OR] simple condition}+;

limits selection criteria to one or more discrete values; values in the parenthesis can be one or more, each separated by a comma


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

selects a range of values; includes the values used in the results

• SELECT colum_name
• FROM table_name
• WHERE column_name
• BETWEEN 'value1' AND 'value2';

To exclude values from the result use:

• SELECT column_name
• FROM table_name
• WHERE (column-name > 'value1')
• AND (column_name < 'value2');

used to exclude a range of values

• SELECT column_name
• FROM table_name
• WHERE column_name
• NOT BETWEEN 'value1' and 'value2';

allows you to search based on a pattern rather than specifying exactly what is desired (as in IN) or spell out a range (as in BETWEEN)               

• SELECT column_name
• FROM table_name WHERE column_name
• LIKE {PATTERN};{PATTERN}: often consists of wildcards

orders the information extracted from a table

• SELECT column_name
• FROM table_name
• [WHERE condition] ORDER BY column name [ASC/DESC]           

•                     
• There WHERE statement is optional, however if it exists, it comes before the ORDER BY               

• ASC – Ascending                             
• DESC – Descending               

• You can order multiple columns by separating first, second… columns with comma               
• This will order by first column first and, in the event of a tie, order by the second

Calculates the average of a column

• SELECT AVG(column_name)
• FROM table_name;

Counts the number of rows in a table               

SELECT COUNT(column_name)                FROM table_name;

Finds the maximum value in a column               

• SELECT MAX(column_name)               
• FROM table_name;

Finds the minimum value in a column               

• SELECT MIN(column_name)               
• FROM table_name;

Finds the total of all values in a column               

• SELECT SUM(column_name)                
• FROM table_name;

allows sums to be grouped based on table conditions               

• SELECTcolumn_name1, SUM(column_name2)               
• FROM table_name               
• GROUP BY column_name1;

the WHERE clause of aggregate functions; may or may not incorporate GROUP BY               

• SELECT column_name1, SUM(column_name2)               
• FROM table_name               
• GROUP BY column_name1         
• HAVING (arithmetic function condition);

renames tables; placed directly after table name in FROM clause; frequently used when performing joins               

• SELECT column_name                
• FROM table_alias.table_name table_alias;

Helps organize output by renaming the column selected; used to rename columns with functions, ie SUM(column_name) to show as column_name               

• SELECT column_alias.column_name1 column_alias               
• FROM table_name;

Used to assign an alias to a column or table.

• SELECT table_alias.column_name AS column_alias
• FROM table_name AS table_alias;

Combining infromation from multiple tables. (See "JOIN and ON" card for alternate)

• SELECT A1.Region_Name REGION, SUM(A2.Sales) SALES
• FROM Geography A1, Store_Information A2
• WHERE A1.Store_Name = A2.Store_Name
• GROUP BY A1.Region_Name;

Combines information from multiple tables. (See "JOINING" card for alternate)

• SELECT A1.column_name1 ALIAS1, SUM(A2.column_name2) ALIAS2
• FROM table_name1 A1, table_name2 A2
• JOIN table_name2 A2
• ON A1.column_name3 = A2.column_name3
• GROUP BY A1.column_name1;

JOIN that returns rows where there is at least one match on both tables.

For example: We want to find out sales by store and we only want to see stores with sales listed in the report...

• SELECT A1.Store_Name STORE, SUM(A2.Sales) SALES
• FROM Geography A1
• INNER JOIN Store_Information A2
• ON A1.Store_Name = A2.Store_Name
• GROUP BY A1.Store_Name;