Udacity Data Scientist Nanodegree : Prerequisite — SQL(L3, L4, L5)

Lesson 3: Aggregating / Lesson 4: Subquery, temporary tables / Lesson 5: Data Cleaning

NULL — No data

WHERE variable IS NULL    

COUNT

SELECT COUNT(*)
FROM accounts;

SUM

Aggregation Reminder

MIN / MAX

AVG — Average

Tips — Median

SELECT *
FROM (SELECT total_amt_usd
FROM orders
ORDER BY total_amt_usd
LIMIT 3457) AS Table1
ORDER BY total_amt_usd DESC
LIMIT 2;

GROUP BY

所以這個程式碼會有錯,因為只有後三個被折疊到

GROUP BY — Expert Tip (看不懂)

DISTINCT

SELECT DISTINCT column1, column2, column3
FROM table1;
SELECT DISTINCT column1, DISTINCT column2, DISTINCT column3
FROM table1;

DISTINCT — Expert Tip

HAVING

HAVING — Expert Tip

DATE function

Lesson 4: Subquery

Subquery

WITH — CTE

跟上面那張圖一樣,更快

Lesson 5: Data cleaning

LEFT & RIGHT

POSITION, STRPOS, & SUBSTR

CONCAT

CAST

COALESCE = IFNULL

理科與藝術交織成靈魂的會計人,喜愛戲劇與攝影,但也喜歡資料科學。