• Chapter 1 Database and SQL
  • Recall questions
  • Three categories of SQL language
  • Data type
  • Set constraints
  • Table definition update
• Chapter 2 SQL Query Basics
  • Recall questions
  • Query constant
  • Remove duplicates
  • Arithmetic and comparison operators
  • logic operators (AND, OR, NOT)
• Chapter 3 Aggregation and Sorting
  • Recall questions
  • Aggregation functions
• Chapter 4 Table Data Update
  • Recall questions
  • INSERT
  • INSERT ... SELECT
  • DELETE (rows)
  • UPDATE
  • Transaction
• Chapter 5 Complex Query
  • Recall questions
  • View and View creation
  • Scalar subquery
  • A powerful example (Scalar subquery)
  • Correlated Query
• Chapter 6 Colorful Functions
  • Recall questions
  • Categories of Functions
  • Predicates (which returns boolean-like values)
  • Use Case to transpose
• Chapter 7 Set Operation
  • Recall questions
  • Row direction join (axis = 1)
  • Column direction join (axis = 0)
• Chapter 8 Advanced Topics: Window Function & Grouping Operator
  • Recall questions
  • Categories of Window Function
  • Features of Window Function
  • Aggregation window function (attention!)
  • Moving average (how to use Frame)
  • Grouping operator

Chapter 1 Database and SQL

Recall questions

1. How to rename a table?
2. How to add a new column to a table?
3. What's the difference between CHAR(n) and VARCHAR(n)?
4. How we categorize SQL language?

Three categories of SQL language

DDL (Data Definition Language)

• CREATE
• DROP
• ALTER

DML (Data Manipulation Language)(90%)

• SELECT
• INSERT
• UPDATE
• DELETE

DCL (Data Control Language)

• COMMIT
• ROLLBACK
• GRANT
• REVOKE

Data type

CHAR(n)

• the length is determined by n
• e.g., when insert 'abc' into CHAR(8), there will be 5 half-angle space stored after 'abc'

VARCHAR(n)

• the length is variable
• e.g., when insert 'abc' into VARCHAR(8), there will only be 'abc' stored

Set constraints

NOT NULL

• if there is no input, error raises

PRIMARY KEY(product_id)

• use primary key to select distinct rows

Table definition update

Add Column

• ALTER TABLE < table name > ADD COLUMN < column definition >;

Rename Table

• ALTER TABLE Poduct RENAME TO Product;

Chapter 2 SQL Query Basics

Recall questions

1. Why we set NOT NULL constraints?
2. What tools we can use to deal with complex situations to calculate logic values?
3. How many logic values are there?

Query constant

example：

SELECT '商品' AS string, 38 AS number, '2009-02-24' AS date, 
        product_id, product_name 
FROM Product;

Remove duplicates

Use DISTINCT

• NULL will be kept, treated as another category of data
• Rows with multiple NULLs will be combined into one row

Arithmetic and comparison operators

Arithmetic NULL (attention!)

• NULL / 0 = NULL
• NULL + 5 = NULL
• all arithmetic involve NULL, the result will always be NULL

Comparison NULL

• NULL <> 2800 (UNKNOWN, this is neither TRUE nor FALSE)
• TURE, FALSE, UNKNOWN

logic operators (AND, OR, NOT)

Use Venn Digram and Truth Tables to deal with complex situations

AND(logic product), OR(logic sum)

Why set constraint NOT NULL

Chapter 3 Aggregation and Sorting

Recall questions

1. What is the execution sequence of SQL?
2. Can you use (alias, numbers, aggregations not in select) in ORDER BY clause?
3. Which aggregation functions apply to all data type?
4. How to count all the non-null rows?

Aggregation functions

Aggregation means multiple rows input, one row output.

COUNT(parameter)

• COUNT(*) will include NULL rows, normally, other functions will only aggregate non-null values
• COUNT(purchase_price) will exclude NULL rows
• COUNT(DISTINCT parameter) to calculate the number of categories of the value.

MAX/MIN & SUM/AVG

• MAX/MIN apply to all data type
• SUM/AVG apply to numeric data type
• DISTINCT can be used in all aggregation functions, help to remove duplicated data

Group By

• Different groups of data
• Each group has one aggregation output
• If NULL is included in aggregation key, it will still show the result

Caveats

• Sequence of execution:

FROM -> WHERE(set row condition) -> GROUP BY(set group condition) -> HAVING -> SELECT -> ORDER BY

• You can only use aggregation in SELECT, HAVING, ORDER BY, not WHERE
• ORDER BY goes to the bottom and you can use alias (do not use numbers), because before you sort, the final table is ready.

Chapter 4 Table Data Update

Recall questions

1. How to update one column's data to NULL?
2. How to delete rows met certain conditions?
3. How to insert data from another table?

INSERT

INSERT ... SELECT

DELETE (rows)

• DELETE FROM < table name >; (keep table, delete all rows; use TRUNCATE < table name > is better.)
• DELETE FROM < table name > WHERE < conditions >; (delete certain rows)

UPDATE

Use NULL to update

Transaction

• Atomicity
• Consistency
• Isolation
• Durability

Chapter 5 Complex Query

Recall questions

1. When to use correlated subquery?
2. When to use scalar subquery?
3. When to create a View?
4. What is the difference between View and Table?

View and View creation

• View stores Select clause, not data
• Should make often-use Select clause to View
• Subquery is one-time View

Scalar subquery

• Return a single value
• combine with comparison operators
• Can be used anywhere you want to use constant or column name

A powerful example (Scalar subquery)

Correlated Query

When comparing within a subdivided group, you need to use a correlated subquery.

right:

wrong:

Chapter 6 Colorful Functions

Recall questions

1. How can you use CASE to transpose row and column?
2. What are Predicates?
3. How many categories of functions are there?

Categories of Functions

• Arithmetic ~
  • ABS()
  • MOD(n, p)
• String ~
  • REPLACE(object, string_be_replaced, string_to_replace)
  • SUBSTRING(object, FROM start_position FOR num_string_to_slice)
• Date ~
  • CURRENT_DATE
  • CURRENT_TIME
  • CURRENT_TIMESTMAP
  • EXTRACT(element FROM date)
• Convert ~
  • CAST(object AS data_type)
  • COALESCE(data1, data2, data3, ...)
    • Return first non-NULL value starting from the left
• Aggregation ~ (only five)

NB! Almost all functions return NULL when input parameter is NULL.

Predicates (which returns boolean-like values)

• LIKE
• BETWEEN
• IS NULL, IS NOT NULL
• IN
• EXISTS
  • Often EXISTS'S parameter is correlated subquery

NB!

1. In most cases, NULL rows won't be selected when using predicates
2. Don't include NULL in NOT IN's parameter, otherwise there won't show any results

Use Case to transpose

Chapter 7 Set Operation

Recall questions

Row direction join (axis = 1)

• UNION
• INTERSECT (same grammar with UNION)
• UNION ALL
• INTERSECT ALL
• EXCEPT

Column direction join (axis = 0)

• INNER JOIN
• LEFT/RIGHT OUT JOIN
• CROSS JOIN

Chapter 8 Advanced Topics: Window Function & Grouping Operator

Recall questions

Window Function can only be used in SELECT clause, not in WHERE and GROUP BY because it executes on the result of WHERE or GROUP BY.

Categories of Window Function

• Aggregation function(SUM, AVG, COUNT, MAX, MIN)
• Dedicated window function(RANK, DENSE, ROW, NUMBER)

Features of Window Function

• Group data (PARTITION BY is not necessary, you can treat the whole table as a group)
• Order data
• But not aggregate data (therefore, result has no less rows)

Aggregation window function (attention!)

Current record as benchmark to calculate stats is the biggest feature of Aggregation of window function.

Moving average (how to use Frame)

• PRECEDING
• FOLLOWING

Grouping operator

• ROLLUP (n+1)

  • From small aggregation to large aggregation
  • GROUPING Function to identify different NULLs

• CUBE (2ⁿ⁾
  

• GROUPING SETS