【大数据开发】MySQL数据库——MySQL查询语句练习day26

现在有两个表
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