SQLSQLSQL

1    select * from emp where deptno=20
2    select empno,ename,deptno from emp where job='CLERK'
3    select * from emp where comm>sal
4    select * from emp where comm>sal*0.2
5    select * from emp where (deptno=10 and job='MANAGER')or(deptno=20 and      job='CLERK')
6    select * from emp where job not in('MANAGER','CLERK')and sal>=2000
7    select distinct job from emp where comm is not null
8    select emp.*,comm+sal from emp
9    select * from emp where comm is null or comm<100
11   select * from emp where (sysdate-hiredate)>12*365
12   select initcap(ename) from emp
13   select ename from emp where length(ename)=6
14   select ename from emp where ename not like '%R%'
15   Select * from emp where  ename like '_M%'
16   select ename substr(ename,1,3) from emp
18   select ename,hiredate from emp order by hiredate
19   select ename,job,comm,sal from emp order by job desc,sal
20   select ename,extract(year from hiredate),extract(month from hiredate) from emp      order by extract(month from hiredate),extract(year from hiredate)
21   select * from emp where extract(month from hiredate) ='2'
22   select extract(year from (sysdate-hiredate)) from emp
23   select dept.*from dept,emp  where dept.deptno=emp.deptno
24   select * from emp where sal>(select sal from emp where ename='SMITH')
25   select a.ename,b.ename from emp a,emp b where a.empno=b.mgr
26   select e.* from emp e,emp e2,dept d
     where e.mar=e2.empno and e.hiredate<e2.hiredate and e.deptno=d.deptno
27   select d.deptno,d.ename,e.ename from dept d,emp e where d.deptno=e.deptno(+)
29   select ename,deptno from emp where job="CLERK"
30   select job from emp group by job having min(sal)>2500
32   select ename from emp where deptno=(select deptno from dept where dname='SALES')
33   select * from emp
     where sal>(select avg(sal) from emp )
34   select * from emp where job=(select job from emp where ename='SCOTT')
     and ename!='SCOTT'
35   select ename,sal from emp where sal in (select sal from emp where deptno=30)and        deptno!=30
36   select ename,sal from emp where sal>all(select sal from emp where deptno=30)
     and deptno!=30
37   select d.dname,count(e.empno),avg(e.sal),avg(months_between(sysdate,hiredate)/12)
     from dept d,emp e
     where e.deptno=d.deptno
     group by d.dname
39   select a.*,b.c1,b.c2
     from dept a,(select deptno ,count(*)c1,avg(sal)c2 from emp group by deptno)b
     where a.deptno=b.deptno
40   select job from emp  where sal in(select min(sal) from emp group by job)
43   select deptno,count(empno),avg(sal)from emp group by deptno
44   select * from emp where sal in (select avg(sal) from emp group by deptno)
45   select * from emp a where sal>(select avg(sal) from emp where deptno=a.deptno)
46   select b.*,c.c1 from (select deptno,avg(sal)c1 from emp group by deptno)c,          (select * from emp a where sal>(select avg(sal) from emp where deptno=a.deptno))b
     where c.deptno=b.deptno
51   select * from emp group by ddept having count(*)>5
52   select * from dept where deptno not in (select deptno from emp where sal<=2000)
53   select a.*,b.* from emp a,dept b,(select deptno from dept where deptno not in          (select distinct deptno from emp where sal<=1000))c  where a.deptno=b.deptno and       b.deptno=c.deptno
56   select a.*,b.*,c.* from emp a,emp b,emp c where a.mgr=b.empno and                     b.deptno=c.deptno
57   select * from dept where deptno in(select deptno,count(*) from emp group by            deptno having count(*)=(select max(count(*)) from emp group by deptno))
62   P304
63   insert into emp(ename,empno,sal,deptno,hiredate) values                         ('oracle',1357,2050,20,'2002年5月10日')
64   insert  into emp(ename,empno,mgr,job,hirdate,sal, comm.,deptno)
     select 'FAN',8000,mgr,job,hirdate,sal,comm.,deptno from emp where ename='SMITH'
65   update emp  e  set sal=1000+(select avg(sal) from emp where deptno=e.deptno)