oracle经典建表语句【scott】--scott数据作业

--1.  选择部门30中的所有员工?
SELECT * FROM EMP E WHERE E.DEPTNO = '30';
--2.    列出所有办事员(秘书)的姓名，编号和部门编号?
SELECT E.ENAME, E.EMPNO, E.DEPTNO FROM EMP E WHERE E.JOB = '秘书';
--3.    找出佣金高于薪金的员工?
SELECT * FROM EMP E WHERE E.COMM > E.SAL;
--4.    找出佣金高于薪金的60%的员工?
SELECT * FROM EMP E WHERE E.COMM - E.SAL > E.SAL * 0.6;
--5.    找出部门10中所有经理(经理)和部门20中所有办事员(秘书)的详细资料？
SELECT *
  FROM EMP E
 WHERE (E.JOB = '秘书' AND E.DEPTNO = '20')
    OR (E.JOB = '经理' AND E.DEPTNO = '10');
--6.    找出部门10中所有经理(经理),部门20中所有办事员(秘书)， 
--既不是经理又不是办事员但其薪金大于或等于2000的所有员工的详细资料？
SELECT *
  FROM EMP E
 WHERE (E.JOB = '秘书' AND E.DEPTNO = '20')
    OR (E.JOB = '经理' AND E.DEPTNO = '10')
    OR (E.SAL >= 2000);
--7.找出收取佣金的员工的不同工作？
SELECT DISTINCT E.JOB FROM EMP E WHERE E.COMM IS NOT NULL;
--8.    找出不收取佣金或收取的佣金低于100的员工？
SELECT *
  FROM EMP E
 WHERE E.COMM IS NULL
    OR E.COMM < 100;
--9.    找出各月倒数第3天受雇的所有员工？
SELECT E.*, LAST_DAY(E.HIREDATE) - 2
  FROM EMP E
 WHERE E.HIREDATE = LAST_DAY(E.HIREDATE) - 2;
--10.    找出早于12年前受雇的员工？
SELECT E.*, ADD_MONTHS(SYSDATE, -12 * 12)
  FROM EMP E
 WHERE E.HIREDATE < ADD_MONTHS(SYSDATE, -12 * 12);
 --11.    以首字母大写的方式显示所有员工的姓名？
 SELECT INITCAP(E.ENAME) FROM EMP E;
 --12.    显示正好为5个字符的员工的姓名？
 select e.ename from emp e where length(e.ename)=5;
 --13.    显示不带有"R"的员工的姓名？
 select * from emp e where e.ename not like '%R%';
 --14.    显示所有员工姓名的前三个字符？
 select substr(e.ename,1,3) from emp e ;
 --15.    显示所有员工的姓名,用a替换所有"A"？
 select replace(e.ename,'A','a') from emp e;
 --16.    显示满10年服务年限的员工的姓名和受雇日期？
 select e.ename,e.hiredate from emp e
 where e.hiredate<add_months(sysdate,-12*10);
 --17.    显示员工的详细资料,按姓名排序？
 select * from emp e order by e.ename;
 --18.    显示员工的姓名和受雇日期,根据其服务年限,将最老的员工排在最前面？
 select e.ename,e.hiredate from emp e order by e.hiredate ;
 --19.    显示所有员工的姓名、工作和薪金,按工作的降序排序,若工作相同则按薪金排序？
 select e.ename,e.job,e.sal from emp e order by e.job desc ,e.sal;
 --20.    显示所有员工的姓名、加入公司的年份和月份,按受雇日期所在月排序,
 --若月份相同则将最早年份的员工排在最前面？
 SELECT E.ENAME,
        TO_CHAR(E.HIREDATE, 'MM') M,
        TO_NUMBER(TO_CHAR(E.HIREDATE, 'YYYY')) Y
   FROM EMP E
  ORDER BY TO_NUMBER(TO_CHAR(E.HIREDATE, 'MM')),
           TO_NUMBER(TO_CHAR(E.HIREDATE, 'YYYY'));
--21. 显示在一个月为30天的情况所有员工的日薪金,忽略余数？
SELECT E.*, FLOOR(E.SAL / 30) DS FROM EMP E;
--22.    找出在(任何年份的)2月受聘的所有员工？
select * from emp e
where to_number(to_char(e.hiredate,'MM')) = 2;
--23.    对于每个员工,显示其加入公司的天数？
SELECT E.*, ceil(SYSDATE - E.HIREDATE) FROM EMP E;
--24.    显示姓名字段的任何位置包含"A"的所有员工的姓名？
select * from emp e where e.ename like '%A%';
--25.    以年月日的方式显示所有员工的服务年限？
SELECT E.ENAME,
       E.HIREDATE,
       SYSDATE,
       FLOOR(MONTHS_BETWEEN(SYSDATE, E.HIREDATE)) MS,
       FLOOR(MONTHS_BETWEEN(SYSDATE, E.HIREDATE) / 12) Y,
       floor(
       MONTHS_BETWEEN(SYSDATE, E.HIREDATE) -
       12 * FLOOR(MONTHS_BETWEEN(SYSDATE, E.HIREDATE) / 12)) M
       
  FROM EMP E;
--26.    列出各种工作职位的最低工资，且显示最低工资大于1500的记录
SELECT E.JOB, MIN(E.SAL) FROM EMP E GROUP BY E.JOB HAVING MIN(E.SAL) > 1500;
--27.    列出所有雇员的雇员名称、部门名称和工资
SELECT E.ENAME, D.DNAME, E.SAL
  FROM EMP E, DEPT D
 WHERE E.DEPTNO = D.DEPTNO ;
--28.    列出从事同一种工作但属于不同部门的雇员的员工信息
SELECT A.ENAME, A.JOB, A.DEPTNO, B.ENAME, B.JOB, B.DEPTNO
  FROM EMP A, EMP B
 WHERE A.JOB = B.JOB
   AND A.DEPTNO <> B.DEPTNO;
--29.    列出按年薪排序的所有雇员的年薪
SELECT E.*, E.SAL * 12 FROM EMP E ORDER BY E.SAL * 12;
--30.    列出至少有一个员工的所有部门
SELECT *
  FROM DEPT D
 WHERE EXISTS (SELECT 1 FROM EMP E WHERE E.DEPTNO = D.DEPTNO);
SELECT * FROM DEPT D WHERE D.DEPTNO IN (SELECT E.DEPTNO FROM EMP E);
--31.    列出薪金比"SMITH"多的所有员工
SELECT *
  FROM EMP E
 WHERE E.SAL > (SELECT A.SAL FROM EMP A WHERE A.ENAME = 'SMITH');
--32. 列出薪金高于公司平均水平的所有员工
SELECT * FROM EMP E WHERE E.SAL > (SELECT AVG(A.SAL) FROM EMP A);
--33.    列出与“SCOTT”从事相同工作的所有员工
SELECT *
  FROM EMP E
 WHERE E.JOB > (SELECT A.JOB FROM EMP A WHERE A.ENAME = 'SCOTT');
--34.    列出某些员工的姓名和薪金，条件是他们的薪金等于部门30中任何一个员工的薪金
SELECT E.ENAME, E.SAL
  FROM EMP E
 WHERE E.SAL IN (SELECT A.SAL FROM EMP A WHERE A.DEPTNO = '30')
   AND E.EMPNO NOT IN (SELECT B.EMPNO FROM EMP B WHERE B.DEPTNO = '30');

--35.    列出所有员工的姓名及其直接上级的姓名
SELECT E.ENAME, (SELECT A.ENAME FROM EMP A WHERE A.EMPNO = E.MGR) LEADER
  FROM EMP E;
--36.    列出各个部门的经理（经理）的最低薪金
SELECT E.DEPTNO, MIN(E.SAL)
  FROM EMP E
 WHERE E.JOB = '经理'
 GROUP BY E.DEPTNO;

 

转载于:https://my.oschina.net/alkz/blog/3071613