数据库第一次小测复习

给出这样的一个模式：

employee(person_name, street, city)
works(person_name, company_name, salary)
company(company_name, city)

找到薪水 $\ge$ 数据库中所有人的薪水的员工姓名

解：关系代数中没有MAX()函数，使用差集来解决：

1. 找出所有“不是最高薪水”的人。
2. 用“所有人”减去这些“不是最高薪水”的人。

$$\text{LowerPay} = \Pi_{works.person_name}(works \bowtie_{works.salary < d.salary} \rho_d(works))$$

$$\text{Result} = \Pi_{person_name}(works) - \text{LowerPay}$$

---

如何使用基本运算符描述$\div$运算：

基本思路：列出全集，用全集做差找出缺失的组合，再剔除缺失的这些

假设 $R$ 的属性是 $(A, B)$，$S$ 的属性是 $(B)$，我们要算 $R \div S$：

$T_1 = \Pi_A(R)$ 找到所有可能符合条件的

$T_2 = T_1 \times S$ 找到全集，产生所有可能的$(A,B)$组合

$T_3=T_2-R$ 得到缺失的部分

$T_4 = \Pi_A(T3)$ 只需要$A$

$Result = T_1 - T_4 = \Pi_A(R) - \Pi_A((\Pi_A(R) \times S) - R)$

---

标量子查询的练习：

In University Schema, find the sections that had the maximum enrollment in Fall 2017

使用with子查询，首先计算一遍每个sec的选课人数；然后再使用标量子查询：

with cte as (
    select course_id, sec_id, count(*) as enrollment
    from takes
    where takes.year = 2017 and takes.semester = 'Fall'
    group by course_id, sec_id, year, semester
    -- 这里带不带year semester无所谓，where已经筛过了
)
select course_id, sec_id
from cte
where enrollment = (select max(enrollment) from cte)

凡是出现在 SELECT 后面、但没有被聚合函数（如 COUNT, SUM）包裹的列，必须全部写在 GROUP BY 后面。

所谓标量子查询，就是这一句：where enrollment = (select max(enrollment) from cte)。

当然，也可以用窗口函数解决此题目：

with cte as (
    select course_id, sec_id, count(*) as cnt
        rank() over (
            partition by year, semester
            order by count(*) desc
        ) as rk
    from takes
    where semester = 'Fall' and year = 2017
    group by course_id, sec_id, year, semester
)
select course_id, sec_id
from cte
where rk = 1

---

-- Find each customer who has an account at *every* branch located in 'Brooklyn'
select customer.ID, customer.customer_name
from customer
natural join depositor
join account on account.account_number = depositor.account_number
join branch on branch.branch_name = account.branch_name
where branch.branch_city = 'Brooklyn'
group by customer.ID, customer.customer_name, account.branch_city
having count(distinct branch.branch_name) = (select count(*) from branch where branch_city = 'Brooklyn')

这题易错点在于最后一个地方需要是distinct branch.branch_name，因为比如一个支行开很多户，不符合题意。

---