`
- 浏览:
7363 次
- 性别:
- 来自:
北京
-
常用SQL:
--求部门中哪些人的薪水最高
select ename,sal from emp join (select max(sal) maxsal ,deptno from emp group by deptno) t on (emp.deptno = t.deptno and emp.sal = t.maxsal);
--求部门平均薪水的等级
select deptno,avg_sal,grade from (select deptno,avg(sal) avg_sal from emp group by deptno) t join salgrade s on avg_sal between losal and hisal;
--求部门平均的薪水等级
select deptno,avg(grade) avg_grade from (select deptno,sal,grade from emp join salgrade s on sal between losal and hisal) t group by t.deptno;
--雇员中有哪些是经理人
select * from emp where empno in (select distinct mgr from emp);
--不准用组函数,求薪水的最大值(面试)
select * from (select sal from emp order by sal desc) t where rownum = 1;
select sal1 from (select e1.sal sal1,e2.sal sal2 from emp e1 left join emp e2 on e1.sal < e2.sal) where sal2 is null;
(上面这一句妙啊,两张表自连接,取第一张表里的薪水大于第二章表的薪水的值,则第一张表里最大的薪水就没有对应的信息)
select distinct sal from emp where sal not in (select distinct e1.sal from emp e1 join emp e2 on (e1.sal < e2.sal));
上面一句比我写的更简洁啊
--求平均薪水最高的部门的部门编号
select deptno,avgsal from (select avg(sal) avgsal, deptno from emp group by deptno) t where t.avgsal = (select max(avg(sal)) from emp group by deptno)
--求平均薪水最高的部门的部门名称
select dname from dept where deptno = (select deptno from (select avg(sal) avgsal, deptno from emp group by deptno) t where t.avgsal = (select max(avg(sal)) from emp group by deptno));
--求平均薪水的等级最低的部门的部门名称
select dname from dept where deptno =(select deptno from (select deptno,avg_sal,grade from (select deptno,avg(sal) avg_sal from emp group by deptno) t join salgrade s on avg_sal between losal and hisal)
d where grade = (select min(grade) from (select deptno,avg(sal) avg_sal from emp group by deptno) t join salgrade s on avg_sal between losal and hisal));
--求部门经理人中平均薪水最低的部门名称
select dname from dept where deptno = (select deptno from (select deptno,avg(sal) avgsal from (select * from emp where empno in (select mgr from emp)) t1 group by t1.deptno) where avgsal=(select min(avgsal) from ( select deptno,avg(sal) avgsal from (select * from emp where empno in (select mgr from emp)) t group by t.deptno )))
分享到:
Global site tag (gtag.js) - Google Analytics
相关推荐
马士兵老师spring框架学习笔记
马士兵oracle笔记,浅显易懂。
Spring学习笔记(马士兵spring视频笔记).docSpring学习笔记(马士兵spring视频笔记).docSpring学习笔记(马士兵spring视频笔记).docSpring学习笔记(马士兵spring视频笔记).doc
马士兵老师mybatis相关学习笔记
)马士兵java框架hibernate学习笔记
马士兵老师HashMap学习笔记
马士兵多线程训练营笔记
马士兵hibernate学习笔记.docx
2023年马士兵oracle笔记.doc
马士兵JAVA笔记(全).docx
马士兵spring学习笔记,方便快速学习spring
马士兵hibernate学习笔记(原版)
马士兵多线程训练营上课笔记
JAVA笔记(根据马士兵的java视频整理).pdf
一.java内存结构 2 二 垃圾收集算法: 3 三 JVM参数 4 四 JVM的垃圾回收集器 7 五 常用参数设置 7
马士兵Spring课堂笔记(超级详细版).pdf
jvm java虚拟机 调优 马士兵 笔记 让你对java虚拟机调优有初步的认识
马士兵Spring学习笔记.docx马士兵Spring学习笔记.docx马士兵Spring学习笔记.docx马士兵Spring学习笔记.docx马士兵Spring学习笔记.docx马士兵Spring学习笔记.docx马士兵Spring学习笔记.docx马士兵Spring学习笔记.docx