
P143
8.1
CONN scott/tiger@orcl
INSERT INTO dept(deptno,dname,loc) VALUES(98,'EDUCATION',NULL);
INSERT INTO dept(deptno, dname) VALUES(99,' EDUCATION');
COMMIT;
SELECT * FROM dept ;

P143
8.2
CONN scott/tiger@orcl
DELETE FROM emp@orcl_cemerp_dblink;
INSERT INTO emp@orcl_cemerp_dblink
         (deptno, ename, empno, sal, comm, mgr) 
  SELECT deptno, ename, empno, sal, comm, mgr FROM emp;
COMMIT;
SELECT * FROM emp;
SELECT * FROM emp@orcl_cemerp_dblink;

P145
8.3
CONN scott/tiger@orcl
UPDATE emp SET comm=800
  WHERE deptno=30;
COMMIT;

SELECT ename, sal, comm, deptno FROM emp
  WHERE deptno=30 ;
 
P145
8.4
CONN scott/tiger@orcl
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;

P146
8.5
CONN scott/tiger@orcl
UPDATE part_emp 
  SET deptno = ( SELECT deptno 
                   FROM part_emp
                   WHERE empno = 7934)
  WHERE empno= 7654 ;
COMMIT;
8.6
CONN scott/tiger@orcl
DELETE FROM emp
   WHERE job = 'SALESMAN'
   AND comm<300;
DELETE FROM (SELECT * FROM emp)
   WHERE job = 'SALESMAN'
   AND comm<300;
8.7
CONN scott/tiger@orcl
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);

P147
8.8
CONN scott/tiger@orcl
DELETE FROM bonus;
INSERT INTO bonus(ename,comm) VALUES ('SMITH',0);
INSERT INTO bonus(ename,comm) VALUES ('ALLEN',0);
COMMIT;
SELECT * FROM bonus;
SELECT deptno,ename,sal,job,comm FROM emp WHERE deptno=30;
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;

P150
8.9
CONN scott/tiger@orcl
SET PAGESIZE 30
SELECT deptno,job 
  FROM emp ORDER BY deptno,job;
SELECT DISTINCT deptno,job 
  FROM emp ORDER BY deptno,job;
8.10 
CONN scott/tiger@orcl
SELECT ename,sal,comm,12*sal
  FROM emp
  WHERE ROWNUM<3;
SELECT ename AS name,sal "Month Income",comm,
       12*(sal+NVL(comm,0)) "Annual Income"
  FROM emp
  WHERE ROWNUM<3;

P151
8.11
CONN scott/tiger@orcl
SELECT ename || ' is a ' || job AS "Employee Information"
  FROM emp 
  WHERE ROWNUM<3;
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-08-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 ;

P153
8.12
CONN scott/tiger@orcl
SELECT ename, sal, emp.deptno, dname
  FROM emp, dept
  WHERE sal>2500 AND emp.deptno=dept.deptno
  ORDER BY deptno;
8.13
CONN scott/tiger@orcl
SET PAGESIZE 20
SELECT worker.ename ename, manager.ename manager
  FROM emp worker, emp manager
  WHERE worker.mgr=manager.empno AND worker.deptno=30;

P154
8.14
CONN scott/tiger@orcl
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;

P155
8.15
CONN scott/tiger@orcl
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;
8.16
CONN scott/tiger@orcl
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' ;

P156
8.17
CONN scott/tiger@orcl
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 
                  );

P157
8.18
CONN scott/tiger@orcl
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);

P159
8.19
CONN hr/hrpwd@orcl
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;
SELECT  employee_id, job_id, COUNT(*) C
FROM (
      SELECT employee_id, job_id
        FROM employees
      UNION ALL
      SELECT employee_id, job_id
        FROM job_history
     )
GROUP BY employee_id, job_id
HAVING COUNT(*)>1
ORDER BY C DESC,employee_id, job_id;

P160
8.20
CONN hr/hrpwd@orcl
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;
8.21
CONN hr/hrpwd@orcl
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;

P161
УվλУ
SELECT department_id, TO_NUMBER(null) location, hire_date
  FROM employees 
UNION 
SELECT department_id, location_id, TO_DATE(null) 
  FROM departments;

P162
8.22
CONN scott/tiger@orcl
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;
8.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;

P163
8.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';

P164
ѯ
CONN scott/tiger@orcl
WITH 
dept_costs AS (
  SELECT dname, SUM(sal) AS dept_total
  FROM emp, dept
  WHERE emp.deptno=dept.deptno
  GROUP BY dname),
avg_cost AS (
  SELECT SUM(dept_total)/COUNT(*) AS dept_avg
  FROM dept_costs )
SELECT * FROM dept_costs
WHERE dept_total > (SELECT dept_avg FROM avg_cost )
ORDER BY dname;
NLS
SELECT substr('л񹲺͹ӢChina',3,4) FROM DUAL;
SELECT substrb('л񹲺͹ӢChina',3,4) FROM DUAL;
SELECT substr('Oracleй˾',3,4) FROM DUAL;

P169
8.25
CONN hr/hrpwd@orcl
SELECT AVG(salary), MAX(salary), MIN(salary), SUM(salary)
  FROM employees
  WHERE job_id like '%REP%' ;
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD';
SELECT MIN(hire_date) , MAX(hire_date)
  FROM employees ;
SET PAGESIZE 200
SELECT department_id, commission_pct
  FROM employees
  ORDER BY department_id;
SELECT count(*) FROM employees WHERE department_id = 50;
SELECT count(commission_pct) FROM employees
  WHERE department_id = 50;
SELECT AVG(commission_pct) FROM employees;
SELECT AVG(NVL(commission_pct,0)) FROM employees;
SELECT COUNT(DISTINCT department_id) FROM employees;
 
P171
8.26
CONN hr/hrpwd@orcl
ALTER SESSION SET nls_date_format = 'YYYY-MM-DD HH24:MI:SS';
ALTER SESSION SET TIME_ZONE = '-5:00';
COL SESSIONTIMEZONE FORMAT A30
SELECT SESSIONTIMEZONE, CURRENT_DATE FROM DUAL;
ALTER SESSION SET TIME_ZONE = '+8:00';
SELECT SESSIONTIMEZONE, CURRENT_DATE FROM DUAL;
COL CURRENT_TIMESTAMP(6) FORMAT A40
COL LOCALTIMESTAMP(6) FORMAT A38
SELECT CURRENT_TIMESTAMP(6), LOCALTIMESTAMP(6) FROM DUAL;
SELECT DBTIMEZONE, SESSIONTIMEZONE FROM DUAL;
SELECT last_name, hire_date, EXTRACT(MONTH FROM hire_date)
  FROM employees WHERE manager_id=103;
SELECT EXTRACT(year FROM sysdate) FROM DUAL;
SELECT FROM_TZ(TIMESTAMP '2009-04-05 09:12:15','+5:00')
  FROM DUAL;
SELECT TO_TIMESTAMP('2009-04-05 09:12:15',
                    'YYYY-MM-DD HH:MI:SS'),
       TO_TIMESTAMP_TZ ('2009-04-05 09:12:15 -8:00',
                        'YYYY-MM-DD HH:MI:SS TZH:TZM') 
  FROM DUAL;
SELECT employee_id,hire_date,
       hire_date+TO_YMINTERVAL('+01-02') AS YMINTERVAL_DATE
  FROM employees WHERE manager_id=103;
SELECT employee_id,hire_date,
       hire_date+TO_YMINTERVAL('-01-02') AS YMINTERVAL_DATE
  FROM employees WHERE manager_id=103;
SELECT tz_offset('US/Eastern') FROM DUAL;
SELECT tz_offset(DBTIMEZONE) FROM DUAL;
 
P172
8.27
CONN hr/hrpwd@orcl
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 ;
 
P173
8.28
CONN hr/hrpwd@orcl
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;
SELECT employee_id EMPID, hire_date HIREDATE,
       salary SAL, manager_id MGR
  FROM employees
  WHERE employee_id > 200;

P174
8.29
CONN hr/hrpwd@orcl
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);
INSERT INTO SALES_SOURCE_DATA
  VALUES(200,35,2009,1780,2000,2300,1890);
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_source_data;
SELECT * FROM sales_info;

P176
8.30
CONN hr/hrpwd@orcl
SELECT ROWNUM as , last_name, salary 
  FROM  (SELECT last_name, salary
          FROM employees
          ORDER BY salary DESC )
  WHERE ROWNUM <= 5 ; 
SELECT last_name, salary
  FROM employees
  WHERE ROWNUM <= 5 
  ORDER BY salary DESC ;
SELECT rownum, last_name, salary
  FROM employees
  WHERE ROWNUM <= 5 
  ORDER BY salary DESC ;

P177
8.31
SELECT department_id,job_id,hire_date,SUM(salary),
       SUM(commission_pct) COMM
  FROM  employees
  WHERE department_id< 90
  GROUP BY ROLLUP (department_id,job_id,hire_date );
8.32
SELECT department_id, job_id, hire_date,
       SUM(salary) SAL, SUM(commission_pct) COMM
  FROM  employees
  WHERE department_id< 90
  GROUP BY CUBE (department_id, job_id, hire_date );

P178
8.33
SELECT department_id, job_id,SUM(salary) SAL,
       GROUPING(department_id) DEPT,GROUPING(job_id) JOB
  FROM  employees
  WHERE department_id< 50
  GROUP BY ROLLUP (department_id, job_id);

P179
8.34
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));
8.35
SELECT department_id, job_id,manager_id, SUM(salary)
  FROM  employees
  WHERE department_id<50
  GROUP BY ROLLUP (department_id,  (job_id, manager_id));

P180
8.36
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);

