多表查询
一、实验目的
1、 掌握外键的概念，使用SELECT语句实现多表查询；
2、 掌握子查询、聚合函数的设计；
3、 掌握连接查询的设计等。
二、实验环境
操作系统：Windows11
应用软件：MySQL Navicat
三、实验内容
【壹】创建下面练习题中的四个表，分别为：
①dept表（部门表：部门编号，部门名称，部门位置）
②emp表（员工表：工号，姓名，职位，上级领导工号，入职日期，工资，部门编号）
③salgrade表（工资等级：等级，最低工资，最高工资）
④tbyear表（公司利润表：年份，利润）
【贰】请使用SQL命令完成以下操作：
1. 查出至少有一个员工的部门，显示部门编号、部门名称、部门位置、部门人数。
2. 列出薪金比关羽高的所有员工。
3. 列出所有员工的姓名及其直接上级的姓名。
4. 列出受雇日期早于直接上级的所有员工的编号、姓名、部门名称。
5. 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。
6. 列出所有文员的姓名及其部门名称，部门的人数。
7. 列出最低薪金大于15000的各种工作及从事此工作的员工人数。
8. 列出在销售部工作的员工的姓名，假定不知道销售部的部门编号。
9. 列出薪金高于公司平均薪金的所有员工信息，所在部门名称，上级领导，工资等级。
10.列出与庞统从事相同工作的所有员工及部门名称。
11.列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金、部门名称。
12.列出每个部门的员工数量、平均工资。
13.查出年份、利润、年度增长比
四、实验步骤和运行结果
【Ⅰ】在使用SQL命令之前，需要进行的操作有：
（1）创建数据库deep
CREATE DATABASE deep;
USE deep;（2）分别创建四个表
-- 部门表
CREATE TABLE dept (
deptno INT PRIMARY KEY auto_increment,-- 部门编号
dname VARCHAR ( 14 ), -- 部门名字
loc VARCHAR ( 13 ) -- 地址
);
-- 员工表
create table emp(
empno int primary key auto_increment,-- 员工编号
ename varchar(10), -- 员工姓名 -
job varchar(9), -- 岗位
mgr int, -- 直接领导编号
hiredate date, -- 雇佣日期，入职日期
sal int, -- 薪水
comm int, -- 提成
deptno int not null, -- 部门编号
foreign key (deptno) references dept(deptno)
);
-- 工资等级表
create table salgrade(
grade INT, -- 等级
losal DECIMAL(10, 2), -- 最低工资
hisal DECIMAL(10, 2) -- 最高工资
);
-- 公司利润表
create table tbyear (
year INT,-- 年份
zz DECIMAL(10, 2) -- 利润
);（3）为这四张表添加数据
INSERT INTO dept VALUES ('10', '教研部', '北京');
INSERT INTO dept VALUES ('20', '学工部', '上海');
INSERT INTO dept VALUES ('30', '销售部', '深圳');
INSERT INTO dept VALUES ('40', '财务部', '广州');
INSERT INTO dept VALUES ('50', '董事会', '太原');
INSERT INTO emp VALUES ('1001', '甘宁', '文员', '1013', '2000-12-17', '8000.00', null, '20');
INSERT INTO emp VALUES ('1002', '黛绮丝', '销售员', '1006', '2001-02-20', '16000.00', '3000.00', '30');
INSERT INTO emp VALUES ('1003', '殷天正', '销售员', '1006', '2001-02-22', '12500.00', '5000.00', '30');
INSERT INTO emp VALUES ('1004', '刘备', '经理', '1009', '2001-04-02', '29750.00', null, '20');
INSERT INTO `emp` VALUES ('1005', '谢逊', '销售员', '1006', '2001-09-28', '12500.00', '14000.00', '30');
INSERT INTO emp VALUES ('1006', '关羽', '经理', '1009', '2001-05-01', '28500.00', null, '30');
INSERT INTO `emp` VALUES ('1007', '张飞', '经理', '1009', '2001-09-01', '24500.00', null, '10');
INSERT INTO emp VALUES ('1008', '诸葛亮', '分析师', '1004', '2007-09-01', '30000.00', null, '20');
INSERT INTO emp VALUES ('1009', '曾阿牛', '董事长', null, '2001-11-17', '50000.00', null, '10');
INSERT INTO emp VALUES ('1010', '韦一笑', '销售员', '1006', '2001-09-08', '15000.00', '0.00', '30');
INSERT INTO emp VALUES ('1011', '周泰', '文员', '1008', '2007-05-28', '11000.00', null, '20');
INSERT INTO emp VALUES ('1012', '程普', '文员', '1006', '2001-12-03', '9500.00', null, '30');
INSERT INTO emp VALUES ('1013', '庞统', '分析师', '1004', '2001-12-09', '30000.00', null, '20');
INSERT INTO emp VALUES ('1014', '黄盖', '文员', '1007', '2002-01-23', '13000.00', null, '10');
INSERT INTO emp VALUES ('1015', '张三', '保洁员', '1009', '1999-09-09', '80000.00', '90000.00', '50');
INSERT INTO salgrade VALUES ('1', '7000', '12000');
INSERT INTO salgrade VALUES ('2', '12000', '14000');
INSERT INTO salgrade VALUES ('3', '14000', '20000');
INSERT INTO salgrade VALUES ('4', '20000', '30000');
INSERT INTO salgrade VALUES ('5', null, '90000');
INSERT INTO tbyear VALUES ('2010', '100');
INSERT INTO tbyear VALUES ('2011', '150');
INSERT INTO tbyear VALUES ('2012', '250');
INSERT INTO tbyear VALUES ('2013', '800');
INSERT INTO tbyear VALUES ('2014', '1000');【Ⅱ】创建完成之后，接下来使用SQL命令完成下列操作：
--1. 查出至少有一个员工的部门。显示部门编号、部门名称、部门位置、部门人数。
SELECT d.deptno,d.dname,d.loc,e1.`count(*)`
FROM dept d INNER JOIN (SELECT deptno,COUNT(*) FROM emp GROUP BY deptno) e1
ON d.deptno=e1.deptno;
-- 2. 列出薪金比关羽高的所有员工。
SELECT *
FROM emp e
WHERE e.sal>(SELECT sal FROM emp WHERE ename='关羽');
-- 3. 列出所有员工的姓名及其直接上级的姓名。
SELECT e1.ename,e2.ename 上级
FROM emp e1 LEFT OUTER JOIN emp e2
ON e1.mgr=e2.empno;
-- 4. 列出受雇日期早于直接上级的所有员工的编号、姓名、部门名称。
SELECT e1.empno,e1.ename,d.dname
FROM emp e1 LEFT OUTER JOIN emp e2 ON e1.mgr=e2.empno
LEFT OUTER JOIN dept d ON e1.deptno=d.deptno
WHERE e1.hiredate<e2.hiredate;
-- 5. 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门
SELECT d.dname,e.*
FROM dept d LEFT OUTER JOIN emp e
ON d.deptno=e.deptno;
-- 6. 列出所有文员的姓名及其部门名称，部门的人数
SELECT e.ename,d.dname,z.`count(*)`
FROM emp e INNER JOIN dept d ON e.deptno=d.deptno
INNER JOIN (SELECT deptno,COUNT(*) FROM emp GROUP BY deptno)z ON z.deptno=d.deptno
WHERE e.job='文员';
-- 7. 列出最低薪金大于15000的各种工作及从事此工作的员工人数
SELECT job,COUNT(*)
FROM emp
GROUP BY job
HAVING MIN(sal)>15000;
-- 8. 列出在销售部工作的员工的姓名，假定不知道销售部的部门编号。
SELECT e1.ename
FROM emp e1 INNER JOIN dept d
ON e1.deptno=d.deptno
WHERE d.dname='销售部';
-- 9. 列出薪金高于公司平均薪金的所有员工信息，所在部门名称，上级领导，工资等级。
SELECT e1.*,e2.ename 上级,d.dname 部门名称,sal.`grade`
FROM emp e1 LEFT OUTER JOIN emp e2 ON e1.mgr=e2.empno
LEFT OUTER JOIN dept d ON e1.deptno=d.deptno
LEFT OUTER JOIN salgrade sal ON e1.sal BETWEEN losal AND hisal
WHERE e1.`sal`>(SELECT AVG(sal) FROM emp);
-- 10.列出与庞统从事相同工作的所有员工及部门名称。
SELECT e.ename,d.dname
FROM emp e INNER JOIN dept d
ON e.deptno=d.deptno
WHERE e.job=(SELECT job FROM emp WHERE ename='庞统');
-- 11.列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金、部门名称。
SELECT e.ename,e.sal,d.dname
FROM emp e LEFT OUTER JOIN dept d
ON e.deptno=d.deptno
WHERE e.sal>ALL(SELECT sal FROM emp WHERE deptno=30);
-- 12.列出每个部门的员工数量、平均工资。
SELECT d.dname,e1.*
FROM (SELECT e.deptno,COUNT(*),AVG(sal) FROM emp e GROUP BY e.deptno)e1 INNER JOIN dept d
ON e1.deptno=d.deptno;
-- 13.查出年份、利润、年度增长比
SELECT tb1.*,IFNULL(CONCAT((tb1.zz-tb2.zz)/tb2.zz*100,'%'),0) '年度增长比'
FROM tbyear tb1 LEFT OUTER JOIN tbyear tb2
ON tb1.`year`=tb2.`year`+1;
