《資料庫系統》期末考複習 — SQL 篇

這邊有請 Gemini 之神幫忙整理，讚嘆 Geimini 🛐，偉哉 Gemini ✴️

SQL 基本語法

#

1. 骨架與基礎

#

這是最基礎的語法，一定要知道。

2.篩選與過濾

#

EXISTS 和 NOT EXISTS 比較難懂，後面會詳細解釋

3.連接與合併

#

4. 統計與分組

#

5. 集合運算

#

基本上比較會用到的是 EXCEPT

6. ❓ EXISTS 與 NOT EXISTS

#

(1). ESISTS

#

　　不要把 EXISTS 當成一般的篩選條件（像 age > 18），請把它想像成一個 「檢查開關」。

• 你可以把它想成一個迴圈，外層每拿出一筆資料，就會進去內層跑一次檢查，只要有找到符合條件的，就會離開迴圈。
• 它只回傳 True (有找到) 或 False (沒找到)。所以通常後面的子查詢會寫成 SELECT 1 因為我們不關心 SELECT 的結果。

舉例 :

SELECT *
FROM 外表 T1
WHERE EXISTS (            -- 1. 啟動檢查開關
    SELECT 1              -- 2. 慣例寫法：不在乎選什麼，有東西就好
    FROM 內表 T2
    WHERE T1.id = T2.id   -- 3. 像橋一樣，把外表和內表連起來
      AND T2.條件 = ...     -- 4. 使用 AND 加上其他檢查條件
);

PS : 外表 = 外部的查詢 ; 內表 = 子查詢

以這個例子來說，只要子查詢的 WHERE 條件有成立就會回傳 True

(2). NOT EXISTS

#

　　這是這份筆記最有價值的部分 ⬅️( Gemini 講的 )

• 口語翻譯：「我要找外表的資料，前提是…這筆資料在內表完全找不到紀錄。」
• 適用場景：
  • 找出「沒有…」的資料（例如：找出沒交過作業的學生）。
  • 取代 NOT IN (為了安全性，因為 NOT IN 遇到 NULL 會壞掉💥/查不到資料)。

舉例 :

SELECT c.name 
FROM customers c
WHERE NOT EXISTS (
    SELECT 1 
    FROM orders o 
    WHERE o.customer_id = c.id
);

如果子查詢的 WHERE o.customer_id = c.id 找不到任何一個成立的，就會回傳 True

霊符「關聯境界・十二試煉」

#

　　口語翻譯 : 「作業的 12 題 SQL 練習題」

題目

#

Consider the following relational database. Give an expression in the SQL to express each of the following queries:

employee (employee_name, street, city)

works (employee_name, company_name, salary)

company (company_name, city)

manages (employee_name, manager_name)

總之就是給你這樣的結構，要根據題意下 SQL

PS : 為了省字數，我直接寫 JOIN 但實際考試記得要用 INNER JOIN，雖然他們倆是一樣的意思，但因為教材用的是 INNER JOIN，所以還是以教材為主

第一試練

#

Find the names and cities of residence of all employees who work for First Bank Corporation.

這題簡單，題目我就不翻譯了，畢竟考試也是全英文考題🔠

順帶一提，像 city = 'First Bank' ，標準的 SQL 應該要用 ' 包起來，而不是 "，需要注意一下

SELECT city,employee_name
FROM employee  JOIN works AS w ON employee_name = w.employee_name
WHERE w.company_name = 'First Bank'

第二試練

#

Find the names of all employees in this database who live in the same city as the company for which they work

這題也算基本，不難🙂

SELECT employee_name
FROM employee  JOIN works AS w ON  employee_name=w.employee_name
WHERE city = w.city

第三試練

#

Find the names of all employees who live in the same city and on the same street as do their managers.

這題就有點複雜了🙃，要注意的是 manages 也是 employee 的一員，所以需要把 manages 的 employee_name 和 manager_name 去和 employee 做連結，因此會需要 2 個 employee，一個代表員工，另一個代表主管。

SELECT e.employee_name
FROM ( employee AS e JOIN manages AS m ON e.employee_name = m.employee_name ) 
        JOIN  employee AS s ON m.manager_name = s.employee_name
WHERE e.city = s.city 
AND   e.street = s.street 

第四試練

#

Find the names of all employees in this database who do not work for First Bank Corporation.

這題需要用到 NOT EXISTS， 核心思想是先用子查詢找到是 First Bank Corporation 的員工，再用 NOT EXISTS 找出不是 First Bank Corporation 的員工

SELECT w.employee_name
FROM works AS w
WHERE NOT EXISTS (
    SELECT 1
    FROM works AS w2 
    WHERE w2.employee_name = w.employee_name
    AND   w2.company_name = 'First Bank Corporation'
)

你可能想說也可以這樣寫⬇️

SELECT w.employee_name
FROM works AS w
WHERE w.company_name <> 'First Bank Corporation'

PS : 以防你不知道 : <> 是不等於的意思

但這樣寫有 Bug，比如像這樣:

如果有人同時打兩份工，這樣寫會出錯，因為第一列的 First Bank Corporation 會被過濾，但第二列的 7-11 不會，此時就會有問題。

第五試練

#

Find the names of all employees who earn more than every employee of “Small Bank Corporation”.

SELECT w.employee_name
FROM works AS w
WHERE w.salary > ALL(
    SELECT w2.salary
    FROM works AS w2
    WHERE w2.company_name = 'Small Bank Corporation'
)

第六試練

#

Find the company with the smallest payroll.

這題可能會看不懂題目在問什麼，它是在問：「找出發薪水總開銷（Payroll）最小的那間公司」，需要注意，因為是問公司，所以記得用 Group by 配合 Having ，不用 Group by 會變成計算員工的加總。

撰寫邏輯是 : 「我要找一間公司，它的薪水總和小於等於 ( <= ) 所有其他公司的薪水總和」

SELECT company_name
FROM works 
GROUP BY company_name
HAVING SUM(salary) <= ALL(
    SELECT SUM(salary)
    FROM works
    GROUP BY company_name
)

第七試練

#

Find those companies whose employees earn a higher salary, on average, than the average salary at First Bank Corporation.

這題要找平均薪水高於 First Bank Corporation 平均薪水的公司，一樣記得 GROUP BY ，GROUP BY 會放在 WHERE 或 FROM 的後面

SELECT company_name
FROM works
GROUP BY company_name
HAVING AVG(salary) >(
    SELECT AVG(salary)
    FROM works
    WHERE company_name = 'First Bank Corporation'
    GROUP BY company_name   -- 這一行其實可以不用寫，因為前面已經鎖定在 `First Bank Corporation` 的範圍
)

第八試練

#

Find all employees who earn more than the average salary of all employees of their company.

SELECT company_name
FROM works AS w
WHERE salary > (
    SELECT AVG(salary)
    FROM works AS w2
    WHERE w.company_name = w2.company_name
)

第九試練

#

Assume the companies may be located in several cities. Find all companies located in every city in which Small Bank Corporation is located.

這題算是最終 Boss ，針對這題下面舉個例子 :

假設 Small Bank Corporation ( SBC ) 在台北和高雄都有分公司，那麼，題目要找的公司，必須 「同時」 在台北和高雄都有分公司，才算合格。

• A 公司：{ 台北 } ➡️ 不行，因為缺了高雄
• B 公司：{ 台北, 高雄 }➡️ 可以，因為都有包含
• C 公司：{ 台北, 高雄, 紐約 }➡️ 可以，雖然多了紐約，但只要都有包含就可以

SELECT company_name
FROM company 
WHERE city IN(      --把 city 不是 'Small Bank Corporation' 的過濾掉
    SELECT city
    FROM company
    WHERE company_name='Small Bank Corporation'
)
BROUP BY company_name -- 記得 GROUP BY
-- 檢查：該公司「在 SBC 城市名單中」出現的次數，是否等於「SBC 總城市數」
HAVING COUNT(distinct city)=(
    SELECT COUNT(distinct city)
    FROM company
    WHERE company_name='Small Bank Corporation'    
)

第十試練

#

Modify the database so that Jones now lives in Newtown.

這題涉及到資料的修改，基本語法是 UPDATE、SET、WHERE

UPDATE employee
SET city = 'Newtown'
WHERE employee_name = 'Jones'

第十一試練

#

Give all employees of First Bank Corporation a 10 percent salary raise.

UPDATE works
SET salary = salary*1.1
WHERE company_name = 'First Bank Corporation'

第十二試練

#

Give all managers in this database a 10 percent salary raise, unless the salary would be greater than $100,000. In such cases, give only a 3 percent raise.

這題要注意兩段 SQL 的先後順序，不可以互換

UPDATE works
SET salary = salary * 1.03
WHERE employee_name IN (SELECT manager_name FROM manages)  -- 篩選出經理
  AND salary >= 100000;

UPDATE works
SET salary = salary * 1.1
WHERE employee_name IN (SELECT manager_name FROM manages)
  AND salary < 100000;