SQL练习题_办公生活

SQL练习题

创始人

2024-02-22 00:35:11

新建数据表

首先建立测试数据库的表，新建数据库的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


            
            
                
                            
            
                
                    上一篇：水浒传中的歇后语

                
                
                    下一篇：征集关于无偿献血的标语                
            
            
            
                
                相关内容


    

    
    
        
        
            
            
                
                
                    
                    
                        
                        热门资讯
                        
                    
                    
                
                
            
            
            
            

                                
                
                    
                    
                        
                    
                    
                    
                    
                        我喜欢你韩语怎么说 sikid...
                        
                    
                    
                
                                
                
                    
                    
                        
                    
                    
                    
                    
                        埃菲尔铁塔在哪 中国仿建埃菲尔...
                        2019年4月26日，广西南宁市，街头惊现一座巨型山寨版埃菲尔铁塔，高约20米，白色塔身，造型逼真，...
                    
                    
                
                                
                
                    
                    
                        
                    
                    
                    
                    
                        苗族的传统节日 贵州苗族节日有...
                        【岜沙苗族芦笙节】岜沙，苗语叫“分送”，距从江县城7.5公里，是世界上最崇拜树木并以树为神的枪手部落...
                    
                    
                
                                
                
                    
                    
                        
                    
                    
                    
                    
                        北京的名胜古迹 北京最著名的景...
                        北京从元代开始，逐渐走上帝国首都的道路，先是成为大辽朝五大首都之一的南京城，随着金灭辽，金代从海陵王...
                    
                    
                
                                
                
                    
                    
                        
                    
                    
                    
                    
                        应用未安装解决办法 平板应用未...
                        ---IT小技术，每天Get一个小技能！一、前言描述苹果IPad2居然不能安装怎么办？与此IPad不...
                    
                    
                
                                
                
                    
                    
                        
                    
                    
                    
                    
                        长白山自助游攻略 吉林长白山游...
                        昨天介绍了西坡的景点详细请看链接：一个人的旅行，据说能看到长白山天池全凭运气，您的运气如何？今日介绍...
                    
                    
                
                                
                
                    
                    
                        
                    
                    
                    
                    
                        脚上的穴位图 脚面经络图对应的...
                        人体穴位作用图解大全更清晰直观的标注了各个人体穴位的作用，包括头部穴位图、胸部穴位图、背部穴位图、胳...
                    
                    
                
                                
                
                    
                    
                        
                    
                    
                    
                    
                        世界上最漂亮的人 世界上最漂亮...
                        此前在某网上，选出了全球265万颜值姣好的女性。从这些数量庞大的女性群体中，人们投票选出了心目中最美...
                    
                    
                
                                
                
                    
                    
                        
                    
                    
                    
                    
                        demo什么意思 demo版本...
                        618快到了，各位的小金库大概也在准备开闸放水了吧。没有小金库的，也该向老婆撒娇卖萌服个软了，一切只...
                    
                    
                
                                
                
                    
                    
                        
                    
                    
                    
                    
                        猫咪吃了塑料袋怎么办 猫咪误食...
                        你知道吗？塑料袋放久了会长猫哦！要说猫咪对塑料袋的喜爱程度完完全全可以媲美纸箱家里只要一有塑料袋的响...
                    
                    
                
                                
                
                    
                    
                        
                    
                    
                    
                    
                        北京的名胜古迹 北京最著名的景...
                        北京从元代开始，逐渐走上帝国首都的道路，先是成为大辽朝五大首都之一的南京城，随着金灭辽，金代从海陵王...
                    
                    
                
                                
                
                    
                    
                        
                    
                    
                    
                    
                        我喜欢你韩语怎么说 sikid...
                        
                    
                    
                
                                
                
                    
                    
                        
                    
                    
                    
                    
                        埃菲尔铁塔在哪 中国仿建埃菲尔...
                        2019年4月26日，广西南宁市，街头惊现一座巨型山寨版埃菲尔铁塔，高约20米，白色塔身，造型逼真，...
                    
                    
                
                                
                
                    
                    
                        
                    
                    
                    
                    
                        苗族的传统节日 贵州苗族节日有...
                        【岜沙苗族芦笙节】岜沙，苗语叫“分送”，距从江县城7.5公里，是世界上最崇拜树木并以树为神的枪手部落...
                    
                    
                
                                
                
                    
                    
                        
                    
                    
                    
                    
                        应用未安装解决办法 平板应用未...
                        ---IT小技术，每天Get一个小技能！一、前言描述苹果IPad2居然不能安装怎么办？与此IPad不...





    
    
        
        
            2026 ©
            办公百科网才丁消费网零点化工网汇川网学习东方游戏办公分享八零互联网 开创问答网 办公应用网明星一百商务投诉网
比特空间历史体育财丁元素汽车元素科技太和茶叶网野仙生活网
港易生活网布谷家居网办公家居网办公旅游网家居问答网彩盯图库网PICSOK才丁消费网        
        
        
        
                        
                办公常识
            
                        
                办公百科
            
                        
                办公生活