Oracle常用练习

--基于ORACLE自带的四张表：EMP/DEPT/SALGRADE/BONUS
--1．列出至少有一个员工的所有部门。 
SELECT D.DNAME "部门名称",COUNT(E.EMPNO) "员工数量" FROM DEPT D INNER JOIN EMP E ON D.DEPTNO=E.DEPTNO
  GROUP BY D.DNAME HAVING COUNT(E.EMPNO)>=1;
SELECT D.DEPTNO "部门编号",D.DNAME "部门名称",COUNT(E.EMPNO) "员工数量" FROM DEPT D INNER JOIN EMP E ON D.DEPTNO=E.DEPTNO
  GROUP BY D.DEPTNO,D.DNAME HAVING COUNT(E.EMPNO)>=1;
--2．列出薪金比“SMITH”多的所有员工。
SELECT E.ENAME,E.SAL FROM EMP E WHERE E.SAL>(SELECT E1.SAL FROM EMP E1 WHERE E1.ENAME='SMITH');


--3．列出所有员工的姓名及其直接上级的姓名。 
SELECT E.ENAME "员工姓名",M.ENAME "上级领导姓名" FROM EMP E INNER JOIN EMP M ON E.MGR=M.EMPNO;
  
--4．列出受雇日期早于其直接上级的所有员工。
SELECT E.ENAME "员工姓名",E.HIREDATE,M.ENAME "上级领导姓名",M.HIREDATE FROM EMP E 
  INNER JOIN EMP M ON E.MGR=M.EMPNO WHERE E.HIREDATE<M.HIREDATE;
 
--5．列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。


SELECT D.DNAME,E.ENAME FROM DEPT D LEFT OUTER JOIN EMP E ON D.DEPTNO=E.DEPTNO ORDER BY D.DEPTNO ;


--6．列出所有“CLERK”（办事员）的姓名及其部门名称。
SELECT E.ENAME,E.JOB,D.DNAME FROM EMP E LEFT OUTER JOIN DEPT D ON E.DEPTNO=D.DEPTNO 
  WHERE E.JOB='CLERK';




--7．列出最低薪金大于1500的各种工作。
SELECT E.JOB,MIN(E.SAL) FROM EMP E GROUP BY E.JOB HAVING MIN(E.SAL)>1500;




--8．列出在部门“SALES”（销售部）工作的员工的姓名，假定不知道销售部的部门编号。 
SELECT E.ENAME,D.DNAME,E.JOB FROM EMP E INNER JOIN DEPT D ON E.DEPTNO=D.DEPTNO 
  WHERE D.DNAME='SALES';


--9．列出薪金高于公司平均薪金的所有员工。 
SELECT E.ENAME,E.SAL FROM EMP E WHERE E.SAL>(SELECT AVG(E1.SAL) FROM EMP E1);


--10．列出与“SCOTT”从事相同工作的所有员工。 
SELECT E.ENAME FROM EMP E WHERE E.JOB=(SELECT E1.JOB FROM EMP E1 WHERE E1.ENAME='SCOTT')
  AND E.ENAME<>'SCOTT';


--11．列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。 
SELECT E3.ENAME,E3.SAL,E3.DEPTNO FROM EMP E3 
  WHERE E3.SAL IN (SELECT E.SAL FROM EMP E WHERE E.DEPTNO=30) AND E3.DEPTNO<>30;










--关于ANY和ALL的区别：
--SELECT TABLE_NAME FROM USER_TABLES;


DROP TABLE S74;
CREATE TABLE S74 ( 
    SNO NUMBER(2),
    SNAME CHAR(5),
    SGENDER CHAR(1),
    SHEIGHT NUMBER(3,2));
SELECT * FROM S74;
INSERT INTO S74 VALUES (1,'ZH','M',1.78);
INSERT INTO S74 VALUES (2,'ZA','F',1.76);
INSERT INTO S74 VALUES (3,'GG','M',1.68);
INSERT INTO S74 VALUES (4,'MM','F',1.61);
INSERT INTO S74 VALUES (5,'G2','M',1.73);
INSERT INTO S74 VALUES (6,'M2','F',1.60);






--查找班级里所有身高比女生都要高的男生
SELECT S.SNAME FROM S74 S 
  WHERE S.SHEIGHT>(SELECT MAX(SF.SHEIGHT) FROM S74 SF WHERE SF.SGENDER='F') AND S.SGENDER='M';
SELECT S.SNAME FROM S74 S
  WHERE S.SHEIGHT>ALL(SELECT SF.SHEIGHT FROM S74 SF WHERE SF.SGENDER='F') AND S.SGENDER='M';






--查找班级里身高比任意一个男生高的女生
SELECT S.SNAME FROM S74 S 
  WHERE S.SHEIGHT>(SELECT MIN(SM.SHEIGHT) FROM S74 SM WHERE SM.SGENDER='M') AND S.SGENDER='F';
SELECT S.SNAME FROM S74 S 
  WHERE S.SHEIGHT>ANY(SELECT SM.SHEIGHT FROM S74 SM WHERE SM.SGENDER='M') AND S.SGENDER='F';










--12．列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。 
SELECT E1.ENAME,E1.SAL,E1.DEPTNO FROM EMP E1 
  WHERE E1.SAL>ALL(SELECT E.SAL FROM EMP E WHERE E.DEPTNO=30);








--13．列出在每个部门工作的员工数量、平均工资和平均服务期限。 
SELECT D.DNAME "部门编号",COUNT(E.EMPNO) "员工数量",TRUNC(AVG(E.SAL),2) "平均工资",
  TRUNC(AVG(MONTHS_BETWEEN(SYSDATE,E.HIREDATE))/12,2) "平均服务期限" FROM DEPT D 
  LEFT OUTER JOIN EMP E ON D.DEPTNO=E.DEPTNO GROUP BY D.DNAME; 






--14．列出所有员工的姓名、部门名称和工资。 
SELECT E.ENAME "员工姓名",D.DNAME "部门名称",E.SAL*12+NVL(E.COMM,0) "年收入" FROM EMP E 
  LEFT OUTER JOIN DEPT D ON E.DEPTNO=D.DEPTNO ORDER BY "年收入" DESC;






--15．列出所有部门的详细信息和部门人数。 


SELECT D.DEPTNO,D.DNAME,D.LOC,COUNT(E.EMPNO) "员工数量" FROM DEPT D 
  LEFT OUTER JOIN EMP E ON D.DEPTNO=E.DEPTNO GROUP BY D.DEPTNO,D.DNAME,D.LOC;






--16．列出各种工作的最低工资。 
SELECT E.JOB,MIN(E.SAL) FROM EMP E GROUP BY E.JOB; 






--17．列出各个部门的MANAGER（经理）的最低薪金。
SELECT D.DEPTNO,D.DNAME,MIN(E.SAL) FROM EMP E RIGHT OUTER JOIN DEPT D ON D.DEPTNO=E.DEPTNO
 WHERE E.JOB='MANAGER' GROUP BY D.DEPTNO,D.DNAME;


SELECT E.ENAME,E.JOB FROM EMP E WHERE E.EMPNO IN (SELECT M.MGR FROM EMP M);






--查询所有员工信息以及对应的薪资等级。
SELECT E.ENAME,S.GRADE FROM EMP E INNER JOIN SALGRADE S ON E.SAL<=S.HISAL AND E.SAL>=S.LOSAL;




--列出各个部门的MANAGER（经理）的薪金以及对应的薪资等级
SELECT E.ENAME,E.JOB,S.GRADE FROM EMP E INNER JOIN SALGRADE S ON E.SAL<=S.HISAL AND E.SAL>=S.LOSAL
  WHERE E.JOB='MANAGER';


--18．列出所有员工的年工资,按年薪从低到高排序。
SELECT E.ENAME,E.SAL*12+NVL(E.COMM,0) "年薪" FROM EMP E ORDER BY "年薪"




--列出所有员工的年工资,按年薪从低到高排序，只列出前五条记录
SELECT ROWNUM,Y.* FROM (SELECT E.ENAME,E.SAL*12+NVL(E.COMM,0) "年薪" FROM EMP E ORDER BY "年薪") Y
 WHERE ROWNUM <=5;




--列出所有员工的年工资,按年薪从低到高排序，只列出第6条到第10条记录


SELECT ROWNUM,Y.* FROM (SELECT E.ENAME,E.SAL*12+NVL(E.COMM,0) "年薪" FROM EMP E ORDER BY "年薪") Y
 WHERE ROWNUM <=10
MINUS
SELECT ROWNUM,Y.* FROM (SELECT E.ENAME,E.SAL*12+NVL(E.COMM,0) "年薪" FROM EMP E ORDER BY "年薪") Y
 WHERE ROWNUM <=5;
 
SELECT * FROM (SELECT ROWNUM RN,Y.* FROM 
 (SELECT E.ENAME,E.SAL*12+NVL(E.COMM,0) "年薪" FROM EMP E ORDER BY "年薪") Y)Y1
 WHERE RN<=10 AND RN>5;
 




--19.列出经理人的名字.
SELECT E.ENAME,E.JOB FROM EMP E WHERE E.EMPNO IN (SELECT M.MGR FROM EMP M);


--20. 不用组函数，求薪水最大值
SELECT T.* FROM (SELECT * FROM EMP E ORDER BY E.SAL DESC) T WHERE ROWNUM<=1;


SELECT E.* FROM EMP E WHERE E.SAL>=ALL(SELECT E1.SAL FROM EMP E1);