第 6章.txt

oracle操作基础基础基础基础基础基础基础基础基础基础基础基础

例6.1 
conn scott/tiger@oradb
INSERT INTO dept(deptno, dname, loc) VALUES(99,' EDUCATION',NULL);
INSERT INTO dept(deptno, dname) VALUES(99,' EDUCATION');
commit;
SELECT * FROM dept ;

例6.2
conn scott/tiger@oradb
DELETE FROM emp@testlink;
INSERT INTO emp@testlink(deptno, ename, empno, sal, comm, mgr) 
SELECT deptno, ename, empno, sal, comm, mgr FROM emp;
commit;
SELECT * FROM emp@testlink ;

例6.3
UPDATE emp SET comm=800
WHERE deptno=30;
commit;
SELECT * FROM emp ORDER BY deptno;

例6.4
SELECT ename,sal FROM part_emp PARTITION (sal_1000);
SELECT ename,sal FROM part_emp PARTITION (sal_2000);
SELECT ename,sal FROM part_emp PARTITION (sal_3000);
SELECT ename,sal FROM part_emp PARTITION (sal_4000);
SELECT ename,sal FROM part_emp PARTITION (sal_max);
UPDATE part_emp PARTITION (sal_3000) SET sal=2500;
commit;
UPDATE part_emp PARTITION (sal_3000) SET sal=1800;
commit;

例6.5 
UPDATE part_emp 
SET deptno = ( SELECT deptno 
             FROM part_emp
             WHERE empno = 7934)
WHERE empno= 7654 ;
COMMIT;

例6.6
DELETE FROM emp
   WHERE job = 'SALESMAN'
   AND comm<300;
DELETE FROM (SELECT * FROM emp)
   WHERE job = 'SALESMAN'
   AND comm<300;

例6.7  
SELECT ename,sal FROM part_emp PARTITION (sal_4000);
DELETE FROM part_emp PARTITION (sal_4000);
commit;
SELECT ename,sal FROM part_emp PARTITION (sal_4000);

例6.8  
DELETE FROM bonus;
INSERT INTO bonus(ename,comm) VALUES ('SMITH',0);
INSERT INTO bonus(ename,comm) VALUES ('ALLEN',0);
COMMIT;
SELECT * FROM bonus;
MERGE INTO bonus D
USING (SELECT ename, sal, job, comm FROM emp WHERE deptno = 30) S
ON (D.ename = S.ename)
WHEN MATCHED THEN UPDATE SET D.comm = D.comm + S.sal
WHEN NOT MATCHED THEN INSERT (D.ename, D.job, D.sal, D.comm)
VALUES (S.ename, S.job, S.sal, S.sal*0.1);
COMMIT;
SELECT * FROM bonus ORDER BY ename;

例6.9 
SELECT deptno,job 
 FROM emp ;
SELECT DISTINCT deptno,job 
 FROM emp ;

例6.10  
SELECT ename,sal,comm,12*sal
  FROM emp
  WHERE ROWNUM<10;
SELECT ename AS name,sal "Month Income",comm,12*sal+comm "Annual Income"
  FROM emp
  WHERE ROWNUM<10;

例6.11 
SELECT ename || ' is a ' || job AS "Employee Information"
  FROM emp 
  WHERE ROWNUM<10;
SELECT DISTINCT hiredate FROM emp WHERE hiredate='09-Jun-81';
SELECT DISTINCT hiredate FROM emp WHERE hiredate='09-6月-81';
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD';
SELECT empno,ename,sal,hiredate FROM emp WHERE hiredate='1981-06-09';
SELECT empno,ename,sal,hiredate FROM emp WHERE hiredate LIKE '1981%'; 
SELECT empno, ename, job, sal
  FROM emp
  WHERE sal>=1500 AND job LIKE '%MAN%';
SELECT deptno, AVG(sal), MAX(sal), MIN(sal), SUM(sal)
  FROM emp
  GROUP BY deptno;
SELECT deptno, AVG(sal)
  FROM emp
  GROUP BY deptno 
  HAVING AVG(sal)>2000 ;

例6.12
SELECT ename , sal , emp.deptno , dname 
FROM emp, dept
WHERE sal>2500 AND emp.deptno=dept.deptno; 

例6.13 
SELECT worker.ename ename, manager.ename manager
FROM emp worker, emp manager
WHERE worker.mgr=manager.empno; 

例6.14  
SELECT DISTINCT deptno FROM emp;
SELECT * FROM dept;
INSERT INTO dept(deptno,dname,loc) VALUES(88,'test_dept','here');
commit;
SELECT dept.deptno dno, dname, ename
  FROM emp, dept
  WHERE emp.deptno=dept.deptno
  ORDER BY dno;
SELECT dept.deptno dno, dname, ename
  FROM emp, dept
  WHERE emp.deptno(+)=dept.deptno
  ORDER BY dno;

例6.15
SELECT deptno,ename,sal
  FROM emp outer
  WHERE sal>(SELECT avg(sal)
             FROM emp
             WHERE deptno=outer.deptno)
  ORDER BY deptno;
SELECT deptno, avg(sal)
  FROM emp
  GROUP BY deptno
  ORDER BY deptno;

例6.16 
SELECT sal
  FROM emp
  WHERE job ='SALESMAN'
  ORDER BY sal;
SELECT empno, ename , job , sal
  FROM emp
  WHERE sal < ANY 
                  (SELECT sal
                   FROM emp
                   WHERE job ='SALESMAN') 
  AND job<>'SALESMAN' ;
SELECT empno, ename , job , sal
  FROM emp
  WHERE sal < ALL 
                  (SELECT sal
                   FROM emp
                   WHERE job ='SALESMAN') 
  AND job<>'SALESMAN' ;

例6.17 
SELECT e.ename
  FROM emp e 
  WHERE e.empno NOT IN 
                      (SELECT m.mgr
                       FROM emp m 
                      );
SELECT e.ename
  FROM emp e 
  WHERE e.empno NOT IN 
                      (SELECT m.mgr
                       FROM emp m 
                       WHERE mgr IS NOT NULL
                      );
SELECT e.ename
  FROM emp e 
  WHERE e.empno IN 
                  (SELECT m.mgr
                   FROM emp m 
                  );

例6.18
SELECT empno, ename, job, deptno
FROM emp outer
WHERE EXISTS (SELECT 'x'
                 FROM emp
                 WHERE mgr = outer.empno);
SELECT deptno, dname
FROM dept d
WHERE NOT EXISTS (SELECT 'x'
                     FROM emp
                     WHERE deptno = d.deptno);

例6.19 
conn hr/hr@oradb
SELECT employee_id, job_id
  FROM employees
UNION
SELECT employee_id, job_id 
  FROM job_history;
SELECT employee_id, job_id
  FROM employees
UNION ALL
SELECT employee_id, job_id
  FROM job_history
ORDER BY 1;
SELECT count(*) FROM employees;
SELECT count(*) FROM job_history;

例6.20 
conn hr/hr@oradb
SELECT employee_id, job_id 
  FROM employees 
INTERSECT 
SELECT employee_id, job_id 
  FROM job_history;
SELECT employee_id,job_id,department_id 
  FROM employees 
INTERSECT 
SELECT employee_id,job_id,department_id
  FROM job_history;

例6.21
CREATE TABLE t(
  a NUMBER,
  b NUMBER,
  c NUMBER);
INSERT INTO t VALUES(NULL,NULL,1);
INSERT INTO t VALUES(NULL,2,2);
INSERT INTO t VALUES(3,3,3);
commit;
SELECT * FROM t WHERE c=1
INTERSECT
SELECT * FROM t WHERE c=2;
SELECT a FROM t WHERE c=1
INTERSECT 
SELECT a FROM t WHERE c=2;
SELECT a FROM t WHERE c=1
UNION
SELECT a FROM t WHERE c=2;
SELECT count(*)  FROM (
                       SELECT a FROM t WHERE c=1
                       UNION ALL
                       SELECT a FROM t WHERE c=2
                      );         
SELECT a , b FROM t WHERE c=1
MINUS
SELECT a , b FROM t WHERE c=2;

例6.22 
COLUMN org_chart FORMAT a20
COLUMN empno FORMAT 99999
COLUMN job FORMAT a10
SET PAGESIZE 50
SELECT LPAD(' ',2*(LEVEL-1))||ename org_chart, empno, mgr, job
  FROM emp 
  START WITH job = 'PRESIDENT' 
  CONNECT BY PRIOR empno = mgr;

例6.23 
SELECT LPAD(' ',2*(LEVEL-1))||ename org_chart, empno, mgr, job 
  FROM emp 
  START WITH job = 'PRESIDENT' 
  CONNECT BY PRIOR empno=mgr AND LEVEL<=2;

例6.24 
SELECT LPAD(' ',2*(LEVEL-1))||ename org_chart, empno, mgr, job 
  FROM emp
  WHERE ename != 'FORD'
  START WITH mgr IS NULL
  CONNECT BY PRIOR empno = mgr;
SELECT LPAD(' ',2*(LEVEL-1))||ename org_chart, empno, mgr, job 
  FROM emp
  START WITH mgr IS NULL
  CONNECT BY PRIOR empno = mgr AND ename != 'FORD'; 

例6.25 
conn hr/hr@oradb
Set pagesize 100
SELECT department_id, job_id, SUM(salary),
        RANK() OVER (ORDER BY SUM(salary) DESC) AS 次序
FROM  employees
GROUP BY department_id, job_id ;

例6.26
conn hr/hr@oradb
Set pagesize 100
SELECT job_id, SUM(salary),
 RANK() OVER (ORDER BY SUM(salary) DESC) AS 次序,
 to_char((CUME_DIST() OVER (ORDER BY SUM(salary) DESC))*100,'990.99') AS "累计比例(%)"
FROM  employees
GROUP BY job_id ;

例6.27
conn hr/hr@oradb
CREATE TABLE sal_history
 AS SELECT employee_id , hire_date, salary 
FROM employees;
DELETE FROM sal_history;
CREATE TABLE mgr_history
 AS SELECT employee_id , manager_id, salary 
    FROM employees;
DELETE FROM mgr_history;
COMMIT;
INSERT ALL
WHEN sal > 10000 THEN
  INTO sal_history VALUES(empid,hiredate,sal)
WHEN mgr > 200 THEN
  INTO mgr_history VALUES(empid,mgr,sal)
SELECT employee_id EMPID, hire_date HIREDATE, salary SAL, manager_id MGR
FROM employees
WHERE employee_id > 200;
SELECT * FROM sal_history;
SELECT * FROM mgr_history;

例6.28 
CREATE TABLE sales_source_data (
  employee_id NUMBER(6),
  week_id NUMBER(2),
  sales_mon NUMBER(8,2),
  sales_tue NUMBER(8,2),
  sales_wed NUMBER(8,2),
  sales_thur NUMBER(8,2),
  sales_fri NUMBER(8,2));
INSERT INTO SALES_SOURCE_DATA VALUES(199,35,1800,1980,1750,2100,1900);
COMMIT;
CREATE TABLE sales_info (
  employee_id NUMBER(6),
  week NUMBER(2),
  workday NUMBER(1),
  sales NUMBER(8,2));
INSERT ALL
INTO sales_info VALUES ( employee_id, week_id, 1, sales_mon )
INTO sales_info VALUES ( employee_id, week_id, 2, sales_tue )
INTO sales_info VALUES ( employee_id, week_id, 3, sales_wed )
INTO sales_info VALUES ( employee_id, week_id, 4, sales_thur )
INTO sales_info VALUES ( employee_id, week_id, 5, sales_fri )
SELECT employee_id, week_id, sales_mon, sales_tue, sales_wed, sales_thur, sales_fri
FROM sales_source_data;
COMMIT;
SELECT * FROM sales_info;

例6.29 
conn hr/hr@oradb
SELECT ROWNUM as 次序, last_name, salary 
FROM  (SELECT last_name, salary
         FROM employees
         ORDER BY salary DESC )
WHERE ROWNUM <= 10 ;

例6.30 
SELECT department_id, job_id, hire_date,SUM(salary), SUM(commission_pct)
  FROM  employees
  WHERE department_id< 90
  GROUP BY ROLLUP (department_id,job_id,hire_date );

例6.31 
SELECT department_id, job_id, hire_date,SUM(salary), SUM(commission_pct)
  FROM  employees
  WHERE department_id< 90
  GROUP BY CUBE (department_id, job_id, hire_date );

例6.32 
SELECT department_id, job_id,SUM(salary), GROUPING(department_id),GROUPING(job_id)
  FROM  employees
  WHERE department_id< 50
  GROUP BY ROLLUP (department_id, job_id);

例6.33 
SELECT department_id, job_id,manager_id, AVG(salary)
  FROM  employees
  WHERE department_id< 50
  GROUP BY
  GROUPING SETS( (department_id,job_id,manager_id),
                   (department_id,manager_id), 
                   (job_id, manager_id));

例6.34 
SELECT department_id, job_id,manager_id, SUM(salary)
  FROM  employees
  WHERE department_id<50
  GROUP BY ROLLUP ( department_id,  (job_id, manager_id) );

例6.35 
SELECT department_id, job_id,manager_id, SUM(salary)
  FROM  employees
  WHERE department_id<50
  GROUP BY department_id, ROLLUP (job_id) , CUBE(manager_id);