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;

MySQL练习题(附带答案和结果)_第1张图片

问题

1、取得每个部门最高薪水的人员名称
2、哪些人的薪水再部门平均薪资以上
3、取得部门中(所有人的)平均薪水等级
4、不使用组函数max,取得最高薪资(两种方案)
5、取得平均薪资最高的部门的部门编号(两种方案)
6、取得平均薪资最高的部门的部门名称
7、求平均薪资的等级最低的部门的部门名称
8、 取得比普通员工(员工代码没有在 mgr 字段上出现的)的最高薪水还要高的领导人姓名
9、取得薪资最高的前5名员工
10、取薪资最高的第六到第十名员工
11、取得最后入职的五名员工
12、取得每个薪资等级有多少员工
13、
有 3 个表 S(学生表),C(课程表),SC(学生选课表)
S(SNO,SNAME)代表(学号,姓名)
C(CNO,CNAME,CTEACHER)代表(课号,课名,教师)
SC(SNO,CNO,SCGRADE)代表(学号,课号,成绩)
问题:
1),找出没选过“黎明”老师的所有学生姓名。
2),列出 2 门以上(含 2 门)不及格学生姓名及平均成绩。
3),即学过 1 号课程又学过 2 号课所有学生的姓名。

CREATE TABLE SC
(
SNO VARCHAR(200),
CNO VARCHAR(200),
SCGRADE VARCHAR(200)
);
CREATE TABLE S
(
SNO VARCHAR(200),
SNAME VARCHAR(200)
);
CREATE TABLE C
(
CNO VARCHAR(200),
CNAME VARCHAR(200),
CTEACHER VARCHAR(200)
);
INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( '1', '语文', '张');
INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( '2', '政治', '王');
INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( '3', '英语', '李');
INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( '4', '数学', '赵');
INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( '5', '物理', '黎明');
commit;
INSERT INTO S ( SNO, SNAME ) VALUES ( '1', '学生 1');
INSERT INTO S ( SNO, SNAME ) VALUES ( '2', '学生 2');
INSERT INTO S ( SNO, SNAME ) VALUES ( '3', '学生 3');
INSERT INTO S ( SNO, SNAME ) VALUES ( '4', '学生 4');
commit;
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '1', '1', '40');
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '1', '2', '30');
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '1', '3', '20');
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '1', '4', '80');
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '1', '5', '60');
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '2', '1', '60');
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '2', '2', '60');
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '2', '3', '60');
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '2', '4', '60');
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '2', '5', '40');
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '3', '1', '60');
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '3', '3', '80');
commit

14、请列出所有员工及领导姓名
15、列出受雇日期早于其直接上级的所有员工的编号、姓名和部门名称
16、列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门
17、列出至少有五个员工的部门
18、列出薪资比"SMITH"多的所有员工信息
19、列出所有"CLERKK"的姓名、部门名称以及部门人数
20、列出最低薪资大于1500的各种工作以及从事该工作的全部员工的信息
21、列出在部门"SALES"工作的员工的姓名(不使用部门编号)
22、列出薪资高于公司平均薪资的所有员工姓名、所在部门、上级领导、工资等级
23、列出与"SCOTT"从事相同工作的所有员工及部门名称
24、列出薪资等于部门编号为30中员工的薪资的其他员工的姓名和薪资
25、列出薪资高于在部门编号为30工作的所有员工的薪资的员工姓名、薪资和部门名称
26、列出在每个部门工作的员工数量,平均工资和平均服务期限
27、列出所有员工的姓名、部门名称和工资
28、列出所有部门的详细信息和人数
29、列出不同工作的最低工资以及该工资的员工的信息
30、列出各个部门"MANAGER"的最低薪资
31、列出所有员工的年工资,按照年薪升序排列
32、列出员工领导的薪资超过3000的员工姓名和领导姓名
33、列出部门名称中带"S"字符的部门员工的工资合计和部门人数
34、给任职日期超过30年的员工加薪10%

问题加答案(附带查询结果)

1、取得每个部门最高薪水的人员名称、部门编号和薪资

// 先查询出每个部门的最高薪资
select deptno,max(sal) as maxsal from emp group by deptno;

// 将上述查询结果当作一张临时表与emp表进行连接查询
select
    e.ename,t.deptno,t.maxsal
from
    emp e
join 
    (select deptno,max(sal) as maxsal from emp group by deptno) t
on
    t.deptno=e.deptno and t.maxsal=e.sal;

MySQL练习题(附带答案和结果)_第2张图片
2、哪些人的薪水在部门平均薪资以上

// 先查询出每个部门的平均薪资avgsal
select deptno,avg(sal) as avgsal from emp group by deptno; 
// 再查询sal>avgsal的员工信息
select 
    e.ename,sal,t.deptno,t.avgsal
from
    emp e
join 
    (select deptno,avg(sal) as avgsal from emp group by deptno) t
on 
    e.deptno=t.deptno and sal>avgsal;

MySQL练习题(附带答案和结果)_第3张图片
3、取得部门中(所有人的)平均薪水的等级

// 先取得每个部门的平均薪资avgsal
select deptno,avg(sal) as avgsal from emp group by deptno;
// 再去查询avgsal的薪资等级
select 
    t.deptno,t.avgsal,s.grade
from 
    salgrade s
join
    (select deptno,avg(sal) as avgsal from emp group by deptno) t
on
    avgsal between s.losal and s.hisal;

MySQL练习题(附带答案和结果)_第4张图片
4、不使用组函数max,取得最高薪资(两种方案)

// 第一种,按降序排列薪资,取第一条
select 
    sal as maxsal
from 
    emp
order by
   sal desc
limit
    1;

MySQL练习题(附带答案和结果)_第5张图片

/// 第二种,自连接,查询出a表中sal小于b表中sal并去重
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);

MySQL练习题(附带答案和结果)_第6张图片
5、取得平均薪资最高的部门的部门编号(两种方案)

第一种:

// 取得部门平均薪资
select deptno,avg(sal) as avgsal from emp group by deptno;
// 降序排列后使用分页查询取第一个
select 
    deptno,avg(sal) as avgsal 
from 
    emp 
group by 
    deptno 
order by 
    avgsal desc 
limit 1;

MySQL练习题(附带答案和结果)_第7张图片

/*第二种:
使用max分组函数
取得部门平均薪资*/
select deptno,avg(sal) as avgsal from emp group by deptno;

// 使用max分组函数取得最大值
select max(t.avgsal) from (select deptno,avg(sal) as avgsal from emp group by deptno) t;

// 最后查询出想要的结果
select
    deptno,avg(sal) as avgsal
from
    emp
group by
    deptno
having
    avgsal = (select max(t.avgsal) from (select avg(sal) as avgsal from emp group by deptno) t);

MySQL练习题(附带答案和结果)_第8张图片
6、取得平均薪资最高的部门的部门名称

// 取得部门平均薪资
select deptno,avg(sal) as avgsal from emp group by deptno;

// 降序排列后使用分页查询取第一个即为最高薪资的部门,获取其部门编号和平均薪资
select
    deptno,avg(sal) as avgsal
from
    emp
group by
    deptno
order by
    avgsal desc
limit 1;
// 连接查询,与salgrade表进行内连接,查询出对应的部门名
select
    e.deptno,avg(e.sal) as avgsal,d.dname
from
    emp e
join 
    dept d
on
    e.deptno=d.deptno
group by
    e.deptno
order by
    avgsal desc
limit 1;

MySQL练习题(附带答案和结果)_第9张图片
7、求平均薪资的等级最低的部门的部门名称

注意:平均薪资等级最低的部门可能不止一个

// 第一步:找出每个部门的平均薪资
select deptno,avg(sal) as avgsal from emp group by deptno;

// 第二步:找出每个部门平均薪资的等级并创建视图view_avgsal_grade    
create 
    view view_avgsal_grade    
as
    select
        t.*,s.grade
    from
        (select deptno,avg(sal) as avgsal from emp group by deptno) t
    join
        salgrade s
    on
        t.avgsal between s.losal and s.hisal;
// 第三步:查出最低薪资等级并创建视图view_min_grade
create 
    view view_min_grade 
as
    select 
        min(grade)
    as 
        mingrade 
    from 
        view_avgsal_grade;
// 第四步:查询出平均薪资等级等于最低薪资等级的信息并创建视图view_min_grade_deptno
create
    view view_min_grade_deptno
as
    select
        vag.deptno,vag.grade
    from
        view_avgsal_grade vag
    join
        view_min_grade vmg
    on
        vag.grade=vmg.mingrade;

// 第五步:视图view_min_grade_deptno与表dept进行连接,查询出部门名
select
    vmgd.*,d.dname
from
    view_min_grade_deptno vmgd
join
    dept d
on 
    vmgd.deptno=d.deptno;

8、 取得比普通员工(员工代码没有在 mgr 字段上出现的)的最高薪水还要高的领导人姓名

// 查询出在mgr字段出现过的员工代码并去重,则员工编号不在(not in)其范围中的员工为普通员工(使用not in时,需要排除小括号中的null)
select distinct mgr from emp is not null;
// 找出普通员工的最高薪资,并创建视图view_max_sal
create 
    view view_max_sal 
as
    select 
        max(sal) as maxsal
    from 
        emp 
    where 
        empno not in (select distinct mgr from emp where mgr is not null);    
// 查出薪资大于普通员工的最高薪资的人的信息即为所求
select
    e.ename,e.sal
from
    emp e
join
    view_max_sal v
on
    e.sal>v.maxsal;

MySQL练习题(附带答案和结果)_第10张图片
9、取得薪资最高的前5名员工

select
    ename,sal
from
    emp
order by
    sal desc
limit 5;

MySQL练习题(附带答案和结果)_第11张图片
10、取薪资最高的第六到第十名员工

select
    ename,sal
from
    emp
order by
    sal desc
limit 5,5;

MySQL练习题(附带答案和结果)_第12张图片
11、取得最后入职的五名员工

// 按入职时间分页降序排序,取前五条
select 
    ename,hiredate
from
    emp
order by
    hiredate desc
limit 0,5;

MySQL练习题(附带答案和结果)_第13张图片
12、取得每个薪资等级有多少员工

// 找出每个员工的薪资等级
select
    e.ename,s.grade
from
    emp e
join
    salgrade s
on
 e.sal between s.losal and s.hisal;

// 按工资等级分组
select
    s.grade,count(s.grade)
from    
    emp e
join
    salgrade s
on
    e.sal between s.losal and s.hisal
group by
    grade;

MySQL练习题(附带答案和结果)_第14张图片
13、(面试题)
有 3 个表 S(学生表),C(课程表),SC(学生选课表)
S(SNO,SNAME)代表(学号,姓名)
C(CNO,CNAME,CTEACHER)代表(课号,课名,教师)
SC(SNO,CNO,SCGRADE)代表(学号,课号,成绩)

CREATE TABLE SC
(
SNO VARCHAR(200),
CNO VARCHAR(200),
SCGRADE VARCHAR(200)
);
CREATE TABLE S
(
SNO VARCHAR(200),
SNAME VARCHAR(200)
);
CREATE TABLE C
(
CNO VARCHAR(200),
CNAME VARCHAR(200),
CTEACHER VARCHAR(200)
);
INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( '1', '语文', '张');
INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( '2', '政治', '王');
INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( '3', '英语', '李');
INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( '4', '数学', '赵');
INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( '5', '物理', '黎明');
commit;
INSERT INTO S ( SNO, SNAME ) VALUES ( '1', '学生 1');
INSERT INTO S ( SNO, SNAME ) VALUES ( '2', '学生 2');
INSERT INTO S ( SNO, SNAME ) VALUES ( '3', '学生 3');
INSERT INTO S ( SNO, SNAME ) VALUES ( '4', '学生 4');
commit;
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '1', '1', '40');
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '1', '2', '30');
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '1', '3', '20');
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '1', '4', '80');
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '1', '5', '60');
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '2', '1', '60');
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '2', '2', '60');
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '2', '3', '60');
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '2', '4', '60');
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '2', '5', '40');
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '3', '1', '60');
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '3', '3', '80');
commit

MySQL练习题(附带答案和结果)_第15张图片
问题:
1),找出没选过“黎明”老师的所有学生姓名。

// 先查询出每个学生所选的课程
select  sc.sno,c.cno,c.cname,c.cteacher from sc join c on sc.cno=c.cno;

// 找出选过"黎明"老师的课的学生的编号
select s.sno from s join (select  sc.sno,c.cno,c.cname,c.cteacher from sc join c on sc.cno=c.cno) t on s.sno=t.sno where t.cteacher='黎明';

MySQL练习题(附带答案和结果)_第16张图片

// 得到选择了"黎明"老师课的学生的编号之后,使用not in查询出没有选择的学生的姓名
select 
    s.sname 
from 
    s 
where 
    sno not in (select s.sno from s join (select  sc.sno,c.cno,c.cname,c.cteacher from sc join c on sc.cno=c.cno) t on s.sno=t.sno where t.cteacher='黎明');

MySQL练习题(附带答案和结果)_第17张图片
2,列出 2 门以上(含 2 门)不及格学生姓名及平均成绩。

// 查询出各学生的不及格的门数
select s.sno,count(sc.scgrade) as countfail from s join sc on s.sno=sc.sno where sc.scgrade<60 group by s.sno;

// 找出不及格门数大于两门的学生的姓名,编号,并创建视图view_sno_sname 
create view view_sno_sname as
select s.sno,s.sname from s join (select s.sno,count(sc.scgrade) as countfail from s join sc on s.sno=sc.sno where sc.scgrade<60 group by s.sno) t on s.sno=t.sno where t.countfail >= 2;

// 找到对应条件的学生编号后连接sc表进行查询
select 
    vs.sname,avg(sc.scgrade) as avgsagrade
from
    sc
join
    view_sno_sname vs
on
    vs.sno=sc.sno
group by
    vs.sname;

MySQL练习题(附带答案和结果)_第18张图片
3,既学过 1 号课程又学过 2 号课所有学生的姓名。

// 查询出各个学生所选的课程
select sno,cno from sc;

// 找出学了1或2的课程的学生
select s.sno,t.cno from s join (select sno,cno from sc) t on s.sno=t.sno where t.cno=1 or t.cno=2;

// 按学号分组,找出课程1和2都学的学生
select 
    s.sname
from 
    s 
join 
    (select sno,cno from sc) t       
on 
    s.sno=t.sno 
where 
    t.cno=1 or t.cno=2                
group by 
    s.sname
having 
    count(t.cno)=2;                  

MySQL练习题(附带答案和结果)_第19张图片
14、请列出所有员工及领导姓名

// 左外连接
select
    a.ename '员工' , ifnull(b.ename,'没有领导') '领导'
from
    emp a
left join
    emp b
on
    a.mgr=b.empno;

MySQL练习题(附带答案和结果)_第20张图片

15、列出受雇日期早于其直接上级的所有员工的编号、姓名和部门名称

// 内连接,找出有领导的员工
select
    a.ename
from
    emp a
join
    emp b
on
    a.mgr=b.empno;

// 条件查询,找出受雇日期早于其领导的员工
select
    a.ename,a.hiredate,b.ename,b.hiredate,a.empno,d.dname
from
    emp a
join
    emp b
on
    a.mgr=b.empno
join
    dept d
on 
    a.deptno=d.deptno
where
    b.hiredate>a.hiredate;

MySQL练习题(附带答案和结果)_第21张图片
16、列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门

select
    e.*,d.*
from
    dept d
left join
    emp e
on 
    d.deptno=e.deptno;
 

MySQL练习题(附带答案和结果)_第22张图片

17、列出至少有五个员工的部门

// 查询出员工数大于等于5 的部门的编号及人数
select
    deptno,count(*)
from
    emp
group by
    deptno
having
    count(*)>=5;

// 连接查询
select
    e.deptno,count(*),d.dname
from
    emp e
join
    dept d
on
    e.deptno=d.deptno
group by
    deptno
having
    count(*)>=5;

MySQL练习题(附带答案和结果)_第23张图片
18、列出薪资比"SMITH"多的所有员工信息

// 先查出SMITH的工资
select sal from emp where ename='SMITH';
// 查出工资高于SMITH
select * from emp where sal>(select sal from emp where ename='SMITH');

MySQL练习题(附带答案和结果)_第24张图片
19、列出所有JOB为"CLERK"的姓名、部门名称以及部门人数

// 查询job为"CLERK"的员工的姓名及部门编号并创建视图view_clerk
create view view_clerk as select ename,deptno from emp where job='clerk';

// 连接表,查出部门名并创建视图view_ename_deptno
create view 
    view_ename_deptno 
as
    select
        v.ename,v.deptno,d.dname
    from
        view_clerk v
    join
        dept d
    on
        v.deptno=d.deptno;
// 查找出每个部门的人数并创建视图view_deptcount
create view 
    view_deptcount 
as
    select 
        deptno,count(*) as deptcount 
    from 
        emp 
    group by 
        deptno;

// view_ename_deptno和view_deptcount连接查询
select
    ved.*,vd.deptcount
from 
    view_ename_deptno ved
join
    view_deptcount vd
on
    ved.deptno=vd.deptno

MySQL练习题(附带答案和结果)_第25张图片
20、列出最低薪资大于1500的各种工作以及从事该工作的全部员工的信息

// 找出最低工资大于1500的工作
select job from emp group by job having min(sal)>1500;

// 查找出从事上述工作的人
select 
    e.*
from 
    emp e
join
    (select job from emp group by job having min(sal)>1500) t
on
    e.job=t.job;

MySQL练习题(附带答案和结果)_第26张图片
21、列出在部门"SALES"工作的员工的姓名,假设不知道部门编号

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

MySQL练习题(附带答案和结果)_第27张图片
22、列出薪资高于公司平均薪资的所有员工姓名、所在部门、上级领导、工资等级

// 查出公司平均工资
select avg(sal) as avgsal from emp;

// 查出工资高于平均工资的员工的ename,deptno,mgr,empno,sal并创建视图view_t
create view view_t as
    select
        e.ename,e.deptno,e.mgr,e.empno,e.sal
    from
        emp e
    join
        (select avg(sal) as avgsal from emp) t
    on
        e.sal>t.avgsal;
// 连接emp表查上级领导,连接dept查部门名,连接salgrade表查工资等级
select
    t.ename,d.dname,e.ename,t.sal,s.grade
from
    view_t t
left join
    emp e
on
    t.mgr=e.empno
join
    dept d
on
    d.deptno=t.deptno
join
    salgrade s
on
    t.sal between s.losal and s.hisal;

MySQL练习题(附带答案和结果)_第28张图片
23、列出与"SCOTT"从事相同工作的所有员工及部门名称

// 查询出SCOTT的工作
select job from emp where ename='scott';

// 查询
select
    e.ename,e.deptno,d.dname
from
    emp e
join
    (select job from emp where ename='scott') t
on
    e.job=t.job
join
    dept d
on
    d.deptno=e.deptno
where
    e.ename != 'scott';

MySQL练习题(附带答案和结果)_第29张图片
24、列出薪资等于部门编号为30中员工的薪资的其他员工的姓名和薪资

select
    ename,sal
from
    emp
where
    sal in(select distinct sal from emp where deptno=30)
and
    deptno != 30;

MySQL练习题(附带答案和结果)_第30张图片
25、列出薪资高于在部门编号为30工作的所有员工的薪资的员工姓名、薪资和部门名称

查询出部门编号为30的员工的最高薪资
select max(sal) maxsal from emp where deptno=30;

// 连接查询
select 
    e.ename,e.sal,d.dname
from
    emp e
join
    (select max(sal) maxsal from emp where deptno=30) t
on
    e.sal>t.maxsal
join
    dept d
on
    e.deptno=d.deptno;

MySQL练习题(附带答案和结果)_第31张图片
26、列出在每个部门工作的员工数量,平均工资和平均服务期限

// 查询每个部门的员工数量
select d.deptno,count(e.ename) from emp e  right join dept d on e.deptno=d.deptno group by deptno;

// 查询每个部门的平均薪资
select d.deptno,ifnull(avg(sal),0) as avgsal from emp e right join dept d on e.deptno=d.deptno group by deptno;

// 查询每个部门的平均服务期限(timeStampDiff函数,计算两个时间的时间差:第一个参数是返回的类型,第二个参数是开始时间,第三个参数是结束时间)
select d.deptno,ifnull(avg(timeStampDiff(year,hiredate,now())),0) as avgtime from emp e right join dept d on e.deptno=d.deptno group by deptno;

MySQL练习题(附带答案和结果)_第32张图片
27、列出所有员工的姓名、部门名称和工资

select
    e.ename,e.sal,d.dname
from
    emp e
join
    dept d
on
    e.deptno=d.deptno;

MySQL练习题(附带答案和结果)_第33张图片
28、列出所有部门的详细信息和人数

select d.*,count(e.ename) from dept d left join emp e on d.deptno=e.deptno group by d.deptno;

MySQL练习题(附带答案和结果)_第34张图片
29、列出不同工作的最低工资以及该工资的员工的信息

// 先查出每个岗位最低工资
select min(sal) as minsal,ename from emp group by job;

// 连接查询,找出job和sal都对应相等的员工得到其详细信息
select 
    e.* 
from 
    emp e 
join 
    (select min(sal) as minsal,job from emp group by job) t 
on 
    e.sal=t.minsal and e.job=t.job;

MySQL练习题(附带答案和结果)_第35张图片
30、列出各个部门"MANAGER"的最低薪资

select
    deptno,min(sal) as minsal
from
    emp
where
    job='manager'
group by
    deptno;

MySQL练习题(附带答案和结果)_第36张图片
31、列出所有员工的年工资,按照年薪升序排列

select
    ename,(sal + ifnull(comm,0)) * 12 as yearsal
from 
    emp
order by
    yearsal asc;

MySQL练习题(附带答案和结果)_第37张图片
32、列出员工领导的薪资超过3000的员工姓名和领导姓名

select
    a.ename '员工姓名',b.ename '领导姓名'
from 
    emp a
join     
    emp b
on
    a.mgr=b.empno
where
    b.sal>3000;

MySQL练习题(附带答案和结果)_第38张图片
33、列出部门名称中带"S"字符的部门员工的工资合计和部门人数

// 查询出部门名称带S的部门
select deptno,dname from dept where dname like '%S%';

// 连接查询
select
   t.deptno,t.dname,ifnull(sum(e.sal),0) as sumsal,count(e.ename) countnum
from 
    emp e
right join
    (select deptno,dname from dept where dname like '%S%') t
on
    e.deptno=t.deptno
group by
    t.deptno;

MySQL练习题(附带答案和结果)_第39张图片
34、给任职日期超过40年的员工加薪10%

// 查询任期超过40年的员工的信息
select ename,empno,hiredate,sal from emp where timeStampDiff(year,hiredate,now())>40;

// 加薪10%
update
    emp e
set
    e.sal=e.sal*1.1
where
    timeStampDiff(year,hiredate,now())>40;

MySQL练习题(附带答案和结果)_第40张图片

你可能感兴趣的