mysql练习题

最新推荐文章于 2022-09-19 11:33:12 发布

chen_xionxion

最新推荐文章于 2022-09-19 11:33:12 发布

阅读量440

点赞数 1

分类专栏： mysql 文章标签： mysql

本文链接：https://blog.youkuaiyun.com/Adobe_java/article/details/118550002

版权

mysql 专栏收录该内容

4 篇文章

订阅专栏

这是一系列关于MySQL查询的实战题目，涵盖了比较同一行的值、获取各部门最高薪水员工、平均薪水、薪资排名等多个方面，涉及员工、部门、薪水、入职日期等多个字段的操作，旨在提升对SQL查询技巧的理解和运用。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

0、比较同一行的值

表中有A B C三列,用SQL语句实现：当A列大于B列时选择A列否则选择B列，当B列大于C列时选择B列否则选择C列。

select (case when a>b then a else b end), 
	(case when b>c then b else c end)  from t_table

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

select
	e.ename,t.*
from
(select max(sal) as maxsal,deptno from emp group by deptno) t
join
	emp e
on
	t.maxsal = e.sal and t.deptno = e.deptno;
+-------+---------+--------+
| ename | maxsal  | deptno |
+-------+---------+--------+
| BLAKE | 2850.00 |     30 |
| SCOTT | 3000.00 |     20 |
| KING  | 5000.00 |     10 |
| FORD  | 3000.00 |     20 |
+-------+---------+--------+

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

//第一步：求出平均薪水
select
	deptno,avg(sal)
from
	emp
group by
	deptno;
+--------+-------------+
| deptno | avg(sal)    |
+--------+-------------+
|     10 | 2916.666667 |
|     20 | 2175.000000 |
|     30 | 1566.666667 |
+--------+-------------+
//第二步：
select
	e.ename,t.*,e.sal
from
(select
	deptno,avg(sal) as avgsal
from
	emp
group by
	deptno) t
join
	emp e
on
	e.deptno = t.deptno and e.sal >= t.avgsal;
+-------+--------+-------------+---------+
| ename | deptno | avgsal      | sal     |
+-------+--------+-------------+---------+
| ALLEN |     30 | 1566.666667 | 1600.00 |
| JONES |     20 | 2175.000000 | 2975.00 |
| BLAKE |     30 | 1566.666667 | 2850.00 |
| SCOTT |     20 | 2175.000000 | 3000.00 |
| KING  |     10 | 2916.666667 | 5000.00 |
| FORD  |     20 | 2175.000000 | 3000.00 |
+-------+--------+-------------+---------+

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

//第一步：求出部门中的薪资等级
select
	e.deptno,e.ename,s.grade
from
	emp e
join
	salgrade s
on
	e.sal between s.losal and s.hisal;
+--------+--------+-------+
| deptno | ename  | grade |
+--------+--------+-------+
|     20 | SMITH  |     1 |
|     30 | ALLEN  |     3 |
|     30 | WARD   |     2 |
|     20 | JONES  |     4 |
|     30 | MARTIN |     2 |
|     30 | BLAKE  |     4 |
|     10 | CLARK  |     4 |
|     20 | SCOTT  |     4 |
|     10 | KING   |     5 |
|     30 | TURNER |     3 |
|     20 | ADAMS  |     1 |
|     30 | JAMES  |     1 |
|     20 | FORD   |     4 |
|     10 | MILLER |     2 |
+--------+--------+-------+
//第二步：
select
	e.deptno,e.ename,avg(s.grade)
from
	emp e
join
	salgrade s
on
	e.sal between s.losal and s.hisal
group by
	e.deptno;
+--------+-------+--------------+
| deptno | ename | avg(s.grade) |
+--------+-------+--------------+
|     10 | CLARK |       3.6667 |
|     20 | SMITH |       2.8000 |
|     30 | ALLEN |       2.5000 |
+--------+-------+--------------+

4、不准用组函数（Max ），取得最高薪水

//1.降序，取第一行数据
select
	*
from
	empe
order by
	sal desc
limit 1;
//2.表的自连接
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)
from
	emp
group by
	deptno;
+--------+-------------+
| deptno | avg(sal)    |
+--------+-------------+
|     10 | 2916.666667 |
|     20 | 2175.000000 |
|     30 | 1566.666667 |
+--------+-------------+
//第二步：
select
    deptno,avg(sal) as avgsal
from
    emp
group by
    deptno
order by
    avgsal desc
limit 1;
+--------+-------------+
| deptno | maxsal      |
+--------+-------------+
|     10 | 2916.666667 |
+--------+-------------+

6、取得平均薪水最高的部门的部门名称

//第一步：取得各部门平均薪水
select
	deptno,avg(sal)
from
	emp
group by
	deptno;
+--------+-------------+
| deptno | avg(sal)    |
+--------+-------------+
|     10 | 2916.666667 |
|     20 | 2175.000000 |
|     30 | 1566.666667 |
+--------+-------------+
//第二步：取得平均薪水最高部门的部门编号
select
    deptno,avg(sal) as avgsal
from
    emp
group by
    deptno
order by
	avgsal desc
limit 1;
+--------+-------------+
| deptno | maxsal      |
+--------+-------------+
|     10 | 2916.666667 |
+--------+-------------+
//第三步：
select
    d.dname,avg(e.sal) as avgsal
from
    emp e
join
	dept d
on
	e.deptno = d.deptno
group by
    d.dname
order by
    avgsal desc
limit 1;
+------------+-------------+
| dname      | maxsal      |
+------------+-------------+
| ACCOUNTING | 2916.666667 |
+------------+-------------+

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

//第一步：求出每个部门的平均薪水
select
	deptno,avg(sal) as avgsal
from
	emp
group by
	deptno;
+--------+-------------+
| deptno | avgsal      |
+--------+-------------+
|     10 | 2916.666667 |
|     20 | 2175.000000 |
|     30 | 1566.666667 |
+--------+-------------+
//第二步：取得平均薪水等级
select
	t.*,s.grade
from(
    select
        e.deptno,avg(e.sal) as avgsal
    from
        emp e
    group by
        e.deptno) t
join
	salgrade s
on
	t.avgsal between s.losal and s.hisal;
+--------+-------------+-------+
| deptno | avgsal      | grade |
+--------+-------------+-------+
|     30 | 1566.666667 |     3 |
|     10 | 2916.666667 |     4 |
|     20 | 2175.000000 |     4 |
+--------+-------------+-------+
//第三步：取得最低的薪水等级
select grade from salgrade 
	where (
		select avg(sal) as avgsal from emp group by deptno order by avgsal asc limit 1
	) between losal and hisal;
+-------+
| grade |
+-------+
|     3 |
+-------+
//第四步：
select
	t.*,s.grade
from(select d.dname,avg(e.sal) as avgsal from emp e join dept d
    on e.deptno = d.deptno group by d.dname) t
join
	salgrade s
on
	t.avgsal between s.losal and s.hisal
where
	s.grade = (select grade from salgrade 
		where (
			select avg(sal) as avgsal from emp group by deptno order by avgsal asc limit 1
		) between losal and hisal);
+-------+-------------+-------+
| dname | avgsal      | grade |
+-------+-------------+-------+
| SALES | 1566.666667 |     3 |
+-------+-------------+-------+

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

//第一步：取得领导的编号
select
 	distinct mgr
from
 	emp
where
 	mgr is not null;
+------+
| mgr  |
+------+
| 7902 |
| 7698 |
| 7839 |
| 7566 |
| 7788 |
| 7782 |
+------+
//第二步：取得普通员工最高薪水
select
	max(sal)
from
	emp
where
	empno not in (select distinct mgr from emp where mgr is not null);
+-------+-------+------+---------+
| empno | ename | mgr  | sal     |
+-------+-------+------+---------+
|  7499 | ALLEN | 7698 | 1600.00 |
+-------+-------+------+---------+
//第四步：比普通员工最高薪水还高的一定是领导
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));
+-------+---------+
| ename | sal     |
+-------+---------+
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| SCOTT | 3000.00 |
| KING  | 5000.00 |
| FORD  | 3000.00 |
+-------+---------+

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

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、取得薪水最高的第六到第十名员工

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 名员工

select
	empno,ename,hiredate
from
	emp
order by
	hiredate desc
limit 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、取得每个薪水等级有多少员工

//第一步：取得每个员工的薪水等级
select
	e.ename,e.sal,s.grade
from
	emp e
join
	salgrade s
on
	e.sal between s.losal and s.hisal;
+--------+---------+-------+
| ename  | sal     | grade |
+--------+---------+-------+
| SMITH  |  800.00 |     1 |
| ALLEN  | 1600.00 |     3 |
| WARD   | 1250.00 |     2 |
| JONES  | 2975.00 |     4 |
| MARTIN | 1250.00 |     2 |
| BLAKE  | 2850.00 |     4 |
| CLARK  | 2450.00 |     4 |
| SCOTT  | 3000.00 |     4 |
| KING   | 5000.00 |     5 |
| TURNER | 1500.00 |     3 |
| ADAMS  | 1100.00 |     1 |
| JAMES  |  950.00 |     1 |
| FORD   | 3000.00 |     4 |
| MILLER | 1300.00 |     2 |
+--------+---------+-------+
//第二步：
select
	s.grade,count(s.grade) as '人数'
from
	emp e
join
	salgrade s
on
	e.sal between s.losal and s.hisal
group by
	s.grade;
+-------+------+
| grade | 人数  |
+-------+------+
|     1 |    3 |
|     2 |    3 |
|     3 |    2 |
|     4 |    5 |
|     5 |    1 |
+-------+------+

13、面试题：

有 3 个表 S(学生表)，C（课程表），SC（学生选课表）
S（SNO，SNAME）代表（学号，姓名）
C（CNO，CNAME，CTEACHER）代表（课号，课名，教师）
SC（SNO，CNO，SCGRADE）代表（学号，课号，成绩）
问题：
1，找出没选过“黎明”老师的所有学生姓名。
2，列出 2 门以上（含2 门）不及格学生姓名及平均成绩。
3，即学过 1 号课程又学过 2 号课所有学生的姓名。

//1、找出没选过“黎明”老师的所有学生姓名
select
	s.sname
from
	sc
join
	c
join
	s
where
	sc.cteacher = '黎明' and sc.cno = c.cno and s.sno = c.sno;
//2、列出 2 门以上（含2 门）不及格学生姓名及平均成绩
select
	s.sno,s.sname,avg(sc1.scgrade)
from(
    select
        sno,count(sno) as count
    from
        sc
    where
        scgrade < 60
) t
join
	s
on
	t.count >= 2 and s.sno = sc.sno;
join
	sc sc1
on
	s.sno = sc1.sno;
//3、即学过 1 号课程又学过 2 号课所有学生的姓名

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

select
	a.ename as '员工',b.ename as '领导'
from
	emp a
left join
	emp b
on
	a.mgr = b.empno;
+--------+-------+
| 员工    | 领导   |
+--------+-------+
| SMITH  | FORD  |
| ALLEN  | BLAKE |
| WARD   | BLAKE |
| JONES  | KING  |
| MARTIN | BLAKE |
| BLAKE  | KING  |
| CLARK  | KING  |
| SCOTT  | JONES |
| KING   | NULL  |
| TURNER | BLAKE |
| ADAMS  | SCOTT |
| JAMES  | BLAKE |
| FORD   | JONES |
| MILLER | CLARK |
+--------+-------+

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

//第一步：取得比上级入职早的员工信息
select
	a.empno,a.ename,a.hiredate,a.deptno
from
	emp a
join
	emp b
on
	a.mgr = b.empno and a.hiredate < b.hiredate;
+-------+-------+------------+--------+
| empno | ename | hiredate   | deptno |
+-------+-------+------------+--------+
|  7369 | SMITH | 1980-12-17 |     20 |
|  7499 | ALLEN | 1981-02-20 |     30 |
|  7521 | WARD  | 1981-02-22 |     30 |
|  7566 | JONES | 1981-04-02 |     20 |
|  7698 | BLAKE | 1981-05-01 |     30 |
|  7782 | CLARK | 1981-06-09 |     10 |
+-------+-------+------------+--------+
//第二步：
select
	a.empno,a.ename,d.dname
from
	emp a
join
	emp b
on
	a.mgr = b.empno 
join
	dept d
on
	a.deptno = d.deptno
where
	a.hiredate < b.hiredate;
+-------+-------+------------+
| empno | ename | dname      |
+-------+-------+------------+
|  7782 | CLARK | ACCOUNTING |
|  7369 | SMITH | RESEARCH   |
|  7566 | JONES | RESEARCH   |
|  7499 | ALLEN | SALES      |
|  7521 | WARD  | SALES      |
|  7698 | BLAKE | SALES      |
+-------+-------+------------+

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

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

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

select
	d.deptno,d.dname,t.count
from(
    select
        deptno,count(deptno) as count
    from
        emp
    group by
        deptno) t
join 
	dept d
on
	t.count >= 5 and t.deptno = d.deptno;
+--------+----------+-------+
| deptno | dname    | count |
+--------+----------+-------+
|     20 | RESEARCH |     5 |
|     30 | SALES    |     6 |
+--------+----------+-------+
select
    deptno,count(deptno) as count
from
    emp
group by
    deptno
having
	count(*) >= 5;
+--------+-------+
| deptno | count |
+--------+-------+
|     20 |     5 |
|     30 |     6 |
+--------+-------+

18、列出薪水比"SMITH" 多的所有员工信息

select
	*
from
	emp
where
	sal > (select sal from emp where ename = 'smith');

19、列出所有"CLERK"( 办事员) 的姓名及其部门名称, 部门的人数

select
	t.*,count(emp.deptno) as count
from(
    select
        e.ename,e.deptno,d.dname
    from
        emp e
    join
        dept d
    where
        job = 'clerk' and e.deptno = d.deptno) t
left join
	emp
on
	t.deptno = emp.deptno
group by
	t.ename;
+--------+--------+------------+-------+
| ename  | deptno | dname      | count |
+--------+--------+------------+-------+
| ADAMS  |     20 | RESEARCH   |     5 |
| JAMES  |     30 | SALES      |     6 |
| MILLER |     10 | ACCOUNTING |     3 |
| SMITH  |     20 | RESEARCH   |     5 |
+--------+--------+------------+-------+

20、列出最低薪水大于 1500 的各种工作及从事此工作的全部雇员人数

//分完组找出组内最低工资大于1500的组，在统计每个组的人数
select
    job,count(*)
from
    emp
group by
    job
having 
	min(sal) > 1500;
+-----------+----------+
| job       | count(*) |
+-----------+----------+
| ANALYST   |        2 |
| MANAGER   |        3 |
| PRESIDENT |        1 |
+-----------+----------+

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

//第一步：取得销售部的部门编号
select
	deptno
from
	dept
where
	dname = 'sales';
//第二步：
select
	ename
from
	emp
where
	deptno = (select deptno from dept where dname = 'sales');
+--------+
| ename  |
+--------+
| ALLEN  |
| WARD   |
| MARTIN |
| BLAKE  |
| TURNER |
| JAMES  |
+--------+

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

//第一步：取得平均薪水
select
	avg(sal)
from
	emp;
+-------------+
| avg(sal)    |
+-------------+
| 2073.214286 |
+-------------+
//第二步：取得高于平均薪水的员工
select
	*
from
	emp
where
	sal > (select avg(sal) from emp);
//第三步：取得高于平均薪水的员工，及所在部门
select
	e.ename,e.sal,d.dname,e.mgr
from
	emp e
join
	dept d
on
	e.deptno = d.deptno and e.sal > (select avg(sal) from emp);
+-------+---------+------------+------+
| ename | sal     | dname      | mgr  |
+-------+---------+------------+------+
| CLARK | 2450.00 | ACCOUNTING | 7839 |
| KING  | 5000.00 | ACCOUNTING | NULL |
| JONES | 2975.00 | RESEARCH   | 7839 |
| SCOTT | 3000.00 | RESEARCH   | 7566 |
| FORD  | 3000.00 | RESEARCH   | 7566 |
| BLAKE | 2850.00 | SALES      | 7839 |
+-------+---------+------------+------+
//第四步：取得上级领导
select
	e.ename as '员工',e.sal,d.dname,em.ename as '领导'
from
	emp e
join
	dept d
on
	e.deptno = d.deptno and e.sal > (select avg(sal) from emp)
left join
	emp em
on
	e.mgr = em.empno;
+-------+---------+------------+-------+
| 员工   | sal     | dname      | 领导   |
+-------+---------+------------+-------+
| CLARK | 2450.00 | ACCOUNTING | KING  |
| KING  | 5000.00 | ACCOUNTING | NULL  |
| JONES | 2975.00 | RESEARCH   | KING  |
| SCOTT | 3000.00 | RESEARCH   | JONES |
| FORD  | 3000.00 | RESEARCH   | JONES |
| BLAKE | 2850.00 | SALES      | KING  |
+-------+---------+------------+-------+
//第五步：
select
	e.ename as '员工',e.sal,d.dname,em.ename as '领导',s.grade
from
	emp e
join
	dept d
on
	e.deptno = d.deptno 
left join
	emp em
on
	e.mgr = em.empno
join
	salgrade s
on
	e.sal between s.losal and s.hisal
where
	and e.sal > (select avg(sal) from emp);
+-------+---------+------------+-------+-------+
| 员工   | sal     | dname      | 领导   | grade |
+-------+---------+------------+-------+-------+
| JONES | 2975.00 | RESEARCH   | KING  |     4 |
| BLAKE | 2850.00 | SALES      | KING  |     4 |
| CLARK | 2450.00 | ACCOUNTING | KING  |     4 |
| SCOTT | 3000.00 | RESEARCH   | JONES |     4 |
| KING  | 5000.00 | ACCOUNTING | NULL  |     5 |
| FORD  | 3000.00 | RESEARCH   | JONES |     4 |
+-------+---------+------------+-------+-------+

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

select
	e.ename,d.dname
from
    emp e
join
	dept d
on
	d.deptno = e.deptno
where
	e.job = (select job from emp where ename = 'scott') and ename != 'scott';
+-------+----------+
| ename | dname    |
+-------+----------+
| FORD  | RESEARCH |
+-------+----------+

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

//第一步：取得部门30员工的薪水
select
	sal
from
	emp
where
	deptno = '30';
+---------+--------+
| sal     | deptno |
+---------+--------+
| 1600.00 |     30 |
| 1250.00 |     30 |
| 1250.00 |     30 |
| 2850.00 |     30 |
| 1500.00 |     30 |
|  950.00 |     30 |
+---------+--------+
//
select
	ename,sal
from
	emp
where
	sal in (select sal from emp where deptno = '30') and deptno != 30;

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

//取得部门30的最高薪水
select
	max(sal)
from
	emp
where
	deptno = '30';
+----------+
| max(sal) |
+----------+
|  2850.00 |
+----------+
//第二步：
select
	e.ename,e.sal,d.dname
from
	emp e
join
	dept d
on
	d.deptno = e.deptno
where
	e.sal > (select max(sal) from emp where deptno = '30');
+-------+---------+------------+
| ename | sal     | dname      |
+-------+---------+------------+
| KING  | 5000.00 | ACCOUNTING |
| JONES | 2975.00 | RESEARCH   |
| SCOTT | 3000.00 | RESEARCH   |
| FORD  | 3000.00 | RESEARCH   |
+-------+---------+------------+

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

select
	d.dname,count(e.deptno) as count,ifnull(avg(e.sal),0) as avgsal,
	ifnull(avg(timestampdiff(year,hiredate,now())),0) as avgdate
from
	emp e
right join
	dept d
on
	e.deptno = d.deptno
group by
	d.deptno;
+------------+-------+-------------+---------+
| dname      | count | avgsal      | avgdate |
+------------+-------+-------------+---------+
| ACCOUNTING |     3 | 2916.666667 | 39.0000 |
| RESEARCH   |     5 | 2175.000000 | 37.2000 |
| SALES      |     6 | 1566.666667 | 39.5000 |
| OPERATIONS |     0 |    0.000000 |  0.0000 |
+------------+-------+-------------+---------+

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

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

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

select
	t.*,count(e.deptno)
from
	(select * from dept) t
left join
	emp e
on
	e.deptno = t.deptno
group by
	e.deptno;
+--------+------------+----------+-----------------+
| DEPTNO | DNAME      | LOC      | count(e.deptno) |
+--------+------------+----------+-----------------+
|     40 | OPERATIONS | BOSTON   |               0 |
|     10 | ACCOUNTING | NEW YORK |               3 |
|     20 | RESEARCH   | DALLAS   |               5 |
|     30 | SALES      | CHICAGO  |               6 |
+--------+------------+----------+-----------------+
//方法2：
select
	d.*,count(e.ename)
from
	dept d
left join
	emp e
on
	e.deptno = d.deptno
group by
	d.dname;

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

select
	e.ename,t.*
from
	(select min(sal) as minsal,job from emp group by job) t
join
	emp e
on
	e.job = t.job and e.sal = t.minsal;
+--------+---------+-----------+
| ename  | minsal  | job       |
+--------+---------+-----------+
| SMITH  |  800.00 | CLERK     |
| WARD   | 1250.00 | SALESMAN  |
| MARTIN | 1250.00 | SALESMAN  |
| CLARK  | 2450.00 | MANAGER   |
| SCOTT  | 3000.00 | ANALYST   |
| KING   | 5000.00 | PRESIDENT |
| FORD   | 3000.00 | ANALYST   |
+--------+---------+-----------+

30、列出各个部门的 MANAGER( 领导) 的最低薪金

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、列出所有员工的年工资, 按年薪从低到高排序

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

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

select
	distinct a.ename as '员工',b.ename as '领导',b.sal
from
	emp a
join
	emp b
on
	a.mgr = b.empno and b.sal > 3000;
+-------+------+---------+
| 员工   | 领导  | sal    |
+-------+------+---------+
| JONES | KING | 5000.00 |
| BLAKE | KING | 5000.00 |
| CLARK | KING | 5000.00 |
+-------+------+---------+

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

select
	d.dname,ifnull(sum(e.sal),0) as sumsal,count(e.deptno) as count
from
	emp e
right join
	dept d
on
	e.deptno = d.deptno
where
	d.dname like '%s%'
group by 
	e.deptno;
+------------+----------+-------+
| dname      | sumsal   | count |
+------------+----------+-------+
| OPERATIONS |     0.00 |     0 |
| RESEARCH   | 10875.00 |     5 |
| SALES      |  9400.00 |     6 |
+------------+----------+-------+

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

//任职超过30年的员工
update emp set sal=sal*1.1 where (to_days(now())-to_days(hiredate))/365>30;

update emp set sal=sal*1 where(timestampdiff(year,hiredate,now())>30);