Oracle 11g数据库基础教程（第2版）

admin管理员组

文章数量:1794759

Oracle 11g数据库基础教程（第2版）

--1 select * from emp where deptno = 10; --2 select empno,ename,deptno from emp where job = 'SA_MAIN'; --3 select empno,sal,comm,(sal+nvl(comm,0)) sum from emp; --4 select * from emp where job='SALESMAN' and deptno=30 or (job = 'MANAGER' and deptno = 20); --5 SELECT * FROM EMP WHERE SAL>=2000 AND JOB NOT IN('CLECK','MANAGER'); --6 select deptno,ename,comm from emp where comm！=0; --7 select * from emp where comm is null or (comm <=100); --8 select ename from emp where ename not like '%S%'; --9 select ename,hiredate from emp order by hiredate; --10 自连接 select a.ename,b.ename magename from emp a join emp b on b.empno=a.mgr; --11 select a.ename,b.hiredate marname,a.hiredate from emp a join emp b on a.hiredate<b.hiredate and a.mgr = b.empno; --12 有问题 select d.deptnod,d.dname,d.loc,e.ename marname from emp e join dept d where e.deptno=d.deptno; --13 select d.deptno,e.* from dept d left join empno d.deptno = e.deptno; --14 select d.deptno,e.* from dept d,emp e where d.deptno=e.deptno(+); select d.deptno,e.* from dept d left join emp e on d.deptno=e.deptno; --15 select empno,ename,dname,job,sal,comm from emp e,dept d where e.deptno=d.deptno; --16 select * from dept d where exists(select * from emp e where e.deptno=d.deptno); --17 select * from emp where sal>(select sal from emp where empno=7499); --18 select * from emp where sal>(select avg(sal) from emp); --19 distinct去除重复行 select max(sal),job from emp group by job; select distinct job,sal from emp e where sal=(select max(sal) from emp where job=e.job); --20 select count(*),deptno,avg(sal) from emp group by deptno; --21 select count(*),job,avg(sal) from emp group by job; --22 select deptno,job,count(*),avg(sal) from emp group by deptno,job order by deptno; --23 select distinct job from emp where sal>5000; --24 select e.*,dname,d.deptno from emp e,dept d where e.deptno=d.deptno and e.deptno in (select deptno from emp group by deptno having avg(sal)<6000); --25 select ename from emp where deptno in(select deptno from dept where dname='SALES'); --26 select * from emp where job=(select job from emp where empno= 7499); --27 select ename,sal from emp where sal>all(select sal from emp where deptno=30); --28 select count(*),avg(sal),avg(round((sysdate-hiredate)/365)),deptno from emp group by deptno; --29 select * from emp where sal = any(select avg(sal) from emp group by deptno); select * from emp where sal in(select avg(sal)from emp group by deptno); --30 select * from emp e1 where sal>(select avg(sal)from emp e2 where e2.deptno=e1.deptno); --31 select e.*,a.avgsal from emp e,(select deptno,avg(sal) as avgsal from emp group by deptno) a where a.deptno=e.deptno and e.sal>a.avgsal; --32 select * from emp e1 where sal> any(select sal from emp e where deptno=20); --33 select * from emp e where nvl(sal,0)+nvl(comm,0) in (select nvl(sal,0)+nvl(comm,0) from emp e1 where e1.deptno=10 and e.sal=e1.sal and em=e1m and e.deptno!=10); --34 select * from emp where deptno in(select deptno from emp group by deptno having count(empno)>5); --35 select * from dept where deptno in(select deptno from emp group by deptno having min(sal)>1000); --36 select * from emp e join dept d on d.deptno in (select deptno from emp where deptno not in (select distinct deptno from emp where sal<1000)) and d.deptno=e.deptno; --37 select * from dept where deptno in(select deptno from emp group by deptno having min(sal)>900 and max(sal)<3000); --38 select * from dept where deptno in (select deptno from emp group by deptno having count(*)=all(select max(count(*)) from emp group by deptno)); --39 select * from (select * from emp where deptno=30 order by sal desc) e where rownum<4; --40 select * from emp where empno in(select empno from(select empno,rownum num from(select empno,sal from emp order by sal desc))where num between 5 and 10); --41 update emp e set sal=1000+(select avg(sal) from emp where deptno=e.deptno); --42 select * from emp where hiredate=last_day(hiredate)-2; --43 select * from emp where round((sysdate-hiredate)/365) > 10; --44 select upper(substr(ename,1,1)) || lower(substr(ename,2,length(ename)-1)) from emp; --45 select * from emp where length(ename)= 6; --46 select * from emp where ename like '_M%'; --47 select replace(ename,'s','S') from emp; --48 select * from emp where to_char(hiredate,'MM')='02';

本文标签： 基础教程数据库oracle