-
SELECT FROM
used to SELECT data FROM tables in a database; will return multiples
SELECT column_name FROM table_name;
-
SELECT DISTINCT
used to SELECT data FROM tables in a database without multiples
SELECT DISTINCT column_name FROM table_name;
-
WHERE clause
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
-
AND OR
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}+;
-
IN (WHERE)
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'...);
-
BETWEEN
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');
-
NOT BETWEEN
used to exclude a range of values
- SELECT column_name
- FROM table_name
- WHERE column_name
- NOT BETWEEN 'value1' and 'value2';
-
LIKE
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
-
ORDER BY
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
-
AVG()
Calculates the average of a column
- SELECT AVG(column_name)
- FROM table_name;
-
COUNT()
Counts the number of rows in a table
SELECT COUNT(column_name) FROM table_name;
-
MAX()
Finds the maximum value in a column
- SELECT MAX(column_name)
- FROM table_name;
-
MIN()
Finds the minimum value in a column
- SELECT MIN(column_name)
- FROM table_name;
-
SUM()
Finds the total of all values in a column
- SELECT SUM(column_name)
- FROM table_name;
-
GROUP BY
allows sums to be grouped based on table conditions
- SELECTcolumn_name1, SUM(column_name2)
- FROM table_name
- GROUP BY column_name1;
-
HAVING
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);
-
Table Alias
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;
-
Column 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;
-
AS
Used to assign an alias to a column or table.
- SELECT table_alias.column_name AS column_alias
- FROM table_name AS table_alias;
-
JOINING
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;
-
JOIN and ON
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;
-
INNER JOIN
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;
|
|