sql查询语句

SQL> select * from emp;   
  
EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO   
----- ---------- --------- ----- ----------- --------- --------- ------   
 7369 SMITH      CLERK      7902 1980-12-17     800.00               20  
 7499 ALLEN      SALESMAN   7698 1981-2-20     1600.00    300.00     30  
 7521 WARD       SALESMAN   7698 1981-2-22     1250.00    500.00     30  
 7566 JONES      MANAGER    7839 1981-4-2      2975.00               20  
 7654 MARTIN     SALESMAN   7698 1981-9-28     1250.00   1400.00     30  
 7698 BLAKE      MANAGER    7839 1981-5-1      2850.00               30  
 7782 CLARK      MANAGER    7839 1981-6-9      2450.00               10  
 7788 SCOTT      ANALYST    7566 1987-4-19     3000.00               20  
 7839 KING       PRESIDENT       1981-11-17    5000.00               10  
 7844 TURNER     SALESMAN   7698 1981-9-8      1500.00      0.00     30  
 7876 ADAMS      CLERK      7788 1987-5-23     1100.00               20  
 7900 JAMES      CLERK      7698 1981-12-3      950.00               30  
 7902 FORD       ANALYST    7566 1981-12-3     3000.00               20  
 7934 MILLER     CLERK      7782 1982-1-23     1300.00               10  
  
14 rows selected   
  
SQL> select *  from dept;   
  
DEPTNO DNAME          LOC   
------ -------------- -------------   
    10 ACCOUNTING     NEW YORK   
    20 RESEARCH       DALLAS   
    30 SALES          CHICAGO   
    40 OPERATIONS     BOSTON  

 

 

 

1 找出佣金高于薪金60%的雇员。   
SELECT * FROM emp WHERE comm>sal*0.6;   
  
2 找出部门10中所有经理和部门20中所有办事员的详细资料。   
SELECT * FROM emp   
WHERE deptno=10 AND JOB='MANAGER' OR deptno=20 AND job='CLERK';   
  
3 找出部门10中所有经理，部门20中所有办事员以及既不是经理又不是办事员但其薪金大于或等2000的所有雇员的详细资料。   
SELECT * FROM emp   
WHERE deptno=10 AND JOB='MANAGER' 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 找出收取佣金的雇员的不同工作。   
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   
(SELECT maxh FROM   
(SELECT MAX(HIREDATE) AS maxh,EXTRACT(MONTH 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 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 H_10Y FROM EMP;   
  
14显示雇员的详细资料，按姓名排序。   
SELECT * FROM EMP ORDER BY ENAME;   
  
15显示雇员姓名，根据其服务年限，将最老的雇员排在最前面。   
SELECT ENAME,HIREDATE FROM EMP ORDER BY HIREDATE;   
  
16显示所有雇员的姓名、工作和薪金，按工作内的工作的降序顺序顺序排序，而工作按薪金排序。   
SELECT ENAME,JOB,SAL   
FROM EMP   
ORDER BY JOB DESC,SAL;   
  
17显示所有雇员的姓名和加入公司的年份和月份，按雇员受雇日所在月排序，并将最早年份的项目排在最前面。   
SELECT ENAME,TO_CHAR(HIREDATE,'YYYY-MM') AS Y_M   
FROM EMP   
ORDER BY TO_CHAR(HIREDATE,'MM'),TO_CHAR(HIREDATE,'YYYY');   
  
18显示在一个月为30天的情况下所有雇员的日薪金，取整。   
SELECT ROUND(SAL/30) AS SAL FROM EMP;   
  
19找出在（任何年份的）2月受聘的所有雇员。   
SELECT * FROM EMP WHERE TO_CHAR(HIREDATE,'MM')='02';   
  
20对于每个雇员，显示其加入公司的天数。   
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以年、月和日显示所有雇员的服务年限。   
SELECT HIREDATE,   
  FLOOR(MONTHS_BETWEEN(SYSDATE,HIREDATE)/12) AS Y,   
  MOD(FLOOR(MONTHS_BETWEEN(SYSDATE,HIREDATE)),12) AS M,   
  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 ygb.ename AS empnm,sjb.ename AS mgrnm   
FROM emp ygb,emp sjb   
WHERE ygb.mgr=sjb.empno;   
  
26列出入职日期早于其直接上级的所有雇员。   
SELECT * FROM emp ygb WHERE hiredate<(SELECT hiredate FROM emp sjb WHERE sjb.empno=ygb.mgr);   
  
27列出部门和这些部门的雇员，同时列出那些没有雇员的部门。   
/*  
SELECT * FROM dept WHERE deptno IN (SELECT DISTINCT deptno FROM emp)  
UNION  
SELECT * FROM dept WHERE deptno NOT IN (SELECT DISTINCT deptno FROM emp)  
*/  
SELECT dname,ename   
FROM dept a LEFT JOIN emp b   
ON a.deptno=b.deptno;   
  
28列出所有“CLERK”（办事员）的姓名及其部门名称。   
SELECT ename,dname   
FROM dept a,emp b   
WHERE a.deptno=b.deptno AND job='CLERK';   
  
29列出各种类型的最低薪金，并使最低薪金大于1500。   
SELECT job,MIN(sal)   
FROM emp   
GROUP BY job   
HAVING MIN(sal)>1500;   
  
30列出从事“SALES”（销售）工作的雇员的姓名，假定不知道销售部的部门编号。   
SELECT ename FROM emp 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=(SELECT JOB FROM EMP WHERE ENAME='SCOTT') AND ENAME<>'SCOTT';   
  
33列出薪金等于在部门30工作的所有雇员的姓名和薪金。   
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列出在每个部门工作的雇员的经理以及其他信息。   
SELECT A.*,B.*   
FROM DEPT A,EMP B   
WHERE A.DEPTNO=B.DEPTNO AND JOB='MANAGER';   
/*  
SELECT A.*,B.*  
FROM (SELECT * FROM EMP WHERE JOB<>'MANAGER') A,(SELECT * FROM EMP WHERE JOB='MANAGER') B  
WHERE A.DEPTNO=B.DEPTNO AND B.JOB='MANAGER'  
ORDER BY A.DEPTNO;  
*/  
  
36列出所有雇员的雇员名称、部门名称和薪金。   
SELECT ENAME,DNAME,SAL   
FROM DEPT A,EMP B   
WHERE A.DEPTNO=B.DEPTNO;   
  
37列出从事同一种工作但属于不同部门的雇员的不同组合。   
SELECT * FROM emp ORDER BY job,deptno;   
  
38列出分配有雇员数量的所有部门的详细信息，即使是分配有0个雇员。   
SELECT * FROM dept WHERE deptno IN (SELECT DISTINCT deptno FROM emp)   
UNION   
SELECT * FROM dept WHERE deptno NOT IN (SELECT DISTINCT deptno FROM emp)   
  
39列出各种类型工作的最低工资。   
SELECT job,MIN(sal)   
FROM emp   
GROUP BY job;   
  
40列出各个部门的MANAGER（经理）的最低薪金。   
SELECT MIN(sal) FROM emp WHERE job='MANAGER';   
  
41列出按年薪排序的所有雇员的年薪。   
SELECT 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表中薪水第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() over(PARTITION BY deptno ORDER BY sal DESC) AS rn FROM emp a)   
WHERE rn=3;   
  
48统计各部门的薪水总和.   
SELECT deptno,SUM(sal) AS sumsal   
FROM emp   
GROUP BY deptno;   
  
--每个部门员工和经理的详细信息:编号,姓名,薪水,入职日期,部门编号   
SELECT a.empno AS eno,a.ename AS enm,a.job AS ejob,a.sal AS esal,a.hiredate AS edate,a.deptno AS edept,   
       b.empno AS mno,b.ename AS mnm,b.job AS mjob,b.sal AS msal,b.hiredate AS mdate,b.deptno AS mdept   
FROM (SELECT * FROM emp WHERE job<>'MANAGER' AND job<>'PRESIDENT') a,(SELECT * FROM emp WHERE job='MANAGER') b   
WHERE a.deptno=b.deptno;