数据库练习题34道

数据库原始数据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

安逸0306

3539人浏览 · 2022-03-08 17:12:36

安逸0306 · 2022-03-08 17:12:36 发布

数据库原始数据

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;

三张表

部门表：

员工表：

薪资登记表：

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

第一步：求每个部门的最高薪水

select deptno,max(sal) as maxsal from emp group by deptno;

结果：

第二步：将求出的表和emp进行连接

select
e.ename,t.*
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、哪些人的薪水在部门的平均薪水之上

第一步：求每个部门的平均薪水

select deptno,avg(sal) as avgsal from emp group by deptno;

结果：
第二步：所求表当做临时表t 和 emp 表进行连接，条件为

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;

结果：

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;

第二步：根据上表的基础上进行分组

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;

4.不准用函数取得最高薪水

思路：order by 排序加limit

SELECT 
ename,sal
FROM 
emp
ORDER BY
sal
DESC
LIMIT
1;

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

我的思路：
第一步：求每个部门的平均薪水

select 
deptno,avg(sal)
from
emp
group by
deptno;

第二步：再上面的基础上加上order BY 和limit

SELECT 
deptno,AVG(sal)AS avgsal
FROM
emp
GROUP BY
deptno
ORDER BY
avgsal
DESC
LIMIT
1;

需要注意的是mysql的执行顺序

from
join
on
where
group by(开始使用select中的别名，后面的语句中都可以使用)
avg,sum....
having
select
distinct
order by
limit

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

我的思路：
先求平均薪水最高的部门名称，肯定涉及了二张表的
的关联 emp和dept

SELECT 
AVG(e.sal) AS avgsal,d.dname
FROM
emp e
JOIN
dept d
ON
e.deptno=d.deptno
GROUP BY
d.dname
ORDER BY
avgsal
DESC
LIMIT
1;

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

平均薪水是800
平均薪水是900
那么他俩都是1级别。

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

第二步：找出每个部门的平均薪水的等级
以上t表和salgrade表连接，条件：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 |
+------------+-------------+-------+
| SALES | 1566.666667 | 3 |
| ACCOUNTING | 2916.666667 | 4 |
| RESEARCH | 2175.000000 | 4 |
+------------+-------------+-------+

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);

+-------+-------------+-------+
| dname | avgsal | grade |
+-------+-------------+-------+
| SALES | 1566.666667 | 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;

第一步：找出普通员工的最高薪水！
not in在使用的时候，后面小括号中记得排除NULL。
select max(sal) from emp where empno not in
(select distinct mgr from emp where mgr is not null);

第二步：找出高于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));

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

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

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

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

11、取得最后入职的 5 名员工

日期也可以进行升序，降序

select 
empno,ename,HIREDATE
from
emp
order by
HIREDATE
desc
limit
5;

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

思路：需要将员工表和薪水等级表进行连接

第一步：求出每个员工的薪水等级
select 
e.empno,e.ename,e.sal,s.grade
from
emp e
join
salgrade s
on
e.sal 
between s.losal and s.hisal;

第二步:根据上面查询的结果，按照grade进行分组

SELECT 
s.grade,count(*)
FROM
emp e
JOIN
salgrade s
ON
e.sal 
BETWEEN s.losal AND s.hisal
group by
s.grade

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

思路：自连接
SELECT
e.ename AS'员工',t.ename AS '领导'
FROM
emp e
JOIN
emp t
ON
e.mgr=t.empno;

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

第一步：
求出受雇日期早于员工上级的编号，姓名
应该要用到自连接
员工为a 领导为b
select 
a.empno,a.ename,a.deptno
from
emp a
join
emp b
on
a.mgr=b.empno
where
a.hiredate<b.hiredate;

第二步：在第一步的基础上，讲第一步的结果当成视图，
和dept进行连接
select
t.*,d.dname
from 
(select 
a.empno,a.ename,a.deptno
from
emp a
join
emp b
on
a.mgr=b.empno
where
a.hiredate<b.hiredate)t
join
dept d
on
t.deptno=d.deptno;

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

我的思路：应该是需要用到外连接了
需要将部门表和员工表进行连接，在这里使用左外连接
select
d.dname,e.*
from
dept d
left join
emp e
on
e.deptno=d.deptno;

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

SELECT
deptno
FROM
emp
GROUP BY
deptno
HAVING
COUNT(*)>=5;

一个完整的 select 语句格式如下

select 字段

from 表名

where …….

group by ……..

having …….(就是为了过滤分组后的数据而存在的—不可以单独的出现)

order by ……..

以上语句的执行顺序

1. 首先执行 where 语句过滤原始数据

2. 执行 group by 进行分组

3. 执行 having 对分组数据进行操作

4. 执行 select 选出数据

5. 执行 order by 排序

原则：能在 where 中过滤的数据，尽量在 where 中过滤，效率较高。having 的过滤是专门对分组之后的数据进行过滤

的。

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

思路：用到了子查询

SELECT
*
FROM
emp
WHERE
sal>(SELECT sal FROM emp WHERE ename='SMITH');

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

我的思路：第一步，先关联二张表，员工表和部门表
select 
e.ename,d.dname
from
emp e
join
dept d
on
e.deptno=d.deptno
where
e.job='CLERK';

第二步：求出每个部门的人数
select
deptno,count(*)as countdept
from
emp
group by
deptno;

第三步：将二张表关联
SELECT 
     t1.*,t2.countdept
FROM

(SELECT 
e.ename,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 countdept
FROM
emp
GROUP BY
deptno) t2

ON
t1.deptno=t2.deptno;

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

SELECT job,COUNT(*) FROM emp GROUP BY job HAVING MIN(sal)>1500;

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

可以用子查询
 SELECT ename FROM emp WHERE 
deptno = 
(SELECT deptno FROM dept WHERE dname='SALES');

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

涉及到多表查询三表查询

select 
	e.ename '员工',d.dname,l.ename '领导',s.grade
from
	emp e
join
	dept d
on
	e.deptno = d.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);

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

思路：需要二张表 emp  dept
 select 
 a.empno,a.ename,b.dname
 from 
 emp a
 join
 dept b
 on
 a.deptno = b.deptno
 where
 JOB=
 (select JOB from emp where ename='SCOTT')
 and
 a.ename!='SCOTT';

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

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

 SELECT 
 ename,sal
 FROM
 emp
 WHERE
 sal IN (SELECT sal FROM emp WHERE deptno=30)
 AND
 deptno!=30;

 其实就是将"="号 改为"IN"。
  原因是"="号的后面取的可以使具体的值也可以是空值，不是判断关键词，
  但是"IN"是会与后面的数据值进行判断的，可以作为判断关键词。

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

我的思路：第一步 求出部门30工作的所有员工的薪金（就是求最高薪金）
SELECT
MAX(sal)
FROM
emp
WHERE
deptno=30;

第二步：员工姓名和薪金，在emp表 当a 部门名称：dept 当b表
select
a.ename,a.sal,b.dname
from
emp a
join
dept b
on 
a.deptno=b.deptno
where
sal>(SELECT
MAX(sal)
FROM
emp
WHERE
deptno=30);

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

没有员工的部门，部门人数是0

SELECT 
	d.deptno, COUNT(e.ename) ecount,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.deptno;

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

   timestampdiff(YEAR, hiredate, now())

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

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

我的思路：员工的姓名 和工资在emp 部门名称在dept 表连接
select 
        e.ename,e.sal,d.dname
from    
        emp e
join  
        dept d
on
        e.deptno=d.deptno;

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

我的思路：部门的详细信息 dept所有字段，和emp统计人数
select 
d.*,count(e.ename)as countname
from
emp e
right join
dept d
on
d.deptno=e.deptno
group by
d.deptno;

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

SELECT
job,MIN(sal)AS minsal
FROM
emp
GROUP BY
job;

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

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

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

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

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

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

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

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

select 
	d.deptno,d.dname,d.loc,count(e.ename),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.deptno,d.dname,d.loc;

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

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

2048 AI社区

有“AI”的1024 = 2048，欢迎大家加入2048 AI社区

更多推荐

学生网上请假系统信息管理系统源码-SpringBoot后端+Vue前端+MySQL【可直接运行】

2048 AI社区

AI与人共存的隐性成本与价值平衡：技术从业者必须看懂的全生命周期账本

AI 与人共存的成本本质，是技术跃迁期的必要投入而非纯粹消耗。某企业的实践印证了这一点：初期投入 300 万元构建人机协同体系，3 年后累计节省运营成本 1200 万元，数据资产增值超 2000 万元。对技术从业者而言，管控共存成本的核心在于：以架构设计降低技术成本，以流程优化降低人力成本，以风险管控降低隐性成本。当 AI 从 “工具” 进化为 “伙伴”，唯有平衡短期投入与长期价值，才能真正驾驭共