Basics

Basic SQL Structures & Command

Order of basic clauses:

1. WITH cte-name AS (Common Table Expression), cte-name2 AS ( ), etc.

2. SELECT list-cols [INTO new-table][AS col-alias]

3. FROM table-source / JOIN another-table-source / ON column

4. WHERE search-condition

5. GROUP BY groupby-expression

6. WINDOW windowfunc-expression

7. HAVING search-condition (within groupby clause)

8. ORDER BY order-expression [ASC ascending | DESC descending]

9. LIMIT num-results

UNION, EXCEPT, INTERSECT, AND, OR, AS, RANGE, IN, BETWEEN, etc. can be used between queries to combine or compare their results. However, the order of execution is different.

Order of execution in SQL commands:

1. FROM / JOIN / ON

2. WHERE

3. GROUP BY

4. WITH CUBE | ROLLUP

5. HAVING

6. SELECT (Window Functions SUM, COUNT, AVG, etc. are executed here!)

7. DISTINCT

8. ORDER BY

9. TOP

10. LIMIT

Command	Description	Example
SELECT	Selects given columns from a given table	SELECT age FROM table selects age col from table
FROM	Table source to display	SELECT * FROM table selects all cols from table
WHERE	Condition to filter table	SELECT age FROM table WHERE age < 20 displays rows with age < 20
(I)LIKE	Matches similar values	SELECT * FROM table WHERE name (I)LIKE '%S%' displays rows with name including (Ss)S
IN (BETWEEN)	Including (within) values	SELECT * FROM table WHERE age IN (19,20,21) selects rows where 19 <= age <= 21
AND (OR)	Logic and (or) operators	SELECT * FROM table WHERE age BETWEEN 19 AND 21 selects rows where 19 <= age <= 21
IS (NOT) NULL	Is data missing (or not)?	SELECT * FROM table WHERE age IS NOT NULL excludes rows where age is missing
GROUP BY	Groups data by a condition	SELECT * FROM table GROUP BY college groups data by college column
HAVING	Filters grouped datasets with given conditions	SELECT * FROM table GROUP BY college HAVING age = 19 groups data by college column where age = 19
ORDER BY	Sorts data asc or desc order	SELECT * FROM table ORDER BY age DESC orders data by age column
LIMIT	Limits the number of results	SELECT * FROM table LIMIT 100 displays first 100 rows of table

SQL Aggregate Functions:

Aggregate	Description	Example
COUNT	Counts num rows in a given column
SUM	Adds values in a given column
MIN (MAX)	Returns minimum (maximum) value in a column
AVG	Calculates the average of group of selected values