子查询练习
复制
create table empployee_demo(
empno
number(
4)
not null primary
key,
ename varchar2(
10)
not null unique,
job varchar2(
9),
mgr
number(
4),
hiredate
date default sysdate,
sal
number(
7,
2)
check(sal>=
500 and sal<=
10000),
comm
number(
7,
2),
deptno
number(
2)
)
DEPTNO
NUMBER(
2)
DNAME VARCHAR2(
14) Y
LOC VARCHAR2(
13) Y
grade
losal
hisal
select *
from employee
where deptno
in
(
select deptno
from department
where dname =
'SALES' or dname =
'ACCOUNTING')
select *
from employee
where job
not in (
'MANAGER');
select *
from employee
where sal <
any(
select sal
from employee
where deptno=
10);
select *
from employee
where sal < all(
select sal
from employee
where deptno=
10);
select *
from employee
where sal < (
select min(sal)
from employee
where deptno=
10);
select d.dname 部门名,emp.cou 部门员工数,emp.avgsal 平均工资,e.ename 最低工资员工名,s.grade 工资等级
from department d,
employee e,
salgrade s,
(
select deptno,
avg(sal) avgsal,
count(empno) cou,
min(sal) minsal
from employee
group by deptno) emp
where d.deptno = emp.deptno
and e.sal = emp.minsal
and e.sal
between s.losal
and s.hisal
select *
from employee e
left join department d
on e.deptno = d.deptno
where e.deptno = (
select deptno
from employee
where hiredate = (
select min(hiredate)
from employee))
select count(empno),job
from employee
where job
in (
select job
from employee
group by job
having min(sal)>
1500
)
group by job
select ename
from employee
where deptno =(
select deptno
from department
where dname=
'SALES')
select e.*,s.grade,boss.ename 上级领导
from employee e,department d,salgrade s,employee boss
where e.deptno = d.deptno
and e.sal
between losal
and hisal
and e.mgr = boss.empno
and e.sal > (
select avg(sal)
from employee)
select ename,e.job,dname
from employee e,department d,(
select deptno,job
from employee
where ename =
'SCOTT') e1
where e.deptno = d.deptno
and e.job = e1.job
and e.deptno=e1.deptno
and ename <>
'SCOTT'
select *
from employee
where deptno = (
select deptno
from employee
where ename=
'SMITH')
and job = (
select job
from employee
where ename =
'SMITH')
select *
from employee
where deptno = (
select deptno
from employee
where ename=
'ALLEN')
and
sal> (
select sal
from employee
where ename =
'MARTIN')
select *
from employee
where sal> (
select sal
from employee
where ename =
'BLAKE')
select *
from employee
where sal > (
select max(sal)
from employee
where deptno=
30)
select *
from employee
where empno
in (
select distinct mgr
from employee
where mgr
is not null);
select deptno,ename,sal
from employee e
where sal>(
select avg(sal)
from employee
where deptno=e.deptno);
select *
from employee
where deptno = (
select deptno
from employee
where ename=
'SMITH')
and job = (
select job
from employee
where ename =
'SMITH')
and sal > (
select sal
from employee
where ename=
'JAMES')
select job,deptno,sal
from employee
where (job,deptno)=(
select job,deptno
from employee
where ename=
'SMITH')
and sal>(
select sal
from employee
where ename=
'JAMES');
select deptno 部门,
count(empno) 员工数量,
avg(sal) 平均工资,
avg(
extract(
year from sysdate)-
extract(
year from hiredate)) 平均服务年限
from employee
group by deptno
order by deptno
select ename,sal
from employee
where sal
in (
select sal
from employee
where deptno =
30)
and deptno!=
30;
select ename,sal
from employee
where sal > all(
select sal
from employee
where deptno =
30)
and deptno!=
30;
select *
from(
select t.*,
rownum rn
from (
select *
from employee
order by sal ) t
where rownum<=
10
)
where rn>=
5
delete from emp_dup a
where a.rowid>(
select min(b.rowid)
from emp_dup b
where a.id = b.id
and a.name = b.name
);
commit;
转载于:https://www.cnblogs.com/chaoyang123/p/11549409.html
相关资源：JAVA上百实例源码以及开源项目
