ORACLE SQL
经典查询练手系列文章
文档作者：胡勇
编制编制地点：海南地点：海南地点：海南..海口编制编制日期：日期：日期：20202011
11年06月
目录
[推荐]ORACLE SQL：经典查询练手第一篇(不懂装懂，永世饭桶！) (3)
[推荐]ORACLE SQL：经典查询练手第二篇(不懂装懂，永世饭桶！) (18)
[推荐]ORACLE SQL：经典查询练手第三篇(不懂装懂，永世饭桶！) (27)
[推荐]ORACLE SQL：经典查询练手第四篇(不懂装懂，永世饭桶！) (39)
[推荐]ORACLE SQL：经典查询练手第五篇(不懂装懂，永世饭桶！) (50)
[推荐]ORACLE SQL：
经典查询练手第一篇(不懂装懂，永世饭桶！)
本文与大家共同讨论与分享ORACLE SQL的一些常用经典查询，欢迎大家补充，同时你认为有那些经典的也可分享出来。在本文中，对每一个问题，你要是认为有什么更好的解决方法也欢迎你及时提出。交流与分享才能共同进步嘛，感谢！
本文使用的实例表结构与表的数据如下：
scott.emp员工表结构如下：
Name Type Nullable Default Comments
-------------------------------------------
EMPNO NUMBER(4)员工号
ENAME VARCHAR2(10)Y员工姓名
JOB VARCHAR2(9)Y工作
MGR NUMBER(4)Y上级编号
HIREDATE DATE Y雇佣日期
SAL NUMBER(7,2)Y薪金
COMM NUMBER(7,2)Y佣金
DEPTNO NUMBER(2)Y部门编号
scott.dept部门表
Name Type Nullable Default Comments
-----------------------------------------
DEPTNO NUMBER(2)部门编号
DNAME VARCHAR2(14)Y部门名称
LOC VARCHAR2(13)Y地点
提示：工资＝薪金＋佣金
scott.emp表的现有数据如下：
SQL>SELECT*FROM EMP;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ----------------------------------------------------------------7369SMITH CLERK79021980-12-17800.0020 7499ALLEN SALESMAN76981981-2-201600.00300.0030 7521WARD SALESMAN76981981-2-221250.00500.0030 7566JONES MANAGER78391981-4-22975.0020 7654MARTIN SALESMAN76981981-9-281250.001400.0030 7698BLAKE MANAGER78391981-5-12850.0030 7782CLARK MANAGER78391981-6-92450.0010 7788SCOTT ANALYST75661987-4-194000.0020 7839KING PRESIDENT1981-11-175000.0010 7844TURNER SALESMAN76981981-9-81500.000.0030 7876ADAMS CLERK77881987-5-231100.0020 7900JAMES CLERK76981981-12-3950.0030 7902FORD ANALYST75661981-12-33000.0020 7934MILLER CLERK77821982-1-231300.0010 102EricHu Developer14552011-5-2615500.0014.0010 104huyong PM14552011-5-2615500.0014.0010 105WANGJING Developer14552011-5-2615500.0014.0010
17rows selected
Scott.dept表的现有数据如下：
SQL>SELECT*FROM DEPT;
DEPTNO DNAME LOC
---------------------------------
10ACCOUNTING NEW YORK
20RESEARCH DALLAS
30SALES CHICAGO
40OPERATIONS BOSTON
5050abc50def
60Developer HaiKou
6rows selected
用SQL完成以下问题列表：
1．列出至少有一个员工的所有部门。
2．列出薪金比“SMITH”多的所有员工。
3．列出所有员工的姓名及其直接上级的姓名。
4．列出受雇日期早于其直接上级的所有员工。
5．列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门
6．列出所有“CLERK”（办事员）的姓名及其部门名称。
7．列出最低薪金大于1500的各种工作。
8．列出在部门“SALES”（销售部）工作的员工的姓名，假定不知道销售部的部门编号。9．列出薪金高于公司平均薪金的所有员工。
10．列出与“SCOTT”从事相同工作的所有员工。
11．列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。
12．列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。
13．列出在每个部门工作的员工数量、平均工资和平均服务期限。
14．列出所有员工的姓名、部门名称和工资。
15．列出所有部门的详细信息和部门人数。
16．列出各种工作的最低工资。
17．列出各个部门的MANAGER（经理）的最低薪金。
18．列出所有员工的年工资,按年薪从低到高排序。
19．用一条sql语句查询出scott.emp表中每个部门工资前三位的数据，显示结果如下：DEPTNO SAL1SAL2SAL3
------------------------------
10550055005500
20400030002975
30285016001500
--------1．列出至少有一个员工的所有部门。---------
SQL>SELECT DNAME FROM DEPT
2WHERE DEPTNO IN(
3SELECT DEPTNO FROM EMP);
DNAME
--------------
RESEARCH
SALES
ACCOUNTING
--------或--------
SQL>SELECT DNAME FROM DEPT WHERE DEPTNO IN(SELECT DEPTNO 2FROM EMP GROUP BY DEPTNO HAVING COUNT(DEPTNO)>=1);
DNAME
--------------
ACCOUNTING
RESEARCH
SALES
--------或--------
SQL>SELECT DNAME FROM DEPT A
2WHERE EXISTS(
3SELECT NULL FROM EMP B
4WHERE B.DEPTNO=A.DEPTNO
5);
DNAME
--------------
RESEARCH
SALES
ACCOUNTING
--------2．列出薪金比“SMITH”多的所有员工。----------
SQL>SELECT*FROM EMP
2WHERE SAL>
3(SELECT SAL FROM EMP WHERE ENAME='SMITH');
--------或--------
SQL>SELECT*FROM EMP A
2WHERE EXISTS(
3SELECT NULL FROM EMP B
4WHERE B.SAL 5AND B.ENAME='SMITH' 6); --------或-------- SQL>SELECT*FROM EMP A, 2(SELECT SAL AS SALARY FROM EMP 3WHERE ENAME='SMITH')B 4WHERE A.SAL>B.SALARY; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ----------------------------------------------------------------7499ALLEN SALESMAN76981981-2-201600.00300.0030 7521WARD SALESMAN76981981-2-221250.00500.0030 7566JONES MANAGER78391981-4-22975.0020 7654MARTIN SALESMAN76981981-9-281250.001400.0030 7698BLAKE MANAGER78391981-5-12850.0030 7782CLARK MANAGER78391981-6-92450.0010 7788SCOTT ANALYST75661987-4-194000.0020 7839KING PRESIDENT1981-11-175000.0010 7844TURNER SALESMAN76981981-9-81500.000.0030 7876ADAMS CLERK77881987-5-231100.0020 7900JAMES CLERK76981981-12-3950.0030 7902FORD ANALYST75661981-12-33000.0020 7934MILLER CLERK77821982-1-231300.0010 102EricHu Developer14552011-5-2615500.0014.0010 104huyong PM14552011-5-2615500.0014.0010 105WANGJING Developer14552011-5-2615500.0014.0010 16rows selected --------3．列出所有员工的姓名及其直接上级的姓名。---------- SQL>SELECT A.ENAME,(SELECT ENAME FROM EMP B WHERE B.EMPNO=A.MGR) 2AS BOSS_NAME FROM EMP A; --------或-------- SQL>SELECT E.ENAME,M.ENAME BOSS_NAME 2FROM EMP E LEFT JOIN EMP M 3ON E.MGR=M.EMPNO; ENAME BOSS_NAME -------------------- SMITH FORD ALLEN BLAKE WARD BLAKE JONES KING MARTIN BLAKE BLAKE KING CLARK KING SCOTT JONES KING TURNER BLAKE ADAMS SCOTT JAMES BLAKE FORD JONES MILLER CLARK EricHu huyong WANGJING 17rows selected --------4．列出受雇日期早于其直接上级的所有员工。---------- SQL>SELECT A.ENAME FROM EMP A 2WHERE A.HIREDATE< 3(SELECT HIREDATE FROM EMP B WHERE B.EMPNO=A.MGR); --------或-------- SQL>SELECT EMP1.ENAME FROM EMP EMP1 2WHERE EMP1.MGR IS NOT NULL 3AND NOT EXISTS( 4SELECT NULL FROM EMP EMP2 5WHERE EMP1.MGR=EMP2.EMPNO 6AND EMP1.HIREDATE>EMP2.HIREDATE); ENAME ---------- SMITH ALLEN WARD JONES BLAKE CLARK 6rows selected --------5．列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门----------SQL>SELECT A.DNAME,B.EMPNO,B.ENAME,B.JOB,B.MGR, 2B.HIREDATE,B.SAL,B.DEPTNO 3FROM DEPT A LEFT JOIN EMP B ON A.DEPTNO=B.DEPTNO; --------或-------- SQL>SELECT A.DNAME,DEPTNO,B.EMPNO,B.ENAME, 2B.JOB,B.MGR,B.ENAME,B.SAL,B.HIREDATE 3FROM DEPT A LEFT JOIN EMP B USING(DEPTNO); DNAME EMPNO ENAME JOB MGR HIREDATE SAL DEPTNO ---------------------------------------------------------------------RESEARCH7369SMITH CLERK79021980-12-17800.0020 SALES7499ALLEN SALESMAN76981981-2-201600.0030 SALES7521WARD SALESMAN76981981-2-221250.0030 RESEARCH7566JONES MANAGER78391981-4-22975.0020 SALES7654MARTIN SALESMAN76981981-9-281250.0030 SALES7698BLAKE MANAGER78391981-5-12850.0030 ACCOUNTING7782CLARK MANAGER78391981-6-92450.0010 RESEARCH7788SCOTT ANALYST75661987-4-194000.0020 ACCOUNTING7839KING PRESIDENT1981-11-175000.0010 SALES7844TURNER SALESMAN76981981-9-81500.0030 RESEARCH7876ADAMS CLERK77881987-5-231100.0020 SALES7900JAMES CLERK76981981-12-3950.0030 RESEARCH7902FORD ANALYST75661981-12-33000.0020 ACCOUNTING7934MILLER CLERK77821982-1-231300.0010 ACCOUNTING102EricHu Developer14552011-5-2615500.0010 ACCOUNTING104huyong PM14552011-5-2615500.0010 ACCOUNTING105WANGJING Developer14552011-5-2615500.0010 50abc OPERATIONS Developer 20rows selected --------6．列出所有“CLERK”（办事员）的姓名及其部门名称。---------- SQL>SELECT A.ENAME,B.DNAME FROM EMP A JOIN DEPT B 2ON A.DEPTNO=B.DEPTNO AND A.JOB='CLERK'; --------或-------- SQL>SELECT ENAME,DNAME 2FROM( 3SELECT ENAME,DEPTNO FROM EMP 4WHERE JOB='CLERK' 5)A JOIN DEPT 6USING(DEPTNO); ENAME DNAME ------------------------ SMITH RESEARCH ADAMS RESEARCH JAMES SALES MILLER ACCOUNTING --------7．列出最低薪金大于1500的各种工作。---------- SQL>SELECT DISTINCT JOB AS HIGHSALJOB 2FROM EMP GROUP BY JOB HAVING MIN(SAL)>1500; --------或-------- SQL>SELECT DISTINCT JOB FROM EMP A 2WHERE A.SAL>1500 3AND NOT EXISTS( 4SELECT NULL FROM EMP B 5WHERE B.JOB=A.JOB 6AND B.SAL HIGHSALJOB ---------- ANALYST Developer MANAGER PM PRESIDENT --------8．列出在部门“SALES”（销售部）工作的员工的姓名，假定不知道销售部的部门编号。---------- SQL>SELECT ENAME FROM EMP 2WHERE DEPTNO=( 3SELECT DEPTNO FROM DEPT 4WHERE DNAME='SALES'); --------或-------- SQL>SELECT ENAME FROM EMP 2JOIN DEPT USING(DEPTNO) 3WHERE DNAME='SALES'; --------或-------- SQL>SELECT ENAME FROM EMP A 2WHERE EXISTS( 3SELECT NULL FROM DEPT B 4WHERE A.DEPTNO=B.DEPTNO 5AND B.DNAME='SALES'); ENAME ---------- WARD MARTIN BLAKE TURNER JAMES 6rows selected --------9．列出薪金高于公司平均薪金的所有员工。---------- SQL>SELECT ENAME FROM EMP 2WHERE SAL>( 3SELECT AVG(SAL) 4FROM EMP); ENAME ---------- JONES BLAKE SCOTT KING FORD EricHu huyong WANGJING 8rows selected --------10．列出与“SCOTT”从事相同工作的所有员工。-------- SQL>SELECT ENAME FROM EMP 2WHERE JOB=(SELECT JOB FROM EMP WHERE ENAME='SCOTT'); --------或-------- SQL>SELECT ENAME FROM EMP A 2WHERE EXISTS( 3SELECT NULL FROM EMP B 4WHERE B.ENAME='SCOTT' 5AND A.JOB=B.JOB); ENAME ---------- SCOTT --------11．列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。--------- SQL>SELECT A.ENAME,A.SAL FROM EMP A 2WHERE A.SAL IN(SELECT B.SAL 3FROM EMP B WHERE B.DEPTNO=30)AND A.DEPTNO<>30; --------或-------- SQL>SELECT ENAME,SAL FROM EMP A 2WHERE A.DEPTNO<>30 3AND EXISTS( 4SELECT NULL FROM EMP B 5WHERE B.DEPTNO=30 6AND A.SAL=B.SAL); ENAME SAL ------------------- --------12．列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。--------- SQL>SELECT ENAME,SAL FROM EMP 2WHERE SAL>(SELECT MAX(SAL)FROM EMP WHERE DEPTNO=30); --------或-------- SQL>SELECT ENAME,SAL FROM EMP 2WHERE DEPTNO<>30 3AND SAL>ALL( 4SELECT SAL FROM EMP 5WHERE DEPTNO=30); ENAME SAL ------------------- JONES2975.00 SCOTT4000.00 KING5000.00 FORD3000.00 EricHu5500.00 huyong5500.00 WANGJING5500.00 7rows selected --------13．列出在每个部门工作的员工数量、平均工资和平均服务期限。--------- SQL>SELECT(SELECT B.DNAME FROM DEPT B WHERE A.DEPTNO=B.DEPTNO) 2AS DEPTNAME,COUNT(DEPTNO)AS DEPTCOUNT,AVG(SAL)AS DEPTAVGSAL 3FROM EMP A GROUP BY DEPTNO; DEPTNAME DEPTCOUNT DEPTAVGSAL ---------------------------------- ACCOUNTING64208.33333 RESEARCH52375 SALES61566.66666 --------14．列出所有员工的姓名、部门名称和工资。--------- SQL>SELECT A.ENAME,(SELECT B.DNAME FROM DEPT B 2WHERE B.DEPTNO=A.DEPTNO) 3AS DEPTNAME,SAL+NVL2(COMM,COMM,0)AS WAGE 4FROM EMP A; ENAME DEPTNAME WAGE ---------------------------------- SMITH RESEARCH800 ALLEN SALES1900 WARD SALES1750 JONES RESEARCH2975 MARTIN SALES2650 BLAKE SALES2850 CLARK ACCOUNTING2450 SCOTT RESEARCH4000 KING ACCOUNTING5000 TURNER SALES1500 ADAMS RESEARCH1100 JAMES SALES950 FORD RESEARCH3000 MILLER ACCOUNTING1300 EricHu ACCOUNTING5514 huyong ACCOUNTING5514 WANGJING ACCOUNTING5514 17rows selected --------15．列出所有部门的详细信息和部门人数。--------- SQL>SELECT A.DEPTNO,A.DNAME,A.LOC,(SELECT COUNT(DEPTNO) 2FROM EMP B WHERE B.DEPTNO=A.DEPTNO 3GROUP BY B.DEPTNO)AS DEPTCOUNT FROM DEPT A; --------或-------- SQL>SELECT DEPTNO,DNAME,LOC,COALESCE(EMP_COUNT,0) 2FROM DEPT LEFT JOIN( 3SELECT COUNT(EMPNO)EMP_COUNT,DEPTNO 4FROM EMP GROUP BY DEPTNO)A 5USING(DEPTNO); DEPTNO DNAME LOC COALESCE(EMP_COUNT,0) ------------------------------------------------------10ACCOUNTING NEW YORK6 20RESEARCH DALLAS5 30SALES CHICAGO6 5050abc50def0 60Developer HaiKou0 110信息科海口0 40OPERATIONS BOSTON0 7rows selected --------16．列出各种工作的最低工资。--------- SQL>SELECT JOB,MIN(SAL) 2FROM EMP 3GROUP BY JOB; --------或-------- SQL>SELECT DISTINCT JOB,SAL 2FROM EMP A 3WHERE NOT EXISTS( 4SELECT NULL FROM EMP B 5WHERE A.JOB=B.JOB 6AND B.SAL JOB SAL ------------------ ANALYST3000.00 CLERK800.00 Developer5500.00 MANAGER2450.00 PM5500.00 PRESIDENT5000.00 SALESMAN1250.00 7rows selected --------17．列出各个部门的MANAGER（经理）的最低薪金。-------- SQL>SELECT DEPTNO,MIN(SAL)FROM EMP WHERE JOB='MANAGER' 2GROUP BY DEPTNO; DEPTNO MIN(SAL) ---------------- 102450 202975 302850 --------或-------- SQL>SELECT DEPTNO,SAL FROM EMP A 2WHERE A.JOB='MANAGER' 3AND EXISTS( 4SELECT NULL FROM EMP B 5WHERE B.DEPTNO=A.DEPTNO); DEPTNO SAL --------------- 202975.00 302850.00 102450.00 --------18．列出所有员工的年工资,按年薪从低到高排序。--------- SQL>SELECT ENAME,(SAL+NVL(COMM,0))*12AS SALPERSAL FROM EMP 2ORDER BY SALPERSAL; --------或-------- SQL>SELECT ENAME,(SAL+COALESCE(COMM,0))*12salpersal 2FROM EMP 3ORDER BY2; ENAME SALPERSAL -------------------- SMITH9600 JAMES11400 ADAMS13200 MILLER15600 TURNER18000 WARD21000 ALLEN22800 CLARK29400 MARTIN31800 BLAKE34200 JONES35700 FORD36000 SCOTT48000 KING60000 EricHu66168 huyong66168 WANGJING66168 17rows selected /*---19．用一条sql语句查询出scott.emp表中每个部门工资前三位的数据，显示结果如下：DEPTNO SAL1SAL2SAL3 ------------------------------ 10550055005500 20400030002975 30285016001500 -------------------------------------------------------------------------------------------------------*/ SQL>SELECT DEPTNO, 2MAX(SAL)SAL1, 3MAX(DECODE(ID,2,SAL))SAL2, 4MIN(SAL)SAL3 5FROM(SELECT SAL,DEPTNO,ID FROM 6(SELECT EMPNO,ENAME,SAL,ROW_NUMBER()OVER( 7PARTITION BY DEPTNO ORDER BY SAL DESC) 8ID,DEPTNO FROM EMP)E 9WHERE E.ID<=3) 10GROUP BY DEPTNO; DEPTNO SAL1SAL2SAL3 ------------------------------------ 10550055005500 20400030002975 30285016001500 ?2011EricHu 原创作品，转贴请注明作者和出处，留此信息。 ------------------------------------------------ cnBlobs:https://www.doczj.com/doc/d38491802.html,/huyong/ CSDN：https://www.doczj.com/doc/d38491802.html,/chinahuyong [推荐]ORACLE SQL： 经典查询练手第二篇(不懂装懂，永世饭桶！) 本文与大家共同讨论与分享ORACLE SQL的一些常用经典查询，欢迎大家补充，同时你认为有那些经典的也可分享出来。在本文中，对每一个问题，你要是认为有什么更好的解决方法也欢迎你及时提出。交流与分享才能共同进步嘛，感谢！ 接上一篇:[推荐]ORACLE SQL：经典查询练手第一篇 本篇相对上篇来说比较简单，如果你对本篇的各测试做得不称心如意的话，我想你是时候给自己充下电了！ 本文使用的实例表结构与表的数据如下： scott.emp员工表结构如下： SQL>DESC SCOTT.EMP; Name Type Nullable Default Comments ------------------------------------------- EMPNO NUMBER(4)员工编号 ENAME VARCHAR2(10)Y员工姓名 JOB VARCHAR2(9)Y职位 MGR NUMBER(4)Y上级编号 HIREDATE DATE Y雇佣日期 SAL NUMBER(7,2)Y薪金 COMM NUMBER(7,2)Y佣金 DEPTNO NUMBER(2)Y所在部门编号 --提示：工资＝薪金＋佣金 scott.dept部门表 SQL>DESC SCOTT.DEPT; Name Type Nullable Default Comments ----------------------------------------- DEPTNO NUMBER(3)部门编号 DNAME VARCHAR2(14)Y部门名称 LOC VARCHAR2(13)Y地点 scott.emp表的现有数据如下： SQL>SELECT*FROM SCOTT.EMP; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ----------------------------------------------------------------7369SMITH CLERK79021980-12-17800.0020 7499ALLEN SALESMAN76981981-2-201600.00300.0030 7521WARD SALESMAN76981981-2-221250.00500.0030 7566JONES MANAGER78391981-4-22975.0020 7654MARTIN SALESMAN76981981-9-281250.001400.0030 7698BLAKE MANAGER78391981-5-12850.0030 7782CLARK MANAGER78391981-6-92450.0010 7788SCOTT ANALYST75661987-4-194000.0020 7839KING PRESIDENT1981-11-175000.0010 7844TURNER SALESMAN76981981-9-81500.000.0030 7876ADAMS CLERK77881987-5-231100.0020 7900JAMES CLERK76981981-12-3950.0030 7902FORD ANALYST75661981-12-33000.0020 7934MILLER CLERK77821982-1-231300.0010 102EricHu Developer14552011-5-2615500.0014.0010 104huyong PM14552011-5-2615500.0014.0010 105WANGJING Developer14552011-5-2615500.0014.0010 17rows selected Scott.dept表的现有数据如下： SQL>SELECT*FROM SCOTT.DEPT; DEPTNO DNAME LOC --------------------------------- 110信息科海口 10ACCOUNTING NEW YORK 20RESEARCH DALLAS 30SALES CHICAGO 40OPERATIONS BOSTON 5050abc50def 60Developer HaiKou 7rows selected 用SQL完成以下问题列表： 1.找出EMP表中的姓名（ENAME）第三个字母是A的员工姓名。 2.找出EMP表员工名字中含有A和N的员工姓名。 3.找出所有有佣金的员工，列出姓名、工资、佣金，显示结果按工资从小到大，佣金从大到小。 4.列出部门编号为20的所有职位。 5.列出不属于SALES的部门。 6.显示工资不在1000到1500之间的员工信息：名字、工资，按工资从大到小排序。 7.显示职位为MANAGER和SALESMAN，年薪在15000和20000之间的员工的信息：名字、职位、年薪。 8.说明以下两条SQL语句的输出结果： SELECT EMPNO,COMM FROM EMP WHERE COMM IS NULL; SELECT EMPNO,COMM FROM EMP WHERE COMM=NULL; 9.让SELECT语句的输出结果为 SELECT*FROM SALGRADE; SELECT*FROM BONUS; SELECT*FROM EMP; SELECT*FROM DEPT; …… 列出当前用户有多少张数据表，结果集中存在多少条记录。 10.判断SELECT ENAME,SAL FROM EMP WHERE SAL>'1500'是否抱错，为什么？ 各试题解答如下（欢迎大家指出不同的方法或建议！）： --------1.找出EMP表中的姓名（ENAME）第三个字母是A的员工姓名。--------- SQL>SELECT ENAME FROM SCOTT.EMP WHERE ENAME LIKE'__A%'; ENAME ---------- ADAMS BLAKE CLARK -------2.找出EMP表员工名字中含有A和N的员工姓名。---------- SQL>SELECT ENAME FROM SCOTT.EMP 2WHERE ENAME LIKE'%A%'AND ENAME LIKE'%N%'; ENAME ----------
相关主题
文本预览