Oracle课后练习-4

1.显示姓名倒数第三个字母为I的雇员的姓名、部门名称。

select ename,dname 
from emp,dept
where emp.deptno=dept.deptno
And ename like '%I__'

2.在部门号为10，20，30的部门中查询出工资超过3000的职工。

select ename,sal,deptno from emp
where sal>3000 and deptno in (10,20,30)

3.查询工资最高的前3名职工所在的部门名。

select dname from dept
where dept.deptno in (
select deptno from 
(select emp.deptno from emp order by sal desc)
where rownum<=3) 

4.查询出和Smith同部门、职务也相同的职工。

select * from emp      
where (deptno,job)  in (select deptno,job from emp where ename='SMITH')
And ename<>'SMITH' --此句可不要

5.查询工资比Smith高的职工人数（分别用子查询和连接查询两种方法完成）

--第一种 子查询
select count(*) from emp 
where sal>(select sal from emp where ename='SMITH')

--第二种 连接查询
select count(*) from emp a,
(select * from emp where ename='SMITH') b
where a.sal>b.sal

6.查询和Smith同一部门，并且岗位为“CLERK”的职工名（分别用子查询和连接查询两种方法完成）

--第一种 连接查询
select a.ename from emp a,emp b
where b.ename='SMITH' 
and a.job='CLERK'
and a.deptno=b.deptno

--第二种 子查询
select ename from emp 
where deptno=(select deptno from emp where ename='SMITH')
and job='CLERK'

7.查询部门平均工资超过Smith所在部门的平均工资的所有部门号（用子查询完成）

select deptno,avg(sal) from emp
group by deptno 
having avg(sal)>
(select avg(sal) from emp 
where deptno=(select deptno from emp where ename='SMITH'))

8.显示所有比其上级管理员更早进入公司的雇员的姓名（Employee）、受雇日期（Emp Hiredate）以及其上级管理员的姓名（Manager）及受雇日期（Mgr Hiredate）。（分别用子查询和连接查询完成）

--第一种 连接查询
select a.ename Employee,a.hiredate EmpHiredate,
b.ename Manageer,b.hiredate "Mgr Hiredate"
from emp a,emp b
where a.mgr=b.empno and a.hiredate<b.hiredate

--第二种 子查询
select c.ename Employee,c.hiredate EmpHiredate,
b.ename Manageer,b.hiredate "Mgr Hiredate"
from emp b,
(select ename ,hiredate,mgr from emp a 
where hiredate<(select hiredate from emp where empno=a.mgr)) c
where b.empno=c.mgr

9.显示雇员姓名、年限工资（以New Salary作为列标题）。年限工资的计算方法：从受雇日期起至今满10年的，工资增加10%；满20年的，工资增加20%，满30年的，工资增加30%；其他的不变。

select ename,sal,hiredate,
decode(trunc(months_between(sysdate,hiredate)/120),
1,sal*1.1,2,sal*1.2,3,sal*1.3,sal) "New Salary"  
from emp

10.查询出工资超过自己所在部门的平均工资的雇员号及工资（用两种方法完成）。

--第一种  连接查询
select  a.empno, a.sal
from emp a, 
(select deptno, avg(sal) avg from  emp group by deptno) b
WHERE   a.deptno = b.deptno  
AND     a.sal > b.avg

--第二种 子查询
select ename,deptno,sal from emp a 
where sal>(
select avg(sal) from emp where deptno=a.deptno)

11.查询所有部门中没有雇员的部门名。

select distinct dept.dname
from dept,emp 
where dept.deptno not in 
(select distinct deptno from emp)

--标准答案
select dname
from dept
where deptno in (select deptno from dept
minus select distinct deptno from emp)