【76-95】34道作业题

相关表

mysql> select * from emp;
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7369 | SMITH  | 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 |
|  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 |    0.00 |     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 |
+-------+--------+-----------+------+------------+---------+---------+--------+

mysql> select * from dept;
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+

mysql> select * from salgrade;
+-------+-------+-------+
| GRADE | LOSAL | HISAL |
+-------+-------+-------+
|     1 |   700 |  1200 |
|     2 |  1201 |  1400 |
|     3 |  1401 |  2000 |
|     4 |  2001 |  3000 |
|     5 |  3001 |  9999 |
+-------+-------+-------+

1. 取得每个部门最高薪水的人员名称

-- 取出每个部门最高薪水  
select deptno, max(sal) from emp group by deptno;

-- 取得每个部门最高薪水的人员名称
select a.deptno, a.ename, a.sal
from emp a
join (select deptno, max(sal) maxsal from emp group by deptno) b
on a.deptno = b.deptno and a.sal = b.maxsal;

2. 哪些人的薪水在部门平均薪水之上

-- 每个部门的平均薪水
select deptno, avg(sal) from emp group by deptno;

-- 哪些人的薪水在部门平均薪水之上
select a.ename, a.sal, a.deptno, b.avgsal
from emp a,
(select deptno, avg(sal) avgsal from emp group by deptno) b
where a.deptno = b.deptno and a.sal > b.avgsal;

3. 取得部门中（所有人的）平均的薪水等级

1. 平均的薪水等级: 先计算每一个薪水的等级，然后找出薪水等级的平均值
2. 平均薪水的等级: 先计算平均薪水，然后找出每个平均薪水的等级值

   -- 求出每个人的薪水等级
   select a.ename, a.sal, a.deptno, b.grade 
   from emp a, salgrade b
   where a.sal between b.losal and b.hisal;
   
   -- 取得部门中（所有人的）平均的薪水等级
   select a.deptno, avg(b.grade)
   from emp a, salgrade b
   where a.sal between b.losal and b.hisal
   group by a.deptno;

4. 不准用组函数（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
  );

5. 取得平均薪水最高的部门的部门编号（至少给出两种解决方案）

• 第一种

  select deptno, avg(sal) avgsal from emp group by deptno order by avgsal desc limit 1;

• 第二种

  -- 取出最高薪水
  select max(a.avgsal)
  from (select avg(sal) avgsal from emp group by deptno) a;
  
  -- 取得平均薪水最高的部门的部门编号
  select deptno, avg(sal) avgsal 
  from emp group by deptno 
  having avgsal = (
      select max(a.avgsal)
      from (select avg(sal) avgsal from emp group by deptno) a
  );

6. 取出平均薪水最高的部门的部门名称

-- 取出平均薪水最高的部门编号
select deptno, avg(sal) avgsal 
from emp group by deptno 
order by avgsal desc limit 1;

-- 取出平均薪水最高的部门的部门名称
select dname
from dept a
join (
    select deptno, avg(sal) avgsal 
    from emp group by deptno 
    order by avgsal desc limit 1
) b
on a.deptno = b.deptno;

7. 求平均薪水的等级最低的部门的部门名称

-- 求每个部门平均薪水的最低的
select deptno, avg(sal) avgsal from emp group by deptno order by avgsal asc limit 1;

-- 求最低的平均薪水的等级
select grade
from salgrade a
where (select avg(sal) avgsal from emp group by deptno order by avgsal asc limit 1) 
between a.losal and a.hisal;

-- 平均薪水的等级最低的部门的部门名称
select a.deptno, c.dname, a.avgsal, b.grade
from (select deptno, avg(sal) avgsal from emp group by deptno) a
join salgrade b
on a.avgsal between b.losal and b.hisal
join dept c
on a.deptno = c.deptno
where b.grade = (
    select grade
    from salgrade a
    where (select avg(sal) avgsal from emp group by deptno order by avgsal asc limit 1) 
    between a.losal and a.hisal
);

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

• not in(集合) 如果集合有空的话需要手动排除

  -- 取得普通员工的最高薪水
  select max(sal) from emp
  where empno not in(select distinct mgr from emp where mgr is not null);
  
  -- 取得领导人名字
  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)
  );

9. 取得薪水最高的前五名员工

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

10. 取得薪水最高的第六道第十名员工

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

11. 取得最后入职的五名员工

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

12. 取得每个薪水等级有多少员工

select b.grade, count(b.grade)
from emp a
join salgrade b
on a.sal between b.losal and b.hisal
group by b.grade;

13. 面试题

1. 有3个表 S(学生表)，C(课程表), SC(学生选课表)

2. S (SNO, SNAME) 代表 (学号,姓名)

3. C (CNO, CNAME, CTEACHER) 代表 (课号, 课名, 教师)

4. SC (SNO, CNO, SCGRADE) 代表 (学号, 课号, 成绩)
   问题:

5. 找出没选过”黎明”老师的所有学生姓名

   -- 找出"黎明"老师教的课号
   select cno
   from c
   where cteacher = '黎明';
   
   -- 找出"黎明"老师教的学生的学号
   select distinct sc.sno
   from sc
   join (
       select cno
       from c
       where cteacher = '黎明'
   ) t
   on sc.cno = t.cno;
   
   -- 找出没选过"黎明"老师的所有学生姓名 
   select s.sname
   from s
   where s.sno not in(
       select distinct sc.sno
       from sc
       join (
           select cno
           from c
           where cteacher = '黎明'
       ) t
       on sc.cno = t.cno
   );

6. 列出2门以上(含2门)不及格学生姓名及平均成绩

   -- 列出2门以上(含2门)不及格的学生学号
   select sc.sno
   from sc
   where sc.scgrade < 60
   group by sc.sno
   having count(sc.cno) >= 2;
   
   -- 列出每个学生的平均成绩
   select sc.sno, avg(sc.scgrade) avggrade
   from sc
   group by sc.sno;
   
   -- 列出2门以上(含2门)不及格学生姓名及平均成绩
   select s.sname, j.avggrade
   from (
       select sc.sno
       from sc
       where sc.scgrade < 60
       group by sc.sno
       having count(sc.cno) >= 2
   ) i
   left join s
   on i.sno = s.sno
   left join (
       select sc.sno, avg(sc.scgrade) avggrade
       from sc
       group by sc.sno
   ) j
   on i.sno = j.sno;

7. 即学过1号课程又学过2号课所有学生的姓名

   -- 学过1号课程的学生学号
   select distinct sc.sno
   from sc
   where sc.cno = 1;
   
   -- 学过2号课程的学生学号
   select distinct sc.sno
   from sc
   where sc.cmo = 2;
   
   -- 即学过1号课程又学过2号课所有学生的姓名  
   select s.sname
   from (
       select distinct sc.sno
       from sc
       where sc.cno = 1
   ) a
   join (
       select distinct sc.sno
       from sc
       where sc.cmo = 2
   ) b
   on a.sno = b.sno
   join s
   on a.sno = s.sno;

14. 列出所有员工及领导的姓名

select a.ename, ifnull(b.ename, "无上级")
from emp a
left join emp b
on a.mgr = b.empno;

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

select a.empno, a.ename, a.hiredate, b.empno, b.ename, b.hiredate, c.dname
from emp a
join emp b
on a.mgr = b.empno
join dept c
on a.deptno = c.deptno
where a.hiredate < b.hiredate;

16. 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门

select *
from dept a
left join emp b
on a.deptno = b.deptno;

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

select b.* 
from dept b
join (
    select a.deptno
    from emp a
    group by a.deptno
    having count(a.deptno) >= 5
) c
on b.deptno = c.deptno;

18. 列出薪水比”SMITH”多的所有员工信息

select a.*
from emp a
where a.sal > (
    select b.sal
    from emp b
    where b.ename = 'SMITH'
);

19. 列出所有”CLERK”（办事员）的姓名及其部门名称，部门的人数

-- 列出所有"CLERK"的姓名和部门编号
select a.ename, a.deptno
from emp a
where a.job = 'CLERK';

-- 列出所有部门的人数
select b.deptno, count(b.deptno)
from emp b
group by b.deptno;

-- 列出所有"CLERK"（办事员）的姓名及其部门名称，部门的人数
select c.ename, e.dname, d.cnt
from (
    select a.ename, a.deptno
    from emp a
    where a.job = 'CLERK'
) c
join (
    select b.deptno, count(b.deptno) cnt
    from emp b
    group by b.deptno
) d
on c.deptno = d.deptno
join dept e
on e.deptno = c.deptno;

20. 列出最低薪金大于1500的各种工作及从事此工作的全部雇佣人数

select a.job, count(*)
from emp a
group by a.job
having min(a.sal) > 1500;

21. 列出在部门”SALES”<销售部>工作的员工的姓名，假定不知道销售部的部门编号

-- 找出销售部的部门编号
select a.deptno
from dept a
where a.dname = 'SALES';

-- 列出在部门"SALES"<销售部>工作的员工的姓名，假定不知道销售部的部门编号
select b.ename
from emp b
where b.deptno = (
    select a.deptno
    from dept a
    where a.dname = 'SALES'
);

22. 列出薪金高于公司平均薪金的所有员工，所在部门，上级领导，雇员的工资等级

-- 高于平均薪水的所有员工
select *
from emp a
where a.sal > (
    select avg(b.sal)
    from emp b
);

-- 列出薪金高于公司平均薪金的所有员工，所在部门，上级领导，雇员的工资等级
select c.empno, c.ename, c.deptno, c.mgr, d.grade
from (
    select a.empno, a.ename, a.mgr, a.sal, a.deptno
    from emp a
    where a.sal > (
        select avg(b.sal)
        from emp b
    )
) c
left join salgrade d
on c.sal between d.losal and d.hisal;

23. 列出与”SCOTT”从事相同工作的所有员工及部门名称

-- 找出"SCOTT"从事的工作
select a.job
from emp a
where a.ename = 'SCOTT';

-- 列出与"SCOTT"从事相同工作的所有员工及部门名称
select b.empno, b.ename, d.deptno, d.dname
from emp b
join (
    select a.job
    from emp a
    where a.ename = 'SCOTT'
) c
on b.job = c.job
join dept d
on b.deptno = d.deptno
where b.ename <> 'SCOTT';

24. 列出薪金等于部门30中员工的薪金的其他员工的姓名和薪金

-- 30部门员工的薪金
select a.sal
from emp a
where a.deptno = 30;

-- 列出薪金等于部门30中员工的薪金的其他员工的姓名和薪金
select b.ename, b.sal
from emp b
where b.sal in(
    select distinct a.sal
    from emp a
    where a.deptno = 30
) and b.deptno <> 30;

25. 列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金，部门名称

-- 在部门30工作的所有员工的最高薪金
select max(a.sal)
from emp a
where a.deptno = 30;

-- 列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金，部门名称
select c.ename, c.sal, d.dname
from (
    select b.ename, b.sal, b.deptno
    from emp b
    where b.sal > (
        select max(a.sal)
        from emp a
        where a.deptno = 30
    )
) c
join dept d
on c.deptno = d.deptno;

26. 列出在每个部门工作的员工数量，平均工资和平均服务期限

• 函数TimeStampDiff(间隔类型，前一个日期，后一个日期)
• 间隔类型：SECOND秒,MINUTE分钟,HOUR小时,DAY天,WEEK星期,MONTH月,QUARTER季度,YEAR年

  select a.deptno, count(b.empno), ifnull(avg(b.sal),0), avg(TimeStampDiff(year, b.hiredate, now()))
  from dept a
  left join emp b
  on a.deptno = b.deptno
  group by a.deptno;

27. 列出所有员工的姓名、部门名称和工资

select a.ename, b.dname, a.sal
from emp a
left join dept b
on a.deptno = b.deptno;

28. 列出所有部门的详细信息和人数

-- 找出所有部门的人数
select a.deptno, count(a.deptno)
from emp a
group by a.deptno;

-- 列出所有部门的详细信息和人数
select b.*, ifnull(c.cnt,0)
from dept b
left join (
    select a.deptno, count(a.deptno) cnt
    from emp a
    group by a.deptno
) c
on b.deptno = c.deptno;

29. 列出各种工作的最低工资及从事此工作的雇员姓名

-- 列出各种工作的最低工资
select a.job, min(a.sal)
from emp a
group by a.job;

-- 列出各种工作的最低工资及从事此工作的雇员姓名
select b.job, b.minsal, c.ename, c.sal
from (
    select a.job, min(a.sal) minsal
    from emp a
    group by a.job
) b
left join emp c
on b.job = c.job and b.minsal = c.sal;

30. 列出各个部门的MANAGER（领导）的最低薪金

select a.deptno, min(a.sal)
from emp a
where a.job = 'MANAGER'
group by a.deptno;

31. 列出所有员工的年工资，按年薪从低到高排序

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

32. 求出员工领导的薪水超过3000的员工名称与领导名称

select a.ename, b.ename, b.sal
from emp a
join emp b
on a.mgr = b.empno
where b.sal > 3000;

33. 求出部门名称中，带’S’字符的部门员工的工资合计、部门人数

-- 找出部门名称中带'S'字符的部门
select a.deptno, a.dname
from dept a
where a.dname like '%S%';

-- 求出部门名称中，带'S'字符的部门员工的工资合计、部门人数
select b.deptno, b.dname, ifnull(sum(c.sal),0), count(c.ename)
from (
    select a.deptno, a.dname
    from dept a
    where a.dname like '%S%'
) b
left join emp c
on b.deptno = c.deptno
group by b.deptno;

34. 给任职日期超过30年的员工加薪10%

update emp a
set a.sal = a.sal * 1.1
where TimeStampDiff(year, a.hiredate, now()) > 30;