作者：胡勇 欢迎大家一起分享知识，本文档事Oracle的一些查询知识。欢迎大家扩充。
占在巨人的肩上〖通过知识共享树立个人品牌〗 推荐] 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>§TEc2卫RoM三MP; 姓名:胡勇 网名: Erich 电话:151089377903/58 QQ:80368704 E-mail:80368704@qq.com Hik:http://www.cnblogs.com/huyongihttp://blog.csdn.netichinahuyong 占在巨人的肩上〖通过知识共享树立个人品牌〗 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 7369 SMITH CLERK 79021980-12-17 800.00 7499 ALLEN SALESMAN76981981-2-20 1600.00 30 7521 WARD SALESMAN76981981-2-22 500.00 7566 JONES MANAGER78391981-4-2 2975.00 7654 MARTIN SALESMAN76981981-9-28 1250.001400.00 7698 BLAKE MANAGER 7839 1981-5 2850.00 30 7782 CLARK MANAGER78391981-6-9 2450.00 7788 SCOTT ANALYST75661987-4-194000.00 20 7839 KING PRESIDENT 1981-11-175000.00 7844 TURNER SALESMAN 76981981-9-8 1500.00 0.00 7876 ADAMS CLERK 77881987-5-23 7900 JAMES CLERK 76981981-123 950.00 000 7902 FORD ANALYST75661981-12-3 3000.00 20 7934 MILLER CLERK77821982-1-231300.00 102 Erich Deve loper14552011-5-2615500.00 14.00 104 huyong 14552011-5-2615500.00 14.00 10 105 WANGJING Developer14552011-5-2615500.0014.00 17 rows selected Scott,dept表的现有数据如下 SOL> SELECT FROM DEPT DEPTNO DNAME LOC 一一一--— 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON C 50def 60 Developer HaiKou 6 用SQL完成以下问题列表: 列出至少有一个员工的所有部门 2.列出薪金比“ SMITH”多的所有员⊥ 3.列出所有员工的姓名及其直接上级的姓名 列出受雇日期早于其直接上级的所有员工 姓名:胡勇 网名: Erich 电话:151089377904/58 QQ:80368704 E-mail:80368704@qq.com Hik:http://www.cnblogs.com/huyongihttp://blog.csdn.netichinahuyong 占在巨人的肩上〖通过知识共享树立个人品牌〗 5.列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门 6.列出所有“ CLERK”(办事员)的姓名及其部门名称。 7.列出最低薪金大」1500的各种工作 8.列岀在部门“ SALES”(销售部)工作的员工的姓名,假定不知道诮售部的部门编号。 9.列出薪金高于公司平均薪金的所有员⊥ 10.列出与“ SCOTT”从事相同工作的所有员工。 l1.列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金 12.列出薪金高」在部门30工作的所有员工的新金的员工姓名和薪金 13.列出在每个部门工作的员工数量、平均工资和平均服务期限 14.列出所有员工的姓名、部门名称和工资 15.列出所有部门的详细信息和部门人数。 16.列出各种工作的最低工资。 17.列出各个部门的 MANAGER(经理)的最低薪金。 18.列出所有员工的年工资按年薪从低到高排序。 19.用一条sqi语句査询出 scott. emp表中每个部门工资前三位的数据,显示结果如下 DEPTNO SAL1 SAL2 SAL3 10 5500 5500 4000 3000 2975 2850 1600 1.列出至少有个员的所有部门。-- SQL> SELECT DNAME ROM DEPT 2 WIEHRE DEPTNO工N SELECT DE PTNO FROM EMP); DNAME RESEARCH SALES ∧ CCOUNTING 或 sQT> SELEC DNAⅣ F, FROM DEPT WHIERE DEPTNO玉N(§ EC DEPTNO 2 FROM EMP GROUP BY DEPTNO HAVING COUN(DEPTNO)>=1)i DNAME ACCOUNTING RESEARCH SALES 姓名:胡勇 网名: Erich 电话:151089377905/58 QQ:80368704 E-mail:80368704@qq.com Hik:http://www.cnblogs.com/huyongihttp://blog.csdn.netichinahuyong 占在巨人的肩上〖通过知识共享树立个人品牌〗 或 QT> SELECT DNAME, FROM DEPT A 2 HERE EXISTS SELECH NUL ROM三MPB 4 WHERE B. DEPNO =A DE PINO ); DNAME RESEARCH SALES ACCOUNTING 2.列出薪金比“ SMITH”多的所有员工 SQL> SELECT FROM EMP 2 SAL (SELECT SAL FROM EMP WHIERE ENAME =SMITH) 一或 SQL> SELEC*卫RoM彐MPA 2 WHER EXISHS SLEC NUT卫Ro三MPB WHERE B, SAL A SAL 5 AND B FNAME SMITH 或 sQL>srRc*卫ROM三MPA, ( SELECT SAL AS SALARY FROM EMP TIERE ENAME=SMTH’)B 4 WHERE ASAT>B SALARY; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 7499 ALLEN SALESMAN76981981-2-20 1600.00300.00 30 7521 WARD SALESMAN76981981-2-22 1250.00500.00 7566 JONES MANAGER78391981-4-2 2975.00 20 7654 MARTIN SALESMAN 76981981-9-28 1250.001400.00 姓名:胡勇 网名: Erich 电话:151089377906/58 QQ:80368704 E-mail:80368704@qq.com Hik:http://www.cnblogs.com/huyongihttp://blog.csdn.netichinahuyong 占在巨人的肩上〖通过知识共享树立个人品牌〗 7698 BLAKE MANAGER78391981-5-1 2850.00 30 7782 CLARK MANAGER78391981-6-9 2450.00 7788 SCOTT ANALYST756619874-19 4000.00 20 7839 KING PRESIDENT 1981-11-175000.00 7844 TURNER SALESMAN 76981981-9-8 1500.00 0.00 30 7876 ADAMS CLERK 77881987-5-23 1100.00 7900 JAMES CLERK 76981981-12-3 950.00 7902 FORD ANALYST75661981-12-3 3000.00 7 934 MILLER CLERK 77821982-1-23 1300.00 102 Erich Developer14552011-5-2615500.00 14.00 104 huyong 14552011-5-2615500.00 14.00 105 WANGJING Developer14552011-5-2615500.0014.0010 16 rows selected 3.列出所有员工的姓名及其直接上级的姓名。 SQL> SELECT A ENAME,(SELECT ENAME FROM EMP B WHERE B. EMENO-A MGR) 2. AS BOSS NAME, FROM FMP A: 或 SQL> SELECT E ENAME,M ENAME BOSS NAME 2卫BFM武EUo工 N EMP N 3 oN E MGR = M. EMPNC ENAME BOSS NAME SMITH FORD ALLEN BLAKE WARD BLAKE Jones KING MARTIN BLAKE BLAKE KING CLARK KING SCOTT JON KING TURNER BLAKE ADAMS SCOTT JAMES BLAKE FORD JONES M⊥LLER CLARK Erich hu nayong 姓名:胡勇 网名: Erich 电话:151089377907/58 QQ:80368704 E-mail:80368704@qq.com Hik:http://www.cnblogs.com/huyongihttp://blog.csdn.netichinahuyong 占在巨人的肩上〖通过知识共享树立个人品牌〗 WANGJING 17 rows selected 4.列出受雇日期早于其直接上级的所有员工 工>ScA, ENAME ROM EMP 2WR烈. HTREDATE< 3(SELECT HIREDATE FROM EMP B WHERE B EMPNO=A. MGR) 或 SQL> SELECT EIMP1.ENAME, FROM EMP EMPT 2 WHIERE EMP1 MGR IS NOT NULL 3 AND NOT EXISTS 4 SELECI NULT FROM EMP EMP 2 WHIR: EMP1. MGR= EMP2 EMPNO AND EMF1HIREDATE EMP2 HIREDATE); ENAME SMITH ALLEN WARD BLAKE CLARK 6 rows selected --5.列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门 SQL> SELECT A. NAME,B EMPNO , B ENAME, B. vOB, B MgR 2B.H工R三DATE,B,SA,B, DE PTNO 3 FROM DEPT A LE JOIN EMP B ON A. DEPTNO-B DEPTNO: SQL SELECT A DNAME, DEPTNO, B EMPNO, B ENAME B UCB, B MGR, B. ENAME, 3. SAL, B. HIREDATE aDP中A“工 N EMP B I工NG(DP中NO); DNAME EMPNO ENAME MGR HIREDATE SAL DEPTNO RESEARCH 7369 SMITH CLERK 79021980-12-17800.0020 SALES 7499 ALLEN SALESMAN76981981-220 1600.00 SALES 7521 WARD SALESMAN76981981-2-22 1250.00 30 RESEARCH 7566 JONES MANAGER 7839 1981-4-2 975.00 姓名:胡勇 网名: Erich 电话:151089377908/58 QQ:80368704 E-mail:80368704@qq.com Hik:http://www.cnblogs.com/huyongihttp://blog.csdn.netichinahuyong 占在巨人的肩上〖通过知识共享树立个人品牌〗 SALES 7654 MARTIN SALESMAN76981981-928 1250.00 30 SALES 7698 BLAKE MANAGER783919815-1 2850.00 30 ACCOUNTING 7782 CLARK MANAGER78391981-6-9 2450.00 10 RESEARCH 7788 SCOTT ANALYST75661987-4-19 4000.00 ACCOUNTING 7839 KING PRES IDENT 1981-11-175000.00 10 SALES 7844 TURNER SALESMAN 76981981-9-8 1500.00 RESEARCH 7876 ADAMS CLERK 77881987-5-231100.0020 SALES 7900 JAMES CLERK 76981981-12-3 950.00 RESEARCH 7902 FORD ANALYST75661981-12-3 3000.00 20 ACCOUNTING 7934 MILLER CLERK 77821982123 1300.00 ACCOUNTING 102 EricHu Developer14552011-5-2615500.00 ACCOUNTING 104 huyong PM 14552011-5-2615500.00 ACCOUNTING 105 WANGJING Developer 1455 2011-5-26 1 5500. 00 10 sabc OPERATIONS Developer 20 rows selected 6.列出所有“ CLERK”(办事员)的姓名及其部门名称。- SOL> SELECT A ENAME, B DNAME FROM EMP A JOIN DEPT B 2 ON A DEPTNO=B DEPTNO AND A. JOR= CLERK SOL> SELECT ENANE DNAME 2. FROM SELECT ENAME DEPTNO FROM EMP WHERE JOB =CLERK A JOIN DEPT 6Us工NG( DEPTN○) ENAME DNAME SMITH RESEARCH ADAMS RESEARCH JAMES SALES MILLER ACCOUNTING --7.列出最低薪金大于1500的各种工作。- SQL> SELECI D工sE工Nc2JOB為 S HIGHSALUOB 姓名:胡勇 网名: Erich 电话:151089377909/58 QQ:80368704 E-mail:80368704@qq.com Hik:http://www.cnblogs.com/huyongihttp://blog.csdn.netichinahuyong 占在巨人的肩上〖通过知识共享树立个人品牌〗 2 FROM EMP GROUP BY JOB HAVENG MIN (SAL) 1500 或 QT>盛cD工s工 NC TOB ROM FMP A 2 WHERE A SAL >1500 3 AND NOT EXISTS 4 SELEC NULI FROM EMP B HEIR B JOB= A JOB 6 AND B. SAL. A SAL)i HIGHSALJOB ANALYST Developer MANAGER PRES IDENT --8.列出在部门“ SALES”(销售部)工作的员工的姓名 假定不知道销售部的部门编号 SQL> SELECT ENAME, FROM FMP 2MIRR2 DEPTN○=( SELECH DEPTNO FROM DEPT N:IERE DNAME -SALES)i 或 SOL> SELECT ENAME FROM EMP 2 JOIN DEPT USING (DEPTNO) 3 IMR DNAME =I SALES 或 SQL> SELECT FINAME, FROM EMP A 2 HR ISS SELEC NULL FROM DEPT B WHERE DEPTNO = 3. DEPTNO 5 AND B DNAME SALES )i ENAME 姓名:胡勇 网名: Erich 电话:1510893779010/58 QQ:80368704 E-mail:80368704@qq.com Hik:http://www.cnblogs.com/huyongihttp://blog.csdn.netichinahuyong