MYSQL34道练习题

最新推荐文章于 2023-03-24 00:55:40 发布
最新推荐文章于 2023-03-24 00:55:40 发布
阅读量345 收藏 2
点赞数
文章标签： mysql 数据库 sql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_44440552/article/details/113740349
版权

学习课程来源：http://www.bjpowernode.com/javavideo/111.html

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

//第一步：找出每个部门的最高薪水
select deptno,max(sal) as maxsal from emp group by deptno;
+----------+--------+
| max(sal) | deptno |
+----------+--------+
|  3000.00 |     20 |
|  2850.00 |     30 |
|  5000.00 |     10 |
+----------+--------+

//第二步：将上表作为临时表t，与emp表进行连接，条件是e.deptno = t.deptno and e.sal = t.maxsal
 select 
     e.ename,t.* 
 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;
+-------+--------+---------+
| ename | deptno | maxsal  |
+-------+--------+---------+
| BLAKE |     30 | 2850.00 |
| SCOTT |     20 | 3000.00 |
| KING  |     10 | 5000.00 |
| FORD  |     20 | 3000.00 |
+-------+--------+---------+

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

//第一步：找出部门的平均薪水
select deptno,avg(sal) as avgsal from emp group by deptno;
+--------+-------------+
| deptno | avgsal      |
+--------+-------------+
|     20 | 2175.000000 |
|     30 | 1566.666667 |
|     10 | 2916.666667 |
+--------+-------------+

//第二步：将上表作为临时表t，与emp表进行连接，条件是e.deptno = t.deptno and e.sal > t.avgsal
select 
     e.ename,e.sal,t.* 
 from 
     emp e 
 join 
     (select deptno,avg(sal) as avgsal from emp group by deptno) t
 on 
     e.deptno = t.deptno and e.sal > t.avgsal;
+-------+---------+--------+-------------+
| ename | sal     | deptno | avgsal      |
+-------+---------+--------+-------------+
| ALLEN | 1600.00 |     30 | 1566.666667 |
| JONES | 2975.00 |     20 | 2175.000000 |
| BLAKE | 2850.00 |     30 | 1566.666667 |
| SCOTT | 3000.00 |     20 | 2175.000000 |
| KING  | 5000.00 |     10 | 2916.666667 |
| FORD  | 3000.00 |     20 | 2175.000000 |
+-------+---------+--------+-------------+

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

//第一步：取得部门中所有人的薪水等级
select 
	e.ename,e.sal,e.deptno,s.grade
from
	emp e
join
	salgrade s
on
	e.sal between s.losal and s.hisal
order by
	e.deptno;
+--------+---------+--------+-------+
| ename  | sal     | deptno | grade |
+--------+---------+--------+-------+
| CLARK  | 2450.00 |     10 |     4 |
| KING   | 5000.00 |     10 |     5 |
| MILLER | 1300.00 |     10 |     2 |
| SMITH  |  800.00 |     20 |     1 |
| JONES  | 2975.00 |     20 |     4 |
| SCOTT  | 3000.00 |     20 |     4 |
| ADAMS  | 1100.00 |     20 |     1 |
| FORD   | 3000.00 |     20 |     4 |
| ALLEN  | 1600.00 |     30 |     3 |
| WARD   | 1250.00 |     30 |     2 |
| MARTIN | 1250.00 |     30 |     2 |
| BLAKE  | 2850.00 |     30 |     4 |
| TURNER | 1500.00 |     30 |     3 |
| JAMES  |  950.00 |     30 |     1 |
+--------+---------+--------+-------+

//第二步：按照部门分组，求平均
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;
+--------+--------------+
| deptno | avg(s.grade) |
+--------+--------------+
|     20 |       2.8000 |
|     30 |       2.5000 |
|     10 |       3.6667 |
+--------+--------------+

4、取得最高薪水

//第一种方法
select max(sal) from emp;
+----------+
| max(sal) |
+----------+
|  5000.00 |
+----------+


//第二种：sal降序，limit1
select ename,sal from emp order by sal desc limit 1;
+-------+---------+
| ename | sal     |
+-------+---------+
| KING  | 5000.00 |
+-------+---------+


//第三种：表的自连接
select 
	sal 
from
 	emp
where 
    sal not in(select distinct a.sal from emp a join emp b on a.sal < b.sal);
+---------+
| sal     |
+---------+
| 5000.00 |
+---------+

5、取得平均薪水最高的部门的部门编号

//第一步：取得各部门的平均薪水
select deptno,avg(sal) as avgsal from emp group by deptno;
+--------+-------------+
| deptno | avgsal      |
+--------+-------------+
|     20 | 2175.000000 |
|     30 | 1566.666667 |
|     10 | 2916.666667 |
+--------+-------------+

//方法1：降序选limit 1
select deptno,avg(sal) as avgsal from emp group by deptno order by deptno desc limit 1;
+--------+-------------+
| deptno | avgsal      |
+--------+-------------+
|     30 | 1566.666667 |
+--------+-------------+

//方法2:先找avgsal的max
select 
	max(t.avgsal) 
from 
	(select avg(sal) as avgsal from emp group by deptno) t;
+---------------+
| max(t.avgsal) |
+---------------+
|   2916.666667 |
+---------------+	

//下一步
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);
+--------+-------------+
| deptno | avgsal      |
+--------+-------------+
|     10 | 2916.666667 |
+--------+-------------+

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

select
	e.deptno,d.dname,avg(e.sal) as avgsal
from
	emp e,dept d
where
	e.deptno = d.deptno
group by
	e.deptno
order by
	e.deptno desc 
limit
    1;
+--------+-------+-------------+
| deptno | dname | avgsal      |
+--------+-------+-------------+
|     30 | SALES | 1566.666667 |
+--------+-------+-------------+

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

//第一步：求各部门平均薪水
select 
	d.dname,avg(sal) as avgsal 
from 
	emp e 
join
	dept d 
on 
	e.deptno = d.deptno 
group by 
	d.dname;
+------------+-------------+
| dname      | avgsal      |
+------------+-------------+
| RESEARCH   | 2175.000000 |
| SALES      | 1566.666667 |
| ACCOUNTING | 2916.666667 |
+------------+-------------+

//第二步：求各部门平均薪水等级,将上表t和salgrade表s连接，条件：t.avgsal between s.losal and s.hisal
select 
	t.*,s.grade
from
	(select d.dname,avg(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;
+------------+-------------+-------+
| dname      | avgsal      | grade |
+------------+-------------+-------+
| RESEARCH   | 2175.000000 |     4 |
| SALES      | 1566.666667 |     3 |
| ACCOUNTING | 2916.666667 |     4 |
+------------+-------------+-------+

//第三步：继续往后添加where条件
//还可以用降序取limit 1，但是不严谨，因为平均薪水最低，平均薪水等级一定最低。但是平均薪水等级最低的不一定是平均薪水最低的。
between s.losal and s.hisal
select 
	t.*,s.grade
from
	(select d.dname,avg(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);

//where条件其实就是s.grade = 3
select avg(sal) as avgsal from emp group by deptno order by avgsal asc limit 1;
+-------------+
| avgsal      |
+-------------+
| 1566.666667 |
+-------------+

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 |
+-------+

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);
+----------+
| max(sal) |
+----------+
|  1600.00 |
+----------+	

//第三步：找出薪水高于1600的
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 ename,sal from emp order by sal desc limit 5;
+-------+---------+
| ename | sal     |
+-------+---------+
| KING  | 5000.00 |
| SCOTT | 3000.00 |
| FORD  | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
+-------+---------+

10、取得薪水最高的第六到第十名员工

select ename,sal from emp order by sal desc limit 5, 5;
+--------+---------+
| ename  | sal     |
+--------+---------+
| CLARK  | 2450.00 |
| ALLEN  | 1600.00 |
| TURNER | 1500.00 |
| MILLER | 1300.00 |
| MARTIN | 1250.00 |
+--------+---------+

11、取得最后入职的 5 名员工，日期降序。

select ename,hiredate from emp order by hiredate desc limit 5;
+--------+------------+
| ename  | hiredate   |
+--------+------------+
| ADAMS  | 1987-05-23 |
| SCOTT  | 1987-04-19 |
| MILLER | 1982-01-23 |
| FORD   | 1981-12-03 |
| 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;
order by 
	s.grade;
+--------+---------+-------+
| ename  | sal     | grade |
+--------+---------+-------+
| SMITH  |  800.00 |     1 |
| ADAMS  | 1100.00 |     1 |
| JAMES  |  950.00 |     1 |
| WARD   | 1250.00 |     2 |
| MARTIN | 1250.00 |     2 |
| MILLER | 1300.00 |     2 |
| ALLEN  | 1600.00 |     3 |
| TURNER | 1500.00 |     3 |
| JONES  | 2975.00 |     4 |
| BLAKE  | 2850.00 |     4 |
| CLARK  | 2450.00 |     4 |
| SCOTT  | 3000.00 |     4 |
| FORD   | 3000.00 |     4 |
| KING   | 5000.00 |     5 |
+--------+---------+-------+

//第二步：继续按照grade分组统计数量
select 
	s.grade,count(*) 
from 
	emp e
join 
	salgrade s
on
	e.sal between s.losal and s.hisal
group by 
	s.grade;
+-------+----------+
| grade | count(*) |
+-------+----------+
|     1 |        3 |
|     3 |        2 |
|     2 |        3 |
|     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 号课所有学生的姓名。

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

select 
	e.ename '员工',m.ename '领导' 
from 
	emp e 
left join 
	emp m 
on 
	e.mgr = m.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、列出受雇日期早于其直接上级的所有员工的编号,姓名,部门名称

emp a 员工表
emp b 领导表
a.mgr = b.empno and a.hiredate < b.hiredate

select 
	a.ename '员工',a.hiredate,b.ename '领导',b.hiredate,d.deptno 
from 
	emp a 
join 
	emp b 
on 
	a.mgr = b.empno 
join 
	dept d 
on 
	d.deptno = a.deptno 
where 
	a.hiredate < b.hiredate;
+-------+------------+-------+------------+--------+
| 员工  | hiredate   | 领导  | hiredate   | deptno |
+-------+------------+-------+------------+--------+
| SMITH | 1980-12-17 | FORD  | 1981-12-03 |     20 |
| ALLEN | 1981-02-20 | BLAKE | 1981-05-01 |     30 |
| WARD  | 1981-02-22 | BLAKE | 1981-05-01 |     30 |
| JONES | 1981-04-02 | KING  | 1981-11-17 |     20 |
| BLAKE | 1981-05-01 | KING  | 1981-11-17 |     30 |
| CLARK | 1981-06-09 | KING  | 1981-11-17 |     10 |
+-------+------------+-------+------------+--------+

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

select 
	d.dname,e.* 
from 
	dept d 
left join 
	emp e 
on 
	e.deptno = d.deptno;
+------------+-------+--------+-----------+------+------------+---------+---------+--------+
| dname      | EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+------------+-------+--------+-----------+------+------------+---------+---------+--------+
| ACCOUNTING |  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
| ACCOUNTING |  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
| ACCOUNTING |  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
| RESEARCH   |  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
| RESEARCH   |  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
| RESEARCH   |  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
| RESEARCH   |  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |
| RESEARCH   |  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
| SALES      |  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
| SALES      |  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
| SALES      |  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
| SALES      |  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
| SALES      |  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
| SALES      |  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
| OPERATIONS |  NULL | NULL   | NULL      | NULL | NULL       |    NULL |    NULL |   NULL |

17、列出至少有 5 个员工的所有部门（按照部门编号分组，计数，筛选出 >= 5）

select 
	e.deptno 
from 
	emp e 
group by 
	e.deptno 
having 
	count(*) >= 5;
+--------+
| deptno |
+--------+
|     20 |
|     30 |
+--------+

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

select 
	e.* 
from 
	emp e 
where 
	e.sal > (select sal from emp where ename = 'SMITH')
+-------+--------+-----------+------+------------+---------+---------+--------+
| 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 |    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 |
+-------+--------+-----------+------+------------+---------+---------+--------+

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

//第一步：找出job为CLERK的员工及其部门
select 
	e.ename,e.job,d.dname,d.deptno
from 
	emp e
join
	dept d
on
	e.deptno = d.deptno
where 
	e.job = 'CLERK';

+--------+-------+------------+--------+
| ename  | job   | dname      | deptno |
+--------+-------+------------+--------+
| MILLER | CLERK | ACCOUNTING |     10 |
| SMITH  | CLERK | RESEARCH   |     20 |
| ADAMS  | CLERK | RESEARCH   |     20 |
| JAMES  | CLERK | SALES      |     30 |
+--------+-------+------------+--------+


//每个部门的人数？
select deptno, count(*) as deptcount from emp group by deptno;
+--------+-----------+
| deptno | deptcount |
+--------+-----------+
|     10 |         3 |
|     20 |         5 |
|     30 |         6 |
+--------+-----------+

//将以上两表做连接
select 
	t1.*,t2.deptcount
from
	(select 
		e.ename,e.job,d.dname,d.deptno
	from 
		emp e
	join
		dept d
	on
		e.deptno = d.deptno
	where 
		e.job = 'CLERK') t1
join
	(select deptno, count(*) as deptcount from emp group by deptno) t2
on
	t1.deptno = t2.deptno;
+--------+-------+------------+--------+-----------+
| ename  | job   | dname      | deptno | deptcount |
+--------+-------+------------+--------+-----------+
| MILLER | CLERK | ACCOUNTING |     10 |         3 |
| SMITH  | CLERK | RESEARCH   |     20 |         5 |
| ADAMS  | CLERK | RESEARCH   |     20 |         5 |
| JAMES  | CLERK | SALES      |     30 |         6 |
+--------+-------+------------+--------+-----------+

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

//第一步：列出所有工作的最低薪资
select job,min(sal) 'minsal' from emp group by job;
+-----------+---------+
| job       | minsal  |
+-----------+---------+
| CLERK     |  800.00 |
| SALESMAN  | 1250.00 |
| MANAGER   | 2450.00 |
| ANALYST   | 3000.00 |
| PRESIDENT | 5000.00 |
+-----------+---------+

//第二步：列出最低薪资大于1500的工作
select job,min(sal) 'minsal' from emp group by job having minsal > 1500;
+-----------+---------+
| job       | minsal  |
+-----------+---------+
| MANAGER   | 2450.00 |
| ANALYST   | 3000.00 |
| PRESIDENT | 5000.00 |

//第三步：
select job,min(sal) 'minsal',count(*) from emp group by job having min(sal) > 1500;
+-----------+---------+----------+
| job       | minsal  | count(*) |
+-----------+---------+----------+
| MANAGER   | 2450.00 |        3 |
| ANALYST   | 3000.00 |        2 |
| PRESIDENT | 5000.00 |        1 |
+-----------+---------+----------+

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

select ename,dname from emp where deptno = (select deptno from dept where dname = 'SALES');
+--------+
| ename  |
+--------+
| ALLEN  |
| WARD   |
| MARTIN |
| BLAKE  |
| TURNER |
| JAMES  |
+--------+

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

//第一步：列出公司平均薪资
select avg(sal) as avgsal from emp;
+-------------+
| avgsal      |
+-------------+
| 2073.214286 |
+-------------+

//第二步：列出薪金高于公司平均薪金的所有员工
select 
	ename,sal  
from 
	emp 
where 
	sal > (select avg(sal) as avgsal from emp);
+-------+---------+
| ename | sal     |
+-------+---------+
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| SCOTT | 3000.00 |
| KING  | 5000.00 |
| FORD  | 3000.00 |
+-------+---------+

//第三步：
select 
	e.ename '员工',d.dname,l.ename '领导',s.grade 
from 
	emp e 
join 
	dept d  
on 
	d.deptno = e.deptno 
left join 
    emp l 
on 
	e.mgr = l.empno 
join 
	salgrade s 
on 
	e.sal between s.losal and s.hisal 
where 
    e.sal > (select avg(sal) from emp);
+-------+------------+-------+-------+
| 员工  | dname      | 领导  | grade |
+-------+------------+-------+-------+
| JONES | RESEARCH   | KING  |     4 |
| BLAKE | SALES      | KING  |     4 |
| CLARK | ACCOUNTING | KING  |     4 |
| SCOTT | RESEARCH   | JONES |     4 |
| FORD  | RESEARCH   | JONES |     4 |
| KING  | ACCOUNTING | NULL  |     5 |
+-------+------------+-------+-------+

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

//第一步：找出SCOTT的工作
select ename,job from emp where ename = 'SCOTT';
+-------+---------+
| ename | job     |
+-------+---------+
| SCOTT | ANALYST |
+-------+---------+

//第二步：
select 
	e.ename,e.job,d.dname 
from 
	emp e 
join 
	dept d 
on 
	e.deptno = d.deptno 
where 
	e.job = (select job from emp where ename = 'SCOTT') 
and 
	e.ename <> 'SCOTT';
+-------+---------+----------+
| ename | job     | dname    |
+-------+---------+----------+
| FORD  | ANALYST | RESEARCH |
+-------+---------+----------+

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

select distinct sal from emp where deptno = 30;
+---------+
| sal     |
+---------+
| 1600.00 |
| 1250.00 |
| 2850.00 |
| 1500.00 |
|  950.00 |
+---------+

select  
	ename,sal 
from 
	emp 
where 
	sal in(select distinct sal from emp where deptno = 30) 
and 
	deptno <> 30;
Empty set (0.00 sec)

25、列出薪金高于在部门 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 
	e.deptno = d.deptno 
where 
	e.sal > (select max(sal) from emp where deptno = 30);
+-------+---------+------------+
| ename | sal     | dname      |
+-------+---------+------------+
| JONES | 2975.00 | RESEARCH   |
| SCOTT | 3000.00 | RESEARCH   |
| KING  | 5000.00 | ACCOUNTING |
| FORD  | 3000.00 | RESEARCH   |
+-------+---------+------------+

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

select 
	d.deptno,
	count(e.ename) count,
	ifnull(avg(e.sal),0) avgsal,
	ifnull(avg(timestampdiff(year,hiredate,now())),0) avgservicetime 
from 
	emp e 
right join 
	dept d 
on 
	e.deptno = d.deptno 
group by 
	d.deptno;
+--------+-------+-------------+----------------+
| deptno | count | avgsal      | avgservicetime |
+--------+-------+-------------+----------------+
|     10 |     3 | 2916.666667 |        39.0000 |
|     20 |     5 | 2175.000000 |        36.8000 |
|     30 |     6 | 1566.666667 |        39.0000 |
|     40 |     0 |    0.000000 |         0.0000 |
+--------+-------+-------------+----------------+

在mysql当中怎么计算两个日期的“年差”，差了多少年？
	TimeStampDiff(间隔类型, 前一个日期, 后一个日期)
	
	timestampdiff(YEAR, hiredate, now())

间隔类型：
	SECOND   秒，
	MINUTE   分钟，
	HOUR   小时，
	DAY   天，
	WEEK   星期
	MONTH   月，
	QUARTER   季度，
	YEAR   年

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

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

+--------+------------+---------+
| ename  | dname      | sal     |
+--------+------------+---------+
| CLARK  | ACCOUNTING | 2450.00 |
| KING   | ACCOUNTING | 5000.00 |
| MILLER | ACCOUNTING | 1300.00 |
| SMITH  | RESEARCH   |  800.00 |
| JONES  | RESEARCH   | 2975.00 |
| SCOTT  | RESEARCH   | 3000.00 |
| ADAMS  | RESEARCH   | 1100.00 |
| FORD   | RESEARCH   | 3000.00 |
| ALLEN  | SALES      | 1600.00 |
| WARD   | SALES      | 1250.00 |
| MARTIN | SALES      | 1250.00 |
| BLAKE  | SALES      | 2850.00 |
| TURNER | SALES      | 1500.00 |
| JAMES  | SALES      |  950.00 |
+--------+------------+---------+

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

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

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

//第一步：找出各种工作的最低工资
select 
	job,min(sal) as minsal
from
	emp
group by
	job;
+-----------+----------+
| job       | minsal   |
+-----------+----------+
| ANALYST   |  3000.00 |
| CLERK     |   800.00 |
| MANAGER   |  2450.00 |
| PRESIDENT |  5000.00 |
| SALESMAN  |  1250.00 |
+-----------+----------+

//第二步：将上表作为表m与emp连接
select 
	e.ename,m.* 
from 
	emp e 
join
	(select job,min(sal) minsal from emp group by job) m 
on 
e.sal = m.minsal and e.job = m.job;
+--------+-----------+---------+
| ename  | job       | minsal  |
+--------+-----------+---------+
| SMITH  | CLERK     |  800.00 |
| WARD   | SALESMAN  | 1250.00 |
| MARTIN | SALESMAN  | 1250.00 |
| CLARK  | MANAGER   | 2450.00 |
| SCOTT  | ANALYST   | 3000.00 |
| KING   | PRESIDENT | 5000.00 |
| FORD   | ANALYST   | 3000.00 |
+--------+-----------+---------+

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

select 
	deptno,min(sal) 
from 
	emp 
where 
	job = 'manager' 
group by 
	deptno;
+--------+----------+
| deptno | min(sal) |
+--------+----------+
|     20 |  2975.00 |
|     30 |  2850.00 |
|     10 |  2450.00 |
+--------+----------+

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

select 
	ename,(sal+ifnull(comm,0))*12 
as 
	yearsal 
from 
	emp 
order by 
	yearsal asc;
+--------+----------+
| ename  | yearsal  |
+--------+----------+
| SMITH  |  9600.00 |
| JAMES  | 11400.00 |
| ADAMS  | 13200.00 |
| MILLER | 15600.00 |
| TURNER | 18000.00 |
| WARD   | 21000.00 |
| ALLEN  | 22800.00 |
| CLARK  | 29400.00 |
| MARTIN | 31800.00 |
| BLAKE  | 34200.00 |
| JONES  | 35700.00 |
| FORD   | 36000.00 |
| SCOTT  | 36000.00 |
| KING   | 60000.00 |
+--------+----------+

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

select 
	e.ename '员工',l.ename '领导' 
from 
	emp e 
join
	emp l 
on 
	e.mgr = l.empno where l.sal > 3000;
+-------+------+
| 员工  | 领导 |
+-------+------+
| JONES | KING |
| BLAKE | KING |
| CLARK | KING |
+-------+------+

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

//第一步：找出带S字符的部门员工
select * from dept where dname like '%S%';
+--------+------------+---------+
| DEPTNO | DNAME      | LOC     |
+--------+------------+---------+
|     20 | RESEARCH   | DALLAS  |
|     30 | SALES      | CHICAGO |
|     40 | OPERATIONS | BOSTON  |
+--------+------------+---------+

//第二步：求其中员工的工资合计及部门人数
select 
	d.*,
	ifnull(sum(e.sal),0) sumsal,
	count(e.ename) count 
from 
	dept d 
left join 
	emp e 
on 
	e.deptno = d.deptno 
where 
	dname like '%S%' 
group by 
	d.deptno;
+--------+------------+---------+----------+-------+
| DEPTNO | DNAME      | LOC     | sumsal   | count |
+--------+------------+---------+----------+-------+
|     20 | RESEARCH   | DALLAS  | 10875.00 |     5 |
|     30 | SALES      | CHICAGO |  9400.00 |     6 |
|     40 | OPERATIONS | BOSTON  |     0.00 |     0 |
+--------+------------+---------+----------+-------+

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

update 
	emp 
set 
	sal = sal * 1.1 
where 
	timestampdiff(YEAR, hiredate, now()) > 30;
yaoqinghao
关注
  • 0
    点赞
  • 踩
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
Mysql命令实操练习.sql文件
11-18
Mysql基本命令实操.sql文件， bjpowernode.sql文件有三张表{ 部门表：dept；员工表:emp；工资等级表:salgrade }
MySQL基础篇 | 经典三十四道练习题
m0_61933976的博客
09-18 5875
关于MySQL的三十四道练习题，大佬们都会做吗
MySQL根据字段a重复，字段b（时间）倒序，只取第一条数据
HeliosJ的博客
09-18 4759
问题描述： 现有如下图所示数据库数据： 我需要从vehicle_year_check_type = 1 的数据中，通过 vehicle_id 车辆id分组，vehicle_year_check_effective_date 有效时间倒序排列，取出相同车辆中的第一条数据，即时间最大的那条数据。 解决办法： 首先使用分析函数row_number() over (partiion by … order by …)对数据进行分组编号，然后取出分组编号为1的数据，即需求中要取的第一条数据。 PARTITION BY
MySQL34道练习题
heliuerya的博客
01-19 3600
MySQL34道练习题
数据库MySQL学习——内含34道MySQL练习题及答案
risroy的博客
09-25 6602
数据库MySQL 1 MySQL数据库简介 1.1 sql、DB、DBMS分别是什么，关系？ DB：DataBase 数据库 DBMS：DateBase Management System 数据库管理系统 SQL：结构化查询语言、sql语句的编译有dbms完成 DBMS负责执行sql语句，通过之心sql语句来操作DB当中的数据 1.2 什么是表？ table 是数据库的基本组成单元，所有的数据都以表格的形式组织，目的是可读性强 行：被称为数据/记录（data） 列：被称为字段（column） 学号（
MySQL练习题（34道）
09-12
此练习题可以配套本人MySQL讲解专栏使用，在本人文件分享中，有nanxi.sql文件，该文件为此练习题配套文件，文档中所有题目均来源于该文件. 适合人群： 初学mysql的任何人群，已经配套专栏系统学习过mysql的任何人群...
MySQL50道练习题.zip
04-13
这里我们有“MySQL50道练习题”的压缩包，它包含了一系列问题，旨在帮助用户深入理解和掌握MySQL的基本操作、查询技巧以及高级功能。通过解答这些练习题，你可以提升自己的SQL技能，更好地应对实际工作中的数据管理...
MYSQL练习题34道.zip
06-16
这份"MYSQL练习题34道.zip"资源包含了34个针对MySQL基础知识的练习题目，旨在帮助初学者巩固和提升他们在数据库操作方面的能力，特别是涉及增删改查（CRUD）的基本操作。 1. **增（Create）**: 在MySQL中，创建数据...
50道MySQL经典练习题
12-14
这个是网上流传的50道SQL练习题，最近拿来练习，刚做完，这里把我做的答案给大家做个参考，如有错误，还请告知。 另外，做题的时候先不要看答案（答案不唯一，只要满足要求即可），要有自己的一个思考过程，这样做...
MySQL34道作业题.md
12-13
适合巩固mysql知识
mysql 分组排序取第一条记录
bswsf的专栏
08-27 1393
select row_number() over(partition by A order by B ) as rowIndex from table 比如取每个班级成绩第一的学生等等 oracle 和 sqlserver支持函数，mysql不行 这里给一个粗暴的方法： 两个行数 GROUP_CONCAT(A ORDER BY B)和substring_index 思路： 1、GROUP_CONCAT(fA ORDER BY B) 按照条件排序后拼装字段，默认逗号，4“，”分隔 2、s.
《MySQL 入门教程》第 10 篇 数据排序
Tony.Dong的专栏
06-18 2018
介绍如何使用 ORDER BY 对查询结果进行排序显示，包括基于单个字段或者多个字段排序、基于表达式排序、空值排序、中文排序以及利用 FIELD 函数实现自定义排序。
老杜MySQL 34道题目详解
gurenchang的博客
07-27 1767
老杜MySQL 34道题目详解
MySQL SQL语句练习题
喝醉酒的小白
08-04 5691
34道MySQL作业题
MySQL基础练习
qq_53046794的博客
03-24 175
数据库的基本操作
MySQL之34道作业题
weixin_48485216的博客
04-15 827
表 部门表dept create database MyTest; use MyTest; create table dept( DEPTNO varchar(20), DNAME varchar(20), LOC varchar(20), primary key(DEPTNO)); insert into dept(deptno,dname,loc) values("10","ACCOUNTING","NEW YORK"), ("20","RESEARCH","DALLAS"), ("30
mysql查询34练
自律
09-21 392
文末是sql脚本，执行建表 1、取得每个部门最高薪水的人员名称 求每个部门部门号和最高薪水 SELECT `DEPTNO`,MAX(`SAL`) maxsal FROM `emp` GROUP BY `DEPTNO`; 最终sql语句 SELECT e.`ENAME`,t.* FROM (SELECT `DEPTNO`,MAX(`SAL`) maxsal FROM `emp` GROUP BY `DEPTNO`) t,emp e WHERE t.`DEPTNO`=e.`DEPTNO` AND t.max
学习练习SQL的数据库employee文件
weixin_34130269的博客
09-12 633
这个地址是一个已经集成了employee的database的sql文件。所以你可以下载这个sql文件，然后执行sql语句到对应的数据库中，这样你就可以练习sql语句了。 第一个链接是用户指导如何使用的。第二个链接是下载数据库的地址。 http://downloads.mysql.com/docs/employee-en.a4.pdf https://la...
MySql7——34道题目
m0_38057941的博客
10-12 1208
1、取得每个部门最高薪水的人员名称 a.取得每个部门最高薪水 select max(sal) as ‘maxsal’,deptno from emp group by deptno; b.emp表与 a步骤的表，进行连接 select e.ename,e.deptno,e.sal,t.maxsal from emp e join (select max(sal) as ‘maxsal’,deptno from emp group by deptno) t on t.maxsal=e.sal; 2.哪
mysql经典50道练习题pdf
最新发布
08-17
MySQL经典50道练习题pdf是一本经典的MySQL数据库练习题集合，对于想要提高MySQL数据库操作能力的人士来说是非常有价值的。 这本练习题集合涵盖了各种MySQL数据库操作方面的题目，包括数据库创建、表创建、数据插入...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值