【SQL题】根据表结构，完成以下查询

【SQL题】根据表结构，完成以下查询

A.求同时在三个部门工作的雇员姓名
B.计算各部门的职工的工资总额,并按照总额大小降序排列
C.查询和其部门经理住址相同的员工的姓名及经理姓名
D.为财务部门的雇员加薪,5年以下加5%,5年以上加3%
E.删除工作年限为0的员工记录,若员工删除后某部门下没有任何员工,删除该部门

 1.创建表格：

-- 删除时，先删除外键约束，再删除主键约束
drop table work;
drop table employee;
drop table department;

create table employee(
	empid varchar(4) primary key,
	empname varchar(20) not null,
	age int,
	address varchar(50),
	sal int

);
create table department(
	deptno varchar(4) primary key,
	dname varchar(20) not null,
	mgr varchar(4)-- 部门经理
	
);
create table work(
	empid varchar(4),
	deptno varchar(4),
	worktime int,

	constraint fk_work_deptno foreign key(deptno) REFERENCES department(deptno)
);
insert into employee 
values('E01','tom',35,'海淀',8000),
('E02','dennis',24,'朝阳',5000),
('E03','john',20,'海淀',4000),
('E04','rick',41,'朝阳',7000),
('E05','bill',45,'西域',7500),
('E06','frank',24,'海淀',6000),
('E07','jank',25,'东域',3000),
('E08','mike',23,'东域',3500);
insert into department 
values
('D01','人事','E04'),
('D02','信息','E01'),
('D03','销售','E05'),
('D04','财务','E06');
insert into work 
values
('E01','D02',6),
('E01','D03',1),
('E01','D04',2),
('E02','D01',2),
('E02','D04',0),
('E03','D02',0),
('E04','D01',4),
('E04','D02',1),
('E05','D03',2),
('E06','D04',4),
('E07','D04',1),
('E08','D03',2);

2.实现查询

-- A.求同时在三个部门工作的雇员姓名
select empname from employee e where (select count(deptno) from work w where w.empid=e.empid)=3;
-- B.计算各部门的职工的工资总额,并按照总额大小降序排列
select d.dname,sum(e.sal) as sum from employee e,department d,work w 
where e.empid = w.empid and w.deptno = d.deptno
group by d.dname 
order by sum desc;
-- C.查询和其部门经理住址相同的员工的姓名及经理姓名
select d.empname,c.leader from(select deptno,empname as leader,address from employee a,department b where a.empid=b.mgr)c,employee d,work e 
where c.deptno=e.deptno and e.empid=d.empid and d.address=c.address and d.empname!=c.leader;