现在有两个表
emp员工表和dept部门表,完成下列查询操作
员工表
CREATE TABLE `emp` (
`empno` int(11) DEFAULT NULL,
`ename` varchar(50) DEFAULT NULL,
`job` varchar(50) DEFAULT NULL,
`mgr` int(11) DEFAULT NULL,
`hiredate` date DEFAULT NULL,
`sal` decimal(7,2) DEFAULT NULL,
`comm` decimal(7,2) DEFAULT NULL,
`deptno` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
dept部门表
CREATE TABLE `dept` (
`deptno` int(11) DEFAULT NULL,
`dname` varchar(14) DEFAULT NULL,
`loc` varchar(13) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
练习
USE mydb1;
1:统计emp表中员工的总数量
SELECT COUNT(empno)
FROM emp
2:统计emp表中获得奖金的员工的数量
SELECT COUNT(comm)
FROM emp
3:求出emp表中所有的工资累加之和
SELECT SUM(sal)
FROM emp
4:求出emp表中所有的奖金累加之和
SELECT SUM(comm)
FROM emp
5:求出emp表中员工的平均工资
SELECT AVG(IFNULL(sal,0))
FROM emp
6:求出emp表中员工的平均奖金
SELECT AVG(IFNULL(comm,0))
FROM emp
7:求出emp表中员工的最高工资
SELECT MAX(IFNULL(sal,0))
FROM emp
8:求出emp表中员工编号的最大值
SELECT MAX(empno)
FROM emp
9:查询emp表中员工的最低工资。
SELECT MIN(IFNULL(sal,0))
FROM emp
10:查询emp表中员工的人数,工资的总和,平均工资,奖金的最大值,奖金的最小值,并且对返回的列起别名。
SELECT COUNT(ename) 员工人数, SUM(sal) 工资总和, AVG(IFNULL(sal,0)) 平均工资, MAX(IFNULL(comm,0)) 奖金最大值, MIN(IFNULL(comm,0)) 奖金最小值
FROM emp
11:查询每个部门的最高工资
SELECT deptno,MAX(sal)
FROM emp
GROUP BY deptno
12:查询每个职位的平均工资
SELECT job,AVG(IFNULL(sal,0))
FROM emp
GROUP BY job
13:查询每个部门的人数
SELECT deptno,COUNT(empno)总数
FROM emp
GROUP BY deptno
14:查询员工工资都大于1000的部门信息
SELECT deptno
FROM emp
GROUP BY deptno
HAVING MIN(sal)>1000
15:查询每个领导(主管)的手下人数
SELECT e2.ename,COUNT(*)
FROM emp e1 JOIN emp e2
ON e1.mgr=e2.empno
GROUP BY e2.ename
16:查询每个部门中每个主管的手下人数
SELECT e2.deptno,e2.ename,COUNT(*)
FROM emp e1 JOIN emp e2
ON e1.mgr=e2.empno
GROUP BY e2.ename,e2.deptno
ORDER BY e2.deptno
17:查询emp表中每个部门的编号,人数,工资总和,最后根据人数进行升序排列,如果人数一致,根据工资总和降序排列。
SELECT deptno, COUNT(empno),SUM(sal)
FROM emp
GROUP BY deptno
ORDER BY COUNT(empno),SUM(sal) DESC
18:查询工资在1000~3000之间的员工信息,及其所在部门的编号,平均工资,最低工资,最高工资,根据平均工资进行升序排列。
SELECT *,AVG(IFNULL(sal,0)),MIN(sal),MAX(sal)
FROM emp
WHERE sal BETWEEN 1000 AND 5000
GROUP BY deptno
ORDER BY AVG(sal)
19:查询含有上级领导的员工,每个职业的人数,工资的总和,平均工资,最低工资,
最后根据人数进行降序排列,如果人数一致,根据平均工资进行升序排列
SELECT job, COUNT(*),SUM(IFNULL(sal,0)),AVG(IFNULL(sal,0)),MIN(IFNULL(sal,0))
FROM emp
WHERE mgr IS NOT NULL
GROUP BY job
ORDER BY COUNT(*) DESC,AVG(IFNULL(sal,0))
20:查询每个部门的平均工资 要求平均工资大于2000
SELECT deptno,AVG(sal)
FROM emp
GROUP BY deptno
HAVING AVG(IFNULL(sal,0))>2000
21:查询emp表中每个部门的平均工资高于2000的部门编号,部门人数,平均工资,最后根据平均工资降序排序
SELECT deptno, COUNT(*),AVG(IFNULL(sal,0))
FROM emp
GROUP BY deptno
HAVING AVG(IFNULL(sal,0))>2000
ORDER BY AVG(IFNULL(sal,0)) DESC
22:查询emp表中工资在1000-3000之间的员工,每个部门的编号,工资总和,平均工资,
过滤掉平均工资低于2000的部门,按照平均工资
进行升序排序。
SELECT deptno,SUM(sal),AVG(IFNULL(sal,0))
FROM emp
WHERE sal BETWEEN 1000 AND 3000
GROUP BY deptno
HAVING AVG(IFNULL(sal,0))<2000
23:查询emp表中不是以s开头,每个职位的名字,人数,工资总和,最高工资,过滤掉平均工资是3000的职位,
根据人数升序排序如果一直根据工资总和降序排序
SELECT e.ename, e.job,COUNT(*),SUM(sal),MAX(sal)
FROM emp e JOIN dept d
ON e.deptno=d.deptno
GROUP BY job
HAVING AVG(IFNULL(sal,0))!=3000 AND e.ename NOT LIKE 's%'
ORDER BY COUNT(*),SUM(sal) DESC
24:查询部门中最高的平均工资
SELECT MAX(t.avg_sal)
FROM (
SELECT AVG(sal) avg_sal
FROM emp
GROUP BY deptno
) t
25:查询部门平均工资最高的部门编号
SELECT t.deptno,MAX(t.avg_sal)
FROM (
SELECT deptno, AVG(sal) avg_sal
FROM emp
GROUP BY deptno
) t
26:查询emp表中工资最高的员工信息
SELECT *,MAX(sal)
FROM emp
27:查询emp表中工资大于平均工资的所有员工的信息
SELECT *
FROM emp e JOIN (
SELECT deptno, AVG(sal) avg_sal
FROM emp
) t
ON e.deptno=t.deptno
HAVING e.sal>(t.avg_sal)
28:查询工资高于20号部门最大工资的员工信息
SELECT *
FROM emp e1
WHERE e1.sal>(
SELECT MAX(sal) m
FROM emp e
WHERE e.deptno=20
)
29:查询和Jones相同工作的其他员工信息
SELECT *
FROM emp
WHERE job = (
SELECT job
FROM emp
WHERE ename='Jones'
)
30:查询工资最低的员工的同事们的信息 (同事=相同job)
SELECT e.*
FROM emp e JOIN (
SELECT job,MIN(sal) min_sal
FROM emp
GROUP BY job
)t
ON e.job=t.job
WHERE e.sal>t.min_sal
31:查询最后入职的员工信息
SELECT *
FROM emp
WHERE hiredate=(
SELECT MAX(hiredate)
FROM emp
)
32:查询名字为king的部门编号和部门名称
SELECT d.deptno,d.dname
FROM emp e JOIN dept d
ON e.deptno=d.deptno
WHERE e.ename='king'
33:查询有员工的部门信息(编号和部门名称)
SELECT p.deptno,p.dname
FROM emp e JOIN dept p
ON e.deptno=p.deptno
WHERE e.ename IS NOT NULL
34:查询平均工资最高的部门信息(最大难度)
SELECT d.*,MAX(t.avg_sal)
FROM dept d JOIN (
SELECT deptno dt, AVG(IFNULL(sal,0)) avg_sal
FROM emp
GROUP BY deptno
)t
ON d.deptno=t.dt
35:每个部门的人数,根据人数排序
SELECT d.*,COUNT(*)
FROM dept d JOIN emp e
ON d.deptno=e.deptno
GROUP BY deptno
36:每个部门中,每个主管的手下人数
SELECT e1.ename,COUNT(e1.empno)
FROM emp e1 JOIN emp e2
WHERE e1.empno=e2.mgr
GROUP BY e1.empno
37:拿最低工资的员工信息
SELECT *
FROM emp
WHERE sal=(
SELECT MIN(sal)
FROM emp
)
38:只有一个下属的主管信息
SELECT e2.*
FROM emp e1 JOIN emp e2
ON e1.mgr=e2.empno
GROUP BY e2.empno
HAVING COUNT(*)=1
39:工资多于平均工资的员工信息
SELECT *
FROM emp
WHERE sal>(
SELECT AVG(sal)
FROM emp
)
40:查询员工信息,部门名称
SELECT e.*,d.dname
FROM dept d JOIN emp e
ON d.deptno=e.deptno
41:员工信息,部门名称,所在城市
SELECT e.*,d.dname,d.loc
FROM dept d JOIN emp e
ON d.deptno=e.deptno