oracle查询实战操作题库（转载）

本文所使用的是oracle11r2版本，在PLSQL Developer上联系查询操作，题目来源网络（好像是某位博主的）

刚开始接触oracle，有部分题目理解不到位，查询不到位，欢迎指正交流。

所使用的表格为emp与dept

emp表：

dept表：

 

 

--1.列出至少有一个员工的所有部门。

我的答案：

select distinct e.deptno,d.dname

from emp e

left join dept d

on e.deptno=d.deptno;

网上答案：

select dname from dept where deptno in(select deptno from emp group by deptno having count(deptno)>=1);

 

--2.列出薪金比“SMITH”多的所有员工。

我的答案：

select empno,ename,sal

from emp

where sal>(

select sal

from emp

where ename='SMITH');

网上答案：

select * from emp where sal>(select sal from emp where ename='SMITH');

 

--3.列出所有员工的姓名及其直接上级的姓名。

我的答案：

select a.ename empname,b.ename manager

from emp a

left join emp b

on a.mgr=b.empno;

网上答案：

select a.ename,(select ename from emp b where b.empno=a.mgr) as boss_name from emp a;  

 

--4.列出受雇日期早于其直接上级的所有员工。

我的答案：

select a.ename as empname,a.HIREDATE as ehire,b.ename as manager,b.hiredate as mhire

from emp a,emp b

where a.mgr=b.empno

and (to_date(a.HIREDATE)-to_date(b.hiredate))<0;

网上答案：

select a.ename from emp a where a.hiredate>(select hiredate from emp b where b.empno=a.mgr);

 

--5.列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门(left join从左表那里返回所有的行，即使在右表中没有匹配的行。)

我的答案：

select dname,empno,ename,job

from dept d left join emp e

on d.deptno=e.deptno

order by dname;

网上答案：

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;

 

--6.列出所有“CLERK”(办事员)的姓名及其部门名称。（:as 是 alias 的缩写,是“别名”的意思，后面接中文需要用 "" 。）

我的答案：

select ename,dname

from emp a left join dept b

on a.deptno=b.deptno

where a.job='CLERK';
网上答案：

select a.ename,(select dname from dept b where b.deptno=a.deptno) as dname from emp a where a.job='CLERK';select a.ename,b.dname from emp a join dept b on a.deptno=b.deptno and a.job='CLERK';  

 

--7.列出最低薪金大于1500的各种工作。(distinct 筛选重复值)

我的答案：

不会

网上答案：

select distinct job from emp group by job having min(sal)>1500;   

 

--8.列出在部门“SALES”(销售部)工作的员工的姓名，假定不知道销售部的部门编号。

我的答案：

select ename

from emp a,dept b

where a.deptno=b.deptno

and b.dname='SALES';

网上答案：

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);

网上答案：

select ename from emp where sal>(select avg(sal) from emp);

 

--10.列出与“SCOTT”从事相同工作的所有员工。

我的答案：

select ename

from emp

where ename not in('SCOTT')

and job=(select job

from emp

where ename='SCOTT');

网上答案：

select ename from emp where job=(select job from emp where ename='SCOTT');

 

--11.列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。(in 操作符允许我们在where子句中规定多个值)

网上答案：

select ename,sal from emp where sal in(select sal from emp where deptno=30);

 

--12.列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。（max() 求最大值）

网上答案：

select ename,sal from emp where sal>(select max(sal) from emp where deptno=30);

 

--13.列出在每个部门工作的员工数量、平均工资和平均服务期限。（group by+[分组字段](可以有多个)用于结合合计函数，根据一个或多个列对结果集进行分组。）

我的答案：

select count(deptno) sl,avg(sal) pjgz

from emp a group by deptno;

网上答案：

select (select b.dname from dept b where b.deptno=a.deptno) 部门, count(deptno) 平均工资,avg(sal) 平均服务期限 from emp a group by deptno;

 

--14.列出所有员工的姓名、部门名称和工资。

我的答案：

select ename,dname,sal

from emp a left join dept b

on a.deptno=b.deptno;

网上答案：

select ename,(select b.dname from dept b where b.deptno= a.deptno) 部门,sal from emp a;

 

--15.列出所有部门的详细信息和部门人数。(count(列名)函数返回指定列的值的数目，返回的是一个数字)

我的答案：

select b.deptno,b.dname,b.loc,(select count(ename) from emp a where a.deptno=b.deptno) coun from dept b

网上答案：

select deptno, dname, loc,(select count(deptno) from emp b where b.deptno=a.deptno group by deptno) 部门人数 from dept a;

 

--16.列出各种工作的最低工资。( min(列名) 求最小值 ；max(列名) 求最大值 )

我的答案：

select job,min(sal)

from emp group by(job);

网上答案：

select job, min(sal+nvl(comm,0) from emp group by job;
select job, min(nvl2(comm,sal+comm,sal)) from emp group by job;

 

--17.列出各个部门的MANAGER(经理)的最低薪金。

我的答案：

select deptno,min(sal)

from (select a.deptno,a.ename,a.sal

from emp a,emp b

where b.mgr=a.empno) group by deptno

网上答案：

select deptno,(select dname from dept b where b.deptno=a.deptno) 部门,min(sal) from emp a where job='MANAGER' group by deptno;

 

--18．列出所有员工的年工资,按年薪从低到高排序。( nvl(表达式,value):如果表达式计算结果为  null ，则返回 value)

网上答案：

select ename,(sal+nvl(comm,0))*12 as salpersal from emp order by salpersal;  
select ename,(nvl2(comm,sal+comm, sal))*12 as salpersal from emp order by salpersal;

 

 

------第二篇

--1. 找出EMP表中的姓名（ENAME）第三个字母是A 的员工姓名。(like 操作符用于在 where 子句中搜索列中的指定模式。 "%" 可用于定义通配符（模式中缺少的字母）。)

我的答案：

select ename

from emp

where ename like '__A%';

网上答案：

select ename from emp where ename like '__A%';

 

--2. 找出EMP表员工名字中含有A 和N的员工姓名。(and 并且 ； or 或者)

我的答案：

select ename from emp

where ename like '%A%' and ename like '%N%';

网上答案：

select ename from emp where ename like '%A%' and ename like '%N%'

 

--3. 找出所有有佣金的员工，列出姓名、工资、佣金，显示结果按工资从小到大，佣金从大到小。（order by 默认按照升序排序；order by 列名 desc 降序）

我的答案：

select ename,sal,comm

from emp

where comm is not null

order by sal,comm desc;

网上答案：

select ename,nvl2(comm,sal+comm,sal) as wage,comm from emp order by wage,comm desc;

 

--4. 列出部门编号为20的所有职位。(和第一篇的第七题类似)

我的答案：

select distinct job

from emp

where deptno=20;

网上答案：

select distinct job from emp where deptno=20;

 

 --5. 列出不属于SALES 的部门。

--in和exists的区别：

--exists（相关子查询）:存在，后面一般都是子查询，不返回列表的值，只是返回一个ture或false的结果

--in：包含，in()后面的子查询 是返回结果集的

网上答案：

select distinct * from dept where dname not in('SALES');select distinct * from dept where exists(select dname from dept where dname='SALES');

 

--6. 显示工资不在1000 到1500 之间的员工信息：名字、工资，按工资从大到小排序。(between ... and 会选取介于两个值之间的数据范围。)

我的答案：

方法一：

select ename,sal

from emp

where sal<1000 or sal>1500

order by sal desc;

方法二：

select ename,sal

from emp

where sal not in (select sal from emp where sal between 1000 and 1500)

order by sal desc ;

 

网上答案：

select ename,nvl2(comm,sal+comm,sal) as wage from emp where nvl2(sal,comm+comm,sal) not between 1000 and 1500 order by wage desc;select ename,nvl2(comm,sal+comm,sal) as wage from emp where nvl2(sal,comm+comm,sal)<1000 or sal+comm>1500 order by wage desc;  

 

-- 7. 显示职位为 MANAGER 和 SALESMAN，年薪在15000 和20000 之间的员工的信息：名字、职位、年薪。

我的答案：

select ename,job,(sal+nvl(comm,0))*12 as ysal

from emp

where job in ('MANAGER','SALESMAN')

and (sal+nvl(comm,0))*12 between 15000 and 20000;

网上答案：

select ename 姓名,job 职位,(sal+nvl(comm,0))*12 年薪 from emp where (sal+comm)*12 between 15000 and 20000 and job in('MANAGER','SALESMAN')

 

 

-- 8. 说明以下两条SQL语句的输出结果：

--    SELECT EMPNO,COMM FROM EMP WHERE COMM IS NULL;  

--    SELECT EMPNO,COMM FROM EMP WHERE COMM = NULL;  

网上答案：

IS NULL：是判断某个‘字段’是否为空，为空并不等价于为空字符串或为数字0；  

NULL ：是判断某个‘值’是否等于NULL，NULL=NULL和NULL="" 都返回false。<br="">

 

--9.让SELECT 语句的输出结果为

-- SELECT * FROM SALGRADE;  

-- SELECT * FROM BONUS;  

-- SELECT * FROM EMP;  

-- SELECT * FROM DEPT;  

-- ……

--列出当前用户有多少张数据表，结果集中存在多少条记录。 

我的答案：

select *

from  user_table;

网上答案：

select * from user_tables;

 

--10.判断SELECT ENAME,SAL FROM EMP WHERE SAL > '1500'是否报错，为什么？

网上答案：

不会报错，这儿存在隐式数据类型的。