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;