第3章 SQL 习题 - 3.9

3.9 考虑下图所示的雇员数据库，其中加下划线的是主码。为下面每个查询写出SQL表达式：

employee(employee_name, street, city)
works(employee_name, company_name, salary)
company(company_name, city)
managers(employee_name, manager_name)

                                             习题3.9、3.10、3.16、3.17和3.20的雇员数据库

为了方便测试答案的正确性，在数据库中创建如下关系：

drop table if exists employee cascade;
drop table if exists works cascade;
drop table if exists company cascade;
drop table if exists managers cascade;
create table employee(
    employee_name varchar not null, 
    street varchar,
    city varchar, 
    primary key (employee_name)
);
create table works(
    employee_name varchar not null, 
    company_name varchar,
    salary numeric(18,2),
    primary key (employee_name)
);
create table company(
    company_name varchar not null, 
    city varchar,
    primary key (company_name)
);
create table managers(
    employee_name varchar not null,
    manager_name varchar,
    primary key (employee_name)
);
insert into employee values('雇员_1', '地址_1', '城市_1');
insert into employee values('雇员_2', '地址_1', '城市_1');
insert into employee values('雇员_3', '地址_2', '城市_2');
insert into employee values('雇员_4', '地址_2', '城市_2');
insert into employee values('雇员_5', '地址_3', '城市_3');
insert into employee values('雇员_6', '地址_3', '城市_3');

insert into works values('雇员_1', '公司_1', 8000);
insert into works values('雇员_2', '公司_1', 9000);
insert into works values('雇员_3', '公司_2', 10000);
insert into works values('雇员_4', '公司_2', 11000);
insert into works values('雇员_5', '公司_3', 12000);
insert into works values('雇员_6', '公司_3', 13000);

insert into company values('公司_1', '城市_1');
insert into company values('公司_2', '城市_2');
insert into company values('公司_3', '城市_3');

insert into managers values('雇员_1', '雇员_2');
insert into managers values('雇员_2', '雇员_2');
insert into managers values('雇员_3', '雇员_4');
insert into managers values('雇员_4', '雇员_4');
insert into managers values('雇员_5', '雇员_6');
insert into managers values('雇员_6', '雇员_6');

a.找出所有为"公司_1"工作的雇员名字及其居住城市。

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

 employee_name | company_name | salary  
---------------+--------------+---------
 雇员_1        | 公司_1       | 8000.00
 雇员_2        | 公司_1       | 9000.00
(2 rows)

b.找出所有为'公司_1'工作且薪金超过10000美元的雇员名字、居住街道和城市。

select E.employee_name, street, city from employee as E 
natural join works where company_name = '公司_1' and salary > 10000;

c. 找出数据库中所有不为'公司_1'工作的雇员。

select * from works where company_name != '公司_1';

 employee_name | company_name |  salary  
---------------+--------------+----------
 雇员_3        | 公司_2       | 10000.00
 雇员_4        | 公司_2       | 11000.00
 雇员_5        | 公司_3       | 12000.00
 雇员_6        | 公司_3       | 13000.00
(4 rows)

d.找出数据库中工资高于'公司_2'的每个雇员的所有雇员。

select * from works where salary > all(
	select salary from works where company_name = '公司_2'
);

 employee_name | company_name |  salary  
---------------+--------------+----------
 雇员_5        | 公司_3       | 12000.00
 雇员_6        | 公司_3       | 13000.00
(2 rows)

e. 假设一个公司可以在好几个城市有分部。找出位于'公司_2'所有所在城市的所有公司。

题目的意思是应该是找出'公司_2'所在的所有城市。

select distinct city from employee natural join
works where company_name = '公司_2';

  city  
--------
 城市_2
(1 row)

f.找出雇员最多的公司。

select company_name, count(employee_name) as employee_count from works
group by company_name having count(employee_name) >= all (
	select count(employee_name) from works 
	group by company_name
);

 company_name | employee_count 
--------------+----------------
 公司_1       |              2
 公司_2       |              2
 公司_3       |              2
(3 rows)

g. 找出平均工资高于'公司_1'平均工资的那些公司。

select company_name, cast(avg(salary) as numeric(18,2)) as avg_salary from works
group by company_name having avg(salary) > all (
	select avg(salary) from works 
	where company_name = '公司_1'
);

 company_name | avg_salary 
--------------+------------
 公司_2       |   10500.00
 公司_3       |   12500.00
(2 rows)