Mysql练习题

SQL脚本导入：

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;

DEPT部门表

EMP 员工表

SALGRDW 工资等级表

题目一：

求出每个部门的最高薪资

思路：求出每个部门的最高薪资

SELECT e.DEPTNO, MAX(e.SAL) as maxsal from EMP e GROUP BY e.DEPTNO;

将以上查询结果作为临时表去使用t(depton,maxsal)

SELECT  e.DEPTNO , e.ENAME, t.maxsal, e.sal
from (SELECT e.DEPTNO, MAX(e.SAL) as maxsal from EMP e GROUP BY e.DEPTNO)t  
join  EMP e  
ON t.DEPTNO = e.DEPTNO where t.maxsal = e.SAL;

题目二：

哪人的薪资在部门平均薪资之上

思路：先查询出每个部门的部门编号和平均薪资

SELECT 
  e.DEPTNO, AVG( e.SAL ) as avgsal
from  emp e
GROUP BY e.DEPTNO;

将以上结果当成临时表t(deptno,avgsal)

SELECT 
t.deptno, e.ENAME
FROM (SELECT 
e.DEPTNO, AVG( e.SAL ) as avgsal
from  emp e
GROUP BY e.DEPTNO ) t
JOIN emp e
on e.DEPTNO = t.DEPTNO
WHERE  e.SAL > t.avgsal

第三题：获取部门中（所有人的）平均薪水等级

有歧义，分两种情况去做

1、取得部门中所有人的平均薪资等级

set1 求出部门的平均薪资

SELECT e.DEPTNO , AVG(e.SAL) as avgsal
FROM   emp  e
GROUP BY e.DEPTNO;

然后通通过临时表进行拼接

SELECT 
  t.DEPTNO,t.avgsal, s.GRADE
FROM
 (
  SELECT e.DEPTNO , AVG(e.SAL) as avgsal
  FROM   emp  e
  GROUP BY e.DEPTNO
) t
JOIN salgrade s
ON   t.avgsal BETWEEN s.LOSAL and s.HISAL;

2、取得部门中所有人的平均的薪资等级

查询出每一个人的薪资等级临时表 t (deptno, ename, grade)

SELECT 
  e.DEPTNO, e.ENAME, s.GRADE
FROM emp e
JOIN salgrade s
ON e.SAL BETWEEN s.LOSAL and s.HISAL
ORDER BY
e.DEPTNO;

然后把等级值进行平均

SELECT 
 t.DEPTNO, AVG(t.GRADE)
FROM
(
SELECT 
  e.DEPTNO as DEPTNO, e.ENAME, s.GRADE as GRADE
FROM emp e
JOIN salgrade s
ON e.SAL BETWEEN s.LOSAL and s.HISAL
ORDER BY
e.DEPTNO
)t 
GROUP BY t.DEPTNO

第四题：不准用组函数（MAX）,取得最高薪水(给出两种解决方案)

方案一：

SELECT SAL from emp ORDER BY SAL DESC LIMIT 1

方案二：两张表去每一行比较取小的那个

SELECT 
  DISTINCT a.SAL
from emp a
JOIN emp b
on a.SAL < b.SAL;

要获取的值是除了上面的之外的：

SELECT SAL FROM emp  WHERE SAL NOT IN (SELECT 
  DISTINCT a.SAL
from emp a
JOIN emp b
on a.SAL < b.SAL);

如果替换下符号 > 那就是得出的最小值了

第五题：取得平均薪资最高的部门的编号

set1 先求出部门平均薪资

SELECT 
  e.deptno, AVG(e.sal) as avgsal
FROM emp e
GROUP BY e.DEPTNO;

如果最高部门的薪资有并列最高的情况怎么处理？需要思考这个维度

set2 求查询结果当成临时表t(deptno,avgsal)

然后进行筛选过滤having 不过有多少个值只要是等于最大值的都给取出来

SELECT
 e.DEPTNO, AVG(e.SAL) as avgsal
FROM
emp e
GROUP BY e.DEPTNO
HAVING avgsal = 
(
SELECT MAX(t.avgsal_temp) as avgsal_max FROM (
SELECT 
 e.DEPTNO,AVG(SAL) as avgsal_temp
from emp e
GROUP BY e.DEPTNO
)t
)

思路小结：先求出平均值的最大值然后二次查询其他的平均值大于最大值就ok了等于的就是我想要的

第六题：取得平均薪资最高的部门的部门名称

set1 求出每个部门的平均工资

SELECT 
 e.DEPTNO,AVG(SAL) as avsal
from emp e
GROUP BY e.DEPTNO;

set2 然后平均工资临时表中获取最大值

set3 查询原表过滤条件是平均值等于最大值

SELECT
 e.DEPTNO,d.DNAME, AVG(e.SAL) as avgsal
FROM
emp e
JOIN dept d
on e.DEPTNO = d.DEPTNO
GROUP BY e.DEPTNO,d.DNAME
HAVING avgsal = 
(
SELECT MAX(t.avgsal_temp) as avgsal_max FROM (
SELECT 
 e.DEPTNO,AVG(SAL) as avgsal_temp
from emp e
GROUP BY e.DEPTNO
)t
)

第七题：求平均薪资的等级最低的部门的部门名称

set1 部门的平均薪资和部门名称

SELECT e.DEPTNO,d.DNAME,AVG(e.SAL)
FROM
emp e
JOIN dept d
ON E.DEPTNO = d.DEPTNO
GROUP BY e.DEPTNO , d.DNAME

set2 再查出每个部门平均薪资等级

SELECT
 t.DEPTNO,t.DNAME, s.GRADE
FROM
(SELECT 
e.DEPTNO,d.DNAME,AVG(e.SAL) as avgsal
FROM
emp e
JOIN
dept d
ON e.DEPTNO = d.DEPTNO
GROUP BY e.DEPTNO , d.DNAME
)t
JOIN 
salgrade s
ON t.avgsal BETWEEN s.LOSAL and s.HISAL;

set 3 获取最低等级（等级是范围比较的结果值可以不一样但是最后的等级是一样的）

将之前的查询结果作为临时表进行查询查询出最低等级的是哪个

SELECT MIN(t_temp.GRADE) as min_grade from 
(
SELECT
 t.DEPTNO,t.DNAME, s.GRADE
FROM
(SELECT 
e.DEPTNO,d.DNAME,AVG(e.SAL) as avgsal
FROM
emp e
JOIN
dept d
ON e.DEPTNO = d.DEPTNO
GROUP BY e.DEPTNO , d.DNAME
)t
JOIN 
salgrade s
ON t.avgsal BETWEEN s.LOSAL and s.HISAL
) as t_temp;

然后进行筛选过滤下

SELECT
 t.DEPTNO,t.DNAME, s.GRADE
FROM
(SELECT 
e.DEPTNO,d.DNAME,AVG(e.SAL) as avgsal
FROM
emp e
JOIN
dept d
ON e.DEPTNO = d.DEPTNO
GROUP BY e.DEPTNO , d.DNAME
)t
JOIN 
salgrade s
ON t.avgsal BETWEEN s.LOSAL and s.HISAL
WHERE  
s.GRADE = 
(SELECT MIN(t_temp.GRADE) as min_grade from 
(
SELECT
 t.DEPTNO,t.DNAME, s.GRADE
FROM
(SELECT 
e.DEPTNO,d.DNAME,AVG(e.SAL) as avgsal
FROM
emp e
JOIN
dept d
ON e.DEPTNO = d.DEPTNO
GROUP BY e.DEPTNO , d.DNAME
)t
JOIN 
salgrade s
ON t.avgsal BETWEEN s.LOSAL and s.HISAL
) as t_temp );

（用having的话必须要在group by之后！！排序后不满足的过滤条件昂）

SELECT
 t.DEPTNO,t.DNAME, s.GRADE
FROM
(SELECT 
e.DEPTNO,d.DNAME,AVG(e.SAL) as avgsal
FROM
emp e
JOIN
dept d
ON e.DEPTNO = d.DEPTNO
GROUP BY e.DEPTNO , d.DNAME
)t
JOIN 
salgrade s
ON t.avgsal BETWEEN s.LOSAL and s.HISAL
WHERE  
s.GRADE = 
(SELECT MIN(t_temp.GRADE) as min_grade from 
(
SELECT
 t.DEPTNO,t.DNAME, s.GRADE
FROM
(SELECT 
e.DEPTNO,d.DNAME,AVG(e.SAL) as avgsal
FROM
emp e
JOIN
dept d
ON e.DEPTNO = d.DEPTNO
GROUP BY e.DEPTNO , d.DNAME
)t
JOIN 
salgrade s
ON t.avgsal BETWEEN s.LOSAL and s.HISAL
) as t_temp );

其实大家看到比较复杂我已经写的很具体了。思路大家自己捋一捋，相信后面的题目都会迎刃而解的！

第八题：查询出比普通员工(员工代码没有在mgr上出现的) 的最高薪资还要高的经理人姓名

首先找出经理

SELECT DISTINCT(mgr) FROM emp;

然后非经理（普通员工）工薪水最高的

SELECT MAX(SAL) as max_sal from emp WHERE EMPNO NOT IN (SELECT DISTINCT(mgr) FROM emp WHERE MGR is not NULL);

然后薪水大于它就OK了 (排除经理人剩下的普通员工里面的最高的薪资，比普通员工薪资还要高的肯定是经理了)

SELECT ENAME from emp WHERE sal > (SELECT MAX(SAL) as max_sal from emp WHERE EMPNO NOT IN (SELECT DISTINCT(mgr) FROM emp WHERE MGR is not NULL));

注意：

in 会忽略掉null 而not in 不会忽略（这样就有null 参与数学运算）

not in 里面的值的关系是and

in 里面的值的关系是 or

看下面：

SELECT * FROM emp WHERE EMPNO NOT IN (SELECT DISTINCT(mgr) FROM emp);

执行结果啥都没有：

第九题：查询出薪水最高的前五名员工

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

第十题：查询出薪资最高的第六到第十名员工

SELECT * FROM emp ORDER BY SAL DESC LIMIT 5,5;

第十一题：查询出最后入职的五名员工（入职晚，日期大）

SELECT * from  emp ORDER BY HIREDATE DESC limit 5;

第十二题：取得每个薪水等级有多少员工

set1 查询出每个员工的薪水等级

SELECT  
  e.ENAME, s.GRADE
FROM

emp e

JOIN salgrade s

on  e.SAL BETWEEN s.LOSAL AND s.HISAL；

set2 按照等级去排序

SELECT  
  e.ENAME, s.GRADE
FROM

emp e

JOIN salgrade s

on  e.SAL BETWEEN s.LOSAL AND s.HISAL

ORDER BY s.GRADE;

set3 按照等级值去分组求出每个等级有多少人

SELECT
 t.GRADE,COUNT(*)as total_grade
FROM
(
SELECT  
  e.ENAME, s.GRADE
FROM

emp e

JOIN salgrade s

on  e.SAL BETWEEN s.LOSAL AND s.HISAL

ORDER BY s.GRADE
)t
GROUP BY t.GRADE;

第十三题：

有3个表S(学生表)，C（课程表），SC（学生选课表）

S（SNO，SNAME）代表（学号，姓名）
C（CNO，CNAME，CTEACHER）代表（课号，课名，教师）
SC（SNO，CNO，SCGRADE）代表（学号，课号，成绩）

问题：
1、找出没选过“黎明”老师的所有学生姓名。
2、列出2门以上（含2门）不及格学生姓名及平均成绩。
3、即学过1号课程又学过2号课所有学生的姓名。

建表：

学生表：

create table s(
    sno int(4) primary key auto_increment,
    sname varchar(32)
);

课程表：

create table c(
    cno int(4) primary key auto_increment,
    cname varchar(32),
    cteacher varchar(32)
);

学生选课表：

学生跟课程的关系是多对多的关系。采用的联合主键。

也需要对于外键的约束添加

create table sc(
    sno int(4),
    cno int(4),
    scgrade double(3,1),
    constraint sc_sno_cno_pk primary key(sno,cno),
    constraint sc_sno_fk foreign key(sno) references s(sno),
    constraint sc_cno_fk foreign key(cno) references c(cno)
);

一张表里面只能有一个主键但是可以有多个外键

测试数据：

insert into s(sname) values('zhangsan');
insert into s(sname) values('lisi');
insert into s(sname) values('wangwu');
insert into s(sname) values('zhaoliu');

insert into c(cname,cteacher) values('Java','吴老师');
insert into c(cname,cteacher) values('C++','王老师');
insert into c(cname,cteacher) values('C##','张老师');
insert into c(cname,cteacher) values('MySQL','郭老师');
insert into c(cname,cteacher) values('Oracle','黎明');

学生课程都有了

学生和课程之间的关系：学生号课程号

insert into sc(sno,cno,scgrade) values(1,1,30);
insert into sc(sno,cno,scgrade) values(1,2,50);
insert into sc(sno,cno,scgrade) values(1,3,80);
insert into sc(sno,cno,scgrade) values(1,4,90);
insert into sc(sno,cno,scgrade) values(1,5,70);
insert into sc(sno,cno,scgrade) values(2,2,80);
insert into sc(sno,cno,scgrade) values(2,3,50);
insert into sc(sno,cno,scgrade) values(2,4,70);
insert into sc(sno,cno,scgrade) values(2,5,80);
insert into sc(sno,cno,scgrade) values(3,1,60);
insert into sc(sno,cno,scgrade) values(3,2,70);
insert into sc(sno,cno,scgrade) values(3,3,80);
insert into sc(sno,cno,scgrade) values(4,3,50);
insert into sc(sno,cno,scgrade) values(4,4,80);

问题：

1、找出没选过“黎明”老师的所有学生姓名。
2、列出2门以上（含2门）不及格学生姓名及平均成绩。
3、即学过1号课程又学过2号课所有学生的姓名。

1、思路：

先找出选过黎明老师的学生编号依赖黎明老师授课的课程编号

SELECT cno from c where cteacher = '黎明';

找出选择该课程的学生编号：

SELECT sno from sc WHERE cno = (SELECT cno from c where cteacher = '黎明');

找出没有选择该课程的学生姓名：

SELECT * FROM s WHERE sno NOT IN (SELECT sno from sc WHERE cno = (SELECT cno from c where cteacher = '黎明'));

2、思路：

对学生分组然后求出不及格的门数

SELECT sc.sno, COUNT(*) as stu_num FROM sc where scgrade < 60 GROUP BY sc.sno;

对group by 之后的结果进行过滤

SELECT sc.sno, COUNT(*) as stu_num FROM sc where scgrade < 60 GROUP BY sc.sno HAVING stu_num >=2;

然后这类学生姓名的查询

SELECT sc.sno,s.sname,COUNT(*) as stu_num 
FROM sc
JOIN s on sc.sno=s.sno
where scgrade < 60 
GROUP BY sc.sno 
HAVING stu_num >=2;

每个学生的平均成绩查询

SELECT
sc.sno, AVG(sc.scgrade) as avg_scgrade
FROM 
 sc
GROUP BY sc.sno;

与之前的进行组合：(一个查名字一个查平均成绩整合到一起)

SELECT t1.sname,t2.avg_scgrade
FROM
(SELECT sc.sno,s.sname,COUNT(*) as stu_num 
FROM sc
JOIN s on sc.sno=s.sno
where scgrade < 60 
GROUP BY sc.sno 
HAVING stu_num >=2
)t1
JOIN
(SELECT
 sc.sno,AVG(sc.scgrade) as avg_scgrade
FROM 
 sc
GROUP BY sc.sno)t2
ON
t1.sno = t2.sno

3.思路

一般很容易这样做：(没有哪一门课程编号即是1又是2的)

SELECT sno FROM sc WHERE cno = 1 and cno = 2;

看下面：

SELECT sno FROM sc WHERE cno = 1 and sc.sno in (SELECT sno from sc where cno = 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);

第十四题：列出所有员工及领导的名字

员工的领导编号等于领导的员工编号

SELECT
 e.ename, b.ename as leader_name
FROM
emp e
LEFT JOIN
emp b
ON
e.mgr = b.empno;

老板上面没上司了昂

外连接的条数永远大于等于内连接的

第十五题：列出受雇日期早于其直接上级的所有员工编号、姓名、部门名称

SELECT 
  d.DNAME,
  e.EMPNO,
  e.ENAME
from 
  emp e
JOIN
  emp b
ON
  e.MGR = b.EMPNO
JOIN
  dept d
ON
  e.DEPTNO = d.DEPTNO  
WHERE  
  e.HIREDATE < B.HIREDATE

第十六题：列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门

思路：

先查出部门名称和该部门下的员工信息

SELECT
  d.DNAME, e.*
 FROM 
  emp e
 RIGHT JOIN 
  dept d
 ON e.DEPTNO = d.DEPTNO

第十七题：列出至少有五个员工的部门

先求出每个部门员工数量

根据部门进行分组

过滤之

SELECT
   e.DEPTNO, COUNT(e.ename) as total_empnum
FROM 
  emp e
GROUP BY 
  e.DEPTNO
HAVING total_empnum >= 5;

第十八题：列出薪资比“SMITH” 多的所有员工信息

就是个子查询而已

SELECT e.* from emp e where e.SAL > (SELECT e.SAL from emp e WHERE ENAME='SMITH');

第十九题：列出所有“CLERK”(办事员)的姓名及部门名称，部门人数

先查询下这个工作名称是这个的员工名字、部门名字、部门编号

SELECT 
  d.deptno,d.dname, e.ename
FROM 
  emp e
JOIN
 dept d
ON
e.deptno = d.deptno
WHERE e.job= 'CLERK'

然后求出每个部门的员工数量

SELECT
t1.deptno,t1.dname,t1.ename,t2.total_emp_num 
FROM
(SELECT 
  d.deptno,d.dname, e.ename
FROM 
  emp e
JOIN
 dept d
ON
e.deptno = d.deptno
WHERE e.job= 'CLERK')t1
JOIN
(
SELECT 
 e.deptno, COUNT(e.ename) as total_emp_num
FROM
 emp e
GROUP BY 
  e.deptno)t2
ON
t1.deptno = t2.deptno;

第二十题：列出最低薪水大于1200的各种工作及从事此工作的全部雇佣人数

思路：

先查询出每个工作的最低薪水，然后大于1500的留下

SELECT 
e.job,
MIN(e.sal) as min_sal
FROM
emp e
GROUP BY
e.job
HAVING min_sal > 1500;

然后：既然已经分组了在分组的基础上统计人数

SELECT 
e.job,
MIN(e.sal) as min_sal,COUNT(e.ename) as total_emp_num
FROM
emp e
GROUP BY
e.job
HAVING min_sal > 1500;

第二十一题：列出在部门“SALES”(销售部)工作的员工的姓名，假定不知道销售部门的部门编号

先根据名字查询出部门编号

SELECT
deptno 
from 
dept 
where dname= 'SALES';

作为子查询条件进行查询：

SELECT ename from emp WHERE deptno  = (SELECT deptno  from dept where dname= 'SALES');

第二十二题：列出薪金高于公司平均薪金的所有员工，所在部门、上级领导、雇员的工资等级

求出公司的平均薪金后不停的Join 进行条件的筛选

SELECT 
e.ename,d.dname,b.ename as leader_name,s.grade
from 
emp e
JOIN dept d
ON e.deptno = d.deptno
LEFT JOIN emp b
ON
e.mgr = b.empno
JOIN salgrade s
ON  
e.sal BETWEEN s.losal AND s.hisal
WHERE
e.sal > (SELECT AVG(sal) as avg_sal FROM emp);

第二十三题：列出与“SCOTT”从事相同工作的所有员工及部门名称

先找到这个人的工作名称

SELECT job from emp where ename = 'SCOTT';

然后

SELECT
  d.dname,e.*
FROM
emp e
JOIN
dept d
ON
e.deptno = d.deptno
where 
e.job = (SELECT job from emp where ename = 'SCOTT');

第二十四题：列出薪金等于部门编号为30的员工薪金的其他员工的姓名和薪金

思路：正常查询然后排除不满足的

SELECT ename ,sal from emp where sal in (SELECT DISTINCT(sal) FROM emp WHERE deptno = 30) and emp.deptno <> 30;

第二十五题：列出薪金高于在部门编号30的所有员工的薪金的员工姓名和薪金、部门名称

思路：先查询出部门编号30的最高薪资

SELECT 
 d.dname,e.ename,e.sal
FROM 
emp e
JOIN
 dept d
on e.deptno = d.deptno
WHERE
 e.sal > (SELECT MAX(sal) FROM emp e where deptno = 30);

第二十六题：列出在每个部门工作的员工数量、平均工资和平均服务期限

函数： to_days(日期类) 返回天数

获取数据库的系统当前时间的函数是： now()

比如可以这么使用：

SELECT AVG( (TO_DAYS(NOW()) - TO_DAYS(hiredate))/365) as avg_server_year from emp;

本题：

SELECT 
 e.deptno,
 COUNT(e.ename) as total_emp,
AVG(e.sal) as avg_sal,
AVG(( TO_DAYS(NOW())-  TO_DAYS(hiredate))/365) as avg_server_year
FROM
emp e
GROUP BY e.deptno;

第二十七题：列出所有员工的姓名、部门名称、和工资

注意所有员工的姓名，所有修饰员工姓名

然后其他的部门也要展示出来所以用的 right join

就是：部门为主表部门可以没有员工

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

第二十八题: 列出所有部门的详细信息和人数

SELECT
 d.deptno,COUNT(e.deptno) as total_emp, d.dname,d.loc
FROM
 emp e
RIGHT JOIN
 dept d
ON 
    e.deptno = d.deptno
GROUP BY
  d.deptno, d.dname, d.loc;

这里要特别注意： group by 的都是right join 的表的属性字段 d.deptno, d.dname, d.loc;

第二十九题：列出各种工作的最低工资及从事此工作的雇员姓名

思路：首先

SELECT
 e.job, MIN(e.sal) as minsal 
FROM
 emp e
GROUP BY
 e.job

然后将以上查询结果当成临时表t(job, minsal)

SELECT
  e.ename
FROM
 emp e
JOIN 
 (SELECT
 e.job, MIN(e.sal) as minsal 
FROM
 emp e
GROUP BY
 e.job)t
ON
 e.job = t.job
WHERE
  e.sal = t.minsal;

第三十题：

列出各个部门 MAMAGER的最低薪资

SELECT
 e.deptno, MIN(e.sal) as min_sal
FROM
emp e
WHERE
 e.job = 'MANAGER'
GROUP BY e.deptno;

第三十一题：列出所有员工的年薪，按照年薪从低到高排序

SELECT
 ename, (sal+IFNULL(comm,0))*12 as year_sal 
from 
 emp
ORDER BY
 year_sal

第三十二题：求出员工领导的薪资超过3000的员工名称和领导名称

SELECT
 e.ename,
 b.ename as leader_name
from 
 emp e
JOIN 
 emp b
on
 e.mgr = b.empno
WHERE
b.sal > 3000;

第三十三题：求出部门名称中带“”s“ 字符的部门员工的工资合计，部门人数

SELECT
 d.dname, 
 SUM(e.sal) as sumsal,
 COUNT(e.ename) as total_emp
FROM
 emp e
JOIN
 dept d
ON
 e.deptno = d.deptno
 WHERE 
 d.dname LIKE "%s%"
GROUP BY
 d.dname;

或者：

SELECT
 d.dname, 
 SUM(e.sal) as sumsal,
 COUNT(e.ename) as total_emp
FROM
 emp e
JOIN
 dept d
ON
 e.deptno = d.deptno
GROUP BY
 d.dname
HAVING
 d.dname LIKE "%s%";

第三十四题：给任职日期超过30奶奶的员工加薪10%

注意 select 永远不会修改底层数据库的数据，只是显示

备份个表：

create table emp_bak as select * from emp;

update表：

update emp_bak 
set 
sal = sal * 1.1 
WHERE 
(TO_DAYS(NOW())-TO_DAYS(hiredate))/365 > 30;

posted @ 2019-03-01 03:32 toov5 阅读(350) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

toov5

Mysql练习题

公告