第3章 SQL 习题 - 3.16

3.16 考虑图3-20中的雇员数据库，其中加下划线的是主码。给出下面每个查询对应的SQL表达式：

a.找出所有为"First Bank Corporation"工作的雇员名字。

数据库表的关系请参考习题3-9。根据我们的数据，现在我们找出“公司_1”的所有雇员吧。

select employee_name from works where company_name = '公司_1';

 employee_name 
---------------
 雇员_1
 雇员_2
(2 rows)

b.找出数据库中所有居住城市和公司所在城市相同的雇员。

select E.employee_name, C.company_name, E.city from 
(employee as E natural join works) join company as C using (company_name, city);

 employee_name | company_name |  city  
---------------+--------------+--------
 雇员_1        | 公司_1       | 城市_1
 雇员_3        | 公司_2       | 城市_2
 雇员_4        | 公司_2       | 城市_2
 雇员_5        | 公司_3       | 城市_3
 雇员_6        | 公司_3       | 城市_3
 雇员_2        | 公司_1       | 城市_1
(6 rows)

c. 找出数据库中所有居住的街道和城市与其经理相同的雇员。

select employee_name, manager_name from
employee natural join managers as M where (street, city) in (
	select street, city from employee where employee.employee_name = M.manager_name
);

 employee_name | manager_name 
---------------+--------------
 雇员_1        | 雇员_2
 雇员_2        | 雇员_2
 雇员_3        | 雇员_4
 雇员_4        | 雇员_4
 雇员_5        | 雇员_6
 雇员_6        | 雇员_6
(6 rows)

d.找出工资高于其所在公司雇员平均工资的所有雇员。

with avg_salary(company_name, avg_salary) as (
	select company_name, cast (avg(salary) as numeric(18, 2)) from works
	group by company_name
)
select employee_name, salary, avg_salary from
works join avg_salary using (company_name) where salary > avg_salary;

 employee_name |  salary  | avg_salary 
---------------+----------+------------
 雇员_4        | 11000.00 |   10500.00
 雇员_6        | 13000.00 |   12500.00
 雇员_2        |  9900.00 |    8950.00
(3 rows)

e.找出工资总和最小的公司。

with salary_sum(company_name, salary_sum) as (
	select company_name, sum(salary) from works
	group by company_name
) select min(salary_sum) from
salary_sum ;--group by company_name;

   min    
----------
 17900.00
(1 row)