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

原创已于 2022-04-25 23:48:35 修改 · 2.6k 阅读

15 ·

CC 4.0 BY-SA版权

文章标签：

#sql #linq #数据库

于 2022-02-19 22:52:03 首次发布

MySQL练习专栏收录该内容

7 篇文章

订阅专栏

本文档通过SQL查询实现A.找出同时在三个部门工作的员工姓名；B.按工资总额降序列出各部门员工；C.查找与部门经理住址相同的员工信息；以及D.为财务部门员工调整工资。涉及表结构操作和复杂查询技巧。

【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;