sql多表查询练习

比眉伴天荒 2022-03-14 08:36 357阅读 0赞

/创建部门表/   
CREATE TABLE dept(   
deptno INT PRIMARY KEY,   
dname VARCHAR(50),   
loc VARCHAR(50)   
);

/创建雇员表/   
CREATE TABLE emp(   
empno INT PRIMARY KEY,   
ename VARCHAR(50),   
job VARCHAR(50),   
mgr INT,   
hiredate DATE,   
sal DECIMAL(7,2),   
COMM DECIMAL(7,2),   
deptno INT,   
CONSTRAINT fk\_emp FOREIGN KEY(mgr) REFERENCES emp(empno)   
);

/创建工资等级表/   
CREATE TABLE salgrade(   
grade INT PRIMARY KEY,   
losal INT,   
hisal INT   
);

/插入dept表数据/   
INSERT INTO dept VALUES (10, '教研部', '北京');   
INSERT INTO dept VALUES (20, '学工部', '上海');   
INSERT INTO dept VALUES (30, '销售部', '广州');   
INSERT INTO dept VALUES (40, '财务部', '武汉');

/插入emp表数据/   
INSERT INTO emp VALUES (1009, '曾阿牛', '董事长', NULL, '2001-11-17', 50000, NULL, 10);   
INSERT INTO emp VALUES (1004, '刘备', '经理', 1009, '2001-04-02', 29750, NULL, 20);   
INSERT INTO emp VALUES (1006, '关羽', '经理', 1009, '2001-05-01', 28500, NULL, 30);   
INSERT INTO emp VALUES (1007, '张飞', '经理', 1009, '2001-09-01', 24500, NULL, 10);   
INSERT INTO emp VALUES (1008, '诸葛亮', '分析师', 1004, '2007-04-19', 30000, NULL, 20);   
INSERT INTO emp VALUES (1013, '庞统', '分析师', 1004, '2001-12-03', 30000, NULL, 20);   
INSERT INTO emp VALUES (1002, '黛绮丝', '销售员', 1006, '2001-02-20', 16000, 3000, 30);   
INSERT INTO emp VALUES (1003, '殷天正', '销售员', 1006, '2001-02-22', 12500, 5000, 30);   
INSERT INTO emp VALUES (1005, '谢逊', '销售员', 1006, '2001-09-28', 12500, 14000, 30);   
INSERT INTO emp VALUES (1010, '韦一笑', '销售员', 1006, '2001-09-08', 15000, 0, 30);   
INSERT INTO emp VALUES (1012, '程普', '文员', 1006, '2001-12-03', 9500, NULL, 30);   
INSERT INTO emp VALUES (1014, '黄盖', '文员', 1007, '2002-01-23', 13000, NULL, 10);   
INSERT INTO emp VALUES (1011, '周泰', '文员', 1008, '2007-05-23', 11000, NULL, 20);   
INSERT INTO emp VALUES (1001, '甘宁', '文员', 1013, '2000-12-17', 8000, NULL, 20);

/插入salgrade表数据/   
INSERT INTO salgrade VALUES (1, 7000, 12000);   
INSERT INTO salgrade VALUES (2, 12010, 14000);   
INSERT INTO salgrade VALUES (3, 14010, 20000);   
INSERT INTO salgrade VALUES (4, 20010, 30000);   
INSERT INTO salgrade VALUES (5, 30010, 99990);

1.查询出部门编号为30的所有员工   
SELECT \* FROM emp WHERE deptno=30;

2.所有销售员的姓名、编号和部门编号。  
SELECT e.ename,e.empno,e.deptno FROM emp e WHERE e.job='销售员';

3.找出奖金高于工资的员工。   
SELECT \* FROM emp WHERE COMM>sal;

4.找出奖金高于工资60%的员工。   
SELECT \* from emp where COMM>sal\*0.6;

5.找出部门编号为10中所有经理，和部门编号为20中所有销售员的详细资料。   
SELECT \* FROM emp where (deptno=10 and job='经理') or (deptno=20 and job='销售员');

6.找出部门编号为10中所有经理，部门编号为20中所有销售员，   
还有即不是经理又不是销售员但其工资大或等于20000的所有员工详细资料。  
SELECT \* FROM emp where (deptno=10 and job='经理') or (deptno=20 and job='销售员')   
or (job NOT IN ('销售员','经理') AND sal>=20000);

7.有奖金的工种。   
SELECT DISTINCT job FROM emp where COMM is not NULL ;

8.无奖金或奖金低于1000的员工。   
SELECT \* from emp WHERE COMM is NULL or emp.COMM<1000;

9.查询名字由三个字组成的员工。   
select \* from emp where ename LIKE '\_';

10.查询2000年入职的员工。   
select \* from emp where hiredate LIKE '2000%';

11.查询所有员工详细信息，用编号升序排序   
SELECT \* FROM emp ORDER BY empno ASC ;

12.查询所有员工详细信息，用工资降序排序，如果工资相同使用入职日期升序排序   
select \* from emp ORDER BY sal desc,hiredate asc;

13.查询每个部门的平均工资   
select deptno,avg(sal) from emp GROUP BY deptno;

14.求出每个部门的雇员数量。   
select deptno,count(1) from emp GROUP BY deptno;

15.查询每种工作的最高工资、最低工资、人数   
SELECT job,max(sal),min(sal),count(1) from emp group by job;

16.显示非销售人员工作名称以及从事同一工作雇员的月工资的总和，并且要满足从事同一工作的雇员的月工资合计大于50000，输出结果按月工资的合计升序排列   
select job,sum(sal) from emp where job<>'销售员' GROUP BY job HAVING sum(sal)>50000 ORDER BY sum(sal) asc;

17.查出至少有一个员工的部门。显示部门编号、部门名称、部门位置、部门人数。  
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;

18.列出受雇日期早于直接上级的所有员工的编号、姓名、部门名称。  
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

19.列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。  
SELECT d.dname,e.\*  
FROM dept d LEFT OUTER JOIN emp e  
ON d.deptno=e.deptno;

20.列出所有文员的姓名及其部门名称，部门的人数。  
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='文员'

21.列出最低薪金大于15000的各种工作及从事此工作的员工人数。  
SELECT job,COUNT(\*)   
FROM emp   
GROUP BY job   
HAVING MIN(sal)>15000

22.列出在销售部工作的员工的姓名，假定不知道销售部的部门编号。  
SELECT e1.ename  
FROM emp e1 INNER JOIN dept d  
ON e1.deptno=d.deptno  
WHERE d.dname='销售部'

23.列出薪金高于公司平均薪金的所有员工信息，所在部门名称，上级领导，工资等级。  
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)

24.列出与庞统从事相同工作的所有员工及部门名称。  
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='庞统')

25.列出薪金高于在部门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)

26.列出每个部门的员工数量、平均工资。  
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;

子查询练习  
1.查出至少有一个员工的部门。显示部门编号、部门名称、部门位置、部门人数。   
SELECT dept.,temp.cnt from dept ,(select deptno,count() cnt from emp GROUP BY deptno) temp WHERE dept.deptno=temp.deptno;

2.列出薪金比关羽高的所有员工。   
select \* from emp where sal>(SELECT sal from emp where ename='关羽');

3.列出所有员工的姓名及其直接上级的姓名。 因为是查询所有员工,所以要用左连接,不能用内连接   
SELECT e1.ename,ifnull(e2.ename,'BOSS') as lead from emp e1 LEFT JOIN emp e2 ON e1.mgr=e2.empno;

4.列出受雇日期早于直接上级的所有员工的编号、姓名、部门名称。   
SELECT e.empno, e.ename, d.dname   
FROM emp e LEFT JOIN emp m   
ON e.mgr=m.empno   
LEFT JOIN dept d ON e.deptno=d.deptno   
WHERE e.hiredate

多表查询  
返回拥有员工的部门名、部门号   
select d.deptno,d.dname from dept d,emp e where d.deptno = e.deptno group by d.deptno;

工资水平多于关羽的员工信息   
select e.\* from emp e where e.sal>(select sal from emp where ename='关羽’);

返回员工和所属上司的姓名   
select e1.ename,e2.ename lead from emp e1 LEFT JOIN emp e2 on e1.mgr=e2.empno ;

返回雇员的雇佣日期早于其上司雇佣日期的员工及其上司姓名   
SELECT e1.\*,e2.ename from emp e1,emp e2 where e1.mgr=e2.empno and e1.hiredate