SQL经典训练试题

最新推荐文章于 2022-07-07 23:15:26 发布
月半女孩的代码之路
最新推荐文章于 2022-07-07 23:15:26 发布
阅读量193
点赞数
分类专栏：数据库
本文链接：https://blog.csdn.net/weixin_45941889/article/details/106868481
版权
数据库专栏收录该内容
4 篇文章 0 订阅
订阅专栏
本文提供了来自知名资源的SQL经典训练试题，旨在帮助读者提升SQL查询技能，涵盖基础到高级的各种题型。
摘要由CSDN通过智能技术生成
#1.取得每个部门最高薪水的人员名称
select DNAME,ENAME,MSAL from bjpowernode.emp as m 
join 
	(SELECT e.DEPTNO, d.DNAME, max(SAL) as MSAL FROM bjpowernode.dept as d
	join bjpowernode.emp as e on d.DEPTNO=e.DEPTNO
	group by e.DEPTNO) as n
on m.DEPTNO=n.DEPTNO
where SAL = MSAL
order by MSAL desc;

select DNAME,ENAME,MSAL from bJpowernode.dept as d
join
(select E.DEPTNO,ENAME,MSAL from bjpowernode.emp as e
inner join 
			(SELECT DEPTNO,max(SAL) as MSAL FROM bjpowernode.emp
			group by DEPTNO) as t
on (e.DEPTNO =t.DEPTNO and e.SAL =t.MSAL)) as m
on d.DEPTNO =m.DEPTNO
ORDER BY MSAL desc;


#2.哪些人的薪水在部门的平均薪水之上
select DNAME,ENAME,SAL,ASAL from bjpowernode.emp as m 
join 
	(SELECT e.DEPTNO, d.DNAME,round( AVG(SAL),2) as ASAL FROM bjpowernode.dept as d
	join bjpowernode.emp as e on d.DEPTNO=e.DEPTNO
	group by e.DEPTNO) as n
on m.DEPTNO=n.DEPTNO
where SAL > ASAL
order by ASAL desc;

select E.DEPTNO,ENAME,SAL,ASAL from bjpowernode.emp as e
inner join 
		(select DEPTNO,round(avg(SAL),2) as ASAL from bjpowernode.emp
		group by DEPTNO) as t
on (e.DEPTNO = t.DEPTNO and SAL >ASAL)
order by e.DEPTNO;


#3.取得部门中（所有人的）平均薪水等级
select t.DEPTNO,T.ASAL,S.* from bjpowernode.salgrade as s
inner join 
			(select DEPTNO,round(avg(SAL),2) as ASAL from bjpowernode.emp
			group by DEPTNO) as t
on (t.ASAL between s.LOSAL and s.HISAL)
order by DEPTNO;


#4.不准用组函数，取得最高薪水
select SAL from bjpowernode.emp
order by SAL desc
limit 1;

select SAL from bjpowernode.emp
order by SAL desc
limit 0, 1;

select SAL from bjpowernode.emp 
where SAL not in (
					select distinct a.SAL from bjpowernode.emp as a 
					join bjpowernode.emp as b
					where (a.SAL < b.SAL));
     
                    
#5取得平均薪水最高的部门的部门编号
select DEPTNO,round(avg(SAL),2) as MSAL from bjpowernode.emp
group by DEPTNO
order by MSAL desc
limit 1;
#当有几个部门平均薪水并列第一时，这样不适用

select DEPTNO,round(avg(SAL),2) as MSAL from bjpowernode.emp
group by DEPTNO
having MSAL = (select max(MSAL)
		from (select DEPTNO,round(avg(SAL),2) as MSAL from bjpowernode.emp
			  group by DEPTNO
              order by MSAL desc) as t);
              

#6.取得平均薪水最高的部门的部门名称
select d.DNAME,MSAL from bjpowernode.dept as d
join   (select DEPTNO,round(avg(SAL),2) as MSAL from bjpowernode.emp
		group by DEPTNO
		having MSAL = (select max(MSAL)
				from (select DEPTNO,round(avg(SAL),2) as MSAL from bjpowernode.emp
					  group by DEPTNO
					  order by MSAL desc) as t)) as m
on d.DEPTNO =m.DEPTNO;

select d.DNAME,round(avg(SAL),2) AS ASAL 
from bjpowernode.emp as e join bjpowernode.dept as d on e.DEPTNO=d.DEPTNO
group by d.DNAME
having ASAL = (select max(MSAL)
			 from (select DEPTNO,round(avg(SAL),2) as MSAL from bjpowernode.emp
				   group by DEPTNO
				   order by MSAL desc) as t);


#7.取薪水最高的前五名员工
select EMPNO,ENAME,SAL,SRANK
from (SELECT *,rank() over (order by SAL desc) as SRANK  FROM bjpowernode.emp) as t
where SRANK<6;
#这个比limit好，因为可能出现相同工资的员工


#8.取得薪水最高的第六名到第十名员工
select EMPNO,ENAME,SAL,SRANK
from (SELECT *,dense_rank() over (order by SAL desc) as SRANK  FROM bjpowernode.emp) as t
where SRANK between 6 and 10;

select EMPNO,ENAME,SAL,SRANK
from (SELECT *,rank() over (order by SAL desc) as SRANK  FROM bjpowernode.emp) as t
where SRANK between 6 and 10;


#9取得比普通员工(员工代码没有在mgr上出现)的最高薪水还要高的经理人姓名
select * from bjpowernode.emp 
where SAL > (select max(SAL) from bjpowernode.emp
             where EMPNO not in (select distinct MGR from bjpowernode.emp
                                 where MGR is not null));
                    
                    
#10.求平均薪水的的等级最低(或最高)的部门名称
select DNAME,ASAL,GRADE
from   (select t.DNAME,t.ASAL,s.GRADE,rank() over (order by s.GRADE desc) as srank 
        from bjpowernode.salgrade as s 
		join (select d.DNAME,round(avg(e.SAL),3) as ASAL 
			  from bjpowernode.emp as e
			  inner join bjpowernode.dept as d on d.DEPTNO= e.DEPTNO
			  group by d.DNAME) as t
		on (t.ASAL between s.LOSAL and s.HISAL)) as m
where srank = 1;

select DNAME,ASAL,GRADE
from (select t.DNAME,t.ASAL,s.GRADE
	  from bjpowernode.salgrade as s 
	  join (select d.DNAME,round(avg(e.SAL),3) as ASAL 
			from bjpowernode.emp as e
			inner join bjpowernode.dept as d on d.DEPTNO= e.DEPTNO
			group by d.DNAME) as t
	  on (t.ASAL between s.LOSAL and s.HISAL)) as m
 where GRADE = (select min(t.GRADE) 
				from (select t.DNAME,t.ASAL,s.GRADE
					  from bjpowernode.salgrade as s 
					  join (select d.DNAME,round(avg(e.SAL),3) as ASAL 
							from bjpowernode.emp as e
							inner join bjpowernode.dept as d on d.DEPTNO= e.DEPTNO
							group by d.DNAME) as t
					  on (t.ASAL between s.LOSAL and s.HISAL)) as t);
						 
						   
#11取得最后(也就是最大的日期)入职的4名员工
select ENAME,JOB,HIREDATE,DEPTNO 
from (select * , rank() over (order by HIREDATE desc) as drank from bjpowernode.emp) as t
where drank <= 4;


#12取得每个薪水等级有多少员工
select s.GRADE,count(*) as amount
from bjpowernode.emp  as  e
join  bjpowernode.salgrade as s 
on e.SAL between s.LOSAL and s.HISAL
group by s.GRADE
order by s.GRADE;


#13列出所有员工及领导的名字
select a.ENAME as clerk ,a.JOB ,b.ENAME as mrg,B.JOB
from bjpowernode.emp as a
left join bjpowernode.emp as b
on a.MGR = b.EMPNO;


#14列出受雇日期早于其直接上级的所有员工编号、姓名、部门名称
select t.EMPNO,t.ENAME,d.DNAME
from bjpowernode.dept as d 
join   (select a.EMPNO,a.ENAME,a.DEPTNO 
        from bjpowernode.emp as a
		join bjpowernode.emp as b
		on a.MGR = b.EMPNO
		where a.HIREDATE < b.HIREDATE) as t
on d.DEPTNO = t.DEPTNO;


#15列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门
select d.*,e.EMPNO,e.ENAME,e.HIREDATE,e.JOB,e.SAL
from bjpowernode.dept as d 
left join bjpowernode.emp as e
on d.DEPTNO = e.DEPTNO
order by d.DEPTNO;


#16列出至少有5个员工的所有部门
select d.DNAME,count(*)
from bjpowernode.dept as d 
join bjpowernode.emp as e
on d.DEPTNO = e.DEPTNO
group by d.DNAME
having count(*) >=5


#17列出薪水比SMITH多的所有员工
select EMPNO,ENAME,SAL 
from  bjpowernode.emp
where SAL > (select SAL from bjpowernode.emp
             WHERE ENAME = "SMITH") ;
             
             
 #18列出所有"CLERK"的姓名及其部门名称、部门人数
select * 
from (select d.DNAME,count(*) as "amount" from bjpowernode.emp as e
		 join bjpowernode.dept as d on e.DEPTNO =d.DEPTNO
		 group by d.DNAME) as t1
join (select e.ENAME,e.JOB, e.DEPTNO,d.DNAME from bjpowernode.emp as e
	  join bjpowernode.dept as d on e.DEPTNO =d.DEPTNO
	  where JOB = "CLERK") as t2
on t1.DNAME = t2.DNAME;

 
 #19列出最低薪水大于1500的各种工作及从事此工作的全部雇员人数
select JOB,min(SAL),count(*) from bjpowernode.emp
group by JOB
having min(SAL) > 1500;


#20列出在部门SALES销售部工作的员工的姓名
select  d.DNAME,e.EMPNO,e.ENAME from bjpowernode.emp as e
join  bjpowernode.dept as d on e.DEPTNO =d.DEPTNO
where d.DNAME = "SALES";

select* from bjpowernode.emp
where DEPTNO = (select DEPTNO from bjpowernode.dept
                where DNAME = "SALES");


#21列出薪水高于公司平均薪水的所有员工、所在部门、上级领导、雇员工资等级
select t.*,s.GRADE from bjpowernode.salgrade as s
join (select t.*,m.ENAME as "leader"
		from bjpowernode.emp as m 
		right join (select e.ENAME,d.DNAME,e.MGR,e.SAL
					from bjpowernode.emp  as e
					join bjpowernode.dept as d on e.DEPTNO = d.DEPTNO
					where SAL > (select round(avg(SAL),3) as ASALA
								 from bjpowernode.emp)) as t
		on m.EMPNO = t.MGR) as t
on t.SAL between s.LOSAL and s.HISAL;


#22列出与"SCOTT"从事相同工作的所有员工及部门名称
select e.ENAME,e.JOB,d.DNAME
from bjpowernode.emp as e
join bjpowernode.dept as d on e.DEPTNO = d.DEPTNO
where JOB = (select JOB from bjpowernode.emp where ENAME = "SCOTT");


#23列出薪金等于部门30中员工的薪金的其他员工的姓名和薪金
select ENAME ,SAL from bjpowernode.emp 
where DEPTNO <> 30
and SAL in (select SAL from bjpowernode.emp where DEPTNO = 30);


#24列出薪金高于在部门30工作的所有员工的薪金的其他员工的姓名、薪金、部门
 select ENAME ,SAL,d.DNAME from bjpowernode.emp as e
 join bjpowernode.dept as d on e.DEPTNO = d.DEPTNO
 where e.DEPTNO <> 30
 and SAL > (select max(SAL) from bjpowernode.emp where DEPTNO = 30);


#25列出在每个部门工作的员工数量、平均工资、平均服务期限
#count(*)会查询包含null的数据
select d.DNAME,count(e.ENAME) as eamount,ifnull(round(avg(SAL),2),0) as ASAL,ifnull(avg(datediff(curdate(),HIREDATE) /365),0) as ASERVICE
from  bjpowernode.emp as e
right join bjpowernode.dept as d on e.DEPTNO =d.DEPTNO
group by d.DNAME;

#计算每个员工的服务年限2种方法
select datediff(curdate(),HIREDATE) /365 from bjpowernode.emp;
select (to_days(now())-to_days(HIREDATE))/365 from bjpowernode.emp;


#26列出所有部门的详细信息和人数
select e.*,t.etotal from bjpowernode.dept as e
join (select d.DEPTNO,count(e.ENAME) as etotal from bjpowernode.emp as e 
      right join bjpowernode.dept as d on e.DEPTNO = d.DEPTNO
      group by d.DEPTNO) as t
on e.DEPTNO =t.DEPTNO;

select d.DEPTNO,d.DNAME,d.LOC,count(e.ENAME)
from bjpowernode.emp as e
right join bjpowernode.dept as d on e.DEPTNO = d.DEPTNO
group by d.DEPTNO,d.DNAME,d.LOC
order by d.DEPTNO;


#27列出各种工作的最低工资以及从事此工作的雇员姓名
select e.ENAME,t.* from bjpowernode.emp as e
join (select JOB,min(SAL) as minsal from bjpowernode.emp
      group by JOB) as t
on e.JOB = t.JOB and  e.SAL = t.minsal
order by t.minsal;


#28列出各个部门MANAGER的最低薪金
select d.DNAME,min(m.SAL) as minsal from bjpowernode.emp as e
join bjpowernode.emp as m on e.MGR = m.EMPNO
join bjpowernode.dept as d on m.DEPTNO = d.DEPTNO
group by d.DNAME;

select d.DEPTNO,d.DNAME,min(e.SAL) as minsal from bjpowernode.emp as e
JOIN bjpowernode.dept as d on e.DEPTNO = d.DEPTNO
where JOB ="MANAGER"
group by d.DEPTNO,d.DNAME ;


#29列出所有员工的年工资(需要加上津贴)，按年薪从低到高排序
select *,(SAL+ifnull(COMM,0))*12 as etotal from bjpowernode.emp
order by etotal;


#30求出员工领导的薪水超过3000的员工名称和领导名称
select e.ENAME "员工名称",e.SAL "员工工资",m.ENAME "领导名称",m.SAL "领导工资" from bjpowernode.emp as e
join bjpowernode.emp as m on e.MGR = m.EMPNO
where m.SAL > 3000;


#31求部门名称中带s字符的部门员工的工资合计、部门人数
select  t.DEPTNO,t.DNAME,ifnull(sum(e.SAL),0),count(e.ENAME) from bjpowernode.emp  as e
right join (select * from bjpowernode.dept
			where DNAME like "%s%") as t 
on e.DEPTNO =t.DEPTNO
group by t.DEPTNO,t.DNAME;

select  t.DEPTNO,t.DNAME,ifnull(sum(e.SAL),0),count(e.ENAME) from bjpowernode.emp  as e
right join bjpowernode.dept as t 
on e.DEPTNO =t.DEPTNO
where DNAME like "%s%"
group by t.DEPTNO,t.DNAME;



#32给任职日期超过35年的员工加薪10%
select ENAME,datediff(Curdate(),HIREDATE)/365 as service,SAL*1.1 as NSAL 
from bjpowernode.emp
where datediff(Curdate(),HIREDATE)/365 >35;

DROP TABLE IF exists EMP_BAK;
CREATE TABLE EMP_BAK AS SELECT* FROM bjpowernode.emp;
select * from EMP_BAK;
UPDATE EMP_BAK  SET SAL = SAL*1.1
where datediff(now(),HIREDATE)/365 >35
题目来源：SQL经典训练试题
月半女孩的代码之路
关注
0
点赞
踩
0

收藏

觉得还不错? 一键收藏
1
评论
SQL经典训练试题

#1.取得每个部门最高薪水的人员名称select DNAME,ENAME,MSAL from bjpowernode.emp as m join (SELECT e.DEPTNO, d.DNAME, max(SAL) as MSAL FROM bjpowernode.dept as d join bjpowernode.emp as e on d.DEPTNO=e.DEPTNO group by e.DEPTNO) as non m.DEPTNO=n.DEPTNOwhere SAL = MS
复制链接

扫一扫