SQL练习题02

最新推荐文章于 2023-06-06 18:45:00 发布
最新推荐文章于 2023-06-06 18:45:00 发布
阅读量260 收藏
点赞数
分类专栏： MySQL 文章标签： sql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/TSCCG/article/details/118945193
版权

SQL练习题02

一.本次练习用到的三个表

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 |
+-------+--------+-----------+------+------------+---------+---------+--------+
14 rows in set (0.01 sec)

dept表

+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+
4 rows in set (0.01 sec)

salgrade表

+-------+-------+-------+
| GRADE | LOSAL | HISAL |
+-------+-------+-------+
|     1 |   700 |  1200 |
|     2 |  1201 |  1400 |
|     3 |  1401 |  2000 |
|     4 |  2001 |  3000 |
|     5 |  3001 |  9999 |
+-------+-------+-------+
5 rows in set (0.01 sec)

二.练习题

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

第一步：找出所有领导人的员工编号

select distinct mgr
from emp
where mgr is not null;

结果：

+------+
| mgr  |
+------+
| 7902 |
| 7698 |
| 7839 |
| 7566 |
| 7788 |
| 7782 |
+------+
6 rows in set (0.00 sec)

第二步：找出普通员工的最高薪资

不在第一步查询结果中的都是普通员工的员工编号

select max(sal)
from emp
where empno not in (select distinct mgr from emp where mgr is not null);

结果：

+----------+
| max(sal) |
+----------+
|  1600.00 |
+----------+
1 row in set (0.00 sec)

第三步：找出高于普通员工最高薪资的领导人

在员工表中，比普通员工最高薪资还高的不可能还是普通员工

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 |
+-------+---------+
6 rows in set (0.01 sec)

10.取得薪水最高的前5名员工

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 |
+-------+---------+
5 rows in set (0.00 sec)

11.取得薪水最高的第6名到第10名员工

select ename,sal
from emp
order by sal desc
limit 5,5;
#limit 第6条记录的下标，长度

结果：

+--------+---------+
| ename  | sal     |
+--------+---------+
| CLARK  | 2450.00 |
| ALLEN  | 1600.00 |
| TURNER | 1500.00 |
| MILLER | 1300.00 |
| MARTIN | 1250.00 |
+--------+---------+
5 rows in set (0.00 sec)

12.取得最后入职的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 |
+--------+------------+
5 rows in set (0.00 sec)

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

第一步：取得所有员工的薪水等级：

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 |
+--------+---------+-------+
14 rows in set (0.00 sec)

第二步：接着把第一步的结果按薪资等级分组，统计每个等级的员工数量

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 |
|     2 |        3 |
|     3 |        2 |
|     4 |        5 |
|     5 |        1 |
+-------+----------+
5 rows in set (0.00 sec)

14.列出所有员工及其对应领导的名字

使用自连接

select a.ename as 'woker',b.ename as 'leader'
from emp a
left join emp b#左外连接，防止king不显示
on a.mgr = b.empno;

结果：

+--------+--------+
| woker  | leader |
+--------+--------+
| 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  |
+--------+--------+
14 rows in set (0.00 sec)

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

emp a

emp b

a.mgr = b.empno

a.hiredate < b.hiradate

select a.empno,a.ename as 'worker',a.hiredate,b.ename as 'leader',b.hiredate,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 | worker | hiredate   | leader | hiredate   | dname      |
+-------+--------+------------+--------+------------+------------+
|  7782 | CLARK  | 1981-06-09 | KING   | 1981-11-17 | ACCOUNTING |
|  7369 | SMITH  | 1980-12-17 | FORD   | 1981-12-03 | RESEARCH   |
|  7566 | JONES  | 1981-04-02 | KING   | 1981-11-17 | RESEARCH   |
|  7499 | ALLEN  | 1981-02-20 | BLAKE  | 1981-05-01 | SALES      |
|  7521 | WARD   | 1981-02-22 | BLAKE  | 1981-05-01 | SALES      |
|  7698 | BLAKE  | 1981-05-01 | KING   | 1981-11-17 | SALES      |
+-------+--------+------------+--------+------------+------------+
6 rows in set (0.00 sec)

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

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

结果：

+-------+--------+--------+------------+
| empno | ename  | deptno | dname      |
+-------+--------+--------+------------+
|  7782 | CLARK  |     10 | ACCOUNTING |
|  7839 | KING   |     10 | ACCOUNTING |
|  7934 | MILLER |     10 | ACCOUNTING |
|  7369 | SMITH  |     20 | RESEARCH   |
|  7566 | JONES  |     20 | RESEARCH   |
|  7788 | SCOTT  |     20 | RESEARCH   |
|  7876 | ADAMS  |     20 | RESEARCH   |
|  7902 | FORD   |     20 | RESEARCH   |
|  7499 | ALLEN  |     30 | SALES      |
|  7521 | WARD   |     30 | SALES      |
|  7654 | MARTIN |     30 | SALES      |
|  7698 | BLAKE  |     30 | SALES      |
|  7844 | TURNER |     30 | SALES      |
|  7900 | JAMES  |     30 | SALES      |
|  NULL | NULL   |   NULL | OPERATIONS |
+-------+--------+--------+------------+
15 rows in set (0.00 sec)

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

select deptno,count(*) as count
from emp
group by deptno
having count >= 5;

结果：

+--------+-------+
| deptno | count |
+--------+-------+
|     20 |     5 |
|     30 |     6 |
+--------+-------+
2 rows in set (0.00 sec)

18.列出工资比员工"SMITH"多的所有员工

第一步：找出员工"SMITH"的工资

select sal from emp where ename = 'SMITH';

结果：

+--------+
| sal    |
+--------+
| 800.00 |
+--------+
1 row in set (0.00 sec)

第二步：找出比"SMITH"工资高的员工

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

结果：

+--------+
| ename  |
+--------+
| ALLEN  |
| WARD   |
| JONES  |
| MARTIN |
| BLAKE  |
| CLARK  |
| SCOTT  |
| KING   |
| TURNER |
| ADAMS  |
| JAMES  |
| FORD   |
| MILLER |
+--------+
13 rows in set (0.00 sec)

19.列出所有职位为"CLERK"的员工姓名及其部门名称，以及其部门的总人数

第一步：找出所有职位为"CLERK"的员工

select ename,deptno,job
from emp
where job = 'CLERK';

结果：

+--------+--------+-------+
| ename  | deptno | job   |
+--------+--------+-------+
| SMITH  |     20 | CLERK |
| ADAMS  |     20 | CLERK |
| JAMES  |     30 | CLERK |
| MILLER |     10 | CLERK |
+--------+--------+-------+
4 rows in set (0.00 sec)

第二步：为第一步的结果添加部门名称

select t.*,d.dname
from (select ename,deptno,job from emp where job = 'CLERK') as t
join dept d
on t.deptno = d.deptno;

结果：

+--------+--------+-------+------------+
| ename  | deptno | job   | dname      |
+--------+--------+-------+------------+
| MILLER |     10 | CLERK | ACCOUNTING |
| SMITH  |     20 | CLERK | RESEARCH   |
| ADAMS  |     20 | CLERK | RESEARCH   |
| JAMES  |     30 | CLERK | SALES      |
+--------+--------+-------+------------+
4 rows in set (0.00 sec)

第三步：查询各个部门的总人数

select deptno,count(*) as count
from emp
group by deptno;

结果：

+--------+-------+
| deptno | count |
+--------+-------+
|     10 |     3 |
|     20 |     5 |
|     30 |     6 |
+--------+-------+
3 rows in set (0.00 sec)

第四步：为第二步结果添加各个部门的总人数

select t.*,d.dname,c.count
from (select ename,deptno,job from emp where job = 'CLERK') as t
join dept d
on t.deptno = d.deptno
join (select deptno,count(*) as count from emp group by deptno) as c
on t.deptno = c.deptno;

结果：

+--------+--------+-------+------------+-------+
| ename  | deptno | job   | dname      | count |
+--------+--------+-------+------------+-------+
| SMITH  |     20 | CLERK | RESEARCH   |     5 |
| ADAMS  |     20 | CLERK | RESEARCH   |     5 |
| JAMES  |     30 | CLERK | SALES      |     6 |
| MILLER |     10 | CLERK | ACCOUNTING |     3 |
+--------+--------+-------+------------+-------+
4 rows in set (0.01 sec)

20.列出最低工资大于1500的工作以及从事此工作的全部雇员人数

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

结果：

+-----------+----------+
| job       | count(*) |
+-----------+----------+
| ANALYST   |        2 |
| MANAGER   |        3 |
| PRESIDENT |        1 |
+-----------+----------+
3 rows in set (0.00 sec)

21.列出在部门"SALES"中工作的员工姓名(假设不知到该部门的部门编号)

第一步：获取该部门的部门编号

select deptno
from dept
where dname = 'SALES';

结果：

+--------+
| deptno |
+--------+
|     30 |
+--------+
1 row in set (0.00 sec)

第二步：获取在部门编号为30的员工姓名

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

结果：

+--------+
| ename  |
+--------+
| ALLEN  |
| WARD   |
| MARTIN |
| BLAKE  |
| TURNER |
| JAMES  |
+--------+
6 rows in set (0.00 sec)

22.列出薪水高于公司平均薪水的所有员工，要求显示员工所在部门、上级领导以及员工的工资等级

第一步：查询公司平均薪水

select avg(sal)
from emp;

结果：

+-------------+
| avg(sal)    |
+-------------+
| 2073.214286 |
+-------------+
1 row in set (0.02 sec)

第二步：列举薪水高于公司平均薪水的所有员工

select ename
from emp
where sal > (select avg(sal) from emp);

结果：

+-------+
| ename |
+-------+
| JONES |
| BLAKE |
| CLARK |
| SCOTT |
| KING  |
| FORD  |
+-------+
6 rows in set (0.00 sec)

第三步：查询薪水高于公司平均薪水的所有员工所在部门、上级领导以及员工的工资等级

select e.ename as 'worker',l.ename as 'leader',d.dname,e.sal,s.grade
from emp e
left join emp l
on e.mgr = l.empno
join dept d
on e.deptno = d.deptno
join salgrade s
on e.sal between s.losal and s.hisal
where e.sal > (select avg(sal) from emp);

结果：

+--------+--------+------------+---------+-------+
| worker | leader | dname      | sal     | grade |
+--------+--------+------------+---------+-------+
| JONES  | KING   | RESEARCH   | 2975.00 |     4 |
| BLAKE  | KING   | SALES      | 2850.00 |     4 |
| CLARK  | KING   | ACCOUNTING | 2450.00 |     4 |
| SCOTT  | JONES  | RESEARCH   | 3000.00 |     4 |
| KING   | NULL   | ACCOUNTING | 5000.00 |     5 |
| FORD   | JONES  | RESEARCH   | 3000.00 |     4 |
+--------+--------+------------+---------+-------+
6 rows in set (0.00 sec)

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

第一步：找出员工”SCOTT“所从事的工作

select job
c
where ename = 'SCOTT';

结果：

+---------+
| job     |
+---------+
| ANALYST |
+---------+
1 row in set (0.01 sec)

第二步：查询除”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 |
+-------+---------+----------+
1 row in set (0.00 sec)

24.列出比30部门中所有员工的工资都高的员工，要求显示员工姓名、薪资和部门名称

第一步：找出30部门员工中工资最高的

select max(sal)
from emp
where deptno = 30;

结果：

+----------+
| max(sal) |
+----------+
|  2850.00 |
+----------+
1 row in set (0.02 sec)

第二步：列出工资比2850高的员工

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      |
+-------+---------+------------+
| KING  | 5000.00 | ACCOUNTING |
| JONES | 2975.00 | RESEARCH   |
| SCOTT | 3000.00 | RESEARCH   |
| FORD  | 3000.00 | RESEARCH   |
+-------+---------+------------+

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

第一步：列出每个部门的员工数量

需要列出所有部门，故使用外连接

select d.dname,count(e.ename) as wokers,ifnull(avg(e.sal),0) as avgsal
from emp e
right join dept d
on e.deptno = d.deptno
group by d.dname;

结果：

+------------+--------+-------------+
| dname      | wokers | avgsal      |
+------------+--------+-------------+
| ACCOUNTING |      3 | 2916.666667 |
| OPERATIONS |      0 |    0.000000 |
| RESEARCH   |      5 | 2175.000000 |
| SALES      |      6 | 1566.666667 |
+------------+--------+-------------+

第二步：显示平均服务期限

select d.dname,count(e.ename) as wokers,ifnull(avg(e.sal),0) as avgsal,ifnull(avg(timestampdiff(year,hiredate,now())),0) as avgservicetime
from emp e
right join dept d
on e.deptno = d.deptno
group by d.dname;

结果：

+------------+--------+-------------+----------------+
| dname      | wokers | avgsal      | avgservicetime |
+------------+--------+-------------+----------------+
| ACCOUNTING |      3 | 2916.666667 |        39.3333 |
| OPERATIONS |      0 |    0.000000 |         0.0000 |
| RESEARCH   |      5 | 2175.000000 |        37.4000 |
| SALES      |      6 | 1566.666667 |        39.5000 |
+------------+--------+-------------+----------------+
4 rows in set (0.00 sec)

在mysql中怎么计算两个日期的”年差“，差了多少年？

#TimeStampDiff(间隔日期类型，前一个日期，后一个日期)
timestampdiff(YEAR,hiradate,now())

间隔日期类型：

  • 秒：second
  • 分：minute
  • 时：hour
  • 天：day
  • 星期：week
  • 月：month
  • 季度：quarter
  • 年：year

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

select d.deptno,d.dname,d.loc,count(*)
from emp e
right join dept d
on e.deptno = d.deptno
group by d.deptno,d.dname,d.loc;

结果：

+--------+------------+----------+----------+
| deptno | dname      | loc      | count(*) |
+--------+------------+----------+----------+
|     10 | ACCOUNTING | NEW YORK |        3 |
|     20 | RESEARCH   | DALLAS   |        5 |
|     30 | SALES      | CHICAGO  |        6 |
|     40 | OPERATIONS | BOSTON   |        1 |
+--------+------------+----------+----------+
4 rows in set (0.00 sec)

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

第一步：找出各种工作的最低工资

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 |
+-----------+---------+
5 rows in set (0.00 sec)

第二步：找出从事各种工作中薪资最低的员工姓名

select e.ename,t.*
from emp e
join (select job,min(sal) as minsal from emp group by job) as t
on e.job = t.job and e.sal = t.minsal;

28.列出各个部门的中工作为”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 |
+--------+----------+
3 rows in set (0.00 sec)

29.列出所有员工的年薪，按年薪正序排列

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

结果：

+--------+----------+
| 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 |
+--------+----------+
14 rows in set (0.01 sec)

30.查询所有员工中领导薪水超过3000的，显示其名称及其领导名称

select a.ename as 'worker',b.ename as 'leader'
from emp a
left join emp b
on a.mgr = b.empno
where b.sal > 3000;

结果：

+--------+--------+
| worker | leader |
+--------+--------+
| JONES  | KING   |
| BLAKE  | KING   |
| CLARK  | KING   |
+--------+--------+
3 rows in set (0.00 sec)

31.查询部门名称中，带”S“字符的部门，要求显示该部门员工的工资合计、部门人数

select d.dname,count(e.ename) as wokers,ifnull(sum(e.sal),0) as sumsal
from emp e
right join dept d
on e.deptno = d.deptno
where d.dname like '%S%'
group by d.dname;

结果：

+------------+--------+----------+
| dname      | wokers | sumsal   |
+------------+--------+----------+
| OPERATIONS |      0 |     0.00 |
| RESEARCH   |      5 | 10875.00 |
| SALES      |      6 |  9400.00 |
+------------+--------+----------+
3 rows in set (0.00 sec)

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

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

结果：

+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7369 | SMITH  | 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 |    0.00 |     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 |
+-------+--------+-----------+------+------------+---------+---------+--------+
14 rows in set (0.00 sec)
TSCCG
关注
专栏目录
数据库---34练习题
X..RH
08-13 1012
1、取得每个部门最高薪水的人员名称 select e.ename ,t.* from emp e join (select deptno,max(sal) maxsal from emp group by deptno) t on t.deptno=e.deptno and t.maxsal=e.sal; 2、哪些人的薪水在部门的平均薪水之上 select e.ename, e.sal from emp e join (select deptno,avg(sal) avgsal from emp gro
MySQL基础06 - 34道练习题
weixin_43636084的博客
03-26 100
【代码】MySQL基础06 - 34道练习题。
3、34道MySQL练习题及答案
博客
06-06 615
列出在部门"SALES"工作的员工的姓名,假定不知道销售部的部门编号。列出薪金高于（在部门30工作的所有员工的薪金）的员工姓名和薪金.部门名称.列出薪金高于公司平均薪金的所有员工,所在部门,上级领导,雇员的工资等级。列出所有"CLERK"(办事员)的姓名及其部门名称,部门的人数.列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门。列出薪金等于（部门30中员工的薪金）的其他员工的姓名和薪金。求出部门名称中,带’S’字符的部门员工的工资合计、部门人数。
老杜34道数据库作业题
zy3256的博客
10-12 962
老杜 34 道 数据库 作业题 （没有 面试题，以及某一个类似的题目）
MySQL8.0做老杜34道作业题。有的未必是按照他的思路，未完结。暂停，学新课，回头接着补，毕竟也要复习
pface的博客
01-14 1021
老杜带你学_mysql入门基础（mysql基础视频+数据库实战)_哔哩哔哩_bilibili https://www.bilibili.com/video/BV1Vy4y1z7EX?p=132&spm_id_from=pageDriver
SQL练习题 SQL练习题 SQL练习题
最新发布
09-06
以下是一些SQL练习题，涵盖了SELECT、INSERT、UPDATE、DELETE、JOIN、SUBQUERY、AGGREGATE、GROUP BY等语句和函数： 选择数据 从 employees 表中选择所有列： SELECT * FROM employees; 从 employees 表中选择 ...
Hive SQL练习题库
04-24
### Hive SQL练习题库知识点详解 #### 一、Hive SQL概述 Hive 是一个基于 Hadoop 的数据仓库工具，可以将结构化的数据文件映射为一张数据库表，并提供简单的 SQL 查询功能，使不熟悉 MapReduce 的用户可以利用 SQL...
MySQL SQL语句练习题及答案
12-21
MySQL SQL语句练习题及答案 本资源提供了 MySQL SQL 语句的练习题及答案，涵盖了创建表、插入数据、删除数据、更新数据、查询数据等多方面的知识点。 一、创建表 在 MySQL 中，创建表使用 CREATE TABLE 语句。...
50道SQL练习题及答案与详细分析
12-14
通过这50道练习题，我们可以深入理解和掌握SQL的基础语法以及高级特性。以下是一些关键知识点的讲解： 1. **创建表**：在SQL中，我们使用`CREATE TABLE`语句来创建新的表格。例如，`CREATE TABLE Student`定义了...
SQL经典练习题_sql练习_
10-03
本资料包中的"SQL经典练习题.sql练习."包含了个人收藏的一系列SQL题目和案例，这些内容常常在面试中出现，对提升SQL技能和准备面试具有很高的价值。 首先，我们要了解SQL的基本概念。SQL主要包括以下几个部分： 1....
老杜MySQL 34道题目详解
gurenchang的博客
07-27 1871
老杜MySQL 34道题目详解
数据库常见面试题集锦，数据库面试题，数据库练习题
aini4568的博客
11-30 386
建表：DEPARTMENTS:DEPARTMENT_ID(primary key),DEPARTMENT_NAME,LOCATION EMPLOYEES:EMPLOYEE_ID(primary key),EMPLOYEE_NAME,EMPLOYEE_JOB,MANAGER,SALARY,DEPARTMENT_ID 1. 列出EMPLOYEES表中各部门的部门号，最高工资，最低工资 ...
数据库练习
weixin_56680764的博客
08-13 1682
数据库练习
MySQL：练习题
璃白的博客
04-27 8008
题目 答案 1、取得每个部门最高薪水的人员名称 // 思路：先查询部门最高工资，再通过最高工资匹配对应人员 SELECT ename,sal,t.deptno FROM emp e JOIN (SELECT deptno,MAX(sal) AS maxsal FROM emp GROUP BY deptno) t ON e.deptno=t.deptno AND e.sal=t.maxsal 2、哪些人的薪水在部门的平均薪水之上 // 思路：先查出部门平均薪水，再查薪水大于平均薪水的员工 SELE
select-----查询练习
Lovely_red_scarf的博客
07-23 380
# 1. 查询工资最低的员工信息: last_name, salary #①查询最低的工资 SELECT MIN(salary) FROM employees #②查询last_name,salary，要求salary=① SELECT last_name,salary FROM employees WHERE salary=( SELECT MIN(salary) FROM employees ); # 2. 查询平均工资最低的部门信息 #方式一： #①各部门的平均工资 SELECT ...
P48 第09章_子查询练习题
weixin_54671087的博客
11-28 2044
第09章_子查询练习题 #1.查询和Zlotkey相同部门的员工姓名和工资 SELECT last_name,salary FROM employees WHERE department_id IN( SELECT department_id FROM employees WHERE last_name='Zlotkey' ) #2.查询工资比公司平均工资高的员工的员工号，姓名和工资。 SELECT employee_id,last_name,salary FROM employee
mysql最高平均工资_mysql查询出平均工资最高的部门编号
weixin_39926014的博客
01-19 3830
mysql查询出平均工资最高的部门编号一.部门表DROP TABLE IF EXISTS `depts`;CREATE TABLE `depts` (`id` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(10) DEFAULT NULL,`description` varchar(10) DEFAULT NULL,PRIMARY KEY (`id`...
数据库(二)
weixin_48100716的博客
09-05 2108
可以对查询的多条数据进行统计查询, 统计方式包括:求平均值,求最大值,求最小值,求和,计数。○ 第3页的5条数据 limit 10,5。○ 第1页的5条数据 limit 0,5。○ 第2页的5条数据 limit 5,5。- 查询id,姓名,工资和工作,按照工资升序排序 请求第1页的5条数据。- 查询所有员工的姓名和工资,按照工资升序排序,请求第2页的5条数据。
用 sql 查询部门工资最高的员工信息（三种解法）
热门推荐
Lucky小黄人的博客
03-05 1万+
今天笔试做了一道简单算法题，一道简单 sql 题，sql 题的大意是分别有员工表和部门表两张表，关联字段是部门id，求每个部门薪资最高的员工的信息，薪资最高有多个人需要全部输出。 解法一 当时我只想到了一种解法 先用子查询查到每个部门的最高工资，然后把用部门id把员工表和部门表关联在一起，最后用部门的最高工资做筛选，完整 sql 如下 select d.name Department, e.na...
SQL练习与试题解析
"SQL练习文档提供了基础的SQL语句练习，包括查询特定条件下的学生信息、课程信息和教师信息，涉及子查询、联接、聚合函数、分组、过滤等核心概念。" 在SQL中，这些练习题涵盖了多个重要的概念和操作： 1. **子查询...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

TSCCG

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值