首先初始化部门表、薪资等级表、员工表数据:
drop table if exists dept;
drop table if exists salgrade;
drop table if exists emp;
create table dept(
deptno int(10) primary key,
dname varchar(14),
loc varchar(13)
);
create table salgrade(
grade int(11),
losal int(11),
hisal int(11)
);
create table emp(
empno int(4) primary key,
ename varchar(10),
job varchar(9),
mgr int(4),
hiredate date,
sal double(7,2),
comm double(7,2),
deptno int(2)
);
insert into dept(deptno,dname,loc) values(10,'ACCOUNTING','NEW YORK');
insert into dept(deptno,dname,loc) values(20,'RESEARCHING','DALLAS');
insert into dept(deptno,dname,loc) values(30,'SALES','CHICAGO');
insert into dept(deptno,dname,loc) values(40,'OPERATIONS','BOSTON');
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,5000);
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
values(7369,'SIMITH','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,null,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);
select * from dept;
select * from salgrade;
select * from emp;
第1题:取得每个部门最高薪水的人员名称
思路:
1、取得每个部门最高薪水的人员
select max(sal),ename from emp group by deptno;
±---------±-------+
| max(sal) | ename |
±---------±-------+
| 5000.00 | CLARK |
| 3000.00 | SIMITH |
| 2850.00 | ALLEN |
±---------±-------+
2、但每个部门最高薪水的可能有多个人员,所以还得查员工表中同为该部门最高薪水的人员
elect e.sal,e.ename,e.deptno from emp e join ( select max(sal) sal,ename
,deptno from emp group by deptno) me on e.sal=me.sal and e.deptno=me.deptno;
±--------±------±-------+
| sal | ename | deptno |
±--------±------±-------+
| 2850.00 | BLAKE | 30 |
| 3000.00 | SCOTT | 20 |
| 5000.00 | KING | 10 |
| 3000.00 | FORD | 20 |
±--------±------±-------+
第2题:哪些人的薪水在部门平均薪水之上
思路:
1、求部门平均薪水
select avg(sal),ename,deptno from emp group by deptno;
2、哪些人的薪水在其部门大于刚求的部门平均薪水
select e.ename,e.sal,e.deptno from emp e join (select avg(sal) sal,ename,
deptno from emp group by deptno) ae on e.deptno = ae.deptno where e.sal>ae.sal;
±------±--------±-------+
| ename | sal | deptno |
±------±--------±-------+
| KING | 5000.00 | 10 |
| JONES | 2975.00 | 20 |
| SCOTT | 3000.00 | 20 |
| FORD | 3000.00 | 20 |
| ALLEN | 1600.00 | 30 |
| BLAKE | 2850.00 | 30 |
±------±--------±-------+
第3题:取得每个部门中所有人的平均薪水等级!
3.1 取得部门中所有人的平均薪水的等级
思路:
1、取得每个部门所有人的平均薪水
select avg(sal), deptno,ename from emp group by deptno;
+-------------+--------+
| avg(sal) | deptno |
+-------------+--------+
| 2916.666667 | 10 |
| 2175.000000 | 20 |
| 1566.666667 | 30 |
+-------------+--------+
2、取得每个部门中所有人的平均薪水等级,上面的表与等级表关联,使用between and求得上表中薪资在等级表中对应的等级。
select t1.avgsal, t1.deptno, salgrade.grade from (select avg(sal) avgsal,
deptno from emp group by deptno)as t1 join salgrade where t1.avgsal between sal
grade.losal and hisal;
+-------------+--------+-------+
| avgsal | deptno | grade |
+-------------+--------+-------+
| 2916.666667 | 10 | 4 |
| 2175.000000 | 20 | 4 |
| 1566.666667 | 30 | 3 |
+-------------+--------+-------+
3.2 取得部门中所有人的薪水等级平均值
思路:
1、取得所有人的薪水等级
select deptno,grade from emp join salgrade salg on emp.sal between salg.l
osal and hisal;
+--------+-------+
| deptno | grade |
+--------+-------+
| 20 | 1 |
| 30 | 3 |
| 30 | 2 |
| 20 | 4 |
| 30 | 2 |
| 30 | 4 |
| 10 | 4 |
| 20 | 4 |
| 10 | 5 |
| 30 | 3 |
| 20 | 1 |
| 30 | 1 |
| 20 | 4 |
| 10 | 2 |
+--------+-------+
2、 取得部门中所有人的薪水等级平均值
mysql> select emp.deptno,avg(grade) from emp join salgrade salg on emp.sal betwe
en salg.losal and hisal group by deptno;
+--------+------------+
| deptno | avg(grade) |
+--------+------------+
| 10 | 3.6667 |
| 20 | 2.8000 |
| 30 | 2.5000 |
+--------+------------+
第4题:不准用组函数(max),取得最高薪水(给出两种解决方案)
第一种:思路:排序limit1
select * from emp order by sal desc limit 0,1;
+-------+-------+-----------+------+------------+---------+------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+-------+-----------+------+------------+---------+------+--------+
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
+-------+-------+-----------+------+------------+---------+------+--------+
第二种:思路:自连接
1、自连接后查出较小的薪水
mysql> select distinct(a1.sal) from emp a1 join emp a2 on a1.sal<a2.sal;
+---------+
| sal |
+---------+
| 800.00 |
| 1250.00 |
| 1500.00 |
| 1100.00 |
| 950.00 |
| 1300.00 |
| 1600.00 |
| 2850.00 |
| 2450.00 |
| 2975.00 |
| 3000.00 |
+---------+
那么不在这个较小的薪水范围内的就是最高的薪水
select e.sal from emp e where e.sal not in ( select distinct(a1.sal) from
emp a1 join emp a2 on a1.sal<a2.sal);
+---------+
| sal |
+---------+
| 5000.00 |
+---------+
1 row in set (0.06 sec)
第5题:取得平均薪水最高的部门的部门编号(可能有一样的最后高薪水部门存在)
思路:1、取得各个部门平均薪水
select avg(sal) ,deptno from emp group by deptno;
+-------------+--------+
| avg(sal) | deptno |
+-------------+--------+
| 2916.666667 | 10 |
| 2175.000000 | 20 |
| 1566.666667 | 30 |
+-------------+--------+
2、取得平均薪水最高的部门的部门编号,这里使用having过滤那些等于最高薪水的,因为不止一个部门等于最高薪水
select avg(e.sal) asal,e.deptno from emp e group by deptno having asal =(
select max(a.asal) from (select avg(sal) asal ,deptno from emp
group by deptno) as a ) ;
+-------------+--------+
| asal | deptno |
+-------------+--------+
| 2916.666667 | 10 |
+-------------+--------+
第6题:取得平均薪水最高的部门的部门名称
思路:在第5题基础上再join部门表获取部门名称
mysql> select avg(e.sal)asal, e.deptno, dname from emp e join dept d on e.deptno
=d.deptno group by deptno having asal=(select max(a.asal) from (select avg(sal)
asal, e.deptno from emp e group by deptno) as a);
+-------------+--------+------------+
| asal | deptno | dname |
+-------------+--------+------------+
| 2916.666667 | 10 | ACCOUNTING |
+-------------+--------+------------+
第7题:求平均薪水的等级最低的部门的部门名称
思路:
1、求每个部门的平均薪水
mysql> select t.asal, s.grade from ( select avg(sal) asal from emp group by dept
no) as t join salgrade s on t.asal between s.losal and s.hisal;
+-------------+-------+
| asal | grade |
+-------------+-------+
| 2916.666667 | 4 |
| 2175.000000 | 4 |
| 1566.666667 | 3 |
+-------------+-------+
2、求上表等级最低的
mysql> select min(tt.grade),tt.deptno from (select t.asal, s.grade,t.deptno from
( select avg(sal) asal,e.deptno from emp e group by deptno) as t join salgrade
s on t.asal between s.losal and s.hisal)as tt;
+---------------+--------+
| min(tt.grade) | deptno |
+---------------+--------+
| 3 | 10 |
+---------------+--------+
3、求上表最低的部门的名称
mysql> select min(tt.grade),tt.deptno,d.dname from (select t.asal, s.grade,t.dep
tno from ( select avg(sal) asal,e.deptno from emp e group by deptno) as t join s
algrade s on t.asal between s.losal and s.hisal)as tt join dept d on tt.deptno=d
.deptno;
+---------------+--------+-------+
| min(tt.grade) | deptno | dname |
+---------------+--------+-------+
| 3 | 30 | SALES |
+---------------+--------+-------+
第8题:取得比普通员工(员工代码没有在mgr上出现的)的最高薪水还要高的经理人姓名 !
思路:
1、查询在mgr上出现的:
mysql> select distinct(mgr) from emp;
+------+
| mgr |
+------+
| 7902 |
| 7698 |
| 7839 |
| 7566 |
| NULL |
| 7788 |
| 7782 |
+------+
2、查询普通员工(员工代码没有在mgr上出现的)
mysql> select * from emp where empno not in ( select distinct(mgr) from emp);
Empty set (0.00 sec)
查出来的是空,这是因为not in 不会自动忽略空值,in会自动忽略空值,not in 里面的参数值的关系值都是and,in里面的参数值的关系都是or,如果有null值,它们就会运算,有null参与的数学运算结果都为null,所以这里要过滤掉null的情况。
mysql> select * from emp where empno not in ( select distinct(mgr) from emp wher
e mgr is not null);
+-------+--------+----------+------+------------+---------+---------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+--------+----------+------+------------+---------+---------+--------+
| 7369 | SIMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | NULL | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |
+-------+--------+----------+------+------------+---------+---------+--------+
3、求出普通员工薪资最高的
mysql> select max(sal) from emp where empno not in ( select distinct(mgr) from e
mp where mgr is not null);
+----------+
| max(sal) |
+----------+
| 1600.00 |
+----------+
4、取得比普通员工(员工代码没有在mgr上出现的)的最高薪水还要高的经理人姓名
mysql> select ename from emp where sal > (select max(sal) from emp where empno n
ot in (select distinct(mgr) from emp where mgr is not null));
+-------+
| ename |
+-------+
| JONES |
| BLAKE |
| CLARK |
| SCOTT |
| KING |
| FORD |
+-------+
第9题:取得薪水最高的前五名员工
mysql> select empno,ename,sal from emp order by sal desc limit 0,5;
+-------+-------+---------+
| empno | ename | sal |
+-------+-------+---------+
| 7839 | KING | 5000.00 |
| 7788 | SCOTT | 3000.00 |
| 7902 | FORD | 3000.00 |
| 7566 | JONES | 2975.00 |
| 7698 | BLAKE | 2850.00 |
+-------+-------+---------+
第10题:取得薪水最高的第六到第十名员工
mysql> select empno,ename,sal from emp order by sal desc limit 5,5;
+-------+--------+---------+
| empno | ename | sal |
+-------+--------+---------+
| 7782 | CLARK | 2450.00 |
| 7499 | ALLEN | 1600.00 |
| 7844 | TURNER | 1500.00 |
| 7934 | MILLER | 1300.00 |
| 7654 | MARTIN | 1250.00 |
+-------+--------+---------+
第11题:取得最后入职的5名员工
mysql> select empno,ename,hiredate from emp order by hiredate desc limit 0,5;
+-------+--------+------------+
| empno | ename | hiredate |
+-------+--------+------------+
| 7876 | ADAMS | 1987-05-23 |
| 7788 | SCOTT | 1987-04-19 |
| 7934 | MILLER | 1982-01-23 |
| 7902 | FORD | 1981-12-03 |
| 7900 | JAMES | 1981-12-03 |
+-------+--------+------------+
第12题:取得每个薪水等级有多少员工
思路:
1、求出各个员工所在薪资等级
mysql> select empno,ename,sal,grade from emp join salgrade on sal between losal
and hisal;
+-------+--------+---------+-------+
| empno | ename | sal | grade |
+-------+--------+---------+-------+
| 7369 | SIMITH | 800.00 | 1 |
| 7499 | ALLEN | 1600.00 | 3 |
| 7521 | WARD | 1250.00 | 2 |
| 7566 | JONES | 2975.00 | 4 |
| 7654 | MARTIN | 1250.00 | 2 |
| 7698 | BLAKE | 2850.00 | 4 |
| 7782 | CLARK | 2450.00 | 4 |
| 7788 | SCOTT | 3000.00 | 4 |
| 7839 | KING | 5000.00 | 5 |
| 7844 | TURNER | 1500.00 | 3 |
| 7876 | ADAMS | 1100.00 | 1 |
| 7900 | JAMES | 950.00 | 1 |
| 7902 | FORD | 3000.00 | 4 |
| 7934 | MILLER | 1300.00 | 2 |
+-------+--------+---------+-------+
2、分组count
mysql> select count(empno),grade from emp join salgrade on sal between losal and
hisal group by grade;
+--------------+-------+
| count(empno) | grade |
+--------------+-------+
| 3 | 1 |
| 3 | 2 |
| 2 | 3 |
| 5 | 4 |
| 1 | 5 |
+--------------+-------+
第13题:
有三个表s(学生表),c(课程表),sc(学生选课表)
S(SNo, SName)代表(学号, 姓名)
C(CNo, CName, CTeacher)代表(课号, 课名, 老师)
SC(SNo, CNo, Score)代表(学号, 课号, 成绩)
首先初始化数据:
drop table if exists s;
drop table if exists c;
drop table if exists sc;
create table s(
sno int(10) primary key auto_increment,
sname varchar(32)
);
create table c(
cno int(10) primary key auto_increment,
cname varchar(32),
cteacher varchar(32)
);
// sc表中学生与课程是多对多关系,主键设成谁(复合主键(sno,cno))
// sno和cno也必须来自学生表和课程表,因此他们是主键的同时也是外键
// 一个表只能有一个主键但是可以有多个外键
create table sc(
sno int(10),
cno int(10),
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('张三');
insert into s(sname) values('李四');
insert into s(sname) values('王五');
insert into s(sname) values('赵六');
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,80);
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,60);
insert into sc(sno,cno,scgrade) values(4,3,50);
insert into sc(sno,cno,scgrade) values(4,5,40);
select * from s;
select * from c;
select * from sc;
问题:
1、找出没选过”黎明”老师的所有学生姓名 !
思路:简单子语句嵌套查询,首先找出选过”黎明”老师的所有学生编号
mysql> select * from sc where sc.cno =(select c.cno from c where c.cteacher = '
黎明');
+-----+-----+---------+
| sno | cno | scgrade |
+-----+-----+---------+
| 3 | 5 | 52 |
| 4 | 5 | 40 |
+-----+-----+---------+
然后找出没选过”黎明”老师的所有学生姓名
mysql> select sname from s where s.sno not in ( select sc.sno from sc where sc.c
no =(select c.cno from c where c.cteacher = '黎明'));
+-------+
| sname |
+-------+
| a |
| b |
+-------+
2、列出2门以上(含2门)不及格学生姓名及平均分
思路:1、列出2门以上(含2门)不及格学生编号
mysql> select count(cno) count,sno from sc where scgrade<60 group by sno havin
g count>1;
+-------+-----+
| count | sno |
+-------+-----+
| 3 | 1 |
+-------+-----+
2、顺便求出其平均分
mysql> select count(cno) count,sno,avg(scgrade) from sc where scgrade<60 group
by sno having count>1;
+-------+-----+--------------+
| count | sno | avg(scgrade) |
+-------+-----+--------------+
| 3 | 1 | 50.0000 |
+-------+-----+--------------+
3、s和上表关联求出姓名
mysql> select sname,t.avg from s join (select count(cno) count,sno,avg(scgrade)
avg from sc where scgrade<60 group by sno having count>1)t on s.sno = t.sno;
+-------+---------+
| sname | avg |
+-------+---------+
| a | 50.0000 |
+-------+---------+
3、既学过1号课程也学过2号课程的学生 (高频)
解法1思路:1、找出既学过1号课程也学过2号课程的学生编号
mysql> select count(cno) count,sno from sc where cno in (1,2) group by sno having count>1;
+-------+-----+
| count | sno |
+-------+-----+
| 2 | 1 |
| 2 | 3 |
+-------+-----+
2、求出学生姓名
mysql> select s.sno,s.sname from s join (select count(cno) count,sno from sc whe
re cno in (1,2) group by sno having count>1)t on s.sno =t.sno;
+-----+-------+
| sno | sname |
+-----+-------+
| 1 | a |
| 3 | c |
+-----+-------+
第14题:列出所有员工及领导的名字(重点“所有”)
解法思路:因为KING是大BOSS,他的上级是null,使用JOIN的话会漏掉他,所以使用left join
mysql> select e1.ename,e1.empno,e1.mgr,e2.ename from emp e1 left join emp e2 on
e1.mgr=e2.empno;
+--------+-------+------+-------+
| ename | empno | mgr | ename |
+--------+-------+------+-------+
| SIMITH | 7369 | 7902 | FORD |
| ALLEN | 7499 | 7698 | BLAKE |
| WARD | 7521 | 7698 | BLAKE |
| JONES | 7566 | 7839 | KING |
| MARTIN | 7654 | 7698 | BLAKE |
| BLAKE | 7698 | 7839 | KING |
| CLARK | 7782 | 7839 | KING |
| SCOTT | 7788 | 7566 | JONES |
| KING | 7839 | NULL | NULL |
| TURNER | 7844 | 7698 | BLAKE |
| ADAMS | 7876 | 7788 | SCOTT |
| JAMES | 7900 | 7698 | BLAKE |
| FORD | 7902 | 7566 | JONES |
| MILLER | 7934 | 7782 | CLARK |
+--------+-------+------+-------+
第15题:列出受雇日期早于其直接上级的所有员工编号、姓名、部门名称(三表连接)
解题思路:
1、子查询出日期早于其直接上级的所有员工编号、姓名
mysql> select e1.ename,e1.empno,e1.mgr,e2.ename,e1.deptno from emp e1 join emp e
2 on e1.mgr=e2.empno and e1.hiredate<e2.hiredate;
+--------+-------+------+-------+--------+
| ename | empno | mgr | ename | deptno |
+--------+-------+------+-------+--------+
| SIMITH | 7369 | 7902 | FORD | 20 |
| ALLEN | 7499 | 7698 | BLAKE | 30 |
| WARD | 7521 | 7698 | BLAKE | 30 |
| JONES | 7566 | 7839 | KING | 20 |
| BLAKE | 7698 | 7839 | KING | 30 |
| CLARK | 7782 | 7839 | KING | 10 |
+--------+-------+------+-------+--------+
2、上面表作为临时表join on部门表
mysql> select t.tname,t.empno,d.dname from (select e1.ename tname,e1.empno,e1.mg
r,e2.ename,e1.deptno from emp e1 join emp e2 on e1.mgr=e2.empno and e1.hiredate<
e2.hiredate) t join dept d on t.deptno=d.deptno;
+--------+-------+-------------+
| tname | empno | dname |
+--------+-------+-------------+
| SIMITH | 7369 | RESEARCHING |
| ALLEN | 7499 | SALES |
| WARD | 7521 | SALES |
| JONES | 7566 | RESEARCHING |
| BLAKE | 7698 | SALES |
| CLARK | 7782 | ACCOUNTING |
+--------+-------+-------------+
第16题:列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门(外连接)
解题思路:部门表left join 员工表
mysql> select dept.deptno, dept.dname,e.empno, e.ename from dept left join emp
e on dept.deptno=e.deptno;
+--------+-------------+-------+--------+
| deptno | dname | empno | ename |
+--------+-------------+-------+--------+
| 20 | RESEARCHING | 7369 | SIMITH |
| 30 | SALES | 7499 | ALLEN |
| 30 | SALES | 7521 | WARD |
| 20 | RESEARCHING | 7566 | JONES |
| 30 | SALES | 7654 | MARTIN |
| 30 | SALES | 7698 | BLAKE |
| 10 | ACCOUNTING | 7782 | CLARK |
| 20 | RESEARCHING | 7788 | SCOTT |
| 10 | ACCOUNTING | 7839 | KING |
| 30 | SALES | 7844 | TURNER |
| 20 | RESEARCHING | 7876 | ADAMS |
| 30 | SALES | 7900 | JAMES |
| 20 | RESEARCHING | 7902 | FORD |
| 10 | ACCOUNTING | 7934 | MILLER |
| 40 | OPERATIONS | NULL | NULL |
+--------+-------------+-------+--------+
第17题:列出至少有5个员工的所有部门
count后使用having进行过滤
mysql> select count(1) as count,deptno from emp group by deptno having count>4;
+-------+--------+
| count | deptno |
+-------+--------+
| 5 | 20 |
| 6 | 30 |
+-------+--------+
第18题:列出薪水比"SIMITH"多的所有员工信息
mysql> select * from emp where sal > (select sal from emp where ename='SIMITH');
+-------+--------+-----------+------+------------+---------+---------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+--------+-----------+------+------------+---------+---------+--------+
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | NULL | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |
+-------+--------+-----------+------+------------+---------+---------+--------+
第19题:列出所有"CLERK"(办事员)的姓名及其部门名称,部门人数
解决思路:
1、列出所有"CLERK"(办事员)的姓名及其部门名称
mysql> select e.ename, e.deptno,d.dname from emp e join dept d on e.deptno=d.de
ptno and e.job='CLERK';
+--------+--------+-------------+
| ename | deptno | dname |
+--------+--------+-------------+
| SIMITH | 20 | RESEARCHING |
| ADAMS | 20 | RESEARCHING |
| JAMES | 30 | SALES |
| MILLER | 10 | ACCOUNTING |
+--------+--------+-------------+
2、求出各个部门人数
mysql> select count(1) count,deptno from emp group by deptno;
+-------+--------+
| count | deptno |
+-------+--------+
| 3 | 10 |
| 5 | 20 |
| 6 | 30 |
+-------+--------+
3、上面两个表联表查询
mysql> select t.ename, t.dname,a.count from (select e.ename, e.deptno,d.dname fr
om emp e join dept d on e.deptno=d.deptno and e.job='CLERK')t join ( select cou
nt(1) count,deptno from emp group by deptno) a on t.deptno=a.deptno;
+--------+-------------+-------+
| ename | dname | count |
+--------+-------------+-------+
| SIMITH | RESEARCHING | 5 |
| ADAMS | RESEARCHING | 5 |
| JAMES | SALES | 6 |
| MILLER | ACCOUNTING | 3 |
+--------+-------------+-------+
第20题:列出最低薪水大于1500的各种工作以及从事此工作的全部雇员人数!
mysql> select e.job, min(e.sal) as minsal, count(e.ename) as total
-> from emp e
-> group by e.job having minsal > 1500;
+-----------+---------+-------+
| job | minsal | total |
+-----------+---------+-------+
| ANALYST | 3000.00 | 2 |
| MANAGER | 2450.00 | 3 |
| PRESIDENT | 5000.00 | 1 |
+-----------+---------+-------+
第21题:列出在部门"SALES"(销售部)工作的员工姓名,假定不知道销售部门的部门编号
mysql> select e.ename from emp e,dept d where e.deptno=d.deptno and d.dname='SAL
ES';
+--------+
| ename |
+--------+
| ALLEN |
| WARD |
| MARTIN |
| BLAKE |
| TURNER |
| JAMES |
+--------+
第22题:列出薪资高于公司平均薪金的所有员工,所在部门,上级领导,雇员的工资等级(四表连接)
mysql> select t.empno, t.ename, t.deptno,d.dname,t.mgr,ee.ename upename,s.grade
from (select empno,ename,deptno,mgr,sal from emp a where a.sal> (select avg(sal)
avg from emp))t join dept d on t.deptno=d.deptno join salgrade s on t.sal betwe
en s.losal and s.hisal left join emp ee on t.mgr=ee.empno;
+-------+-------+--------+-------------+------+---------+-------+
| empno | ename | deptno | dname | mgr | upename | grade |
+-------+-------+--------+-------------+------+---------+-------+
| 7566 | JONES | 20 | RESEARCHING | 7839 | KING | 4 |
| 7698 | BLAKE | 30 | SALES | 7839 | KING | 4 |
| 7782 | CLARK | 10 | ACCOUNTING | 7839 | KING | 4 |
| 7788 | SCOTT | 20 | RESEARCHING | 7566 | JONES | 4 |
| 7839 | KING | 10 | ACCOUNTING | NULL | NULL | 5 |
| 7902 | FORD | 20 | RESEARCHING | 7566 | JONES | 4 |
+-------+-------+--------+-------------+------+---------+-------+
第23题:列出与"SCOTT"从事相同工作的所有员工及部门名称!
解题思路:join on 对中间结果过滤,where对最后结果过滤
mysql> select t.empno,t.ename,t.deptno,d.dname from (select empno, ename,deptno
from emp e where job=(select job from emp where ename='SCOTT'))t join dept d on
t.deptno=d.deptno;
+-------+-------+--------+-------------+
| empno | ename | deptno | dname |
+-------+-------+--------+-------------+
| 7788 | SCOTT | 20 | RESEARCHING |
| 7902 | FORD | 20 | RESEARCHING |
+-------+-------+--------+-------------+
第24题:列出薪资等于部门30中员工的薪资的其他部门员工的姓名和薪资
解题思路: 使用in,在某个范围内
mysql> select * from emp e where e.sal in (select distinct sal from emp e where
e.deptno=30) and e.deptno!=30;
Empty set (0.00 sec)
第25题:列出薪资高于在部门30工作的所有员工的薪资的员工姓名、薪资和部门名称
mysql> select e.ename,e.sal,e.deptno,d.dname from emp e join dept d on e.deptno=
d.deptno where sal>(select max(sal) from emp where deptno ='30');
+-------+---------+--------+-------------+
| ename | sal | deptno | dname |
+-------+---------+--------+-------------+
| JONES | 2975.00 | 20 | RESEARCHING |
| SCOTT | 3000.00 | 20 | RESEARCHING |
| KING | 5000.00 | 10 | ACCOUNTING |
| FORD | 3000.00 | 20 | RESEARCHING |
+-------+---------+--------+-------------+
第26题:列出在每个部门工作的员工数量,平均薪资和平均服务期限(to_days)(now())!
解题思路:to_days函数:距离公元元年多少天
mysql> select deptno,count(empno), avg(sal),(to_days (now())-to_days( hiredate))
/365 from emp group by deptno;
+--------+--------------+-------------+-----------------------------------------
-+
| deptno | count(empno) | avg(sal) | (to_days (now())-to_days( hiredate))/365
|
+--------+--------------+-------------+-----------------------------------------
-+
| 10 | 3 | 2916.666667 | 39.2740
|
| 20 | 5 | 2175.000000 | 39.7507
|
| 30 | 6 | 1566.666667 | 39.5726
|
+--------+--------------+-------------+-----------------------------------------
第27题:列出所有员工的姓名、部门名称和工资!
解题思路:列出所有的时候,注意使用left join和right join ,防止为NULL的情况。
mysql> select e.ename,d.dname, e.sal from emp e right join dept d on e.deptno=d.
deptno;
+--------+-------------+---------+
| ename | dname | sal |
+--------+-------------+---------+
| SIMITH | RESEARCHING | 800.00 |
| ALLEN | SALES | 1600.00 |
| WARD | SALES | 1250.00 |
| JONES | RESEARCHING | 2975.00 |
| MARTIN | SALES | 1250.00 |
| BLAKE | SALES | 2850.00 |
| CLARK | ACCOUNTING | 2450.00 |
| SCOTT | RESEARCHING | 3000.00 |
| KING | ACCOUNTING | 5000.00 |
| TURNER | SALES | 1500.00 |
| ADAMS | RESEARCHING | 1100.00 |
| JAMES | SALES | 950.00 |
| FORD | RESEARCHING | 3000.00 |
| MILLER | ACCOUNTING | 1300.00 |
| NULL | OPERATIONS | NULL |
+--------+-------------+---------+
第28题:列出所有部门的详细信息和人数
mysql> select d.*,count(e.empno) from dept d left join emp e on d.deptno=e.deptn
o group by d.deptno;
+--------+-------------+----------+----------------+
| deptno | dname | loc | count(e.empno) |
+--------+-------------+----------+----------------+
| 10 | ACCOUNTING | NEW YORK | 3 |
| 20 | RESEARCHING | DALLAS | 5 |
| 30 | SALES | CHICAGO | 6 |
| 40 | OPERATIONS | BOSTON | 0 |
+--------+-------------+----------+----------------+
第29题:列出各种工作的最低工资以及从事此工作的雇员姓名!
mysql> select minsal ,e.ename from emp e join (select min(sal) minsal,ename,job
from emp group by job)t on e.job =t.job where e.sal=t.minsal;
+---------+--------+
| minsal | ename |
+---------+--------+
| 800.00 | SIMITH |
| 1250.00 | WARD |
| 1250.00 | MARTIN |
| 2450.00 | CLARK |
| 3000.00 | SCOTT |
| 5000.00 | KING |
| 3000.00 | FORD |
+---------+--------+
第30题:列出各个部门manager的最低薪资
mysql> select deptno,min(sal) from emp where job = 'MANAGER' group by deptno;
+--------+----------+
| deptno | min(sal) |
+--------+----------+
| 10 | 2450.00 |
| 20 | 2975.00 |
| 30 | 2850.00 |
+--------+----------+
第31题:列出所有员工的年工资,按年薪从低到高排序(ifnull)
ifnull() 函数用于判断第一个表达式是否为 NULL,如为 NULL 则返回第二个参数的值,如果不为 NULL 则返回第一个参数的值。
comm是提成
mysql> select ename,(12*sal+ifnull(comm,0)) from emp order by sal asc;
+--------+-------------------------+
| ename | (12*sal+ifnull(comm,0)) |
+--------+-------------------------+
| SIMITH | 9600.00 |
| JAMES | 11400.00 |
| ADAMS | 13200.00 |
| WARD | 15500.00 |
| MARTIN | 16400.00 |
| MILLER | 15600.00 |
| TURNER | 18000.00 |
| ALLEN | 19500.00 |
| CLARK | 29400.00 |
| BLAKE | 34200.00 |
| JONES | 35700.00 |
| FORD | 36000.00 |
| SCOTT | 36000.00 |
| KING | 60000.00 |
+--------+-------------------------+
第32题:求出员工领导的薪水超过3000的员工名称和领导名称
mysql> select e1.ename,e2.ename from emp e1 left join emp e2 on e1.mgr=e2.empno
where e2.sal>3000;
+-------+-------+
| ename | ename |
+-------+-------+
| JONES | KING |
| BLAKE | KING |
| CLARK | KING |
+-------+-------+
第33题:求部门名称中带s字符的部门员工的工资合计、部门人数(like 与通配符)
mysql> select sum(sal), count(1),d.dname from emp e join dept d on e.deptno=d.de
ptno where d.dname like '%s%' group by d.deptno;
+----------+----------+-------------+
| sum(sal) | count(1) | dname |
+----------+----------+-------------+
| 10875.00 | 5 | RESEARCHING |
| 9400.00 | 6 | SALES |
+----------+----------+-------------+
第34题:给任职日期超过30年的员工加薪10%(select 是查询,这个是修改update)
为了不影响原表,做了备份表emp1
mysql> update emp1 set sal=sal*1.1 where (to_days(now())-to_days(hiredate)/365)>
30;
mysql> select * from emp1 where (to_days(now())-to_days(hiredate)/365)>30;
+-------+--------+-----------+------+------------+---------+---------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+--------+-----------+------+------------+---------+---------+--------+
| 7369 | SIMITH | CLERK | 7902 | 1980-12-17 | 880.00 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1760.00 | 300.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1375.00 | 500.00 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 3272.50 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1375.00 | 1400.00 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 3135.00 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2695.00 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3300.00 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5500.00 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1650.00 | NULL | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1210.00 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 1045.00 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3300.00 | NULL | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1430.00 | NULL | 10 |
+-------+--------+-----------+------+------------+---------+---------+--------+
题目很多,但是做完还是很有收获的~