SELECT DISTINCT -- 去重，只返回唯一的行。
SELECT ALL -- 返回所有行（包括重复的），默认。

Aggerate

select avg(salary) -- ignore null automatically

select count(distinct ID)

select count(*)

select count(distinct *) -- This is not legal

-- Query table
SELECT * FROM students
WHERE class = 5   -- <> means not equal  
ORDER BY grade, age DESC
LIMIT 5 OFFSET 5;

-- Query table
SELECT * FROM students
WHERE class = 5
ORDER BY grade, age DESC;

-- Query table
SELECT class, AVG(grade) AS avg
FROM students
GROUP BY class
HAVING grade >= 80
ORDER BY avg DESC;

-- Select few columns (rename)
SELECT name AS 'Titles', grade AS 'Score'
FROM movies;

SELECT AVG(grade) FROM students;
-- AVG, SUM, MAX, MIN, COUNT, ROUND(<>,#)

-- calculate total rows
SELECT COUNT(*) FROM students;

-- ignore NULL
SELECT COUNT(grade) FROM students;

SELECT COUNT(DISTINCT class) FROM students;

Set operation

union intersect except

each of the above eliminates duplicates, use below for repetition:

union all, intersect all, except all

except all the number of the duplicates is the subtraction

  1. 考虑“出现次数”（duplicates）。对每个值 v，结果里出现次数 = max(0, 左边次数 − 右边次数)。

    举例：

    左边( Fall 2017 ) 选出 A, A, B

    右边( Spring 2018 ) 选出 A, C

UNION

INTERSECT