06.txt

Oracle database 10g基础教程（第二版） 源码

create table simpleTable(col number);
insert into simpleTable values(1);
insert into simpleTable values(2);
insert into simpleTable values(3);

 

select col
from simpleTable;

set serverout on;
begin
    for cur1 in (select col
                 from simpleTable) loop
        dbms_output.put_line(cur1.col || '是simpleTalbe表中的值。');
    end loop;
end;
/








desc emp;
select empno, ename, job, hiredate
from emp;




select ename, hiredate, sal, comm
from emp;




select ename, job, comm, comm*2+250
from emp;


select ename as EmployeeName, sal Salary, deptno "Department Number"
from emp;


select ename, job, hiredate, sal
from emp
where job = 'CLERK';
select ename, job, hiredate, sal
from emp
where job = 'MANAGER';
select ename, job, hiredate, sal
from emp
where job = 'manager';



select ename, sal, hiredate, sal
from emp
where hiredate < '1-7月-81';
select ename, sal, hiredate, sal
from emp
where hiredate > '1-7月-81';


select ename, job, hiredate, sal
from emp
where ename like 'A%';

select ename, job, hiredate, sal
from emp
where ename like 'A\%' escape '\';


select ename, job, hiredate, sal
from emp
where sal between 1500 and 2500;
select ename, job, hiredate, sal
from emp
where sal >= 1500 and sal <= 2500;




select ename, job, hiredate, sal
from emp
where job = 'SALESMAN' or job = 'PRESIDENT' and sal > 1500;
select ename, job, hiredate, sal
from emp
where (job = 'SALESMAN' or job = 'PRESIDENT') and sal > 1500;





select ename, job, hiredate, sal
from emp
order by sal;



select ename, job, hiredate, sal
from emp
order by sal desc;



select ename, job, hiredate, sal, sal*12 YearSalary
from emp
order by sal*12 desc;

select ename, job, hiredate, sal
from emp
order by hiredate, sal desc;



select ename, job, hiredate, sal
from emp
order by 3, sal desc;






select sum(sal), avg(sal), sum(sal*12), avg(sal*12)
from emp;





select ename, deptno, sal, comm
from emp;

select count(ename), count(deptno), count(distinct deptno), count(sal), count(comm)
from emp;


select ename, nvl2(ename, 'HAVE NAME', 'NO NAME'), comm, nvl(comm, '0')
from emp;


select exp(1), exp(10), power(2.71828183, 10)
from dual;


select to_char(1234567.89, '009,999,999.9900'),
       to_char(1234567.89, '999999.99EEEE'),
       to_char(567, 'RN')
from dual;





select deptno, avg(sal), max(sal), min(sal)
from emp
group by deptno;
select avg(sal), max(sal), min(sal)
from emp
group by deptno;

select ename, avg(sal), max(sal), min(sal)
from emp
group by deptno;













select deptno, job, sum(sal), avg(sal)
from emp
group by deptno, job;
select job, deptno, sum(sal), avg(sal)
from emp
group by job, deptno;








select deptno, avg(sal)
from emp
where avg(sal) >= 2000
group by deptno;
select deptno, avg(sal)
from emp
group by deptno
having avg(sal) >= 2000;



select empno, ename, deptno, hiredate, sal from emp;
select deptno, dname, loc from dept;

select ename, dname, hiredate, sal, loc
from emp, dept;

select ename, dname, hiredate, sal, loc
from emp, dept
where emp.deptno = dept.deptno;


select ename, d.deptno, dname
from emp e, dept d
where e.deptno(+) = d.deptno
order by ename;

select worker.ename || '的经理是' || manager.ename
from emp worker, emp manager
where worker.mgr = manager.empno;


select empno, ename
from emp
where ename like 'A%' or ename like 'B%'
union
select empno, ename
from emp
where ename like 'B%' or ename like 'C%';



select empno, ename
from emp
where ename like 'A%' or ename like 'B%'
union all
select empno, ename
from emp
where ename like 'B%' or ename like 'C%';







select empno, ename
from emp
where ename like 'A%' or ename like 'B%'
intersect
select empno, ename
from emp
where ename like 'B%' or ename like 'C%';




select empno, ename
from emp
where ename like 'A%' or ename like 'B%'
minus
select empno, ename
from emp
where ename like 'B%' or ename like 'C%';



desc dept
insert into dept
values(50, 'Office', 'Hong Kong');
select *
from dept;


insert into dept
values(&deptNo, &deptName, &deptLocation);


select empno, ename, deptno
from emp
where ename like 'C%';
update emp 
set deptno = 20
where empno = 7782;


update emp 
set deptno = 55
where deptno = 20;


select * 
from dept;
delete from dept
where deptno = 50 or deptno = 60;



select sal
from emp
where ename = 'CLARK';
select empno, ename, job, hiredate, sal
from emp
where sal > (select sal
             from emp
             where ename = 'CLARK');




select empno, ename, job, hiredate, sal
from emp
where job = (select job
             from emp
             where empno = 7369);



select empno, ename, job, hiredate, sal
from emp
where sal = (select max(sal) from emp)
   or sal = (select min(sal) from emp);



select max(sal)
from emp
group by deptno;
select ename, deptno, job, hiredate, sal
from emp 
where sal = (select max(sal)
             from emp
             group by deptno);




select max(sal)
from emp
group by deptno;
select ename, deptno, job, hiredate, sal
from emp 
where sal in (select max(sal)
             from emp
             group by deptno);



select ename, job, hiredate, sal, deptno
from emp
where (sal, nvl(comm, -1)) in (select sal, nvl(comm, -1)
                               from emp
                               where deptno = 20);


select empno, ename, job, deptno
from emp
where empno = 7499 or empno = 7782;
update emp
set (job, deptno) = (select job, deptno
                     from emp
                     where empno = 7499)
where empno = 7782;




select a.ename, a.deptno, a.sal, b.avgsalary
from emp a, (select deptno, avg(sal) as avgsalary
             from emp
             group by deptno) b
where a.deptno = b.deptno and a.sal > b.avgsalary;