--基于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);
--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);