Oracle中Scott账户常见表的样本查询
使用scott的表
雇员表：记录了一个雇员的基本信息
EMP（雇员表）
NO????????????????字段???????????? 类型????????????????????????????描述
1??????????????EMPNO??????????????NUMBER(4)???????????????? 雇员编号
2??????????????ENAME??????????????VARCHAR2(10)??????????????表示雇员姓名
3??????????????JOB????????????????VARCHAR2(9)?????????????? 表示工作职位
4??????????????MGR????????????????NUMBER(4)???????????????? 表示一个雇员的领导编号
5??????????????HIREDATE?????????? DATE??????????????????????表示雇佣日期
6??????????????SAL????????????????NUMBER(7,2)?????????????? 表示月薪，工资
7??????????????COMM?????????????? NUMBER(7,2)?????????????? 表示奖金或佣金（commission）
8??????????????DEPTNO???????????? NUMBER(2)???????????????? 表示部门编号
部门表：表示一个部门的具体信息
DEPT（部门表）
NO???????????????? 字段???????????? 类型????????????????????????????????描述
1?????????????? DEPTNO????????????NUMBER(2)???????????????? 部门编号
2?????????????? DNAME???????????? VARCHAR2(14)??????????????部门名称
3?????????????? LOC?????????????? VARCHAR2(13)??????????????部门位置
奖金表：表示一个雇员的工资及奖金。
BONUS（奖金表）
NO???????????????? 字段???????????? 类型????????????????????????????????描述
1?????????????? ENAME???????????? VARCHAR2(10)??????????????雇员姓名
2?????????????? JOB?????????????? VARCHAR2(9)?????????????? 雇员工作
3?????????????? SAL?????????????? NUMBER????????????????????雇员工资
4?????????????? COMM??????????????NUMBER????????????????????雇员奖金
一个公司是有等级制度，用此表表示一个工资的等级
SALGRADE（工资等级表）（工资 ＝ 薪金 ＋ 佣金）
NO???????????????? 字段???????????? 类型????????????????????????????????描述
1?????????????? GRADE???????????? NUMBER????????????????????等级名称
2?????????????? LOSAL???????????? NUMBER????????????????????此等级的最低工资
3?????????????? HISAL???????????? NUMBER????????????????????此等级的最高工资
1．列出至少有一个员工的所有部门。(两个表联合查询，及group by...having的用法)?
select dname from dept where deptno in(select deptno from emp group by deptno having count(*)>1);
2．列出薪金比“SMITH”多的所有员工。(经典的自连接查询)?
select ename from emp where sal>(select sal from emp where ename like\'SMITH\');
3．列出所有员工的姓名及其直接上级的姓名。(多次对自己查询,为表的取个别名，内部查询可以像对象一样引用外部的对象的字段，这里引用与编程中的作用域相似，即与{}类比)?
select ename,(select ename from emp where empno =a.mgr) from emp a ;
4．列出受雇日期早于其直接上级的所有员工。(同上,日期可直接拿来比较)?
select ename from emp a where HIREDATE<(select HIREDATE from emp where empno in(a.mgr));
5．列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门(以emp表为主，左连接查询)?
select dname,emp.* from dept left join emp on dept.deptno=emp.deptno;
6．列出所有“CLERK”（办事员）的姓名及其部门名称。(域，注意())?
select ename,(select dname from dept where deptno in(a.deptno)) as dname from emp a where JOB like\'CLERK\';
7．列出最低薪金大于1500的各种工作。?
select job from emp where sal>1500;
8．列出在部门“SALES”（销售部）工作的员工的姓名，假定不知道销售部的部门编号。(经典的两个表连接)?
select ename from emp where deptno=(select deptno from dept where dname like\'SALES\');
9．列出薪金高于公司平均薪金的所有员工。(反复查自己)?
select ename from emp where sal>( select avg( sal) from emp);
10．列出与“SCOTT”从事相同工作的所有员工。(排除自己)?
select ename from emp where job in(select job from emp where ename like\'SCOTT\') and ename!=\'SCOTT\' ;
11．列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。(any的用法，且排挤)?
select ename,sal from emp where sal=any(select sal from emp wheredeptno=30) and deptno!=30;
12．列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。(max的用法)?
select sal,ename from emp where sal>(select max(sal) from emp where deptno=30);
13．列出在每个(每个是关键字,对此group by)部门工作的员工数量、平均工资和平均服务期限。(经典的group by用法)?
select deptno,count(*),avg(a.sal),avg(sysdate-HIREDATE) from emp a group by deptno;
14．列出所有员工的姓名、部门名称和工资.(经典的两个表的连接查询，用具体的名称替换一个表中的主键的id (解决很多人在实际运用中会遇到的不能绑定多列的问题)，也可用where来查询 ,与题5比较)?
select ename,sal,(select dname from dept a where a.deptno=b.deptno)as dname from emp b;
15．列出所有部门的详细信息和部门人数。(因为是*，将显示dept和后面临时表b的全部字段(注意:不只是dept的字段,注意*号))?
select * from dept a left join (select deptno,count(*) from emp group by deptno) b on a.deptno=b.deptno ;
16．列出各种(与每个同义(参看题13))工作的最低工资。?
select job,min(sal) from emp group by job ;
17．列出各个部门的MANAGER（经理,经理唯一，不用group by）的最低薪金。?
select min(sal) from emp where job like\'MANAGER\';(因为MANAGER是值不是字段，所以不能用小写)
18．列出所有员工的年工资,按年薪从低到高排序。(nvl:空转化函数)?
select ename,sal+nvl(comm,0) as sal from emp order by sal ;
