Oracle经典练习题及标准答案

oracle经典练习sql

/*1、选择在部门30中员工的所有信息*/

select * from scott.emp where deptno = '30'

/*2、列出职位为（MANAGER）的员工的编号，姓名 */

select empno, ename from scott.emp where job = 'MANAGER'

/*3、找出奖金高于工资的员工*/

select * from scott.emp where comm > sal

/*4、找出每个员工奖金和工资的总和 */

select ename, sal + nvl(comm, 0) from scott.emp

/*5、找出部门10中的经理(MANAGER)和部门20中的普通员工(CLERK) */

select *

from scott.emp

where deptno = '10'

and job = 'MANAGER'

union

select *

from scott.emp

where job = 'CLERK'

and deptno = '20'

/*6、找出部门10中既不是经理也不是普通员工，而且工资大于等于2000的员工 */ select *

from scott.emp

where job != 'MANAGER'

and job != 'CLERK'

and sal > 2000

/*7、找出有奖金的员工的不同工作 */

select distinct(job) from scott.emp where comm is not null

/*8、找出没有奖金或者奖金低于500的员工*/

select *

from scott.emp

where comm is not null

and comm > 500

/*9、显示雇员姓名，根据其服务年限，将最老的雇员排在最前面 */

select ename

from scott.emp

order by (months_between(sysdate, hiredate) / 12) desc

select ename,hiredate from scott.emp order by hiredate

/*10、找出每个月倒数第三天受雇的员工*/

select * from scott.emp where hiredate = last_day(hiredate) - 2

/*11、分别用case和decode函数列出员工所在的部门，deptno=10显示'部门10',

deptno=20显示'部门20'

deptno=30显示'部门30'

deptno=40显示'部门40'

否则为'其他部门'*/

select ename,

case deptno

when 10 then

'部门10'

when 20 then

'部门20'

when 30 then

'部门30'

when 40 then

'部门40'

else

'其他部门'

end 工资情况

from scott.emp

select ename,

decode(deptno,

10,

'部门10',

20,

'部门20',

30,

'部门30',

40,

'部门40',

'其他部门') 工资情况

from scott.emp

/*12、分组统计各部门下工资>500的员工的平均工资*/

select avg(sal) from scott.emp where sal > 500 group by deptno

/*13、统计各部门下平均工资大于500的部门*/

select deptno from scott.emp group by deptno having avg(sal) > 500

/*14、算出部门30中得到最多奖金的员工奖金 */

select max(comm) from scott.emp where deptno = 30

/*15、算出部门30中得到最多奖金的员工姓名*/

select ename

from scott.emp

where deptno = 30

and comm = (select max(comm) from scott.emp where deptno = 30)

/*16、算出每个职位的员工数和最低工资*/

select count(ename), min(sal), job from scott.emp group by job

/*17、列出员工表中每个部门的员工数，和部门no */

select count(ename), deptno from scott.emp group by deptno

/*18、得到工资大于自己部门平均工资的员工信息*/

select *

from scott.emp e

where sal > (select avg(sal) from scott.emp where e.deptno = deptno)

select *

from scott.emp e1,

(select avg(sal) sals, deptno from scott.emp group by deptno) e2

where sal > sals

and e1.deptno = e2.deptno

/*19、分组统计每个部门下，每种职位的平均奖金（也要算没奖金的人）和总工资(包括奖金) */

select avg(nvl(comm,0)), sum(sal + nvl(comm, 0))

from scott.emp

group by deptno,job

/*20、笛卡尔集*/

select * from scott.emp, scott.dept

/*21、显示员工ID,名字，直属主管ID，名字*/

select empno,

ename,

mgr,

(select ename from scott.emp e1 where e1.empno = e2.mgr) 直属主管名字

from scott.emp e2

/*22、DEPT表按照部门跟EMP表左关联*/

select *

fromscott.dept, scott.emp

wherescott.dept.deptno = scott.emp.deptno(+)

/*23、使用此语句重复的内容不再显示了*/

select distinct (job) from scott.emp

/*24、重复的内容依然显示 */

select *

from scott.emp

UNION ALL

select * from scott.emp

/*23和24题和22题是一样的 */

/*25、只显示了两个表中彼此重复的记录。*/

select *

fromscott.dept, scott.emp

wherescott.dept.deptno(+) = scott.emp.deptno

/*26、只显示了两张表中的不同记录*/

select * from scott.emp union select * from scott.emp

minus

(select * from scott.emp intersect select * from scott.emp)

(select * from scott.emp minus select * from scott.emp)

union

(select * from scott.emp minus select * from scott.emp)

表结构相同先union 只能有 -

/*27、列出员工表中每个部门的员工数，和部门no */

select count(ename), deptno from scott.emp group by deptno

/*28、列出员工表中每个部门的员工数（员工数必须大于3），和部门名称*/ select count(deptno),

deptno,

(selectdname from scott.dept where scott.dept.deptno = e1.deptno)

from scott.emp e1

group by deptno having count(deptno)>3

/*29、找出工资比jones多的员工*/

select *

from scott.emp

where sal > (select sal from scott.emp where ename = 'JONES')

/*30、列出所有员工的姓名和其上级的姓名 */

select ename,

(select ename from scott.emp e1 where e1.empno = e2.mgr) 上级的姓名

from scott.emp e2

/*31、以职位分组，找出平均工资最高的两种职位 */

select job

from scott.emp

group by job

having avg(sal) in (select max(sal) from scott.emp group by job )

select job

from (select job, avg(sal)

from scott.emp

group by job

order by avg(sal) desc)

whererownum<= 2

最大的：

select max(max_sal)

from (select job, avg(sal) max_sal from scott.emp group by job)

/*32、查找出不在部门20，且比部门20中任何一个人工资都高的员工姓名、部门名称*/

select ename, dname

from scott.emp e1, scott.dept e2

where e1.deptno = e2.deptno

and e1.deptno <> 20

and sal > (select max(sal) from scott.emp where deptno = '20')

/* …… 此处隐藏：5208字，全部文档内容请下载后查看。喜欢就下载吧 ……