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)