1、取得每个部门最高薪水的人员名称
第一步:求出每个部门的最高薪水
SELECT e.deptno,MAX(e.sal) FROM emp e GROUP BY e.deptno;
第二部:将以上查询结果当成一个临时表t(deptno,maxsal)
SELECT e.deptno,e.ename,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 ORDER BY e.deptno;
2、哪些人的薪水在部门平均薪水之上?
查出平均工资SELECT e.deptno,AVG(e.sal) AS aal FROM emp e GROUP BY e.deptno;
SELECT e.deptno,e.ename,e.sal,a.aal FROM
(SELECT e.deptno,AVG(e.sal) AS aal FROM emp e GROUP BY e.deptno) a
JOIN emp e ON a.deptno=e.deptno WHERE e.sal>a.aal ;
SELECT e.deptno;
3、取得部门中(所有人的)平均薪水等级(平均的薪水等级)
求平均薪水的等级
求部门平均薪水SELECT e.deptno,AVG(e.sal) AS tsal FROM emp e GROUP BY e.deptno;
SELECT t.deptno,s.grade
FROM ( SELECT e.deptno,AVG(e.sal) AS tsal FROM emp e GROUP BY e.deptno) t
JOIN salgrade s
ON t.tsal between s.losal and s.hisal ;
求部门中所有人的平均的薪水等级
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;
求部门平均等级,将以上查询结果当成临时表t
SELECT t.deptno,avg(t.grade)AS vgrade FROM (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)t GROUP BY t.deptno;
4、求最高薪水(两种方法不准用MAX)
方法一 SELECT sal from emp ORDER BY sal DESC LIMIT 1;
方法二 SELECT sal FROM emp where sal not in
(SELECT DISTINCT a.sal
from emp a JOIN emp b on
a.sal<b.sal);
5、取得平均薪水最高的部门的部门编号
SELECT e.deptno ,avg(e.sal)AS avgsal from emp e GROUP BY e.deptno;
SELECT MAX(t.avgsal) as maxAvgsal from (SELECT e.deptno ,avg(e.sal)AS avgsal from emp e GROUP BY e.deptno)t;
SELECT e.deptno,avg(e.sal)AS avgsal from emp e
GROUP BY e.deptno HAVING avgsal=(SELECT MAX(t.avgsal)
as maxAvgsal from (SELECT e.deptno ,avg(e.sal)AS
avgsal from emp e GROUP BY e.deptno)t);
6、 取得平均薪水最高的部门和部门名称
先求平均SELECT e.deptno,AVG(e.sal)as avgsal from emp e GROUP BY e.deptno;
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) as maxAvgsal from
(select e.deptno,avg(e.sal) as avgsal from emp e group by e.deptno)t);
7、求平均薪水的等级最低的部门名称
第一步:部门的平均薪水
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;
第二步等级最低的部门名称
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;
第三步:将以上查询结果当成临时表t
SELECT min(t.grade) as mingrade 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)t;
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.grade) as mingrade 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)t);
7、 求平均薪水等级最低的部门的部门名称
select d.dname, avg(s.grade) agrade
from emp e
join dept d on e.deptno =d.deptno
join salgrade s on e.sal between s.losal and s.hisal
group by d.dname order by agrade asc limit 1;
8、取得比普通员工(院代码没有在mgr字段出现的)的最高薪水还要高的领导人姓名
第一步找出比普通员工(员工代码没有在mgr上出现的)的最高薪水还要高的经理人姓名
SELECT DISTINCT mgr from emp;
select * from emp where empno in (SELECT DISTINCT mgr from emp);
select max(sal) from emp where empno not in (SELECT DISTINCT mgr from emp where mgr is not null);
not in 不会自动忽略空值,in会自动忽略空值
select ename from emp where sal>1600;
最终语句
select ename from emp where sal>(select max(sal) from emp where empno not in (SELECT DISTINCT mgr from emp where mgr is not null));
9、 取得薪水最高的前五名员工(mysql 默认排序是升序)
select *from emp order by sal desc limit 0,5;
10、取得薪水最高的第六到第十名员工
select *from emp order by sal desc limit 5,5;
11、取得最后入职的5名员工
select * from emp order by hiredate desc limit 5;
12、取得每个薪水等级有多少员工
第一步查询每个员工的薪水等级
select e.ename,s.grade from emp e
join salgrade s on e.sal between s.losal and s.hisal order by s.grade;
第二部计算每个等级的员工数
select t.grade,COUNT(t.ename )as con from (select e.ename,s.grade from emp e
join salgrade s on e.sal between s.losal and s.hisal )t group by t.grade;
13、面试题(58-59)
有三个表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(sno,sname) values(1,‘a’);
insert into s(sno,sname) values(2,‘b’);
insert into s(sno,sname) values(3,‘c’);
insert into s(sno,sname) values(4,‘d’);
insert into c(cno,cname,cteacher) values(1,‘java’,‘王老师’);
insert into c(cno,cname,cteacher) values(2,‘C++’,‘张老师’);
insert into c(cno,cname,cteacher) values(3,‘C#’,‘李老师’);
insert into c(cno,cname,cteacher) values(4,‘mysql’,‘周老师’);
insert into c(cno,cname,cteacher) values(5,‘oracle’,‘黎明’);
insert into sc(sno,cno,scgrade) values(1,1,50);
insert into sc(sno,cno,scgrade) values(1,2,50);
insert into sc(sno,cno,scgrade) values(1,3,50);
insert into sc(sno,cno,scgrade) values(2,2,80);
insert into sc(sno,cno,scgrade) values(2,3,70);
insert into sc(sno,cno,scgrade) values(2,4,59);
insert into sc(sno,cno,scgrade) values(3,1,60);
insert into sc(sno,cno,scgrade) values(3,2,61);
insert into sc(sno,cno,scgrade) values(3,3,99);
insert into sc(sno,cno,scgrade) values(3,4,100);
insert into sc(sno,cno,scgrade) values(3,5,52);
insert into sc(sno,cno,scgrade) values(4,3,82);
insert into sc(sno,cno,scgrade) values(4,4,99);
insert into sc(sno,cno,scgrade) values(4,5,40);
问题:
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 * from sc where scgrade<60;先求不及格成绩
select sc.sno,s.sname,COUNT() as studentNum
from sc join s on sc.sno=s.sno
where scgrade<60 GROUP BY sc.sno,s.sname HAVING studentNum>=2;
求出平均成绩
SELECT sc.sno,avg(sc.scgrade)as scgrade
from sc group by sc.sno;
思路t1.sno=t2.sno;
最终语句
select t1.sname,t2.scgrade
from (select sc.sno,s.sname,COUNT() as studentNum
from sc join s on sc.sno=s.sno
where scgrade<60 GROUP BY sc.sno,s.sname HAVING studentNum>=2)t1
JOIN (SELECT sc.sno,avg(sc.scgrade)as scgrade
from sc group by sc.sno)t2
on t1.sno=t2.sno;
3、既学过1号课程又学过2号课所有学生的姓名
SELECT sno from sc where cno=1;
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);
14、列出所有员工及领导的姓名
select e.ename,b.ename as leadername
from emp e left
join emp b
on e.mgr=b.empno;
15、列出受雇日期早于其直接上级的所有员工的编号、姓名、部门名称
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;
16、列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门
SELECT d.dname, e.*
from emp e right
JOIN dept d
ON e.deptno=d.deptno;
17、列出至少有5个员工的所有部门
SELECT d.dname,COUNT(e.deptno)as coun
FROM emp e
JOIN dept d
ON d.deptno=e.deptno
GROUP BY d.deptno,e.deptno having COUNT(e.deptno)>=5;
另一种写法
select d.deptno,d.dname,count(e.deptno) from
emp e join dept d on e.deptno = d.deptno
group by e.deptno having count(e.deptno) >= 5;
没要求部门名称可以这么写,只需部门编号
SELECT e.deptno,COUNT(e.ename)as conanme
FROM emp e
GROUP BY e.deptno
HAVING conanme>=5;
18、列出薪金比‘simith’多的所有员工信息
simith的工资 SELECT sal from emp where ename=‘simith’;
SELECT * FROM emp WHERE sal>(SELECT sal from emp where ename=‘simith’);
19、列出所有‘clerk’(办事员)的姓名及部门名称,部门人数
SELECT e.ename,d.dname
FROM emp e
JOIN dept d
on e.deptno=d.deptno
WHERE e.job=‘clerk’;
求出每个部门的人数
SELECT e.deptno,COUNT(e.ename)as couname
FROM emp e GROUP BY e.deptno;
最终语句
SELECT t1.deptno,t1.ename,t1.dname,t2.couname
FROM (SELECT e.ename, d.deptno,d.dname FROM emp e JOIN dept d
on e.deptno=d.deptno WHERE e.job=‘clerk’)t1 right
JOIN (SELECT e.deptno,COUNT(e.ename)as couname
FROM emp e GROUP BY e.deptno)t2
ON t1.deptno=t2.deptno;
20、列出最低薪金大于1500的各种工作及从事此工作的全部雇员人数
先求各种工作的最低薪资
SELECT e.job, MIN(sal) AS minsal,COUNT(ename)AS couname
FROM emp e
group by e.job HAVING minsal>1500;
21、列出在部门‘sales’(销售部)工作的员工的姓名,假定不知道销售部的部门编号
SELECT deptno from dept where dname=‘sales’;
SELECT ename,deptno from emp e where
deptno=(SELECT deptno from dept where dname=‘sales’);
22、列出薪金高于公司平均薪金的所有员工,所在部门,上级领导,雇员的工资等级
select avg(sal) from emp;
最终语句
SELECT e.ename,d.dname,b.ename as leadername,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) from emp);
23、列出与‘scott’从事相同工作的所有员工及部门名称
从事什么工作 SELECT job from emp where ename=‘scott’;
SELECT e.ename,d.dname from emp e join dept d
on e.deptno=d.deptno where e.job=(SELECT job from emp where ename=‘scott’);
24、列出薪金等于部门30中员工的薪金的其他员工的姓名和薪金
用distinct去重
SELECT distinct sal from emp where deptno=30;
最终语句
SELECT ename,sal from emp where sal in
(SELECT distinct sal from emp where deptno=30) and
deptno<>30;
25、列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金,部门名称
SELECT sal from emp where deptno=30;
SELECT max(t1.sal) from emp where deptno=30;
最终语句
SELECT e.ename,e.sal,d.dname from emp e join dept d on e.deptno=d.deptno
where e.sal >(SELECT max(sal) from emp where deptno=30);
26、列出在每个部门工作的员工数量,平均工资和平均服务期限
to days(日期类型)->天数
获取数据库的系统当前时间的函数
SELECT ename,(TO_DAYS(NOW())-TO_DAYS(hiredate))/365 as serveryear from emp;
SELECT avg((TO_DAYS(NOW())-TO_DAYS(hiredate))/365) as avgserveryear from emp;
SELECT e.deptno,COUNT(e.ename)as couname,avg(e.sal)as avgsal,
avg((TO_DAYS(NOW())-TO_DAYS(hiredate))/365) as avgserveryear
from emp e GROUP BY e.deptno ;
(排序可在末尾加上ORDER BY couname ASC)
27、列出所有员工的姓名、部门名称、工资
SELECT d.dname,e.ename,e.sal from emp e right join dept d on
e.deptno=d.deptno ;
(排序可在末尾加上 ORDER BY d.dname)
28、列出所有部门的详细信息和人数
SELECT d.deptno,d.dname,d.loc,COUNT(e.ename)as total from emp e
right join dept d on d.deptno=e.deptno GROUP BY d.deptno,d.deptno,d.loc;
29、列出各种工作的最低工资以及从事此工作的雇员姓名
SELECT e.job,min(e.sal)as minsal from emp e group by e.job;
SELECT e.ename,t.minsal 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;
select e.ename,e.sal,a.minsal from emp e
join (select job,min(sal) minsal from emp group by job) a
on e.sal =a.minsal;
30、列出各个部门的manager的最低薪金
SELECT deptno,MIN(sal)as minsal from emp where job=‘manager’ group by deptno;
31、列出员工的年工资,按年薪从低到高排序
SELECT ename,(sal+IFNULL(comm,0))*12 as nianxin from emp order BY nianxin ASC;
32、求出员工领导的薪水超过3000的员工名称与领导姓名
SELECT e.ename,b.ename as leadername,b.sal
from emp e join emp b on e.mgr=b.empno where b.sal>3000;
33、求出部门名称中,带有‘s’字符的部门员工的工资合计、部门人数
SELECT dname,deptno from dept WHERE dname LIKE ‘%s%’;
SELECT t.dname,sum(e.sal),COUNT(e.ename)
FROM emp e join (SELECT dname,deptno from dept WHERE dname LIKE ‘%s%’)t
on t.deptno=e.deptno GROUP BY t.dname;
SELECT d.dname,SUM(e.sal),COUNT(e.ename) from emp e JOIN
dept d on e.deptno=d.deptno WHERE d.dname like ‘%s%’ GROUP BY d.dname;
34、给任职日期超过35年的员工加薪10%
create table emp_p as select * from emp;
UPDATE emp_p set sal=sal*1.1 where (TO_DAYS(NOW())-TO_DAYS(hiredate))>35;