SQL练习题_科技

SQL练习题

创始人

2024-02-21 15:55:12

0次

新建数据表

首先建立测试数据库的表，新建数据库的sql语句如下，大家可以粘贴成一个sql文件，然后新建所有的表并插入所有的数据：

新建数据库sql文件：

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;

现在我们可以看到有3张表，分别是：emp(员工信息表）、dept(部门信息表）、salgrade(薪水等级表），三张表的各个构成如下：

emp(员工信息表）

dept(部门信息表）

salgrade(薪水等级表）

下面开始我们的练习题：

第一题：取得每个部门最高薪水的人员明称

思路：

答案：

SELECT emp.ENAME,t.* 
FROM emp 
JOIN (SELECT DEPTNO,max(sal)as maxsal FROM `emp`GROUP BY DEPTNO)t 
ON t.DEPTNO = emp.DEPTNO 
and t.maxsal = emp.SAL

第二题：哪些人薪水在部门的平均薪水之上

思路：

答案：

SELECT emp.ENAME,emp.SAL,t.* 
FROM emp 
JOIN (SELECT DEPTNO,avg(sal)as avgsal FROM `emp`GROUP BY DEPTNO)t 
on t.DEPTNO = emp.DEPTNO 
and emp.SAL>t.avgsal

第三题：取得部门中（所有人的）平均薪水的等级 / 平均的薪水等级

（1）平均薪水的等级

思路：

答案：

SELECT salgrade.GRADE,t.* 
FROM salgrade 
JOIN (SELECT DEPTNO,avg(sal)as avgsal FROM `emp`GROUP BY DEPTNO)t 
ont.avgsal BETWEEN salgrade.LOSAL and salgrade.HISAL

（2）平均的薪水等级

思路：

答案：

SELECT emp.DEPTNO,avg(salgrade.GRADE)as avgGRADE
FROM emp 
JOIN salgrade 
on emp.SAL BETWEEN salgrade.LOSAL and salgrade.HISAL 
GROUP BY emp.DEPTNO

第四题：不用组函数（Max），取得最高薪水

思路：

答案：

SELECT emp.ENAME,emp.SAL 
FROM emp 
ORDER BY emp.SAL DESC LIMIT 1

第五题：取得平均薪水最高的部门的部门名称

思路：

答案：

第一种：

SELECT dept.DNAME, max(t.avgsal)as maxavgsal 
FROM dept 
JOIN (SELECT DEPTNO,avg(sal)as avgsal FROM `emp`GROUP BY DEPTNO)t 
on dept.DEPTNO = t.DEPTNO

第二种：

SELECT dept.DNAME, max(t.avgsal)as maxavgsal 
FROM dept 
JOIN(SELECT DEPTNO,avg(sal)as avgsal FROM `emp`GROUP BY DEPTNO ORDER BY avgsal DESC LIMIT 1)t 
on dept.DEPTNO = t.DEPTNO

【重要】第六题：求平均薪水等级最低的部门的部门名称

思路：

求出最低平均值
SELECT avg(sal)as avgsal FROM `emp`GROUP BY DEPTNO ORDER BY avgsal ASC LIMIT 1求出最低平均值对应等级
SELECT salgrade.GRADE FROM salgrade JOIN (SELECT avg(sal)as avgsal FROM `emp`GROUP BY DEPTNO ORDER BY avgsal ASC LIMIT 1)r on r.avgsal BETWEEN salgrade.LOSAL and salgrade.HISAL求出dept和salgrade联合
select dept.DNAME,avg(sal)as avgsal FROM emp JOIN dept on emp.DEPTNO = dept.DEPTNO GROUP BY emp.DEPTNO

答案：

SELECT t.*,salgrade.GRADE 
FROM (select dept.DNAME,avg(sal)as avgsal FROM emp JOIN dept on emp.DEPTNO = dept.DEPTNO GROUP BY emp.DEPTNO)t join salgrade 
on t.avgsal BETWEEN salgrade.LOSAL and salgrade.HISAL 
WHERE GRADE = (SELECT salgrade.GRADE FROM salgrade JOIN (SELECT avg(sal)as avgsal FROM `emp`GROUP BY DEPTNO ORDER BY avgsal ASC LIMIT 1)r on r.avgsal BETWEEN salgrade.LOSAL and salgrade.HISAL)

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

思路：

答案：

SELECT ENAME 
FROM emp WHERE SAL>(SELECT max(SAL)as maxsal FROM emp WHERE EMPNO NOT in(SELECT DISTINCT MGR FROM emp WHERE MGR is NOT null)) and EMPNO in (SELECT DISTINCT MGR FROM emp WHERE MGR is NOT null)

第八题：取得薪水最高的前五名

SELECT ENAME,SAL FROM emp ORDER BY SAL DESC LIMIT 5

第九题：取得薪水最高的6-10名

SELECT ENAME,SAL FROM emp ORDER BY SAL DESC LIMIT 5,5

第十题：取得最后入职的5名员工

SELECT ENAME,HIREDATE FROM emp ORDER BY HIREDATE DESC LIMIT 5

第十一题：取得每个薪水有多少员工

思路：

答案：

SELECT salgrade.GRADE,count(*)
FROM emp 
JOIN salgrade 
WHERE emp.SAL BETWEEN salgrade.LOSAL AND salgrade.HISAL 
GROUP BY salgrade.GRADE

【重要】第十二题：列出所有员工及对应领导的姓名

思路：

答案：

select a.ENAME '员工',b.ENAME '领导' 
from emp a
left JOINemp b
on a.mgr = b.EMPNO

第十三题：列出受雇日期早于其上级领导的所有员工的编号、姓名、部门名称

思路：

答案：

select a.EMPNO '编号',a.ENAME '姓名',a.HIREDATE '雇佣时间',dept.DNAME '部门名称',b.ENAME '领导' ,b.HIREDATE
from emp a
left JOINemp b
on a.mgr = b.EMPNO
JOINdept
on a.DEPTNO = dept.DEPTNO
where a.HIREDATE

 
第十四题：列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门 
SELECT emp.*,dept.* FROM emp JOIN dept on emp.DEPTNO = dept.DEPTNO
 
第十五题：列出至少有5个员工的所有部门 
SELECT DEPTNO FROM emp GROUP BY DEPTNO HAVING count(*) >= 5 
第十六题：列出薪水比“SMITH”多的所有员工的信息 
 
思路： 
答案： 
select emp.* FROM emp where sal>(SELECT SAL FROM emp where ENAME = 'SMITH') 
第十七题：列出所有“CLERK”（办事员）的姓名及其部门名称、部门的人数 
 
思路： 
答案： 
SELECT emp.ENAME,emp.DEPTNO,dept.DNAME ,t.num as '部门人数'
FROM emp 
JOIN dept 
on emp.DEPTNO = dept.DEPTNO
left JOIN(SELECT count(*)as num,emp.DEPTNO from emp GROUP BY DEPTNO)t
ONemp.DEPTNO = t.DEPTNO
WHERE JOB = 'CLERK' 
第十八题：列出最低薪金大于1500的各种工作及从事此工作的全部雇员人数 
 
思路： 
答案：  
select count(*)as num,JOB 
FROM emp 
GROUP BY job 
HAVING JOB in (SELECT JOB from emp GROUP BY JOB HAVING min(SAL)>1500) 
第十九题：列出在部门“SALES”<销售部>工作的员工的姓名，假定不知道销售部的部门编号 
 
思路： 
答案： 
SELECT ENAME 
FROM emp 
WHERE DEPTNO = (SELECT DEPTNO FROM dept WHERE DNAME = "SALES") 
 【重要】第二十题：列出薪金高于公司平均薪金的所有员工，所在部门、上级领导、雇员的工资的等级 
 
思路： 
答案： 
SELECTemp.ENAME,dept.DNAME,l.ENAME,salgrade.GRADE
fromemp
JOINdept
ONdept.DEPTNO =emp.DEPTNO
LEFt JOINemp l
ONemp.MGR = l.EMPNO
JOINsalgrade
ONemp.SAL BETWEEN salgrade.LOSAL AND salgrade.HISAL
WHEREemp.SAL>(SELECT avg(SAL) from emp) 
 第二十一题：列出与“SCOTT”从事相同工作的所有员工及部门名称 
 
思路： 
答案： 
SELECT ENAME,JOB,dept.DNAME 
FROM emp 
JOIN dept 
ON emp.DEPTNO = dept.DEPTNO 
WHERE JOB = (SELECT JOB FROM emp WHERE ENAME = "SCOTT") 
第二十二题：列出薪金等于部门30中员工的薪金的其他员工的姓名和薪金 
 
思路： 
答案： 
SELECT ENAME,SAL FROM emp WHERE SAL in (SELECT DISTINCT SAL FROM emp WHERE DEPTNO = 30) AND DEPTNO < 30  
第二十三题：列出薪金高于部门30工作的所有员工的薪金的员工姓名和薪金，部门名称 
 
思路： 
答案： 
SELECT ENAME,SAL,dept.DNAME 
FROM emp 
JOIN dept 
on dept.DEPTNO = emp.DEPTNO 
WHERE SAL > (SELECT max(SAL) FROM emp WHERE DEPTNO = 30) 
第二十四题：列出在每个部门工作的员工数量，平均工资和平均服务期限 
 
思路： 
答案：  
SELECT dept.DEPTNO,count(ENAME),IFNULL(avg(SAL),0) ,avg(TIMESTAMPDIFF(YEAR,HIREDATE,NOW()))
FROM emp 
right JOIN dept 
on emp.DEPTNO = dept.DEPTNO 
GROUP BY dept.DEPTNO 
【注意】MySQL中对应日期计算的函数 
Mysql中对于日期的计算的函数 间隔类型可以是 
YEAR 年
QUARTER 季度
MONTH 月
WEEK 周
DAY 日
HOUR 小时
MINUTE 分钟
SECOND 秒SELECT TIMESTAMPDIFF(YEAR,HIREDATE,NOW()) FROM emp 
第二十五题：列出所有员工的姓名、部门名称、工资 
 
思路： 
答案： 
SELECT e.ENAME,d.DNAME,e.SAL
FROM emp e
JOIN dept d
on e.DEPTNO = d.DEPTNO 
 第二十六题：列出所有部门的详情信息和人数 
 
答案： 
SELECT dept.* ,count(ENAME) FROM emp right JOIN dept on emp.DEPTNO = dept.DEPTNO GROUP BY DEPTNO 
 第二十七题：列出各种工作的最低工资及从事此工作的雇员姓名 
 
思路： 
答案： 
SELECT emp.ENAME,t.* 
FROM emp 
JOIN (SELECT MIN(sal)as MINSAL,JOB from emp GROUP BY JOB)t 
on emp.JOB = t.JOB AND emp.SAL = t.MINSAL 
 第二十八题：列出各个部门的MANAGER（领导）的最低薪金 
 
思路： 
答案： 
SELECT min(SAL) ,t.MGR,t.DEPTNO 
FROM emp 
right JOIN (SELECT MGR,emp.DEPTNO FROM emp RIGHT JOIN dept ON emp.DEPTNO = dept.DEPTNO )t 
on t.DEPTNO = emp.DEPTNO  
WHERE JOB = "MANAGER" 
GROUP BY t.DEPTNO 
 第二十九题：列出所有员工的年工资，按年薪从低到高排序 
SELECT ENAME,(SAL+IFNULL(COMM,0))*12 as YEARSsal FROM emp ORDER BY YEARSsal ASC 
第三十题：求出员工领导薪水超过3000的员工名称与领导名称 
 
思路： 
答案： 
//第一种
SELECT emp.ENAME '员工名称', t.ENAME '领导名称' 
FROM emp 
JOIN (SELECT a.EMPNO,a.ENAME FROM emp a left JOIN emp b on a.MGR = b.EMPNO WHERE a.SAL>3000)t 
ON t.EMPNO = emp.MGR//第二种（自连接写法）
SELECT a.ENAME'员工名称',b.ENAME'领导名称' 
FROM emp a 
JOIN emp b 
ON a.MGR = b.EMPNO 
WHERE b.SAL > 3000 
第三十一题：求出部门名称中，带'S'字符的部门员工的工资合计、部门人数 
 
思路： 
答案： 
SELECT dept.*,count(emp.ENAME),ifnull(sum(emp.SAL),0)
FROM emp 
right join dept 
ON emp.DEPTNO = dept.DEPTNO 
WHEREdept.DNAME LIKE "%S%"
GROUP BY dept.DEPTNO 
第三十二题： 给任职日期超过30年的员工加薪10% 
UPDATE emp SET SAL = SAL*1.1 WHERE (TIMESTAMPDIFF(YEAR,HIREDATE,NOW()))>30


            
            
                
                            
            
                
                    上一篇：MyBatis框架一二级缓存含代码演示

                
                
                    下一篇：【Vue】Vuex-store                
            
            
            
                
                相关内容


    

    
    
        
        
            
            
                
                
                    
                    
                        
                        热门资讯
                        
                    
                    
                
                
            
            
            
            

                                
                
                    
                    
                        
                    
                    
                    
                    
                        监控摄像头接入GB28181平...
                        流程简介将监控摄像头的视频在网站和APP中直播，要解决的几个问题是：1&...
                    
                    
                
                                
                
                    
                    
                        
                    
                    
                    
                    
                        Windows10添加群晖磁盘...
                        在使用群晖NAS时，我们需要通过本地映射的方式把NAS映射成本地的一块磁盘使用。 通过...
                    
                    
                
                                
                
                    
                    
                        
                    
                    
                    
                    
                        protocol buffer...
                        目录   目录 什么是protocol buffer 1.protobuf 1.1安装  1.2使用...
                    
                    
                
                                
                
                    
                    
                        
                    
                    
                    
                    
                        Fluent中创建监测点
                        1 概述某些仿真问题，需要创建监测点，用于获取空间定点的数据࿰...
                    
                    
                
                                
                
                    
                    
                        
                    
                    
                    
                    
                        educoder数据结构与算法...
                                                                          ...
                    
                    
                
                                
                
                    
                    
                        
                    
                    
                    
                    
                        MySQL下载和安装（Wind...
                        前言：刚换了一台电脑，里面所有东西都需要重新配置，习惯了所...
                    
                    
                
                                
                
                    
                    
                        
                    
                    
                    
                    
                        MFC文件操作
                         MFC提供了一个文件操作的基类CFile，这个类提供了一个没有缓存的二进制格式的磁盘...
                    
                    
                
                                
                
                    
                    
                        
                    
                    
                    
                    
                        在Word、WPS中插入AxM...
                        引言 我最近需要写一些文章，在排版时发现AxMath插入的公式竟然会导致行间距异常&#...
                    
                    
                
                                
                
                    
                    
                        
                    
                    
                    
                    
                        有效的括号
                        一、题目  给定一个只包括 '('，')'，'{'，'}'...
                    
                    
                
                                
                
                    
                    
                        
                    
                    
                    
                    
                        【Ctfer训练计划】——(三...
                        作者名：Demo不是emo  主页面链接：主页传送门 创作初心ÿ...





    
    
        
        
            2025 ©
            发门网
            
            比特空间深知网行业商贸网一路旅游网        
        
        
        
                        
                资讯
            
                        
                生活
            
                        
                科技
            
                        
                sitemap