SQL 5:

1. JOIN：ON indicates how the two tables (the one after the FROM and the one after the JOIN) relate to each other.

如：ON teams.school_name = players.school_name

.* to return all the columns

2. INNER JOIN: eliminate rows from both tables that do not satisfy the join condition set forth in the ON statement;

In mathematical terms, an inner join is the intersection of the two tables.

The results can only support one column with a given name.

3. OUTER JOIN: in an outer join, unmatched rows in one or both tables can be returned.

There are a few types of outer joins:

LEFT JOIN returns only unmatched rows from the left table;

RIGHT JOIN returns only unmatched rows from the right table;

FULL OUTER JOIN returns unmatched rows from both tables.

4. WHERE OR ON:

在INNER JOIN中，ON......后可以加and(后加限定的内容，相当于JOIN的条件)，与INNER JOIN后加WHERE限定是无差别的（都可以）；

在LEFT JOIN中是不同的，因为在and后先filter，然后再join；在WHERE后，是先join，再filter

5. UNION: allows you to stack one dataset on top of the other, and allows you to write two separate SELECT statements,you can treat them differently before appending.

UNION only appends distinct values;

UNION ALL append all the values from the second table 

UNION ALL < UNION?

6. 用ON做JOIN的连接词，可以加任何conditional statement, 如ON..... AND.....运算或>,<,!=等条件

7. SELF JOIN: join a table to itself

the same table can easily be referenced multiple times using different aliases

 

SQL3:

1. Using comments

use--(two dashes) to comment out everything to the right of them on a given line

leave comments across multiple lines using /* to begin the comment and */ to close it(以/*开始，以*/结束)

可以用comments对query中可能产生的错误进行标注，如在SELECT中任一list前加--（two dashes）可使该list不产生results，以检查是否该list出现了错误（debug）

Basic SQL结束

二. Intermediate SQL

1. SQL Aggregate Functions

Arithmetic operators only perform operations across rows. Aggragate functions are used to perform operations across entire columns.

COUNT: SELECT COUNT (*) 或 (1)

不包含空值（not include null value）

SUM: only used on columns containing numerical values;

only aggragate vertically;

treats nulls as 0

MIN/MAX: SQL aggregation functions that return the lowest and highest values in a particular column.

MIN will return the lowest number, earliest date, or non-numerical value as close alphabetically to "A" as possible.

MAX returns the highest number, the latest date, or the non-numerical value closest alphabetically to "Z"

AVG: only be used on numerical columns and ignores nulls completely

2. GROUP BY: aggregate only part of a table, separate data into groups, which can be aggregated independently of one another

GROUP by multiple columns- separate column names with commas.

Using GROUP BY with ORDER BY(把GROUP BY的结果进行排序整理)

最后可加LIMIT限制显示数据结果

注：GROUP BY出现的部分必须要出现在SELECT中（SELECT要与GROUP BY的内容一致）

3. HAVING: to filter a query that has been aggregated.（作用类似于WHERE，但WHERE doesn't allow you to filter on aggregate columns)，一般用在GROUP BY之后

WHERE在aggregate之前使用，HAVING在aggregate之后使用

Query clause order: SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT

4. CASE WHEN... THEN: SQL's way of handling if/then logic

always goes in the SELECT clause; every CASE statement must end with the END statement; the ELSE statement is optional, and provides a way to capture values not specified in the WHEN/THEN statements.

常见：CASE WHEN... THEN... (ELSE) END

 

 

 

 

 

 

 

SQL 4:

1. CASE WHEN condition 1 THEN result 1

            WHEN condition 2 THEN result 2

             ELSE result 3 END AS column_name

2. Using CASE with aggregate functions:

Write a query containing the CASE statement first, then COUNT, SUM, AVG......

3. Using CASE inside of aggregate functions (Pivoting)

COUNT (CASE WHEN)

4. DISTINCT: used for viewing unique values.

include multiple columns in a SELECT DISTINCT clause, the results will contain all of the unique pairs of those columns.

注：include DISTINCT once in the SELECT clause

particularly helpful when exploring a new data set. 

using DISTINCT in aggregations-

COUNT DISTINCT

COUNT (DISTINCT CASE WHEN.....)

using DISTINCT, particularly in aggregations, can slow your queries down quite a bit.

5. JOIN: working with data from multiple tables

SELECT * 

FROM a table JOIN another table ON....

重命名不用加AS

 

 

上节课作业:

question: <='N'，如果出现N，等<'O'，为什么=N不能出现N？

SQL2:

1. LIKE: match on similar values

用在WHERE中，在限定中找相似，加%或-和‘’

ILIKE不区分大小写，LIKE区分大小写

2. IN: specify a list of values that you'd like to include in the results

用在WHERE中，value用逗号连接，非数字加‘’

3. BETWEEN AND

4. IS NULL

5. AND

6. OR

AND和OR一起用要加（），注意括号位置

7. NOT

IS NOT NULL用的较多，NOT不能与>,<,=一起使用

8. ORDER BY

默认按照升序顺序排列，降序排列在ORDER BY后加DESC，先数字，再字母，如ORDER BY year DESC, year_rank DESC(先按照year降序排序，再按照year rank降序排序)，ORDER BY year，year_rank DESC(先按照year升序排序，再按照year rank降序排序)

以各种分组排列，中间加逗号，如ORDER BY year, year_rank

用在限定环境中，放在WHERE之后