数据库SQL语句--多表查询 上机练习（2）

--创建数据库：
create database 航空信息系统;

--创建表
create table 机型表
( 机型编号  nchar(2) primary key,
  飞机名称  nvarchar(20) unique,
  航程 int 
)
create table 员工信息表
( 员工号  nchar(4) primary key,  
  姓名  nvarchar(20), 
  薪水  int,  
  入职年份 smallint 
)
create table 准驾机型表 --请注意，不是每个员工都可以驾驶飞机
(员工号  nchar(4),  
 机型编号  nchar(2),  
 证书年份 smallint, 
 primary key (员工号, 机型编号), 
 foreign key (员工号)  references 员工信息表(员工号),
 foreign key (机型编号)  references 机型表(机型编号)
)

--插入表数据
--员工表
insert into 员工信息表
values('e001','孔明',80000,2005)
insert into 员工信息表
values('e002','玄德',10000,1995)
insert into 员工信息表
values('e003','云长',80000,1995)
insert into 员工信息表
values('e004','翼德',100000,1995)
insert into 员工信息表
values('e005','子龙',120000,1998)
insert into 员工信息表
values('e006','孟起',70000,2015)
insert into 员工信息表
values('e007','汉升',80000,2012)
insert into 员工信息表
values('e008','公谨',110000,2002)
insert into 员工信息表
values('e009','子敬',90000,2000)
insert into 员工信息表
values('e010','仲谋',20000,2001)
insert into 员工信息表
values('e011','孟德',30000,1990)
insert into 员工信息表
values('e012','奉孝',110000,1992)
insert into 员工信息表
values('e013','元直',60000,2001)
insert into 员工信息表
values('e014','文远',90000,1998)
insert into 员工信息表
values('e015','奉先',120000,1990)
--机型表
insert into 机型表
values('01','波音707',3000)
insert into 机型表
values('02','波音737',5000)
insert into 机型表
values('03','波音757',7000)
insert into 机型表
values('04','波音777',9000)
insert into 机型表
values('05','a300',2000)
insert into 机型表
values('06','a310',4000)
insert into 机型表
values('07','a330',6000)
insert into 机型表
values('08','a340',8000)
insert into 机型表
values('09','歼20',5000)
insert into 机型表
values('10','飞豹',4000)
insert into 机型表
values('11','苏-27',5000)
insert into 机型表
values('12','苏-33',6000)
--准驾机型
insert into 准驾机型表
values('e001','01',1990)
insert into 准驾机型表
values('e001','03',1995)
insert into 准驾机型表
values('e002','02',1999)
insert into 准驾机型表
values('e002','04',1998)
insert into 准驾机型表
values('e002','08',2000)
insert into 准驾机型表
values('e003','06',2001)
insert into 准驾机型表
values('e003','07',2002)
insert into 准驾机型表
values('e004','09',2003)
insert into 准驾机型表
values('e004','02',1998)
insert into 准驾机型表
values('e004','03',1999)
insert into 准驾机型表
values('e004','04',2011)
insert into 准驾机型表
values('e007','01',2013)
insert into 准驾机型表
values('e007','05',2014)
insert into 准驾机型表
values('e008','03',2018)
insert into 准驾机型表
values('e008','07',1999)
insert into 准驾机型表
values('e009','09',2007)
insert into 准驾机型表
values('e012','01',2015)
insert into 准驾机型表
values('e012','04',2016)
insert into 准驾机型表
values('e013','05',2017)
insert into 准驾机型表
values('e013','06',2016)
insert into 准驾机型表
values('e013','07',2015)

--查询表数据
select * from 机型表;
select * from 员工信息表;
select * from 准驾机型表;

--1、查询飞行员薪水不少于80000的飞机名称
select 飞机名称 from 机型表
where 机型编号 in
(select 准驾机型表.机型编号
from 员工信息表,准驾机型表
where 薪水>=80000 and 员工信息表.员工号=准驾机型表.员工号);

--2、查询满足以下条件的飞机名称：能驾驶该飞机的所有飞行员的工资都不少于80000
--答案(1)：
select 飞机名称
from 准驾机型表,员工信息表,机型表
where 机型表.机型编号=准驾机型表.机型编号 and 员工信息表.员工号=准驾机型表.员工号
group by 飞机名称
having min(薪水)>=80000
--答案(2)：
select 飞机名称
from 机型表 join 准驾机型表 on 机型表.机型编号 =准驾机型表.机型编号
           join 员工信息表 on 员工信息表.员工号=准驾机型表.员工号
group by 飞机名称
having min(员工信息表.薪水)>=80000;

--3、列出可以驾驶三种及以上机型的驾驶员的员工号
select * from
(select 员工号,count(*) as 驾驶机型数量
from 准驾机型表
group by 员工号) as newtable
where 驾驶机型数量>=3;

--4、对于每个能驾驶3种及以上机型的飞行员，列出其员工号，及其所能驾驶飞机中的最大航程。
--答案(1)：
select newtable_2.员工号,max(机型表.航程) as 最大航程
from 机型表,
(select 员工号,机型编号 from 准驾机型表 where 员工号 in 
(select 员工号 from (select 员工号,count(*) as 驾驶机型数量 from 准驾机型表 group by 员工号) as newtable_1 where 驾驶机型数量>=3)) as newtable_2
where newtable_2.机型编号=机型表.机型编号
group by newtable_2.员工号;
--答案(2)：
select  员工号,max(航程) as 可驾驶飞机的最大航程
from 机型表 a join 准驾机型表 b on a.机型编号=b.机型编号 
group by 员工号	--根据员工号分组，机型编号三个以上的输出
having count(*)>=3;
--验证答案：
select * from 机型表,准驾机型表
where 机型表.机型编号=准驾机型表.机型编号;

--5、列出飞行员的平均薪水和所有员工的平均薪水的差额。
--答案(1)：
select ABS(员工平均薪水-飞行员平均薪水) from
(select avg(薪水) as 员工平均薪水 from 员工信息表) as newtable1,
(select avg(薪水) as 飞行员平均薪水 from 员工信息表
where 员工信息表.员工号 in (select distinct 准驾机型表.员工号 from 员工信息表,准驾机型表 where 员工信息表.员工号=准驾机型表.员工号)) as newtable2;
--ABS()函数取绝对值
--所有员工平均薪水：select avg(薪水) as 员工平均薪水 from 员工信息表;
--飞行员员工薪水：select avg(薪水) as 飞行员平均薪水 from 员工信息表 where 员工信息表.员工号 in (select distinct 准驾机型表.员工号 from 员工信息表,准驾机型表 where 员工信息表.员工号=准驾机型表.员工号);
--原做法（错误：员工号重复）
select 员工平均薪水-飞行员平均薪水 from
(select avg(薪水) as 员工平均薪水 from 员工信息表) as newtable1,
(select avg(薪水) as 飞行员平均薪水 from 员工信息表,准驾机型表 where 员工信息表.员工号=准驾机型表.员工号) as newtable2;
--答案(2)：
select 
 (select avg(薪水) from 员工信息表 where 员工号 in (select 员工号 from 准驾机型表))
  -
 (select avg(薪水) from 员工信息表)
 as 平均薪水差额;

--6、列出能够驾驶飞机机型最多的飞行员的员工号。请注意：你不知道这样的员工有几个。
select 员工号 from (select 员工号,count(*) as 驾驶机型数量 from 准驾机型表 group by 员工号) as newtable2
where 驾驶机型数量=(select max(驾驶机型数量) as 驾驶机型数量
from (select 员工号,count(*) as 驾驶机型数量 from 准驾机型表 group by 员工号) as newtable);
--验证：
select 员工号,count(*) as 驾驶机型数量 from 准驾机型表 group by 员工号;
select max(驾驶机型数量) as 驾驶机型数量 from(select 员工号,count(*) as 驾驶机型数量 from 准驾机型表 group by 员工号) as newtable;

--7、列出可以驾驶波音飞机的飞行员名字（飞机名以“波音”开头）
select 姓名 from 员工信息表 where 员工号 in (
select distinct 员工号 from 准驾机型表 where 机型编号 in (
select 机型编号 from 机型表 where 飞机名称 like '波音%'));
--验证：
select 员工信息表.姓名,准驾机型表.机型编号,机型表.飞机名称 from 准驾机型表,机型表,员工信息表
where (准驾机型表.机型编号=机型表.机型编号) and (员工信息表.员工号=准驾机型表.员工号);

--8、列出只能驾驶航程大于4500的飞机的飞行员名字。请注意：姓名不是员工信息表的主键，也没有unique约束，意味着姓名有可能出现重复值。
--只能驾驶航程大于4500的飞机就是该飞行员驾驶的飞机的航程都是大于4500的
--答案(1):
select 姓名
from 员工信息表,(select 员工号 from 准驾机型表,机型表 where 准驾机型表.机型编号=机型表.机型编号 group by 员工号 having min(航程)>4500) as newtable
where 员工信息表.员工号=newtable.员工号
--答案(2)：
select 姓名
from 员工信息表 a join  (select 员工号
  from 准驾机型表 b Join  机型表 c on  b.机型编号=c.机型编号
  group by 员工号
  having min(航程)>4500) d
on a.员工号=d.员工号
--如果姓名唯一，也可以按照下面方法：
select 姓名
from 员工信息表 a join 准驾机型表 b on a.员工号=b.员工号
     Join  机型表 c on b.机型编号=c.机型编号
group by 姓名 
having min(航程)>4500
--只能驾驶的意思是该飞行员驾驶的飞机的航程都是大于4500的，原做法列出的是能驾驶航程大于4500的飞行员的名字，而不是只能驾驶航程大于4500的飞行员的名字
--原做法：
select 姓名 from 员工信息表 where 员工号 in (
select distinct 员工号 from 准驾机型表 where 机型编号 in (
select 机型编号 from 机型表 where 航程>4500))

--9、列出不会开“歼20”飞机的飞行员姓名。注意，是飞行员，不是员工
--答案(1)：
select 姓名 from 员工信息表 where 员工号 in (
select distinct 员工号 from 准驾机型表 where 员工号 not in (
select distinct 员工号 from 准驾机型表 where 机型编号 in (
select 机型编号 from 机型表 where 飞机名称 like '%歼20%')));
--答案(2):
select 姓名 
from 员工信息表 
where  员工号 in (
  select 员工号 from 准驾机型表
  where 员工号 not in
      (select 员工号 
	   from 准驾机型表 a join 机型表 b on a.机型编号 =b.机型编号 
	   where b.飞机名称 ='歼20')
  )
--验证：
select 员工信息表.姓名,准驾机型表.机型编号,机型表.飞机名称 from 准驾机型表,机型表,员工信息表
where (准驾机型表.机型编号=机型表.机型编号) and (员工信息表.员工号=准驾机型表.员工号);

--10、查询截止2023年，入职时间大于等于20年，薪水大于等于100000的。非飞行员员工的员工号和姓名
--答案(1)：
select 员工号,姓名 from 
(select * from 员工信息表 where 员工号 not in (select distinct 员工号 from 准驾机型表)) as newtable 
 where year(getdate())-入职年份>=20 and 薪水 >=100000;
 --验证：
select distinct 员工信息表.员工号,员工信息表.姓名,员工信息表.薪水 from 准驾机型表,机型表,员工信息表
where 员工信息表.员工号 not in (select distinct 员工号 from 准驾机型表)
--答案(2)：
select 员工号,姓名 
from 员工信息表
where 薪水 >=100000 and (2023-入职年份) >=20
      and 员工号 not in (select 员工号 from 准驾机型表)