Oracle中绝对经典的单表查询练习，非常值得一练
来源：互联网 发布：淘宝网男士的鞋子 编辑：程序博客网 时间：2024/04/20 06:06
这是一个学习Oracle的查询语句的一个非常好的练习，其中包括了一些简单的查询，已经具有Oracle自身特点的单行函数的应用，非常值得一看。
--1、选择部门30的所有员工SELECT * FROM EMP WHERE deptno=30;--结果 SELECT * FROM EMP WHERE deptno=30; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO----- ---------- --------- ----- ----------- --------- --------- ------ 7499 ALLEN SALESMAN 7698 20-二月-81 1600.00 300.00 30 7521 WARD SALESMAN 7698 22-二月-81 1250.00 500.00 30 7654 MARTIN SALESMAN 7698 28-九月-81 1250.00 1400.00 30 7698 BLAKE MANAGER 7839 01-五月-81 2850.00 30 7844 TURNER SALESMAN 7698 08-九月-81 1500.00 0.00 30 7900 JAMES CLERK 7698 03-十二月-81 950.00 30 --2、列出所有办事员（CLERK）的姓名，员工编号和部门编号SELECT ename 姓名,empno 员工编号,deptno 部门编号 FROM emp WHERE job='CLERK';--结果姓名 员工编号 部门编号---------- -------- --------SMITH 7369 20ADAMS 7876 20JAMES 7900 30MILLER 7934 10--3、找出佣金高于薪金的员工SELECT * FROM EMP WHERE NVL(comm,0)>sal;--结果EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO----- ---------- --------- ----- ----------- --------- --------- ------ 7654 MARTIN SALESMAN 7698 28-九月-81 1250.00 1400.00 30--4、找出佣金高于薪金60%的员工SELECT * FROM EMP WHERE NVL(comm,0)>sal*0.6;--结果EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO----- ---------- --------- ----- ----------- --------- --------- ------ 7654 MARTIN SALESMAN 7698 28-九月-81 1250.00 1400.00 30--5、找出部门10中的所有经理（MANAGER）和部门20的所有办事员（CLERK）的详细资料SELECT * FROM EMP WHERE (job='MANAGER' AND deptno=10) OR (job='CLERK' AND deptno=20);--结果EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO----- ---------- --------- ----- ----------- --------- --------- ------ 7369 SMITH CLERK 7902 17-十二月-80 800.00 20 7782 CLARK MANAGER 7839 09-六月-81 2450.00 10 7876 ADAMS CLERK 7788 23-五月-87 1100.00 20 --6、找出部门10的所有经理（MANAGER），部门20中所有办事员（CLERK），既不是办事员也不是经理但是薪金大于2000的所有员工的详细资料SELECT * FROM EMP WHERE (job='MANAGER' AND deptno=10) OR (job='CLERK' AND deptno=20) OR (job NOT IN('MANAGER','CLERK') AND sal>2000);--结果EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO----- ---------- --------- ----- ----------- --------- --------- ------ 7369 SMITH CLERK 7902 17-十二月-80 800.00 20 7782 CLARK MANAGER 7839 09-六月-81 2450.00 10 7788 SCOTT ANALYST 7566 19-四月-87 3000.00 20 7839 KING PRESIDENT 17-十一月-81 5000.00 10 7876 ADAMS CLERK 7788 23-五月-87 1100.00 20 7902 FORD ANALYST 7566 03-十二月-81 3000.00 20 6 rows selected--7、找出收取佣金的员工的员工的不同工作SELECT DISTINCT(job) FROM EMP WHERE comm IS NOT NULL AND comm !=0;--结果 JOB---------SALESMAN --8、找出不取佣金或者是收取佣金小于100的员工详细信息SELECT * FROM EMP WHERE comm IS NULL OR comm<100;--结果EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO----- ---------- --------- ----- ----------- --------- --------- ------ 7369 SMITH CLERK 7902 17-十二月-80 800.00 20 7566 JONES MANAGER 7839 02-四月-81 2975.00 20 7698 BLAKE MANAGER 7839 01-五月-81 2850.00 30 7782 CLARK MANAGER 7839 09-六月-81 2450.00 10 7788 SCOTT ANALYST 7566 19-四月-87 3000.00 20 7839 KING PRESIDENT 17-十一月-81 5000.00 10 7844 TURNER SALESMAN 7698 08-九月-81 1500.00 0.00 30 7876 ADAMS CLERK 7788 23-五月-87 1100.00 20 7900 JAMES CLERK 7698 03-十二月-81 950.00 30 7902 FORD ANALYST 7566 03-十二月-81 3000.00 20 7934 MILLER CLERK 7782 23-一月-82 1300.00 10 11 rows selected--9、找出各月的倒数第三天受雇佣的员工--每个员工的雇佣时间是不一样的，所有需要找出每个员工雇佣的时间所在月份的最后一天，--之后按照‘日期-数字’的方式求出前三天的日期，这个日期必须和雇佣日期相符才能满足条件。SELECT LAST_DAY(hiredate),EMP.* FROM EMP WHERE LAST_DAY(hiredate)-2=hiredate;--结果LAST_DAY(HIREDATE) EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO------------------ ----- ---------- --------- ----- ----------- --------- --------- ------30-九月-81 7654 MARTIN SALESMAN 7698 28-九月-81 1250.00 1400.00 30--10、找出12早于年前受雇的员工--先使用MONTHS_BETWEEN(SYSDATE,hiredate)求出雇佣的月份，然后除以12的到雇佣的年份SELECT MONTHS_BETWEEN(SYSDATE,hiredate)/12 雇佣年份,EMP.* FROM EMP WHERE MONTHS_BETWEEN(SYSDATE,hiredate)/12 > 12;--结果 雇佣年份 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---------- ----- ---------- --------- ----- ----------- --------- --------- ------31.5449952 7369 SMITH CLERK 7902 17-十二月-80 800.00 2031.3702640 7499 ALLEN SALESMAN 7698 20-二月-81 1600.00 300.00 3031.3648877 7521 WARD SALESMAN 7698 22-二月-81 1250.00 500.00 30 31.25 7566 JONES MANAGER 7839 02-四月-81 2975.00 2030.7654253 7654 MARTIN SALESMAN 7698 28-九月-81 1250.00 1400.00 3031.1713393 7698 BLAKE MANAGER 7839 01-五月-81 2850.00 3031.0665006 7782 CLARK MANAGER 7839 09-六月-81 2450.00 1025.2062855 7788 SCOTT ANALYST 7566 19-四月-87 3000.00 2030.6283285 7839 KING PRESIDENT 17-十一月-81 5000.00 1030.8191887 7844 TURNER SALESMAN 7698 08-九月-81 1500.00 0.00 3025.1121995 7876 ADAMS CLERK 7788 23-五月-87 1100.00 2030.5826296 7900 JAMES CLERK 7698 03-十二月-81 950.00 3030.5826296 7902 FORD ANALYST 7566 03-十二月-81 3000.00 2030.4455328 7934 MILLER CLERK 7782 23-一月-82 1300.00 10 14 rows selected --11、以首字母大写的方式显示员工姓名 SELECT INITCAP(ename) FROM emp;--结果INITCAP(ENAME)--------------SmithAllenWardJonesMartinBlakeClarkScottKingTurnerAdamsJamesFordMiller 14 rows selected--12、显示姓名正好为5个长度的所有员工SELECT * FROM EMP WHERE LENGTH(ename) = 5;--结果EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO----- ---------- --------- ----- ----------- --------- --------- ------ 7369 SMITH CLERK 7902 17-十二月-80 800.00 20 7499 ALLEN SALESMAN 7698 20-二月-81 1600.00 300.00 30 7566 JONES MANAGER 7839 02-四月-81 2975.00 20 7698 BLAKE MANAGER 7839 01-五月-81 2850.00 30 7782 CLARK MANAGER 7839 09-六月-81 2450.00 10 7788 SCOTT ANALYST 7566 19-四月-87 3000.00 20 7876 ADAMS CLERK 7788 23-五月-87 1100.00 20 7900 JAMES CLERK 7698 03-十二月-81 950.00 30 8 rows selected--13、显示带有'R'的员工的详细SELECT * FROM EMP WHERE ename LIKE '%R%';--结果EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO----- ---------- --------- ----- ----------- --------- --------- ------ 7521 WARD SALESMAN 7698 22-二月-81 1250.00 500.00 30 7654 MARTIN SALESMAN 7698 28-九月-81 1250.00 1400.00 30 7782 CLARK MANAGER 7839 09-六月-81 2450.00 10 7844 TURNER SALESMAN 7698 08-九月-81 1500.00 0.00 30 7902 FORD ANALYST 7566 03-十二月-81 3000.00 20 7934 MILLER CLERK 7782 23-一月-82 1300.00 10 6 rows selected--14、显示员工姓名的前三个字符SELECT ename 全名,SUBSTR(ename,0,3) 前三个字符 FROM EMP;--结果全名 前三个字符---------- ----------SMITH SMIALLEN ALLWARD WARJONES JONMARTIN MARBLAKE BLACLARK CLASCOTT SCOKING KINTURNER TURADAMS ADAJAMES JAMFORD FORMILLER MIL 14 rows selected --15、显示所有员工的姓名，用‘a’替换所有的'A'SELECT ename 原名,REPLACE(ename,'A','a') 大A替换为小a FROM EMP;--结果原名 大A替换为小A---------- ------------SMITH SMITHALLEN aLLENWARD WaRDJONES JONESMARTIN MaRTINBLAKE BLaKECLARK CLaRKSCOTT SCOTTKING KINGTURNER TURNERADAMS aDaMSJAMES JaMESFORD FORDMILLER MILLER 14 rows selected--16、--列出满10年雇佣期限的员工的详细信息SELECT * FROM EMP WHERE MONTHS_BETWEEN(SYSDATE,hiredate)/12>10;--结果EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO----- ---------- --------- ----- ----------- --------- --------- ------ 7369 SMITH CLERK 7902 17-十二月-80 800.00 20 7499 ALLEN SALESMAN 7698 20-二月-81 1600.00 300.00 30 7521 WARD SALESMAN 7698 22-二月-81 1250.00 500.00 30 7566 JONES MANAGER 7839 02-四月-81 2975.00 20 7654 MARTIN SALESMAN 7698 28-九月-81 1250.00 1400.00 30 7698 BLAKE MANAGER 7839 01-五月-81 2850.00 30 7782 CLARK MANAGER 7839 09-六月-81 2450.00 10 7788 SCOTT ANALYST 7566 19-四月-87 3000.00 20 7839 KING PRESIDENT 17-十一月-81 5000.00 10 7844 TURNER SALESMAN 7698 08-九月-81 1500.00 0.00 30 7876 ADAMS CLERK 7788 23-五月-87 1100.00 20 7900 JAMES CLERK 7698 03-十二月-81 950.00 30 7902 FORD ANALYST 7566 03-十二月-81 3000.00 20 7934 MILLER CLERK 7782 23-一月-82 1300.00 10 14 rows selected--显示员工的详细资料，并按姓名排序SELECT * FROM EMP ORDER BY ename;--结果SELECT * FROM EMP ORDER BY ename; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO----- ---------- --------- ----- ----------- --------- --------- ------ 7876 ADAMS CLERK 7788 23-五月-87 1100.00 20 7499 ALLEN SALESMAN 7698 20-二月-81 1600.00 300.00 30 7698 BLAKE MANAGER 7839 01-五月-81 2850.00 30 7782 CLARK MANAGER 7839 09-六月-81 2450.00 10 7902 FORD ANALYST 7566 03-十二月-81 3000.00 20 7900 JAMES CLERK 7698 03-十二月-81 950.00 30 7566 JONES MANAGER 7839 02-四月-81 2975.00 20 7839 KING PRESIDENT 17-十一月-81 5000.00 10 7654 MARTIN SALESMAN 7698 28-九月-81 1250.00 1400.00 30 7934 MILLER CLERK 7782 23-一月-82 1300.00 10 7788 SCOTT ANALYST 7566 19-四月-87 3000.00 20 7369 SMITH CLERK 7902 17-十二月-80 800.00 20 7844 TURNER SALESMAN 7698 08-九月-81 1500.00 0.00 30 7521 WARD SALESMAN 7698 22-二月-81 1250.00 500.00 30 14 rows selected--18、显示员工的姓名和受雇日期，并按照老的员工排在前面的方式显示出来SELECT ename,hiredate FROM EMP ORDER BY hiredate ASC;--结果ENAME HIREDATE---------- -----------SMITH 17-十二月-80ALLEN 20-二月-81WARD 22-二月-81JONES 02-四月-81BLAKE 01-五月-81CLARK 09-六月-81TURNER 08-九月-81MARTIN 28-九月-81KING 17-十一月-81JAMES 03-十二月-81FORD 03-十二月-81MILLER 23-一月-82SCOTT 19-四月-87ADAMS 23-五月-87 14 rows selected--19、显示所有员工的姓名、工作和薪金，按工作的降序排列，工作相同则按照薪金的升序排列SELECT ename,job,sal FROM emp ORDER BY job DESC,sal ASC;--结果ENAME JOB SAL---------- --------- ---------WARD SALESMAN 1250.00MARTIN SALESMAN 1250.00TURNER SALESMAN 1500.00ALLEN SALESMAN 1600.00KING PRESIDENT 5000.00CLARK MANAGER 2450.00BLAKE MANAGER 2850.00JONES MANAGER 2975.00SMITH CLERK 800.00JAMES CLERK 950.00ADAMS CLERK 1100.00MILLER CLERK 1300.00SCOTT ANALYST 3000.00FORD ANALYST 3000.00 14 rows selected --20、显示所有员工的姓名、加入公司的年份和月份、按接受所在雇佣月排序，若月的相同则按最早年份的员工排在最前面--本程序需要求出所雇的日期的年份和月份，然后再来显示SELECT ename 姓名,TO_CHAR(hiredate,'yyyy') 年份， TO_CHAR(hiredate,'mm') 月份 FROM emp ORDER BY 月份,年份;--结果姓名 年份 月份---------- ---- ----MILLER 1982 01ALLEN 1981 02WARD 1981 02JONES 1981 04SCOTT 1987 04BLAKE 1981 05ADAMS 1987 05CLARK 1981 06TURNER 1981 09MARTIN 1981 09KING 1981 11SMITH 1980 12JAMES 1981 12FORD 1981 12 14 rows selected--21、显示一个月为30天的情况下，所有员工的日薪，忽略余数SELECT ename 姓名,sal 月薪,TRUNC(sal/30) 日薪 FROM EMP;--结果姓名 月薪 日薪---------- --------- ----------SMITH 800.00 26ALLEN 1600.00 53WARD 1250.00 41JONES 2975.00 99MARTIN 1250.00 41BLAKE 2850.00 95CLARK 2450.00 81SCOTT 3000.00 100KING 5000.00 166TURNER 1500.00 50ADAMS 1100.00 36JAMES 950.00 31FORD 3000.00 100MILLER 1300.00 43--22、找出在（任何年份的）2月受雇的员工SELECT * FROM EMP WHERE TO_CHAR(hiredate,'mm') = 02;--结果EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO----- ---------- --------- ----- ----------- --------- --------- ------ 7499 ALLEN SALESMAN 7698 20-二月-81 1600.00 300.00 30 7521 WARD SALESMAN 7698 22-二月-81 1250.00 500.00 30 --23、对于每个员工显示其来到公司的天数 SELECT ename 姓名,SYSDATE-hiredate 来公司的天数 FROM EMP;--结果姓名 来公司的天数---------- ------------SMITH 11520.760555ALLEN 11455.760555WARD 11453.760555JONES 11414.760555MARTIN 11235.760555BLAKE 11385.760555CLARK 11346.760555SCOTT 9206.7605555KING 11185.760555TURNER 11255.760555ADAMS 9172.7605555JAMES 11169.760555FORD 11169.760555MILLER 11118.760555 14 rows selected--24、显示姓名字段的任何位置包含有’A‘的员工 SELECT * FROM EMP WHERE ename LIKE '%A%';--结果EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO----- ---------- --------- ----- ----------- --------- --------- ------ 7499 ALLEN SALESMAN 7698 20-二月-81 1600.00 300.00 30 7521 WARD SALESMAN 7698 22-二月-81 1250.00 500.00 30 7654 MARTIN SALESMAN 7698 28-九月-81 1250.00 1400.00 30 7698 BLAKE MANAGER 7839 01-五月-81 2850.00 30 7782 CLARK MANAGER 7839 09-六月-81 2450.00 10 7876 ADAMS CLERK 7788 23-五月-87 1100.00 20 7900 JAMES CLERK 7698 03-十二月-81 950.00 30 7 rows selected--25、以月份的方式显示所有员工的服务年限--结果分析：-- 第一步：求出员工的雇佣年数，雇佣的月数除以12SELECT ename,TRUNC(MONTHS_BETWEEN(SYSDATE,hiredate)/12) 年数 FROM EMP; ENAME 年数---------- ----------SMITH 31ALLEN 31WARD 31JONES 31MARTIN 30BLAKE 31CLARK 31SCOTT 25KING 30TURNER 30ADAMS 25JAMES 30FORD 30MILLER 30 14 rows selected-- 第二步：求除去的出去年份之后的月份 SELECT ename,TRUNC(MONTHS_BETWEEN(SYSDATE,hiredate)/12) 年数,TRUNC(MOD(MONTHS_BETWEEN(SYSDATE,hiredate),12)) 月数 FROM emp; ENAME 年数 月数---------- ---------- ----------SMITH 31 6ALLEN 31 4WARD 31 4JONES 31 3MARTIN 30 9BLAKE 31 2CLARK 31 0SCOTT 25 2KING 30 7TURNER 30 9ADAMS 25 1JAMES 30 6FORD 30 6MILLER 30 5 14 rows selected-- 第三步：求出的除去年份和月份之后的天数SELECT ename,TRUNC(MONTHS_BETWEEN(SYSDATE,hiredate)/12) 年数,TRUNC(MOD(MONTHS_BETWEEN(SYSDATE,hiredate),12)) 月数,TRUNC(SYSDATE-ADD_MONTHS(hiredate,MONTHS_BETWEEN(SYSDATE,hiredate))) 天数 FROM emp;ENAME 年数 月数 天数---------- ---------- ---------- ----------SMITH 31 6 15ALLEN 31 4 12WARD 31 4 10JONES 31 3 0MARTIN 30 9 4BLAKE 31 2 1CLARK 31 0 23SCOTT 25 2 13KING 30 7 15TURNER 30 9 24ADAMS 25 1 9JAMES 30 6 29FORD 30 6 29MILLER 30 5 9 14 rows selected--最后结果SELECT ename,TO_CHAR(SYSDATE,'YYYY-MM-DD'),TO_CHAR(hiredate,'YYYY-MM-DD') 雇佣日期,TRUNC(MONTHS_BETWEEN(SYSDATE,hiredate)/12) 年数,TRUNC(MOD(MONTHS_BETWEEN(SYSDATE,hiredate),12)) 月数,TRUNC(SYSDATE-ADD_MONTHS(hiredate,MONTHS_BETWEEN(SYSDATE,hiredate))) 天数 FROM emp;--结果 ENAME TO_CHAR(SYSDATE,'YYYY-MM-DD') 雇佣日期 年数 月数 天数---------- ----------------------------- ---------- ---------- ---------- ----------SMITH 2012-07-02 1980-12-17 31 6 15ALLEN 2012-07-02 1981-02-20 31 4 12WARD 2012-07-02 1981-02-22 31 4 10JONES 2012-07-02 1981-04-02 31 3 0MARTIN 2012-07-02 1981-09-28 30 9 4BLAKE 2012-07-02 1981-05-01 31 2 1CLARK 2012-07-02 1981-06-09 31 0 23SCOTT 2012-07-02 1987-04-19 25 2 13KING 2012-07-02 1981-11-17 30 7 15TURNER 2012-07-02 1981-09-08 30 9 24ADAMS 2012-07-02 1987-05-23 25 1 9JAMES 2012-07-02 1981-12-03 30 6 29FORD 2012-07-02 1981-12-03 30 6 29MILLER 2012-07-02 1982-01-23 30 5 9 14 rows selected
- Oracle中绝对经典的单表查询练习，非常值得一练
- ruby初级入门知识（绝对经典，非常值得看）
- Oracle基础知识3 : 单表查询练习
- Oracle基础知识3 : 单表查询练习
- ORACLE SQL：经典查询练习
- Java文件操作大全（绝对的经典，值得收藏！）
- Java文件操作大全（绝对的经典，值得收藏！）
- 10年软件开发中获得的最宝贵的经验！非常值得你一读
- Oracle SQL：经典查询练习（3）
- oracle经典查询练手-练习1
- oracle经典查询练手-练习2
- oracle经典查询练手-练习3
- oracle经典查询练手-练习4
- oracle经典查询练手-练习5
- 绝对经典的 Oracle 几个使用技巧
- 绝对经典的 Oracle 几个使用技巧
- mysql 单表查询练习
- 多表查询经典练习
- 拷贝构造函数之我见
- 上线仅两月的免费游戏Auto Club Revolution ARPPU达24美元
- 为什么我的myeclipse没有Add Web Service Capabilities
- 手动批量处理 “ORA-02292: 违反完整约束条件 - 已找到子记录”
- 关于PHP5.3作废函数的处理方法
- Oracle中绝对经典的单表查询练习，非常值得一练
- 关于二维离散傅立叶变换计算的一些东西
- c#程序的远程调试
- uva 408 - Uniform Generator
- 管道通信
- mysql 备份与还原
- Bash history命名配置及使用
- HttpClient 学习整理
- iframe---assignRoles
