Oracle学习笔记+作业

表数据来自 oracle经典表 emp,dept,salgrade

SELECT * FROM CARGOINFO;
–（1） 总航运次数
SELECT COUNT(DISTINCT(WATERID)) TOTAL FROM CARGOINFO;

–（2） 各承运公司承运次数排行
SELECT SHIPPERID,COUNT(SHIPPERID) FROM (SELECT DISTINCT(WATERID),SHIPPERID FROM CARGOINFO ORDER BY WATERID) GROUP BY SHIPPERID;

–（3） 各承运公司在2015年一月的承运次数排行
SELECT COUNT(SS) AS COUNTS,SS FROM
(SELECT WATERID,min(SHIPPERID) SS,min(STARTTIME) ST FROM CARGOINFO WHERE
TO_CHAR(STARTTIME,’YYYYMM’)=’201501’ GROUP BY WATERID)
GROUP BY ss;

–（4） 大、中、小船型的承运次数
SELECT SHIPSIZE,COUNT(SHIPSIZE) FROM (SELECT DISTINCT(WATERID),SHIPSIZE FROM CARGOINFO ORDER BY WATERID) GROUP BY SHIPSIZE;

–（5） 每日的航运次数
SELECT COUNT(*) TIMES,C.STARTTIME,C.ENDTIME FROM CARGOINFO C
GROUP BY C.STARTTIME,C.ENDTIME;

SELECT * FROM EMP;
SELECT * FROM DEPT;
SELECT * FROM SALGRADE;
–（一）
–1．列出至少有一个员工的所有部门。
WITH TT AS (SELECT DEPTNO FROM EMP GROUP BY DEPTNO HAVING COUNT(DEPTNO)>0)
SELECT D.DNAME FROM DEPT D,TT WHERE D.DEPTNO = TT.DEPTNO;

–2．列出薪金比“SMITH”多的所有员工。
WITH TT AS (SELECT SAL FROM EMP WHERE ENAME=’SMITH’)
SELECT ENAME,E.SAL FROM EMP E,TT WHERE E.SAL>TT.SAL;

–3．列出所有员工的姓名及其直接上级的姓名。
SELECT E1.ENAME MEMBER,E2.ENAME MGR FROM EMP E1 LEFT JOIN EMP E2 ON E1.MGR=E2.EMPNO;

–4．列出受雇日期早于其直接上级的所有员工。
WITH TT AS (SELECT E1.ENAME MEMBER,E1.HIREDATE EH,E2.ENAME MGR,E2.HIREDATE RH FROM EMP E1 LEFT JOIN EMP E2 ON E1.MGR=E2.EMPNO)
SELECT TT.MEMBER,TT.EH FROM TT WHERE TO_CHAR(TT.EH,’YYYYMMDD’) < TO_CHAR(TT.RH,’YYYYMMDD’);

–5．列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门
SELECT D.DNAME,E.ENAME FROM EMP E,DEPT D WHERE E.DEPTNO=D.DEPTNO ORDER BY D.DNAME DESC;

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

–7．列出最低薪金大于1500的各种工作。
SELECT E.JOB,E.SAL FROM EMP E WHERE E.SAL>1500;

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

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

–10．列出与“SCOTT”从事相同工作的所有员工。
WITH TT AS (SELECT E1.JOB THEJOB FROM EMP E1 WHERE E1.ENAME=’SCOTT’)
SELECT E.ENAME,E.JOB FROM EMP E,TT WHERE E.JOB=TT.THEJOB;

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

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

–13．列出在每个部门工作的员工数量、平均工资和平均服务期限。
SELECT D.DNAME,COUNT(E.ENAME),AVG(E.SAL),AVG((TO_DATE(‘2015-07-28’,’YYYY-MM-DD’)-E.HIREDATE)) FROM EMP E,DEPT D WHERE E.DEPTNO=D.DEPTNO GROUP BY D.DNAME;

–14．列出所有员工的姓名、部门名称和工资。
SELECT E.ENAME,D.DNAME,E.SAL FROM EMP E,DEPT D WHERE E.DEPTNO=D.DEPTNO;

–15．列出所有部门的详细信息和部门人数。
WITH TT AS (SELECT E.DEPTNO DEPTNOO,COUNT(E.DEPTNO) NUM FROM EMP E GROUP BY E.DEPTNO)
SELECT D.DEPTNO,D.DNAME,D.LOC,TT.NUM FROM DEPT D,TT WHERE D.DEPTNO=TT.DEPTNOO;

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

–17．列出各个部门的MANAGER（经理）的最低薪金。
WITH TT AS (SELECT E.DEPTNO,MIN(E.SAL) LOSAL FROM EMP E GROUP BY E.DEPTNO)
SELECT D.DNAME,TT.LOSAL FROM DEPT D,TT WHERE TT.DEPTNO = D.DEPTNO;

–18．列出所有员工的年工资,按年薪从低到高排序。
SELECT E.ENAME,E.SAL*12 FROM EMP E ORDER BY SAL*12;

–（二）
–1. 找出EMP表中的姓名（ENAME）第三个字母是A 的员工姓名。
SELECT E.ENAME FROM EMP E WHERE REGEXP_LIKE(E.ENAME,’^..A.*$’);

–2. 找出EMP表员工名字中含有A 和N的员工姓名。
SELECT E.ENAME FROM EMP E WHERE REGEXP_LIKE(E.ENAME,’^.[AN].$’);

–3. 找出所有有佣金的员工，列出姓名、工资、佣金，显示结果按工资从小到大，佣金从大到小。
SELECT E.ENAME,E.SAL,E.COMM FROM EMP E WHERE E.COMM IS NOT NULL ORDER BY E.SAL ASC,E.COMM DESC;

–4. 列出部门编号为20的所有职位。
SELECT E.JOB FROM EMP E WHERE E.DEPTNO=20;

–5. 列出不属于SALES 的部门。
SELECT D.DNAME FROM DEPT D WHERE D.DNAME != ‘SALES’;

–6. 显示工资不在1000 到1500 之间的员工信息：名字、工资，按工资从大到小排序。
SELECT E.ENAME,E.SAL FROM EMP E WHERE E.SAL<1000 OR E.SAL>1500 ORDER BY E.SAL DESC;

–7. 显示职位为MANAGER 和SALESMAN，年薪在15000 和20000 之间的员工的信息：名字、职位、年薪。
SELECT E.ENAME,E.JOB,E.SAL*12 FROM EMP E WHERE (E.JOB=’MANAGER’ OR E.JOB=’SALESMAN’) AND (E.SAL*12 BETWEEN 15000 AND 20000);

–8. 说明以下两条SQL语句的输出结果：
–SELECT EMPNO,COMM FROM EMP WHERE COMM IS NULL;
当COMM为空时输出EMP表中的EMPNO和COMM
–SELECT EMPNO,COMM FROM EMP WHERE COMM = NULL;
当COMM是”null“时输出EMP表中的EMPNO和COMM

–9. 让SELECT 语句的输出结果为
–SELECT * FROM SALGRADE;
–SELECT * FROM BONUS;
–SELECT * FROM EMP;
–SELECT * FROM DEPT;

列出当前用户有多少张数据表，结果集中存在多少条记录。
SELECT TABLE_NAME FROM USER_TABLES;

SELECT COUNT(*) FROM USER_TABLES;

–10. 判断SELECT ENAME,SAL FROM EMP WHERE SAL > ‘1500’是否抱错，为什么？
不报错，因为oracle自动转换了