【Oracle练习】⑥第7章 子查询
来源:互联网 发布:mac 破解软件大全 编辑:程序博客网 时间:2024/05/16 16:06
第七章
1. 列出至少有一个雇员的所有部门
SELECT DISTINCT dname FROM dept WHERE deptnoIN (SELECT DISTINCT deptno FROM emp)
2. 列出薪金比"SMITH"多的所有雇员
SELECT ename,sal FROM emp WHERE sal >(SELECT sal FROM emp WHERE ename = ‘SMITH’)
3. 列出入职日期早于其直接上级的所有雇员SELECT ename FROM emp e WHERE hiredate <(SELECT hiredate FROM emp WHERE empno= e.mgr)
4. 找员工姓名和直接上级的名字SELECT EMP.ENAME AS 员工姓名,MGR.ENAME AS 经理姓名
FROM EMP,(SELECT ENAME,EMPNO FROM EMP ) MGR
WHERE EMP.MGR = MGR.EMPNO(+)
SELECT DNAME 部门名称 ,cou 人数
FROM DEPT D ,(SELECT DEPTNO,COUNT(EMPNO) COU FROM EMP GROUP BY DEPTNO) E
WHERE D.DEPTNO = E.DEPTNO
SELECT *
FROM EMP
WHERE (DEPTNO,SAL) IN (SELECT DEPTNO,MAX(SAL) FROM EMP GROUP BY DEPTNO)
SELECT ENAME,SAL,DEPTNO
FROM EMP E
WHERE (SELECT COUNT(EMPNO) FROM EMP WHERE DEPTNO = E.DEPTNO AND SAL >E.SAL ) <=1
SELECT last_name, salary FROM employees WHERE department_id=(SELECT department_id FROM employees WHERE employee_id=200 )
9. 显示出和姓名中包含"W"的员工相同部门的员工姓名
FROM employees WHERE department_id IN( SELECT department_id FROM employees WHERE last_name LIKE '%a%' )
10. 显示出工资大于平均工资的员工姓名,工资
SELECT last_name, salary FROM employees WHERE salary>(SELECT AVG(salary) FROM employees )
11. 显示出工资大于本部门平均工资的员工姓名,工资
SELECT last_name, salary FROM employees WHERE salary> ANY(SELECT AVG(salary) FROM employees GROUP BY department_id )
12. 显示员工"KING"所管理的员工姓名
SELECT first_name||last_name NAME, salary, department_id FROM employees WHERE manager_id IN(SELECT employee_id FROM employees WHERE last_name = 'King' )
13. 显示每位经理管理员工的最低工资,及最低工资者的姓名
SELECT emp.employee_id, emp.salary, emp.department_id, emp.manager_id FROM employees emp WHERE NOT EXISTS(SELECT man.salary FROM employees man WHERE man.manager_id=emp.manager_id AND man.salary(SELECT hire_date FROM employees WHERE salary=(SELECT MAX(salary) FROM employees))
14. 显示比工资最高的员工参加工作时间晚的员工姓名,参加工作时间
15. 显示出平均工资最高的的部门平均工资及部门名称
SELECT ROWNUM, department_name, avg_sal FROM( SELECT department_name, AVG(salary) AS avg_sal FROM employees e, departments d WHERE e.department_id=d.department_id GROUP BY department_name ORDER BY avg_sal DESC ) WHERE ROWNUM=1 SELECT avg_sal, department_name FROM ( SELECT AVG(salary) AS avg_sal, department_name FROM departments dep JOIN employees emp ON dep.department_id = emp.department_id GROUP BY department_name ) WHERE avg_sal = ( SELECT MAX(avg_sal) FROM ( SELECT AVG(salary) AS avg_sal, department_name FROM departments dep JOIN employees emp ON dep.department_id = emp.department_id GROUP BY department_name ) )
- 【Oracle练习】⑥第7章 子查询
- oracle练习之 子查询
- 第7章 子查询
- 【Oracle练习】④第5章 多表查询
- 06-Oracle学习_练习-子查询 和 连接查询
- 第8章 子查询
- 【oracle学习】3.子查询和集合运算练习
- (05)oracle新人笔记------子查询练习(重点)
- 子查询练习
- 【Oracle练习】①第2章 编写简单的查询语句
- 【Oracle入门系列】第05章_多表连接与子查询
- oracle查询练习
- oracle查询练习
- ORACLE 查询练习
- ORACLE 查询练习
- Oracle数据库查询练习
- Oracle查询练习1
- oracle查询练习2
- hdoj 1213 How Many Tables
- Unity3D Android的配置文件
- 【Oracle练习】⑤第6章 分组函数
- db2codepage设置
- unity3D的数据库连接操作
- 【Oracle练习】⑥第7章 子查询
- 概念:二元查找树
- mac 下终端访问文件出现“Permission Denied”解决方案
- MKMapView地理编码(即根据地址来转换成坐标)
- poj2528--Mayor's posters(线段树+离散化)
- zoj2297Survival (状态压缩DP)
- POJ 1088 滑雪 记忆化优化题解
- 如何在meidastreamer中添加h264编码器
- 【练手小程序——01】迷宫问题