Oracle SQL部分练习题
SQL练习题
注：查询列表不建议用 “*”
1.列出至少有一个雇员的所有部门；
a.
1、mysql相关操作
2、【CodeVS1163】访问艺术馆
3、java final 修饰变量
4、JAVA 基础编程练习题44 【程序 44 偶数的素数和】
5、为web开发加分的tooltip
select * from dept where deptno in(select distinct deptno from emp);b. (oracle11gCBO 新特性 in（多个值）会智能过滤掉重复字段，通过执行计划验证)；
select * from dept where deptno in (select deptno from emp group by deptno having count(deptno)>=1);c.
select * from dept a where exists (select null from emp b where a.deptno=b.deptno);2.列出薪金比smith多的所有雇员
select ename from emp where sal>(select sal from emp where ename='SMITH');3.列出雇员的姓名及其直接上级姓名(注：列出执行计划原由于显示结果不同，结果显示第二个执行计划要优秀一点，第一个执行计划走了全表扫描也符合常理，第二个走的唯一索引，索引>堆表；测试表较小没有参考价值仅得出结果不同，有利于以后做查询优化) a.
select e.ename,p.ename from emp e,emp p where p.empno(+)=e.mgr; select p.ename,e.ename from emp p left join emp e on e.empno=p.mgr;
执行计划：
Execution Plan
----------------------------------------------------------
Plan hash value: 2341341676
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 280 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN OUTER | | 14 | 280 | 7 (15)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 14 | 140 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP | 14 | 140 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("E"."EMPNO"(+)="P"."MGR")
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
15 consistent gets
0 physical reads
0 redo size
823 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
14 rows processed
b.
select e.ename,(select ename from emp p where p.empno=e.mgr)as BoosName from emp e;
执行计划：
Execution Plan
----------------------------------------------------------
Plan hash value: 4000517069
--------------------------------------------------------------------------------
------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
------
| 0 | SELECT STATEMENT | | 14 | 140 | 3 (0)| 00:0
0:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 10 | 1 (0)| 00:0
0:01 |
|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| 00:0
0:01 |
| 3 | TABLE ACCESS FULL | EMP | 14 | 140 | 3 (0)| 00:0
0:01 |
--------------------------------------------------------------------------------
------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("P"."EMPNO"=:B1)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
17 consistent gets
0 physical reads
0 redo size
849 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
14 rows processed
4.列出入职日期早于其直接上级的所有雇员
a. select p.ename from emp e,emp p where e.empno=p.mgr and p.hiredate<e.hiredate;
b.
select e.ename from emp e where e.hiredate<(select hiredate from emp p where p.empno=e.mgr);
5、列出部门名称和这些部门的雇员,同时列出那些没有雇员的部门 a.
select a.dname,b.ename from dept a,emp b where a.deptno=b.deptno(+);
b.
select a.dname,b.ename from dept a left join emp b on a.deptno=b.deptno
6、列出所有“CLERK”（办事员）的姓名及其部门名称
select b.ename,a.dname from dept a,emp b where a.deptno=b.deptno and b.job='CLERK';
7、列出各种工作类别的最低薪金，显示最低薪金大于1500的记录
select min(sal)as minsal,job from emp group by job having min(sal)>1500;
--8、列出从事“SALESMAN”（销售）工作的雇员的姓名，假定不知道销售部的部门编号
select ename from emp where deptno=(select deptno from dept where dname='SALES');
9、列出薪金高于公司平均水平的所有雇员
select ename from emp where sal>(select avg(sal) from emp);
10、列出与“SCOTT”从事相同工作的所有雇员
select ename from emp where job=(select job from emp where ename='SCOTT');
11、列出某些雇员的姓名和薪金，条件是他们的薪金等于部门30中任何一个雇员的薪金
select ename,sal from emp where sal in (select sal from emp where deptno=30);
12、列出某些雇员的姓名和薪金，条件是他们的薪金高于部门30中所有雇员的薪金
select ename,sal from emp where sal > (select max(sal) from emp where deptno=30);
13、列出每个部门的信息以及该部门中雇员的数量
select a.deptno,a.dname,a.loc,b.ss from dept a,(select deptno,count(ename) ss from emp group by deptno) b where a.deptno=b.deptno;
14、列出所有雇员的雇员名称、部门名称和薪金
select a.ename,b.dname,a.sal from emp a,dept b where a.deptno=b.deptno(+);
15、列出从事同一种工作但属于不同部门的雇员的不同组合
select a.ename,b.ename,a.job,b.job,a.deptno,b.deptno from emp a,emp b where a.job=b.job and a.deptno<>b.deptno;
16、列出分配有雇员数量的所有部门的详细信息，即使是分配有0个雇员 a.
select a.deptno,a.dname,a.loc,nvl(b.ss,0) from dept a, (select deptno,count(ename) ss from emp group by deptno) b where a.deptno=b.deptno(+);
b.
select a.deptno,dname,loc,count(empno) ss from dept a,emp b where a.deptno=b.deptno(+) group by a.deptno,a.dname,a.loc;
17、列出各种类别工作的最低工资
select job,min(sal) as minjobsal from emp group by job;
18、列出各个部门的MANAGER（经理）的最低薪金
select deptno,min(sal) ss from emp where job='MANAGER' group by deptno;
19、列出按年薪排序的所有雇员的年薪
select ename,(sal*12+nvl(comm,0)) as nianxin from emp order by nianxin;
20、列出薪金水平处于第四位的雇员 a.
select ename,a.tt from (select rownum tt,ename,(sal*12+nvl(comm,0)) as nianxin from emp)a where a.tt=4;b.
select * from (select ename,sal,rank()over(order by sal desc)as nn from emp) where nn=4;优质内容筛选与推荐>>
1、mysql相关操作
2、【CodeVS1163】访问艺术馆
3、java final 修饰变量
4、JAVA 基础编程练习题44 【程序 44 偶数的素数和】
5、为web开发加分的tooltip