表创建及数据见文章最后
1、取得每个部门最高薪水的人员名称
select
t.deptno,t.maxsal,e.ename
from
emp e
join
(select max(sal) as maxsal,deptno from emp group by deptno) t
on
e.sal = t.maxsal and e.deptno = t.deptno;
2、哪些人的薪水在部门的平均薪水之上
select
e.*
from
emp e
join
(select avg(sal) as avgsal,deptno from emp group by deptno) t
on
e.sal>t.avgsal and e.deptno = t.deptno order by deptno;
3、取得部门中（所有人的）平均的薪水等级，如下：
select
e.deptno,avg(s.grade)
from
emp e
join
salgrade s
on
e.sal between s.losal and s.hisal group by e.deptno;
4、不准用组函数（Max），取得最高薪水
select
*
from
emp
order by sal desc limit 0,1;
5、取得平均薪水最高的部门的部门编号
1.
select
deptno
from
emp
group by
deptno
order by avg(sal) desc limit 0,1;
2.
select
avg(s.sal) as avgsal,s.deptno
from
emp s
group by
s.deptno
having
avgsal=(select max(e.avgsal) as maxavgsal from (select avg(sal) as avgsal,deptno from emp group by deptno) e) ;
6、取得平均薪水最高的部门的部门名称
select
se.deptno,d.dname
from
(select avg(s.sal) as avgsal,s.deptno from emp s group by s.deptno having avgsal=(select max(e.avgsal) as maxavgsal from (select avg(sal) as avgsal,deptno from emp group by deptno) e) )se
join
dept d
on se.deptno = d.deptno;
7、求平均薪水的等级最低的部门的部门名称
select
d.dname
from
dept d
where d.deptno = (select asal.deptno from (select avg(sal) as avgsal,deptno from emp group by deptno )asal join salgrade ag on asal.avgsal between ag.losal and ag.hisal order by ag.grade limit 0,1);
8、取得比普通员工(员工代码没有在mgr字段上出现的)的最高薪水还要高的领导人姓名
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)) and empno in(select distinct mgr from emp where mgr is not null);
9、取得薪水最高的前五名员工
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 limit 0,5;
12、取得每个薪水等级有多少员工
select
count(sg.grade),sg.grade
from
emp e
join
salgrade sg on e.sal between sg.losal and sg.hisal group by sg.grade;
13、面试题
有3个表S(学生表)，C（课程表），SC（学生选课表）
S（SNO，SNAME）代表（学号，姓名）
C（CNO，CNAME，CTEACHER）代表（课号，课名，教师）
SC（SNO，CNO，SCGRADE）代表（学号，课号，成绩）
问题：
1，找出没选过“黎明”老师的所有学生姓名。
select
*
from
student
where
sno not in (select sno from student_course where cno = (select cno from course where cteacher = '黎明'));
2，列出2门以上（含2门）不及格学生姓名及平均成绩。
select
s.sname,sc.avgg
from
student s
join (select scbjg.sno,scbjg.avgg from (select count(sno) as snonum,sno,avg(scgrade) as avgg from student_course where scgrade<60 group by sno)scbjg where scbjg.snonum>=2) sc on s.sno = sc.sno;
3，即学过1号课程又学过2号课所有学生的姓名。
select
*
from
student
where sno in (select sc1.sno from (select * from student_course where cno =1) sc1 join (select * from student_course where cno = 2) sc2 on sc1.sno = sc2.sno);
请用标准SQL语言写出答案，方言也行（请说明是使用什么方言）。
14、列出所有员工及领导的姓名
select
ep.ename,t.mgrname
from
emp ep
left join(select em.ename as mgrname ,e.mgr,e.empno from emp e left join emp em on e.mgr = em.empno) t on (ep.mgr = t.mgr )and (ep.empno = t.empno);
15、列出受雇日期早于其直接上级的所有员工的编号,姓名,部门名称
select
t.empno,t.ename,d.dname
from (select * from emp e where hiredate < (select hiredate from emp where empno = e.mgr ))t join dept d on t.deptno = d.deptno;
16、列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门.
select
e.*,d.*
from
dept d
left join
emp e
on
d.deptno = e.deptno
order by
deptno;
17、列出至少有5个员工的所有部门
select
d.dname
from
dept d
left join
emp e
on
d.deptno = e.deptno
group by
d.dname
having
count(e.empno)>=5;
18、列出薪金比"SMITH"多的所有员工信息.
select
*
from
emp
where
sal >(select sal from emp where ename ='smith');
19、列出所有"CLERK"(办事员)的姓名及其部门名称,部门的人数.
SELECT
t.ename, t.dname, p.deptnum
FROM
(
SELECT
e.*, d.dname
FROM
emp e
JOIN
dept d
ON
e.job='clerk' AND e.deptno = d.deptno
) t
JOIN (
SELECT
COUNT(ds.dname) AS deptnum, ds.dname
FROM
emp s
JOIN
dept ds
ON
s.job='clerk' AND ds.deptno = s.deptno
GROUP BY
s.deptno, ds.dname
) p
ON
t.dname = p.dname;
20、列出最低薪金大于1500的各种工作及从事此工作的全部雇员人数.
select
job,count(job) as jobnums
from
emp
where
job in (select distinct job from emp where sal > 1500)
group by
job;
21、列出在部门"SALES"<销售部>工作的员工的姓名,假定不知道销售部的部门编号.
select
*
from
emp
where deptno = (select deptno from dept where dname = 'sales');
22、列出薪金高于公司平均薪金的所有员工,所在部门,上级领导,雇员的工资等级.
select
e.ename as emname,d.dname as deptname,emgr.ename as mgrname, sg.grade as emgrade
from
emp e
join
dept d
on
e.deptno = d.deptno
left join
emp emgr
on
e.mgr = emgr.empno
join
salgrade sg
on
e.sal between sg.losal and hisal
where
e.sal>(select avg(sal) from emp);
23、列出与"SCOTT"从事相同工作的所有员工及部门名称.
select
e.*,d.dname
from
emp e
join
dept d
on
e.deptno = d.deptno
where
job = (select job from emp where ename = 'scott');
24、列出薪金等于部门30中员工的薪金的其他员工的姓名和薪金.
select
*
from
emp
where
sal in (select sal from emp where deptno = 30) and deptno != 30;
25、列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金.部门名称.
select
e.ename,e.sal,d.dname
from
emp e
join
dept d
on
e.deptno = d.deptno
where
sal > (select max(sal) from emp where deptno = 30);
26、列出在每个部门工作的员工数量,平均工资和平均服务期限.
select
d.deptno, ifnull(count(e.empno) ,0) as '员工数量',ifnull(avg(e.sal) ,0) as '平均工资' , ifnull(avg(Timestampdiff(year ,e.hiredate,now() )) , 0) as '平均服务期限'
from
emp e
right join
dept d
on
e.deptno = d.deptno
group by
d.deptno;
27、列出所有员工的姓名、部门名称和工资。
select
e.ename ,d.dname,e.sal
from
emp e
left join
dept d
on
e.deptno = d.deptno;
28、列出所有部门的详细信息和人数
select
d.*,t.nums
from
dept d
left join
( select ifnull(count(empno),0) as nums , dm.deptno from emp em right join dept dm on em.deptno = dm.deptno group by deptno) t
on
d.deptno = t.deptno;
29、列出各种工作的最低工资及从事此工作的雇员姓名
select
e.*
from
emp e
join
(select job,min(sal) as minsal from emp group by job) t
on
e.job = t.job and e.sal = t.minsal;
30、列出各个部门的MANAGER(领导)的最低薪金
select
d.deptno as '部门',ifnull(min(e.sal),0) as '最低薪金'
from
emp e
right join
dept d
on
e.deptno = d.deptno
where
e.empno in (select mgr from emp) or e.mgr is null
group by
d.deptno;
31、列出所有员工的年工资,按年薪从低到高排序
select
ename as '姓名',sal*12 as '年薪'
from
emp
order by
'年薪';
32、求出员工领导的薪水超过3000的员工名称与领导名称
select
e.ename as '员工姓名',emgr.ename as '领导姓名'
from
emp e
join
( select empno,ename,sal from emp where ((empno in (select mgr from emp)) or (mgr is null)) and sal>3000) emgr
on
e.mgr = emgr.empno;
33、求出部门名称中,带'S'字符的部门员工的工资合计、部门人数.
select
d.dname as '部门名称', ifnull(t.sumsal,0) as '工资合计',ifnull(t.deptnums,0) as '部门人数'
from
dept d
left join
(select sum(sal) as sumsal ,deptno,count(empno) as deptnums from emp where ename like '%s%' group by deptno)t
on
d.deptno = t.deptno;
34、给任职日期超过30年的员工加薪10%.
update
emp
set
sal = sal *1.1
where
Timestampdiff(year,hiredate,now()) > 41;
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;
