Oracle數(shù)據(jù)庫查詢練習及答案

1、1找出傭金高于薪金 60%的雇員。SELECT * FROM emp WHERE comm>sal*;2找出部門10中所有經(jīng)理和部門 20中所有辦事員的詳細資料。SELECT * FROM emp WHERE deptno=10 AND JOB='MANAGER' OR deptno=20 AND job='CLERK'3找出部門10中所有經(jīng)理，部門 20中所有辦事員以及既不是經(jīng)理又不是辦事員但其薪金 大于或等2000的所有雇員的詳細資料。SELECT * FROM emp WHERE deptno=10 AND JOB='MANAGER'

2、 OR deptno=20 AND job='CLERK' OR JOB NOT IN('MANAGER','CLERK') AND SAL>=2000;SELECT * FROM emp WHERE deptno=10 AND JOB='MANAGER' OR deptno=20 AND job='CLERK' OR (JOB<>'MANAGER' AND JOB<>'MANAGER' AND SAL>=2000);4找出收取傭金的雇員的不同工作

3、。SELECT DISTINCT JOB FROM EMP WHERE COMM IS NOT NULL;5找出不收取傭金或收取的傭金低于300的雇員。SELECT * FROM EMP WHERE COMM IS NULL OR COMM<300;6找出各月最后一天受雇的所有雇員。SELECT * FROM EMP WHERE HIREDATE=LAST_DAY(HIREDATE);-找出各月最后受雇的所有雇員SELECT* FROM emp WHERE hiredate IN (SELECTmaxh FROM (SELECTMAX(HIREDATE) maxh,EXTRACT(MON

4、TH FROM hiredate)FROM EMP GROUP BY EXTRACT(MONTH FROM hiredate);7找出晚于26年之前受雇的雇員。SELECT * FROM emp WHERE months_between(SYSDATE,hiredate)<=26*12;8顯示只有首字母大寫的的所有雇員的姓名。SELECT * FROM emp WHERE ename=initcap(ename);9顯示正好為5個字符的雇員的姓名。SELECT * FROM emp WHERE length(ename)=5;10顯示不帶有“ R”的雇員姓名。SELECT * FROM

5、emp WHERE instr(ename,'R')=0;SELECT * FROM emp WHERE ename NOT LIKE '%R%'11顯示所有雇員的姓名的前三個字符。SELECT substr(ename,1,3) AS en3 FROM emp;12顯所有雇員的姓名，用 a替換所有“ A”。SELECT REPLACE(ename,'A','a') FROM emp;13顯示所有雇員的姓名以及滿10年服務年限的日期。SELECT ENAME,HIREDATE,ADD_MONTHS(HIREDATE,120) AS

6、 H_10Y FROM EMP;14顯示雇員的詳細資料，按姓名排序。SELECT * FROM EMP ORDER BY ENAME;15顯示雇員姓名，根據(jù)其服務年限，將最老的雇員排在最前面。SELECT ENAME,HIREDATE FROM EMP ORDER BY HIREDATE;16顯示所有雇員的姓名、工作和薪金，按工作內(nèi)的工作的降序順序順序排序，而工作按薪金排序。SELECT ENAME,JOB,SAL FROM EMP ORDER BY JOB DESC,SAL;17顯示所有雇員的姓名和加入公司的年份和月份，按雇員受雇日所在月排序，并將最早年份的項目排在最前面。SELECT EN

7、AME,TO_CHAR(HIREDATE,'YYYY-MM') AS Y_M FROM EMP ORDER BY TO_CHAR(HIREDATE,'MM'),TO_CHAR(HIREDATE,'YYYY');18顯示在一個月為30天的情況下所有雇員的日薪金，取整。SELECT ROUND(SA30) AS SAL FROM EMP;19找出在(任何年份的)2月受聘的所有雇員。SELECT * FROM EMP WHERE TO_CHAR(HIREDATE,'MM')='02'20對于每個雇員，顯示其加入公司的天數(shù)

8、。SELECT CEIL(SYSDATE-HIREDATE) AS DY FROM EMP;21顯示姓名字段的任何位置，包含“A”的所有雇員的姓名。SELECT ENAME FROM EMP WHERE ENAME LIKE '%A%'SELECT ENAME FROM EMP WHERE INSTR(ENAME,'A')>0;22以年、月和日顯示所有雇員的服務年限。SELECTHIREDATE,FLOOR(MONTHS_BETWEEN(SYSDATE,HIREDATE)/12) ASYMOD(FLOOR(MONTHS_BETWEEN(SYSDATE,HI

9、REDATE),12)ASM,MOD(FLOOR(SYSDATE-HIREDATE),30) AS D FROM EMP;23列出至少有一個雇員的所有部門。SELECT * FROM DEPT WHERE DEPTNO IN (SELECT DISTINCT DEPTNO FROM EMP);24列出薪金比“ SMITH”多的所有雇員。SELECT * FROM EMP WHERE SAL>(SELECT SAL FROM EMP WHERE ENAME='SMITH');25列出所有雇員的姓名及其上級的姓名。SELECT AS empnm, AS mgrnm FROM

10、emp ygb,emp sjb WHERE =;26列出入職日期早于其直接上級的所有雇員。SELECT * FROM emp ygb WHERE hiredate<(SELECT hiredate FROM emp sjb WHERE =;27列出部門和這些部門的雇員，同時列出那些沒有雇員的部門。SELECT * FROM dept WHERE deptno IN (SELECT DISTINCT deptno FROM emp)UNIONSELECT * FROM dept WHERE deptno NOT IN (SELECT DISTINCT deptno FROM emp)SEL

11、ECT dname,ename FROM dept a LEFT JOIN emp b ON =;28列出所有“ CLERK (辦事員)的姓名及其部門名稱。SELECT ename,dname FROM dept a,emp b WHERE = AND job='CLERK'29列出各種類型的最低薪金，并使最低薪金大于1500。SELECT job,MIN(sal) FROM emp GROUP BY job HAVING MIN(sal)>1500;30列出從事“ SALES (銷售)工作的雇員的姓名，假定不知道銷售部的部門編號。SELECT ename FROM em

12、p WHERE deptno=(SELECT deptno FROM dept WHERE dname='SALES');31列出薪金高于公司平均水平的所有雇員。SELECT * FROM EMP WHERE SAL>(SELECT AVG(SAL) FROM EMP);32列出與“SCOTT從事相同工作的所有雇員。SELECT* FROM EMP WHERE JOB=(SELECJOB FROM EMP WHERE ENAME='SCOTT') AND ENAME<>'SCOTT'33列出薪金等于在部門30工作的所有雇員的姓名

13、和薪金。SELECT * FROM EMP WHERE SAL IN(SELECT SAL FROM EMP WHERE DEPTNO=30);34列出薪金高于在部門30工作的所有雇員的姓名和薪金。SELECT * FROM EMP WHERE SAL >ALL(SELECT SAL FROM EMP WHERE DEPTNO=30);35列出在每個部門工作的雇員的經(jīng)理以及其他信息。SELECT A.*,B.* FROM DEPT A,EMP B WHERE = AND JOB='MANAGER'SELECT A.*,B.* FROM (SELECT * FROM EMP

14、 WHERE JOB<>'MANAGER') A,(SELECT * FROM EMPWHERE JOB='MANAGER') B WHERE = AND ='MANAGER'ORDER BY ;36列出所有雇員的雇員名稱、部門名稱和薪金。SELECT ENAME,DNAME,SAL FROM DEPT A,EMP B WHERE =;37列出從事同一種工作但屬于不同部門的雇員的不同組合。SELECT * FROM emp ORDER BY job,deptno;38列出分配有雇員數(shù)量的所有部門的詳細信息，即使是分配有0個雇員。SEL

15、ECT * FROM dept WHERE deptno IN (SELECT DISTINCT deptno FROM emp) UNIONSELECT * FROM dept WHERE deptno NOT IN (SELECT DISTINCT deptno FROM emp)39列出各種類型工作的最低工資。SELECT job,MIN(sal) FROM emp GROUP BY job;40列出各個部門的 MANAGER (經(jīng)理)的最低薪金。SELECT MIN(sal) FROM emp WHERE job='MANAGER'41列出按年薪排序的所有雇員的年薪。S

16、ELECT sal*12 AS y_sal FROM emp ORDER BY sal*12;42列出薪金水平處于第四位的雇員。SELECT * FROM(SELECT a.*,row_number() over(ORDER BY sal DESC) AS rn FROM emp a)WHERE rn=4;43查找EMP表中前5條記錄SELECT * FROM emp WHERE ROWNUM<=5;44查找EMP表中10條以后的記錄SELECT * FROM (SELECT a.*,ROWNUM AS rn FROM emp a) WHERE rn>10;45查找EMP表中薪水第

17、5高的員工SELECT * FROM (SELECT a.*,row_number() over(ORDER BY sal DESC) AS rn FROM emp a) WHERE rn=5;46查找EMP表部門30中薪水第3的員工SELECT * FROM (SELECT a.*,row_number() over(PARTITION BY deptno ORDER BY sal DESC) AS rn FROM emp a) WHERE rn=5 AND deptno=30;47查找EMP表中每部門薪水第 3的員工SELECT * FROM (SELECT a.*,row_number(

18、) over(PARTITION BY deptno ORDER BY sal DESC) AS rn FROM emp a) WHERE rn=3;48統(tǒng)計各部門的薪水總和.SELECT deptno,SUM(sal) AS sumsal FROM emp GROUP BY deptno;-每個部門員工和經(jīng)理的詳細信息：編號 姓名，薪水，入職日期，部門編號SELECT AS eno, AS enm, AS ejob, AS esal, AS edate, AS edept, AS mno, AS mnm, AS mjob, ASmsal, AS mdate, AS mdept FROM (S

19、ELECT* FROM emp WHERE job<>'MANAGER' AND job<>'PRESIDENT') a,(SELECT * FROM emp WHERE job='MANAGER') bWHERE =;- -1.列出至少有一個員工的所有部門。select * from dept where deptno in(select distinct deptno from emp);select count(*),deptno from emp group by deptno having count(*)>

20、;1;- -2.列出薪金比“ SMITH”多的所有員工。select * from emp where sal>(select sal from emp where ename='SMITH');- -3.列出所有員工的姓名及其直接上級的姓名select ename,(select ename from emp where empno= from emp a;select , from emp a,emp b where =(+);- -4.列出受雇日期早于其直接上級的所有員工。select ename from emp e where hiredate <(sel

21、ect hiredate from emp where empno=;- -5.列出部門名稱和這些部門的員工信息，同時列出那些沒有員工的部門。select dname,ename from dept left join emp on =;select dname,ename from dept a,emp b where = (+);- -6.列出所有“ CLERK (辦事員)的姓名及其部門名稱。select dname,ename from dept a,emp b where = and job='CLERK'select (select dname from dept w

22、here deptno= as dname ,ename from emp a where job='CLERK'- -7.列出最低薪金大于 1500的各種工作。select job from emp group by job having min(sal)>1500;select job,min(sal) msal from emp group by job having min(sal)>1500;- -8.列出在部門“ SALES (銷售部)工作的員工的姓名，假定不知道銷售部的部門編號。select ename from emp where deptno=(s

23、elect deptno from dept where dname='SALES');- -9.列出薪金高于公司平均薪金的所有員工。select ename from emp where sal>(select avg(sal) from emp);- -10.列出與“ SCOTT從事相同工作的所有員工。select * from emp where job=(select job from emp where ename='SCOTT');- -11.列出薪金等于部門30中員工的薪金的所有員工的姓名和薪金。select ename,sal from emp where sal=any(select sal from emp where deptno=30);select * from emp where sal in (select sal from emp where deptno=30);- -12.列出薪金高于在部門30工作的所有員工的