几道sql的练习题

最新推荐文章于 2022-06-14 13:57:36 发布

zhanggh900921

最新推荐文章于 2022-06-14 13:57:36 发布

阅读量493

点赞数

CC 4.0 BY-SA版权

本文链接：https://blog.youkuaiyun.com/zhanggh900921/article/details/46388449

本文深入探讨了SQL查询中的高级技巧，包括如何高效地查找最高薪水、部门平均薪水等级、员工薪水平均等级等复杂查询。文章还介绍了如何通过SQL解决实际问题，如找出不是经理的最高薪水员工、特定薪水范围内的员工等。通过这些示例，读者可以学习到如何运用SQL进行数据挖掘和分析。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1,求部门中哪些人的薪水最高
select ename,sal from emp
join (select max(sal) max_sal,deptno from emp group by deptno) t
on(emp.sal = t.max_sal and emp.deptno = t.deptno);

2,求部门平均薪水的等级
select deptno,avg_sal,grade from
(select avg(sal) avg_sal,deptno from emp group by deptno) t
join salgrade s on(t.avg_sal between s.losal and s.hisal);

3,求部门中每个人薪水等级的平均值
select deptno,avg(grade) from
(select deptno,ename,sal,grade from salgrade s join emp on (emp.sal between s.lowsal and s.hisal));
group by deptno

4.雇员中哪些是经纪人
select ename from emp where empno in (select distinct mgr from emp);
distinct emp.mgr ???

5，不用组函数，求薪水的最高值（面试题）
select distinct sal from emp where sal not in
(select distinct p.sal from emp p join emp q on (p.sal<q.sal))

6,求平均薪水最高的部门的部门编号
select deptno,avg_sal from
(select avg(sal) avg_sal,deptno from emp group by depno)
where avg_sal =
select max(avg_sal) from
(select avg(sal) avg_sal,deptno from emp group by depno)

函数嵌套模式

select deptno,avg_sal from
(select avg(sal) avg_sal,deptno from emp group by depno)
where avg_sal =
(select max(avg(sal)) avg_sal from emp group by deptno)

7,求平均薪水最高的部门的部门名称
select dname from dept where deptno =
(
select deptno from
(select avg(sal) avg_sal,deptno from emp group by depno)
where avg_sal =
select max(avg_sal) from
(select avg(sal) avg_sal,deptno from emp group by depno)
)

8,求平均薪水的等级最低的部门的部门名称
select dname,deptno,grade,avg_sal from
(
select deptno,avg_sal,grade from
(select deptno,avg(sal) avg_sal from emp group by deptno) t
join salgrade s on(t.avg_sal between s.losal and s.hisal)
) t1
join dept on(t1.deptno = dept.deptno)

where t1.grade =
select min(grade) from
(
select deptno,avg_sal,grade from
(select deptno,avg(sal) avg_sal from emp group by deptno) t
join salgrade s on(t.avg_sal between s.losal and s.hisal)
)
)
8,比普通员工的最高薪水还要高的经理人的名称
select ename from emp where
empno in (select distinct mgr from emp where mgr is not null)
and sal >
(select max(sal) from emp where empno not in
(select distinct mgr from emp where mgr is not null));

9,求雇员薪水最高的前5名
select ename,sal from
(select ename,sal from emp order by sal desc)
where rownum<=5;

10，求雇员薪水最高的第6到第10名
select ename,sal from
(select ename,sal,rownum r from
(select ename,sal from emp order by sal desc))
where r>=6 and r<=10;