2
ORACLE SQL 经典查询练手系列文章实践笔记
source link: https://wsgzao.github.io/post/oracle-sql/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
ORACLE SQL 经典查询练手系列文章实践笔记
原作者 @EricHu 写的 Oracle SQL 经典查询联手系列文章已经很详细了，我根据 Oracle 11g 的实践过程重新做了梳理，之后会在扩展阅读中增加一部分 Oracle 官方培训教材供大家参考学习。
不懂装懂，永世饭桶
2015 年 03 月 13 日 - 撰写初稿
阅读原文 - https://wsgzao.github.io/post/oracle-sql/
- ORACLE SQL 经典查询练手系列文章收尾(目录篇) - http://www.cnblogs.com/huyong/archive/2011/06/29/2092124.html
-- 输出每页行数，缺省为 24，为了避免分页，可设定为 0
set pagesize 0
-- 输出一行字符个数，缺省为 80
set linesize 300
column format
-- 如你的 EMPNO，是整型的数据，输入以下命令，数字截为四位数显示
column EMPNO format 9999;
-- 如你的 ENAME 为字符型，输入以下命令，字符串截为 10 位显示
column ENAME format a10;
-- 重置为默认值
clear columns;
ORACLE SQL：经典查询练手第一篇
Oracle11g 创建 scott 用户 emp 表
su - oracle
cd $ORACLE_HOME/rdbms/admin
sqlplus '/as sysdba'
@utlsampl.sql
conn scott/tiger
实例表结构与表的数据
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
-------- ---------- ---------- ---------- ------------ ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
scott.dept 表的现有数据
SQL> select * from dept;
DEPTNO DNAME LOC
---------- ---------------------------- --------------------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL 问题列表
- 列出至少有一个员工的所有部门。
- 列出薪金比 “SMITH” 多的所有员工。
- 列出所有员工的姓名及其直接上级的姓名。
- 列出受雇日期早于其直接上级的所有员工。
- 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门
- 列出所有“CLERK”（办事员）的姓名及其部门名称。
- 列出最低薪金大于 1500 的各种工作。
- 列出在部门“SALES”（销售部）工作的员工的姓名，假定不知道销售部的部门编号。
- 列出薪金高于公司平均薪金的所有员工。
- 列出与 “SCOTT” 从事相同工作的所有员工。
- 列出薪金等于部门 30 中员工的薪金的所有员工的姓名和薪金。
- 列出薪金高于在部门 30 工作的所有员工的薪金的员工姓名和薪金。
- 列出在每个部门工作的员工数量、平均工资和平均服务期限。
- 列出所有员工的姓名、部门名称和工资。
- 列出所有部门的详细信息和部门人数。
- 列出各种工作的最低工资。
- 列出各个部门的 MANAGER（经理）的最低薪金。
- 列出所有员工的年工资, 按年薪从低到高排序。
-- 列出至少有一个员工的所有部门
SQL> select dname from dept where deptno in(select deptno from emp);
DNAME
----------------------------
ACCOUNTING
RESEARCH
SALES
-- 列出薪金比“SMITH” 多的所有员工
SQL> select * from emp where sal > (select sal from emp where ename = 'SMITH');
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- ---------- ---------- ------------ ---------- ---------- ----------
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
13 rows selected.
-- 列出所有员工的姓名及其直接上级的姓名
SQL> select a.ename,(select ename from emp b where b.empno=a.mgr) as boss_name from emp a;
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
14 rows selected.
-- 列出受雇日期早于其直接上级的所有员工
SQL> select a.ename from emp a where a.hiredate<(select hiredate from emp b where b.empno=a.mgr);
ENAME
----------
SMITH
ALLEN
WARD
JONES
BLAKE
CLARK
6 rows selected.
-- 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门
SQL> select a.dname,b.empno,b.ename,b.job,b.mgr,b.hiredate,b.sal,b.deptno from dept a left join emp b on a.deptno=b.deptno;
DNAME EMPNO ENAME JOB MGR HIREDATE SAL DEPTNO
---------- ---------- ---------- ---------- ---------- ------------ ---------- ----------
ACCOUNTING 7782 CLARK MANAGER 7839 09-JUN-81 2450 10
ACCOUNTING 7839 KING PRESIDENT 17-NOV-81 5000 10
ACCOUNTING 7934 MILLER CLERK 7782 23-JAN-82 1300 10
RESEARCH 7566 JONES MANAGER 7839 02-APR-81 2975 20
RESEARCH 7902 FORD ANALYST 7566 03-DEC-81 3000 20
RESEARCH 7876 ADAMS CLERK 7788 23-MAY-87 1100 20
RESEARCH 7369 SMITH CLERK 7902 17-DEC-80 800 20
RESEARCH 7788 SCOTT ANALYST 7566 19-APR-87 3000 20
SALES 7521 WARD SALESMAN 7698 22-FEB-81 1250 30
SALES 7844 TURNER SALESMAN 7698 08-SEP-81 1500 30
SALES 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 30
SALES 7900 JAMES CLERK 7698 03-DEC-81 950 30
SALES 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
SALES 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 30
OPERATIONS
15 rows selected.
-- 列出所有“CLERK”（办事员）的姓名及其部门名称
SQL> select a.ename,b.dname from emp a join dept b on a.deptno=b.deptno and a.job='CLERK';
ENAME DNAME
---------- ----------
MILLER ACCOUNTING
SMITH RESEARCH
ADAMS RESEARCH
JAMES SALES
-- 列出最低薪金大于 1500 的各种工作
SQL> select distinct job as HighSalJob from emp group by job having min(sal)>1500;
HIGHSALJOB
------------------
PRESIDENT
MANAGER
ANALYST
-- 列出在部门“SALES”（销售部）工作的员工的姓名，假定不知道销售部的部门编号
SQL> select ename from emp where deptno=(select deptno from dept where dname='SALES');
ENAME
----------
ALLEN
WARD
MARTIN
BLAKE
TURNER
JAMES
6 rows selected.
-- 列出薪金高于公司平均薪金的所有员工
SQL> select ename from emp where sal>(select avg(sal) from emp);
ENAME
----------
JONES
BLAKE
CLARK
SCOTT
KING
FORD
6 rows selected.
-- 列出与“SCOTT” 从事相同工作的所有员工
SQL> select ename from emp where job=(select job from emp where ename='SCOTT');
ENAME
----------
SCOTT
FORD
-- 列出薪金等于部门 30 中员工的薪金的所有员工的姓名和薪金
SQL> select a.ename,a.sal from emp a where a.sal in (select b.sal from emp b where b.deptno=30) and a.deptno<>30;
no rows selected
-- 列出薪金高于在部门 30 工作的所有员工的薪金的员工姓名和薪金
SQL> select ename,sal from emp where sal>(select max(sal) from emp where deptno=30);
ENAME SAL
---------- ----------
JONES 2975
SCOTT 3000
KING 5000
FORD 3000
-- 列出在每个部门工作的员工数量、平均工资和平均服务期限
SQL> select (select b.dname from dept b where a.deptno=b.deptno) as deptname ,count(deptno) as deptcount,avg(sal) as deptavgsal from emp a group by deptno;
DEPTNAME DEPTCOUNT DEPTAVGSAL
---------------------------- ---------- ----------
SALES 6 1566.66667
RESEARCH 5 2175
ACCOUNTING 3 2916.66667
-- 列出所有员工的姓名、部门名称和工资
SQL> select a.ename,(select b.dname from dept b where b.deptno=a.deptno) as deptname,sal from emp a;
ENAME DEPTNAME SAL
---------- ---------------------------- ----------
SMITH RESEARCH 800
ALLEN SALES 1600
WARD SALES 1250
JONES RESEARCH 2975
MARTIN SALES 1250
BLAKE SALES 2850
CLARK ACCOUNTING 2450
SCOTT RESEARCH 3000
KING ACCOUNTING 5000
TURNER SALES 1500
ADAMS RESEARCH 1100
JAMES SALES 950
FORD RESEARCH 3000
MILLER ACCOUNTING 1300
14 rows selected.
-- 列出所有部门的详细信息和部门人数
SQL> select a.deptno,a.dname,a.loc,(select count(deptno) from emp b where b.deptno=a.deptno group by b.deptno) as deptcount from dept a;
DEPTNO DNAME LOC DEPTCOUNT
---------- ---------- -------------------------- ----------
10 ACCOUNTING NEW YORK 3
20 RESEARCH DALLAS 5
30 SALES CHICAGO 6
40 OPERATIONS BOSTON
-- 列出各种工作的最低工资
SQL> select job,avg(sal) from emp group by job;
JOB AVG(SAL)
---------- ----------
CLERK 1037.5
SALESMAN 1400
PRESIDENT 5000
MANAGER 2758.33333
ANALYST 3000
-- 列出各个部门的 MANAGER（经理）的最低薪金
SQL> select deptno,min(sal) from emp where job='MANAGER' group by deptno;
DEPTNO MIN(SAL)
---------- ----------
30 2850
20 2975
10 2450
-- 列出所有员工的年工资, 按年薪从低到高排序
SQL> select ename,(sal+nvl(comm,0))*12 as salpersal from emp order by salpersal;
ENAME SALPERSAL
---------- ----------
SMITH 9600
JAMES 11400
ADAMS 13200
MILLER 15600
TURNER 18000
WARD 21000
ALLEN 22800
CLARK 29400
MARTIN 31800
BLAKE 34200
JONES 35700
FORD 36000
SCOTT 36000
KING 60000
14 rows selected.
ORACLE SQL：经典查询练手第二篇
实例表结构与表的数据
和第一篇相同
SQL 问题列表
- 找出 EMP 表中的姓名（ENAME）第三个字母是 A 的员工姓名。
- 找出 EMP 表员工名字中含有 A 和 N 的员工姓名。
- 找出所有有佣金的员工，列出姓名、工资、佣金，显示结果按工资从小到大，佣金从大到小。
- 列出部门编号为 20 的所有职位。
- 列出不属于 SALES 的部门。
- 显示工资不在 1000 到 1500 之间的员工信息：名字、工资，按工资从大到小排序。
- 显示职位为 MANAGER 和 SALESMAN，年薪在 15000 和 20000 之间的员工的信息：名字、职位、年薪。
- 说明以下两条 SQL 语句的输出结果：
SELECT EMPNO,COMM FROM EMP WHERE COMM IS NULL;
SELECT EMPNO,COMM FROM EMP WHERE COMM = NULL; - 让 SELECT 语句的输出结果为
SELECT FROM SALGRADE;
SELECT FROM BONUS;
SELECT FROM EMP;
SELECT FROM DEPT;
……
列出当前用户有多少张数据表，结果集中存在多少条记录。 - 判断 SELECT ENAME,SAL FROM EMP WHERE SAL > ‘1500’是否抱错，为什么？
-- 找出 EMP 表中的姓名（ENAME）第三个字母是 A 的员工姓名
SQL> SELECT ENAME FROM SCOTT.EMP WHERE ENAME LIKE '__A%';
ENAME
----------
BLAKE
CLARK
ADAMS
-- 找出 EMP 表员工名字中含有 A 和 N 的员工姓名
SQL> SELECT ENAME FROM SCOTT.EMP WHERE ENAME LIKE '%A%' AND ENAME LIKE '%N%';
ENAME
----------
ALLEN
MARTIN
-- 找出所有有佣金的员工，列出姓名、工资、佣金，显示结果按工资从小到大，佣金从大到小
SQL> SELECT ENAME,SAL + COMM AS WAGE,COMM FROM SCOTT.EMP ORDER BY WAGE,COMM DESC;
ENAME WAGE COMM
---------- ---------- ----------
TURNER 1500 0
WARD 1750 500
ALLEN 1900 300
MARTIN 2650 1400
SCOTT
KING
ADAMS
JAMES
FORD
MILLER
BLAKE
JONES
SMITH
CLARK
14 rows selected.
-- 列出部门编号为 20 的所有职位
SQL> SELECT DISTINCT JOB FROM EMP WHERE DEPTNO = 20;
JOB
----------
CLERK
MANAGER
ANALYST
-- 列出不属于 SALES 的部门
SQL> SELECT DISTINCT * FROM SCOTT.DEPT WHERE DNAME <> 'SALES';
DEPTNO DNAME LOC
---------- ---------- --------------------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
40 OPERATIONS BOSTON
-- 显示工资不在 1000 到 1500 之间的员工信息：名字、工资，按工资从大到小排序
SQL> SELECT ENAME,SAL + COMM AS WAGE FROM SCOTT.EMP WHERE SAL + COMM NOT BETWEEN 1000 AND 1500 ORDER BY WAGE DESC;
ENAME WAGE
---------- ----------
MARTIN 2650
ALLEN 1900
WARD 1750
-- 显示职位为 MANAGER 和 SALESMAN，年薪在 15000 和 20000 之间的员工的信息：名字、职位、年薪
SQL> SELECT ENAME NAME,JOB JOB,(SAL + COMM) * 12 AS YEAR_SALARY FROM SCOTT.EMP WHERE (SAL + COMM) * 12 BETWEEN 15000 AND 20000 AND JOB IN('MANAGER','SALESMAN');
NAME JOB YEAR_SALARY
-------------------- ---------- -----------
TURNER SALESMAN 18000
/*----- 说明以下两条 SQL 语句的输出结果：
SELECT EMPNO,COMM FROM EMP WHERE COMM IS NULL;
SELECT EMPNO,COMM FROM EMP WHERE COMM = NULL;
----------*/
SQL> SELECT EMPNO,COMM FROM EMP WHERE COMM IS NULL;
EMPNO COMM
---------- ----------
7369
7566
7698
7782
7788
7839
7876
7900
7902
7934
10 rows selected.
SQL> SELECT EMPNO,COMM FROM EMP WHERE COMM = NULL;
no rows selected
-- 说明：IS NULL 是判断某个字段是否为空，为空并不等价于为空字符串或为数字 0；
-- 而 =NULL 是判断某个值是否等于 NULL，NULL = NULL 和 NULL <> NULL 都为 FALSE。
/*----- 让 SELECT 语句的输出结果为
SELECT * FROM SALGRADE;
SELECT * FROM BONUS;
SELECT * FROM EMP;
SELECT * FROM DEPT;
……
列出当前用户有多少张数据表，结果集中存在多少条记录。
----------*/
SQL> SELECT 'SELECT * FROM'||TABLE_NAME||';' FROM USER_TABLES;
'SELECT*FROM'||TABLE_NAME||';'
------------------------------------------------------------------------------------------
SELECT * FROM SALGRADE;
SELECT * FROM BONUS;
SELECT * FROM EMP;
SELECT * FROM DEPT;
-- 语句 SELECT ENAME,SAL FROM EMP WHERE SAL > '1500'是否抱错？
SQL> SELECT ENAME,SAL FROM EMP WHERE SAL > '1500';
ENAME SAL
---------- ----------
ALLEN 1600
JONES 2975
BLAKE 2850
CLARK 2450
SCOTT 3000
KING 5000
FORD 3000
7 rows selected.
-- 说明不会抱错，这儿存在隐式数据类型的。
ORACLE SQL：经典查询练手第三篇
ORACLE SQL：经典查询练手第四篇
ORACLE SQL：经典查询练手第五篇
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK