Oracle_SQL练习_04

--1、列出至少有一个雇员的所有部门
/*SELECT DISTINCT t1.deptno, t2.dname
FROM   emp t1, dept t2
WHERE  t1.deptno = t2.deptno
ORDER  BY t1.deptno*/
--2、列出薪金比"SMITH"多的所有雇员
/*SELECT empno, ename, nvl(sal, 0) AS salary
FROM   emp
WHERE  nvl(sal, 0) > (SELECT nvl(sal, 0) FROM emp WHERE ename = 'SMITH')
*/
--3、列出所有雇员的姓名及其直接上级的姓名
/*SELECT t1.ename, t2.ename
FROM   (SELECT ename, mgr FROM emp) t1, (SELECT ename, empno FROM emp) t2
WHERE  t1.mgr = t2.empno*/
--4、列出入职日期早于其直接上级的所有雇员
/*SELECT t1.ename, t1.hiredate, t2.ename, t2.hiredate
FROM   (SELECT ename, mgr, hiredate FROM emp) t1,
       (SELECT ename, empno, hiredate FROM emp) t2
WHERE  t1.mgr = t2.empno
       AND t1.hiredate < t2.hiredate*/
--5、列出部门名称和这些部门的雇员,同时列出那些没有雇员的部门
/*SELECT dept.dname, emp.ename
FROM   emp, dept
WHERE  emp.deptno(+) = dept.deptno
*/
/*SELECT *
FROM   emp
RIGHT  JOIN dept
ON     emp.deptno = dept.deptno*/
--6、列出所有"CLERK"（办事员）的姓名及其部门名称
/*SELECT emp.ename, dept.dname
FROM   emp, dept
WHERE  emp.deptno = dept.deptno
       AND emp.job = 'CLERK'*/
--7、列出各种工作类别的最低薪金，显示最低薪金大于1500的记录
/*SELECT t2.*
FROM   (SELECT deptno, MIN(nvl(sal, 0)) AS minsal
        FROM   emp
        GROUP  BY deptno
        ORDER  BY deptno) t2
WHERE  t2.minsal > 1500*/
--8、列出从事"SALES"（销售）工作的雇员的姓名，假定不知道销售部的部门编号
/*SELECT *
FROM   emp
WHERE  job = 'SALESMAN'*/
--9、列出薪金高于公司平均水平的所有雇员
/*SELECT *
FROM   emp
WHERE  nvl(sal, 0) > (SELECT AVG(nvl(sal, 0)) FROM emp)
ORDER  BY deptno*/
--10、列出与"SCOTT"从事相同工作的所有雇员
/*SELECT *
FROM   emp
WHERE  job = (SELECT JOB FROM emp WHERE ename = 'SCOTT')
       AND ename <> 'SCOTT'*/
--11、列出某些雇员的姓名和薪金，条件是他们的薪金等于部门30中任何一个雇员的薪金
/*SELECT *
FROM   emp
WHERE  nvl(sal, 0) IN (SELECT nvl(sal, 0) FROM emp WHERE deptno = 30)
       AND deptno IN (10, 20)
*/
--12、列出某些雇员的姓名和薪金，条件是他们的薪金高于部门30中所有雇员的薪金
/*SELECT *
FROM   emp
WHERE  nvl(sal, 0) > ANY (SELECT nvl(sal, 0) FROM emp WHERE deptno = 30)
       AND deptno IN (10, 20)
*/
--13、列出每个部门的信息以及该部门中雇员的数量
/*SELECT dept.deptno, dept.dname, dept.loc, t1.cnt
FROM   dept,
       (SELECT deptno, COUNT(*) cnt FROM emp GROUP BY deptno ORDER BY deptno) t1
WHERE  dept.deptno = t1.deptno
*/
--14、列出所有雇员的雇员名称、部门名称和薪金
/*SELECT emp.ename AS 雇员名称,
       dept.dname AS 部门名称,
       nvl(emp.sal, 0) AS 薪金
FROM   emp, dept
WHERE  emp.deptno = dept.deptno*/
--15、列出从事同一种工作但属于不同部门的雇员的不同组合 ??
/*SELECT e1.ename, e2.ename, e1.job
FROM   emp e1, emp e2
WHERE  e1.job = e2.job
       AND e1.deptno <> e2.deptno
ORDER  BY job*/
--16、列出分配有雇员数量的所有部门的详细信息，即使是分配有0个雇员
/*SELECT *
FROM   emp, dept
WHERE  emp.deptno(+) = dept.deptno*/
--17、列出各种类别工作的最低工资
/*SELECT job 工作类别, MIN(nvl(sal, 0) + nvl(comm, 0)) 最低工资
FROM   emp
GROUP  BY job*/
--18、列出各个部门的MANAGER（经理）的最低薪金
/*SELECT deptno, MIN(nvl(sal, 0)) 最低薪金
FROM   emp
WHERE  job = 'MANAGER'
GROUP  BY deptno*/
--19、列出按年薪排序的所有雇员的年薪
/*SELECT emp.*, (nvl(sal, 0) + nvl(comm, 0)) * 12 AS 年薪
FROM   emp
ORDER  BY (nvl(sal, 0) + nvl(comm, 0)) DESC*/
--20、列出薪金水平处于第四位的雇员
/*SELECT t2.*
FROM   (SELECT t1.*, rownum grade
        FROM   (SELECT * FROM emp ORDER BY nvl(sal, 0) DESC) t1) t2
WHERE  t2.grade = 4*/
---
/*SELECT t1.*, rownum grade
FROM   (SELECT * FROM emp ORDER BY nvl(sal, 0) DESC) t1
WHERE  rownum <= 4
MINUS
SELECT t1.*, rownum grade
FROM   (SELECT * FROM emp ORDER BY nvl(sal, 0) DESC) t1
WHERE  rownum <= 3*/
--21、查出工资的前4名(工资 = sal + comm)
/*SELECT t1.*, rownum grade
FROM   (SELECT * FROM emp ORDER BY nvl(sal, 0) + nvl(comm, 0) DESC) t1
WHERE  rownum <= 4
*/
--22、查出2~4名(工资 = sal + comm)?
/*SELECT t1.*
FROM   (SELECT e.*,
               row_number() over(ORDER BY nvl(sal, 0) + nvl(comm, 0) DESC) grade
        FROM   emp e) t1
WHERE  t1.grade BETWEEN 2 AND 4*/
--23、查出员工工资在3~6名的员工每人扣100元 (工资 = sal + comm)?
--24、查出员工工资在3~6名的员工每人扣100元后,姓名,工资,部门,等级 (工资 = sal + comm)?