Please enable JavaScript.
Coggle requires JavaScript to display documents.
Quick SQL Cheatsheet - Coggle Diagram
Quick SQL Cheatsheet
Finding Data Queries
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 conditon;
- SELECT * FROM table_name WHERE conditon1 AND (condition2 OR condition 3);
- SELECT * FROM table_name WHERE EXISTS (SELECT column_name FROM table_name WHERE condition);
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, column DESC
DISTINCT: filters away duplicate values and returns rows of specified column
- SELECT DISTINCT column_name;
BETWEEN: operator select values within a given range inclusive
- SELECT column_names FROM table_name WHERE column_name BETWEEN value 1 AND value 2;
- SELECT * FROM Products WHERE (column_name BETWEEN value1 AND value2) AND NOT column_name2 IN (value 3, value4);
- SELECT * FROM Products WHERE column_name BETWEEN #01/07/1999# AND #03/12/1999#
SELECT: use to select data from a database
- SELECT * FROM table_name;
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;
- Not all database systems support SELECT TOP. The MySQL equivalent is the LIMIT clause.
- SELECT column_names FROM table_name LIMIT offset, count;
IN: operator that allows you to specify multiple values in a WHERE clause
- essentially the IN operator is shorthand for multiple OR conditons
- SELECT column_names FROM table_name WHERE column_name IN (value1, value2, ...);
- SELECT column_names FROM table_name WHERE column_name IN (SELECT STATEMENT);
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;
LIKE: operator used in a WHERE clause to search for a specific pattern in a column
- % 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 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"
AS: aliases are used to assign a temporary nae 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;
UNION: set operator used to combine the result-set of two or more SELECT statements
- Each SELECT statement with UNION must have the same number of columns
- The columns must have similar data types
- The columns in each SELECT statement must also be in the same order
- SELECT columns_names FROM table1 UNION SELECT column_name FROM table2;
- UNION operator only selects distinct values, UNION ALL will allow duplicates
INTERSECT: set operator which is used to return the records that two SELECT statements have in common
- Generally used the same ways as UNION above
- SELECT columns_names FROM table1 INTERSECT SELECT column_name FROM table2;
EXCEPT: set operator used to return all the records in the first SELECT statement that are not found in the second SELECT statement
- Generally used the same way as UNION above
- SELECT columns_names FROM table1 EXCEPT SELECT column_name FROM table2;
WITH: often used for retrieving hierarchical data or re-using temp result set several times in a query. Also referred to as "Common Table Expression"
- WITH RECURSIVE cte AS (Select
HAVING: this clause was added to SQL because the WHERE keyword could not be used with aggregate functions
- SELECT COUNT (column_name1), column_name2 FROM table GROUP BY column_name2 HAVING COUNT (column_name1) >5;
GROUP BY: statement often used with aggregate functions (COUNT, MAX, MIN, SUM, AVG) to group the result-set by one or more columns
- SELECT column_name1, COUNT(column_name2) FROM table_name WHERE condition GROUP BY column_name1 ORDER BY COUNT(column_name2) DESC;
3. Reporting Queries
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;
-
COUNT: returns the # of occurences
- SELECT COUNT (DISTINCT column_name);
-
-
-
-
-