【MySql 数据库综合练习02】

MySql 数据库综合练习02

提示：部分数据不满足查询要求，即结果为null

---

---

提示：以下均为内容为 题目 + sql查询语句 + 查询结果图
SQL执行顺序:
from ===> where ===> group by ===> having ===> select ===> order by ===> limit

一、数据库创建代码

二、题目

01、查询部门编号为30的部门的员工详细信息

-- 01、查询部门编号为30的部门的员工详细信息
SELECT *
FROM emp
WHERE deptno = '30'

02、查询从事clerk工作的员工的编号、姓名以及其部门号

-- 02、查询从事clerk工作的员工的编号、姓名以及其部门号
SELECT empno,ename,deptno
FROM emp
WHERE job = 'clerk'

03、查询奖金多于基本工资的员工的信息

-- 03、查询奖金多于基本工资的员工的信息
SELECT *
FROM emp
WHERE comm > sal

04、查询奖金多于基本工资60%的员工的信息

-- 04、查询奖金多于基本工资60%的员工的信息
SELECT *
FROM emp 
WHERE comm > (sal*0.6)

05、查询部门编号为10的部门经理 (job=manager) 和部门编号为20的部门中工作为CLERK的职员信息

-- 05、查询部门编号为10的部门经理 (job=manager)   和部门编号为20的部门中工作为CLERK的职员信息
SELECT *
FROM emp
WHERE job='manager' OR  (deptno = '20' AND  job = 'CLERK')

06、查询部门编号为10的部门经理或部门编号为20的部门工作为CLERK的职员信息或者既不是经理也不是CLERK但是工资高于2000的员工信息

-- 06、查询部门编号为10的部门经理或部门编号为20的部门工作为CLERK的职员信息或者既不是经理也不是CLERK但是工资高于2000的员工信息
SELECT *
FROM emp
WHERE emp.job = 'manager' OR (emp.deptno = '20' AND emp.job ='CLERK') OR (emp.sal>2000 AND emp.job NOT IN('manager','CLERK') )

07、查询获得奖金的员工的信息(1、信息都在员工表中；获得奖金==comm is not null and comm>0)

-- 07、查询获得奖金的员工的信息(1、信息都在员工表中；获得奖金==comm is not null and comm>0)
SELECT *
FROM emp
WHERE emp.comm IS NOT NULL AND  emp.comm >0

08、查询奖金少于100或者没有获得奖金的员工的信息

-- 08、查询奖金少于100或者没有获得奖金的员工的信息
SELECT *
FROM emp
WHERE emp.comm IS NULL OR emp.comm<100

09、查询姓名以A、B、S开头的员工的信息

-- 09、查询姓名以A、B、S开头的员工的信息
SELECT *
FROM emp
WHERE emp.ename LIKE 'A%' OR  emp.ename LIKE 'S%' OR  emp.ename LIKE 'B%'

10、查询找到姓名长度为6个字符的员工的信息

-- 10、查询找到姓名长度为6个字符的员工的信息
SELECT *
FROM emp
WHERE LENGTH( emp.ename) =6;

11、查询姓名中不包含R字符的员工信息。

-- 11、查询姓名中不包含R字符的员工信息。
SELECT *
FROM emp 
WHERE emp.ename NOT LIKE '%R%';

12、查询员工的详细信息并按姓名排序

-- 12、查询员工的详细信息并按姓名排序
SELECT *
FROM emp
ORDER BY emp.ename ASC

13、查询员工的信息并按工作降序工资升序排列

-- 13、查询员工的信息并按工作降序工资升序排列
SELECT *
FROM emp
ORDER BY emp.sal ASC

14、计算员工的日薪(按30天计)

-- 14、计算员工的日薪(按30天计)
SELECT (emp.sal/30)  AS '日薪',emp.ename,emp.empno,emp.sal
FROM emp
ORDER BY 日薪

15、查询姓名中包含字符A的员工的信息

-- 15、查询姓名中包含字符A的员工的信息
SELECT *
FROM emp
WHERE emp.ename LIKE '%A%'

16、查询拥有员工的部门的部门名和部门号

-- 16、查询拥有员工的部门的部门名和部门号
SELECT dept.dname,dept.deptno
FROM dept
INNER JOIN emp ON (dept.deptno = emp.deptno)
GROUP BY dept.deptno

17、查询工资多于smith的员工信息。

-- 17、查询工资多于smith的员工信息。
SELECT *
FROM emp
WHERE emp.sal > (SELECT sal FROM emp WHERE emp.ename = 'smith')

18、查询员工和及其所属经理的姓名(同一个部门，job=manager)—自连接

-- 18、查询员工和及其所属经理的姓名(同一个部门，job=manager)---自连接
SELECT A.*,B.ename
FROM emp A
JOIN emp B ON (B.deptno = A.deptno)
WHERE B.job = 'manager' AND A.job <> 'manager'

19、查询雇佣日期早于其经理雇佣日期的员工及其经理姓名

-- 19、查询雇佣日期早于其经理雇佣日期的员工及其经理姓名
SELECT A.*,B.ename
FROM emp A
JOIN emp B ON (A.deptno = B.deptno)
WHERE B.job = 'manager' AND  A.hiredate < B.hiredate AND A.job <> 'manager'

20、查询员工姓名及其所在的部门名称

-- 20、查询员工姓名及其所在的部门名称
SELECT emp.ename,D.dname
FROM emp
JOIN dept D ON (emp.deptno = D.deptno)

21、查询工作为clerk的员工姓名及其所在部门名称

-- 21、查询工作为clerk的员工姓名及其所在部门名称\
SELECT emp.ename,D.dname
FROM emp
JOIN dept D ON (D.deptno = emp.deptno)
WHERE emp.job = 'clerk'

22、查询各部门号及其部门中的最低工资

-- 22、查询各部门号及其部门中的最低工资
SELECT D.deptno,D.dname,(SELECT MIN(sal) FROM emp WHERE deptno = D.deptno)
FROM emp 
JOIN dept D ON (D.deptno = emp.deptno )
GROUP BY D.deptno

23、查询销售部sales的所有员工的姓名

-- 23、查询销售部sales的所有员工的姓名
SELECT emp.ename
FROM emp
JOIN dept D ON (D.deptno = emp.deptno)
WHERE D.dname = 'sales'

24、查询工资水平大于平均工资的员工

-- 24、查询工资水平大于平均工资的员工
SELECT A.ename
FROM emp A
WHERE A.sal > (SELECT AVG(sal) FROM emp WHERE emp.deptno = A.deptno)

25、查询与SCOTT从事相同工作的员工。

-- 25、查询与SCOTT从事相同工作的员工。
SELECT *
FROM emp 
WHERE emp.job = (SELECT job FROM emp WHERE ename = 'scott') AND ename <> 'scott'

26、查询与部门编号为30的员工工资水平相同的员工信息

-- 26、查询与部门编号为30的员工工资水平相同的员工信息
SELECT emp.ename
FROM emp 
JOIN salgrade S ON (emp.sal BETWEEN S.losal AND S.hisal)
WHERE emp.deptno <> 30 AND grade  >= (SELECT AVG(grade)
FROM salgrade
WHERE (SELECT AVG(sal)
FROM emp 
WHERE emp.deptno='30')>= losal AND (SELECT AVG(sal)
FROM emp 
WHERE emp.deptno='30') <= hisal)

27、查询工资高于部门编号为30的部门所有员工工资水平的员工信息

-- 27、查询工资高于部门编号为30的部门所有员工工资水平的员工信息
SELECT emp.ename
FROM emp 
JOIN salgrade S ON (emp.sal BETWEEN S.losal AND S.hisal)
WHERE emp.deptno <> 30 AND grade  > (SELECT AVG(grade)
FROM salgrade
WHERE (SELECT AVG(sal)
FROM emp 
WHERE emp.deptno='30')>= losal AND (SELECT AVG(sal)
FROM emp 
WHERE emp.deptno='30') <= hisal)

28、查询部门号、部门名、部门所在位置及其每个部门的员工总数

-- 28、查询部门号、部门名、部门所在位置及其每个部门的员工总数
SELECT D.deptno,D.dname,D.loc,COUNT(emp.deptno)
FROM dept D
JOIN emp  ON  (D.deptno = emp.deptno)
GROUP BY emp.deptno

29、查询员工的姓名、工资及其所属部门

-- 29、查询员工的姓名、工资及其所属部门
SELECT emp.ename,emp.sal,D.dname
FROM emp
JOIN dept D ON(emp.deptno = D.deptno)

30、查询员工的详细信息(含部门名)

-- 30、查询员工的详细信息(含部门名)
SELECT emp.*,D.dname
FROM emp
JOIN dept D ON (emp.deptno = D.deptno)

31、查询各工作名称以及从事此工作的最低工资

-- 31、查询各工作名称以及从事此工作的最低工资
SELECT job,MIN(sal)
FROM emp 
GROUP BY emp.job

32、计算员工的年薪并且以年薪排序

-- 32、计算员工的年薪并且以年薪排序
SELECT (E.sal*12 + (SELECT (IF(comm IS NULL,0,comm)) FROM emp WHERE emp.empno =E.empno)) AS DESCSAL,E.ename
FROM emp E
ORDER BY DESCSAL ASC

33、查询工资为第4级别的员工的姓名及其工资

-- 33、查询工资为第4级别的员工的姓名及其工资
SELECT emp.ename,emp.sal
FROM emp 
WHERE emp.sal BETWEEN (SELECT losal  FROM  salgrade WHERE grade = 4 ) AND (SELECT hisal  FROM  salgrade WHERE grade = 4 )

34、查询工资为第3等级的职员名字、工资、所属部门以及部门所在地

-- 34、查询工资为第3等级的职员名字、工资、所属部门以及部门所在地
SELECT emp.ename,emp.sal,dept.dname,dept.loc
FROM emp 
JOIN salgrade ON (salgrade.grade = 3)
JOIN dept ON (dept.deptno = emp.deptno)
WHERE emp.sal BETWEEN salgrade.losal AND salgrade.hisal

35、查询工资等级大于smith的员工的信息

-- 35、查询工资等级大于smith的员工的信息
SELECT emp.*
FROM emp
JOIN salgrade ON (emp.sal BETWEEN salgrade.losal AND salgrade.hisal)
WHERE  salgrade.grade > (SELECT grade
FROM emp 
JOIN salgrade S ON (emp.sal BETWEEN S.losal AND S.hisal)
WHERE emp.ename = 'smith')