MySQL 34道作业题

该文档是:MySQL数据库的34道题目巩固基础

博客连接:https://www.loveuluo.cn

日期:2020-11-25

准备工作三张表

emp表:

image-20201125102156470

salgrade表:

image-20201125102222782

dept表:

image-20201125102353468

建表语句:

DROP TABLE IF EXISTS EMP;
DROP TABLE IF EXISTS DEPT;
DROP TABLE IF EXISTS SALGRADE;

CREATE TABLE DEPT
       (DEPTNO int(2) not null ,
    DNAME VARCHAR(14) ,
    LOC VARCHAR(13),
    primary key (DEPTNO)
    );
CREATE TABLE EMP
       (EMPNO int(4)  not null ,
    ENAME VARCHAR(10),
    JOB VARCHAR(9),
    MGR INT(4),
    HIREDATE DATE  DEFAULT NULL,
    SAL DOUBLE(7,2),
    COMM DOUBLE(7,2),
    primary key (EMPNO),
    DEPTNO INT(2) 
    )
    ;

CREATE TABLE SALGRADE
      ( GRADE INT,
    LOSAL INT,
    HISAL INT );

INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
10, 'ACCOUNTING', 'NEW YORK'); 
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
20, 'RESEARCH', 'DALLAS'); 
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
30, 'SALES', 'CHICAGO'); 
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
40, 'OPERATIONS', 'BOSTON'); 
commit;
 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7369, 'SMITH', 'CLERK', 7902,  '1980-12-17'
, 800, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7499, 'ALLEN', 'SALESMAN', 7698,  '1981-02-20'
, 1600, 300, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7521, 'WARD', 'SALESMAN', 7698,  '1981-02-22'
, 1250, 500, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7566, 'JONES', 'MANAGER', 7839,  '1981-04-02'
, 2975, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7654, 'MARTIN', 'SALESMAN', 7698,  '1981-09-28'
, 1250, 1400, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7698, 'BLAKE', 'MANAGER', 7839,  '1981-05-01'
, 2850, NULL, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7782, 'CLARK', 'MANAGER', 7839,  '1981-06-09'
, 2450, NULL, 10); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7788, 'SCOTT', 'ANALYST', 7566,  '1987-04-19'
, 3000, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7839, 'KING', 'PRESIDENT', NULL,  '1981-11-17'
, 5000, NULL, 10); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7844, 'TURNER', 'SALESMAN', 7698,  '1981-09-08'
, 1500, 0, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7876, 'ADAMS', 'CLERK', 7788,  '1987-05-23'
, 1100, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7900, 'JAMES', 'CLERK', 7698,  '1981-12-03'
, 950, NULL, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7902, 'FORD', 'ANALYST', 7566,  '1981-12-03'
, 3000, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7934, 'MILLER', 'CLERK', 7782,  '1982-01-23'
, 1300, NULL, 10); 
commit;
 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
1, 700, 1200); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
2, 1201, 1400); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
3, 1401, 2000); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
4, 2001, 3000); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
5, 3001, 9999); 
commit;

1、取得每个部门最高薪水的人员名称

SELECT MAX(sal),deptno FROM emp GROUP BY deptno #先获取每个部门最大工资
SELECT e.ename,e.sal,e.deptno FROM emp e JOIN (SELECT MAX(sal) maxsal,deptno FROM emp GROUP BY deptno) s ON e.sal = s.maxsal and e.deptno = s.deptno; #把最大工资表当成临时表

image-20201125102413829

2、哪些人的薪水在部门的平均薪水之上

SELECT avg(sal),deptno FROM emp GROUP BY deptno #先获取每个部门平均工资
SELECT e.ename,e.deptno,e.sal,s.avgsal FROM emp e JOIN (SELECT avg(sal) avgsal,deptno FROM emp GROUP BY deptno) s ON e.sal > s.avgsal AND e.deptno = s.deptno; #把平均工资表当成临时表

image-20201125102419247

3、取得部门中(所有人的)平均的薪水等级,如下:

SELECT e.ename,e.sal,s.grade,e.deptno FROM emp e JOIN salgrade s ON e.sal BETWEEN s.losal and s.hisal; #先获取每个员工的工资等级
#把每个员工的工资表当作一个表进行FROM后嵌套子查询
SELECT s.deptno,avg(s.grade) FROM (SELECT e.ename,e.sal,s.grade,e.deptno FROM emp e JOIN salgrade s ON e.sal BETWEEN s.losal and s.hisal) s GROUP BY s.deptno;

image-20201125102424479

4、不准用组函数(Max),取得最高薪水 (给出两种解决方案)

**第一种:
SELECT * FROM emp ORDER BY sal desc limit 0,1; #倒叙,从零开始取第一个
第二种:
SELECT DISTINCT a.sal FROM emp a join emp b ON a.sal<b.sal #自连接,获得emp表中每行工资小于所有工资的数据然后去重,结果肯定都会小于最大的所以都会显示,除了最大的
SELECT sal from emp WHERE sal not in (SELECT DISTINCT a.sal FROM emp a join emp b ON a.sal<b.sal); #找出工资不在上表中的结果的值就是最大的**

image-20201125102436152

5、取得平均薪水最高的部门的部门编号 (给出两种解决方案)

第一种:
SELECT avg(sal) avgsal,deptno FROM emp GROUP BY deptno ORDER BY avgsal desc limit 0,1 #将每个部门平均薪水进行排序取第一个
第二种:
SELECT avg(sal) avgsal,deptno FROM emp GROUP BY deptno #先获得每个部门平均薪水表
SELECT max(e.avgsal),e.deptno FROM (SELECT avg(sal) avgsal,deptno FROM emp GROUP BY deptno) e #把每个部门平均薪水表当作临时表然后获取最大平均薪资

image-20201125102442437

6、取得平均薪水最高的部门的部门名称

SELECT max(e.avgsal),e.deptno FROM (SELECT avg(sal) avgsal,deptno FROM emp GROUP BY deptno) e #获得平均薪水最高的部门的部门编号
SELECT max(e.avgsal),e.deptno,d.dname FROM (SELECT avg(sal) avgsal,deptno FROM emp GROUP BY deptno) e JOIN dept d ON e.deptno = d.deptno #进行表连接查询

image-20201125102448094

7、求平均薪水的等级最低的部门的部门名称

#先获取每个部门的平均薪水
SELECT deptno,avg(sal) avgsal FROM emp GROUP BY deptno
#获取最低的平均薪水
SELECT deptno,avg(sal) avgsal FROM emp GROUP BY deptno ORDER BY avgsal limit 0,1;
#那么最低的平均薪水对应的等级一定是对应等级,连接查询获得最低等级
SELECT b.GRADE FROM (SELECT deptno,avg(sal) avgsal FROM emp GROUP BY deptno ORDER BY avgsal limit 0,1) a JOIN salgrade b ON a.avgsal BETWEEN b.LOSAL AND b.HISAL
#获取每个部门的平均薪水和对应的部门名称
SELECT c.deptno,c.avgsal,d.dname FROM (SELECT deptno,avg(sal) avgsal FROM emp GROUP BY deptno ) c JOIN dept d ON c.deptno = d.deptno ;
#先获取所有部门的平均薪水等级和对应的部门名称 ,然后把所有平均薪水等级 = 上边语句查询出的最低等级的记录拿出(因为是最低薪水等级的部门可能有多个)
SELECT e.deptno,s.grade,e.dname
FROM (SELECT c.deptno,c.avgsal,d.dname FROM (SELECT deptno,avg(sal) avgsal FROM emp GROUP BY deptno ) c JOIN dept d ON c.deptno = d.deptno ) e
JOIN salgrade s
ON e.avgsal BETWEEN s.LOSAL AND s.HISAL
WHERE s.grade = (SELECT b.GRADE FROM (SELECT deptno,avg(sal) avgsal FROM emp GROUP BY deptno ORDER BY avgsal limit 0,1) a JOIN salgrade b ON a.avgsal BETWEEN b.LOSAL AND b.HISAL );

image-20201125102454662

8、取得比普通员工(员工代码没有在mgr字段上出现的)的最高薪水还要高的领导人姓名

#先获取所有mgr(领导) 需要去掉null不然可能会影响下边的筛选
SELECT DISTINCT mgr FROM emp WHERE mgr is not null;
#找出员工代码不在mgr中的,那就是普通员工不是领导,获得普通员工
SELECT empno,ename FROM emp WHERE empno not in (SELECT DISTINCT mgr FROM emp WHERE mgr is not null);
#获取普通员工的最高薪水
SELECT max(sal) FROM emp WHERE empno not in (SELECT DISTINCT mgr FROM emp WHERE mgr is not null);
#获取比普通员工最高薪水 还要高的领导人的名字(工资比普通员工最高薪水还要高的肯定是领导)
SELECT ename,sal FROM emp WHERE sal > (SELECT max(sal) FROM emp WHERE empno not in (SELECT DISTINCT mgr FROM emp WHERE mgr is not null));

image-20201125102459973

9、取得薪水最高的前五名员工

SELECT * FROM emp ORDER BY sal desc LIMIT 0,5;

image-20201125102509022

10、取得薪水最高的第六到第十名员工

SELECT * FROM emp ORDER BY sal desc LIMIT 5,5; #从第五个开始 取后边的5个

image-20201125102516692

11、取得最后入职的5名员工

SELECT * FROM emp ORDER BY hiredate DESC LIMIT 0,5;

image-20201125102521321

12、取得每个薪水等级有多少员工

#先获取所有员工的薪水等级
SELECT e.empno,e.sal,s.grade FROM emp e JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal
#FROM后嵌套子查询 然后按grade分组 分组函数count计数
SELECT s.grade,count(s.grade) FROM (SELECT e.empno,e.sal,s.grade FROM emp e JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal) s GROUP BY grade

image-20201125102527954

13、面试题

有3个表S(学生表),C(课程表),SC(学生选课表)

S(SNO,SNAME)代表(学号,姓名)

C(CNO,CNAME,CTEACHER)代表(课号,课名,教师)

SC(SNO,CNO,SCGRADE)代表(学号,课号,成绩)

问题:

1,找出没选过“黎明”老师的所有学生姓名。

2,列出2门以上(含2门)不及格学生姓名及平均成绩。

3,即学过1号课程又学过2号课所有学生的姓名。

请用标准SQL语言写出答案,方言也行(请说明是使用什么方言)。

---------------------------------------------------------------------------------------------

# 建表语句
CREATE TABLE SC
(
  SNO      VARCHAR(200),
  CNO      VARCHAR(200),
  SCGRADE  VARCHAR(200)
);

CREATE TABLE S
(
  SNO    VARCHAR(200 ),
  SNAME  VARCHAR(200)
);

CREATE TABLE C
(
  CNO       VARCHAR(200),
  CNAME     VARCHAR(200),
  CTEACHER  VARCHAR(200)
);

INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( '1', '语文', '张'); 
INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( '2', '政治', '王'); 
INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( '3', '英语', '李'); 
INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( '4', '数学', '赵'); 
INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( '5', '物理', '黎明'); 
commit;
 
INSERT INTO S ( SNO, SNAME ) VALUES ( '1', '学生1'); 
INSERT INTO S ( SNO, SNAME ) VALUES ( '2', '学生2'); 
INSERT INTO S ( SNO, SNAME ) VALUES ( '3', '学生3'); 
INSERT INTO S ( SNO, SNAME ) VALUES ( '4', '学生4'); 
commit;
 
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '1', '1', '40'); 
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '1', '2', '30'); 
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '1', '3', '20'); 
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '1', '4', '80'); 
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '1', '5', '60'); 
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '2', '1', '60'); 
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '2', '2', '60'); 
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '2', '3', '60'); 
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '2', '4', '60'); 
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '2', '5', '40'); 
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '3', '1', '60'); 
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '3', '3', '80'); 
commit;

问题1.找出没选过“黎明”老师的所有学生姓名。

即:image-20201125102658075

 #所有学生的学生表
SELECT s.sname,sc.* FROM s LEFT JOIN sc ON s.sno=sc.sno
 #所有表关联起来
SELECT
*
FROM 
(SELECT s.sname,sc.* FROM s LEFT JOIN sc ON s.sno=sc.sno) d 
LEFT JOIN c ON d.cno = c.cno
 #找出学生中选了黎明做老师的
SELECT
d.sname,c.cteacher
FROM 
(SELECT s.sname,sc.* FROM s LEFT JOIN sc ON s.sno=sc.sno) d 
LEFT JOIN c ON d.cno = c.cno
WHERE c.cteacher = '黎明'
 #相反 去掉这两个选了黎明做老师的就是没选的
SELECT sname FROM s WHERE sname NOT IN
(SELECT
d.sname
FROM 
(SELECT s.sname,sc.* FROM s LEFT JOIN sc ON s.sno=sc.sno) d 
LEFT JOIN c ON d.cno = c.cno
WHERE c.cteacher = '黎明')

问题2:列出2门以上(含2门)不及格学生姓名及平均成绩。

 #所有表关联起来 并只显示学生名字和成绩
SELECT
d.sname,d.SCGRADE
FROM 
(SELECT s.sname,sc.* FROM s LEFT JOIN sc ON s.sno=sc.sno) d 
LEFT JOIN c ON d.cno = c.cno
 #在所有表关联起来的基础上改动得到不及格的学生
SELECT
sname,COUNT(sname) '不及格的门数'
FROM 
(SELECT s.sname,sc.* FROM s LEFT JOIN sc ON s.sno=sc.sno) d 
LEFT JOIN c ON d.cno = c.cno
WHERE scgrade < 60
GROUP BY sname 
HAVING COUNT(sname)>=2

 #查出不及格的学生的平均成绩

第一种方法:
SELECT a.sname,avg(a.scgrade) FROM (SELECT
d.sname,d.SCGRADE
FROM 
(SELECT s.sname,sc.* FROM s LEFT JOIN sc ON s.sno=sc.sno) d 
LEFT JOIN c ON d.cno = c.cno) a
WHERE sname in(SELECT
sname
FROM 
(SELECT s.sname,sc.* FROM s LEFT JOIN sc ON s.sno=sc.sno) d 
LEFT JOIN c ON d.cno = c.cno
WHERE scgrade < 60
GROUP BY sname 
HAVING COUNT(sname)>=2)
GROUP BY sname;

第二种方法:
SELECT
    s.SNAME,
    sum( sc.SCGRADE ) AS 总分数,
    (select SUM(SCGRADE) / count(*) from sc where SNO = s.SNO) as 平均成绩
FROM
    sc
    LEFT JOIN c ON c.CNO = sc.CNO
    LEFT JOIN s ON s.SNO = sc.SNO 
WHERE
    sc.SCGRADE < 60 
GROUP BY s.SNAME 
HAVING count( * ) >= 2

问题3:即学过1号课程又学过2号课所有学生的姓名。

 #先查出学过2的学生,然后再查出 学过1的又在(in) 学过2的学生之中的学生
SELECT
       s.sname
FROM
       sc 
JOIN
       s
ON
       sc.sno = s.sno
WHERE
      cno = 1 and sc.sno in(select sno from sc where cno = 2);

14、列出所有员工及领导的姓名

#需要使用左外连接 因为有mgr的值为null,但是也需要显示出来
SELECT e1.ename '员工',e2.ename '领导' FROM emp e1 LEFT JOIN emp e2 ON e1.mgr = e2.empno

image-20201125102722799

15、列出受雇日期早于其直接上级的所有员工的编号,姓名,部门名称

#找出所有员工的直接上级 这里使用内连接 因为没有上级的不需要显示 然后使用WHERE条件判断

SELECT e1.ename '员工',e1.hiredate,e2.ename '领导',e2.hiredate FROM emp e1 JOIN emp e2 ON e1.mgr = e2.empno WHERE e1.hiredate<e2.hiredate

image-20201125102800661

16、列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门.

#使用左外连接 部门表是左表因为需要列出没有员工的部门

SELECT d.dname,e.* FROM dept d LEFT JOIN emp e ON d.deptno = e.deptno

image-20201125102804983

17、列出至少有5个员工的所有部门

#获取至少有5个员工的部门编号

SELECT deptno,count(deptno) countd FROM emp GROUP BY deptno HAVING countd>=5;
#使用表连接查出部门的名称
SELECT d.dname,a.deptno FROM (SELECT deptno,count(deptno) countd FROM emp GROUP BY deptno HAVING countd>=5) a JOIN dept d ON a.deptno = d.deptno;

image-20201125102809855

18、列出薪金比"SMITH"多的所有员工信息.

#WHERE后边嵌套子查询

SELECT * FROM emp WHERE sal>(SELECT sal FROM emp WHERE ename = 'SMITH')

image-20201125102813438

19、列出所有"CLERK"(办事员)的姓名及其部门名称,部门的人数.

第一种做法:
#先获得所有job是CLERK的信息
SELECT * FROM emp WHERE job = 'CLERK';
#获取每个部门的人数
*SELECT deptno,count() FROM emp GROUP BY deptno;
#把上边两张表都当成临时表进行嵌套 并且三张表连表查询
SELECT d.dname,j.ename,e.count1 FROM dept d JOIN (SELECT FROM emp WHERE job = 'CLERK') j ON j.deptno = d.deptno JOIN (SELECT deptno,count() count1 FROM emp GROUP BY deptno) e ON j.deptno=e.deptno;
第二种做法:
#先获得所有job是CLERK的信息
SELECT FROM emp WHERE job = 'CLERK';*
#获取每个部门的人数
*SELECT deptno,count() FROM emp GROUP BY deptno;
#获取每个部门的人数和对应的部门名称
SELECT d.dname,e. FROM dept d JOIN (SELECT deptno,count() FROM emp GROUP BY deptno) e ON d.deptno = e.deptno
#把上边的表当成临时表和job是CLERK的临时表进行嵌套查询,并使用连表查询
SELECT a.ename,b.dname,b.con FROM (SELECT FROM emp WHERE job = 'CLERK') a JOIN (SELECT d.dname,e. FROM dept d JOIN (SELECT deptno,count() con FROM emp GROUP BY deptno) e ON d.deptno = e.deptno) b ON a.deptno = b.deptno*

image-20201125102818526

20、列出最低薪金大于1500的各种工作及从事此工作的全部雇员人数.

#先获取最低薪金大于1500的各种工作

SELECT job,MIN(sal) minsal,deptno FROM emp GROUP BY job HAVING minsal>1500
#连表查询
SELECT e.job,count(*) FROM emp e JOIN (SELECT job,MIN(sal) minsal,deptno FROM emp GROUP BY job HAVING minsal>1500) j ON j.job=e.job GROUP BY e.job

image-20201125102821556

21、列出在部门"SALES"<销售部>工作的员工的姓名,假定不知道销售部的部门编号.

第一种:

SELECT e.ename FROM emp e JOIN dept d ON e.deptno = d.deptno WHERE d.dname = 'SALES'
第二种:
select ename from emp where deptno = (select deptno from dept where dname = 'SALES');

image-20201125102824844

22、列出薪金高于公司平均薪金的所有员工,所在部门,上级领导,雇员的工资等级.

#先列出薪金高于公司平均薪金的所有员工

SELECT * FROM emp WHERE sal > (SELECT avg(sal) FROM emp);
#多表联查
SELECT
e.ename '姓名',s.grade '薪资等级',d.dname '部门名称',e2.ename '上级领导'
FROM (SELECT * FROM emp WHERE sal > (SELECT avg(sal) FROM emp)) e
LEFT JOIN emp e2 ON e.mgr = e2.empno
JOIN salgrade s ON e.sal BETWEEN s.LOSAL AND s.HISAL
JOIN dept d ON e.deptno=d.deptno

image-20201125102830607

23、列出与"SCOTT"从事相同工作的所有员工及部门名称.

#查处SCOTT所从事的工作

SELECT job FROM emp WHERE ename = 'SCOTT'
#WHERE后嵌套子查询 获得和"SCOTT"从事相同工作的所有员工
SELECT * FROM emp WHERE job = (SELECT job FROM emp WHERE ename = 'SCOTT') AND ename != 'SCOTT';
#多表联查
SELECT j.ename,d.dname FROM (SELECT * FROM emp WHERE job = (SELECT job FROM emp WHERE ename = 'SCOTT') AND ename != 'SCOTT') j JOIN dept d ON j.deptno=d.deptno;

image-20201125102835899

24、列出薪金等于部门30中员工的薪金的其他员工的姓名和薪金.

#列出薪金等于部门30的所有薪资 并且去重的

SELECT DISTINCT sal FROM emp WHERE deptno = 30;
#连接查询
SELECT e1.ename,e1.sal,e1.deptno FROM emp e1 JOIN (SELECT DISTINCT sal FROM emp WHERE deptno = 30) e2 ON e1.sal = e2.sal WHERE e1.deptno != 30

25、列出薪金高于在部门30工作的所有员工的最高薪金的员工姓名和薪金.部门名称.

#列出薪金等于部门30的最大薪资 并且去重
SELECT DISTINCT max(sal) FROM emp WHERE deptno = 30;
#WHERE后嵌套子查询
SELECT * FROM emp e1 WHERE sal>(SELECT DISTINCT max(sal) FROM emp WHERE deptno = 30)

image-20201125102845720

26、列出在每个部门工作的员工数量,平均工资和平均服务期限.

#如果平均工资是null,设置成0 平均服务期限=系统当前年份-入职年份**
#难点 怎么进行年份的相减(计算两个日期之间的时间间隔的方法) :TimeStampDiff(间隔类型, 前一个日期, 后一个日期)
SELECT d.deptno,count(e.deptno),IFNULL(avg(e.sal),0),IFNULL(avg(TIMESTAMPDIFF(YEAR,hiredate,now())),0) as avgyear FROM dept d LEFT JOIN emp e ON d.deptno=e.deptno GROUP BY deptno;**

image-20201125102855998

27、列出所有员工的姓名、部门名称和工资。

SELECT e.ename,e.sal,d.dname FROM emp e JOIN dept d ON e.deptno = d.deptno

image-20201125102901024

28、列出所有部门的详细信息和人数

SELECT d.*,count(d.deptno) as '人数' FROM dept d LEFT JOIN emp e ON d.deptno=e.deptno GROUP BY deptno;

image-20201125102906049

29、列出各种工作的最低工资及从事此工作的雇员姓名

#先获得每个工作的最低薪资
SELECT min(sal) minsal,job FROM emp GROUP BY job;
#需要先找出每个工作的最低薪资,因为最低的工资相同的人可能有多个,所以需要表连接查询
SELECT * FROM emp e JOIN (SELECT min(sal) minsal,job FROM emp GROUP BY job) j ON j.job=e.job AND minsal = e.sal;

image-20201125102911998

30、列出各个部门的MANAGER(领导)的最低薪金

SELECT DISTINCT MIN(sal) minsal,deptno FROM emp WHERE job = "MANAGER" GROUP BY deptno

image-20201125102919603

31、列出所有员工的年工资,按年薪从低到高排序

SELECT ename,(sal+IFNULL(comm,0))*12 as '年薪' FROM emp ORDER BY '年薪'

image-20201125102928963

32、求出员工领导的薪水超过3000的员工名称与领导名称

#找出所有的员工的领导
SELECT e1.ename '员工',e2.ename as '领导',e2.sal FROM emp e1 JOIN emp e2 ON e1.mgr=e2.empno WHERE e2.sal>3000

image-20201125102936725

33、求出部门名称中,带'S'字符的部门员工的工资合计、部门人数.

SELECT d.deptno,d.dname,d.loc,IFNULL(sum(e.sal),0) sumsal,count(e.ename) FROM emp e RIGHT JOIN dept d ON e.deptno = d.deptno WHERE d.dname LIKE '%S%' group by d.deptno,d.dname,d.loc;

image-20201125102942626

34、给任职日期超过30年的员工加薪10%.

UPDATE emp SET sal = sal*1.1 WHERE TimeStampDiff(YEAR,HIREDATE, now())>30

最后修改:2020 年 12 月 11 日 09 : 42 AM
如果觉得我的文章对你有用,请随意赞赏