Oracle Database 10g SQL Fundamentals II - Practice 3

 

Oracle Database 10g SQL Fundamentals II - Practice 3
3) Manipulating Large Data Sets

7.
a)
INSERT ALL
WHEN SAL > 20000 THEN
INTO SPECIAL_SAL VALUES(EMPID, SAL)
ELSE
INTO SAL_HISTORY VALUES(EMPID, HIREDATE, SAL)
INTO MGR_HISTORY VALUES(EMPID, MGR, SAL)
SELECT EMPLOYEE_ID AS EMPID
, HIRE_DATE AS HIREDATE
, SALARY AS SAL
, MANAGER_ID AS MGR
FROM EMPLOYEES
WHERE EMPLOYEE_ID < 125
;

8.
g)
INSERT ALL
 INTO SALES_INFO VALUES(EMPLOYEE_ID, WEEK_ID, SALES_MON)
 INTO SALES_INFO VALUES(EMPLOYEE_ID, WEEK_ID, SALES_TUE)
 INTO SALES_INFO VALUES(EMPLOYEE_ID, WEEK_ID, SALES_WED)
 INTO SALES_INFO VALUES(EMPLOYEE_ID, WEEK_ID, SALES_THUR)
 INTO SALES_INFO VALUES(EMPLOYEE_ID, WEEK_ID, SALES_FRI)
 SELECT EMPLOYEE_ID, WEEK_ID, SALES_MON, SALES_TUE, SALES_WED, SALES_THUR, SALES_FRI
 FROM SALES_SOURCE_DATA
 ;

9. 아직 잘 모르겠음....
CREATE TABLE emp_data
  (first_name  VARCHAR2(20)
  ,last_name   VARCHAR2(20)
  , email     VARCHAR2(30)
  )
ORGANIZATION EXTERNAL
(
 TYPE oracle_loader
 DEFAULT DIRECTORY emp_dir
 ACCESS PARAMETERS
 (
  RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
  NOBADFILE
  NOLOGFILE
  FIELDS
  ( first_name POSITION ( 1:20) CHAR
  , last_name POSITION (22:41) CHAR
  ,  email   POSITION (43:72) CHAR )
 )
 LOCATION ('emp.dat') ) ;

 

10.
Alter table emp_hist modify email varchar(45);

MERGE INTO EMP_HIST f USING EMP_DATA h
 ON (f.first_name = h.first_name
 AND f.last_name = h.last_name)
WHEN MATCHED THEN
 UPDATE SET f.email = h.email
WHEN NOT MATCHED THEN
 INSERT (f.first_name
    , f.last_name
    , f.email)
 VALUES (h.first_name
    , h.last_name
    , h.email);

select * from EMP_HIST;

 

11.
a)
update emp3 set department_id =60 where last_name ='Kochhar';
commit;
update emp3 set department_id =50 where last_name ='Kochhar';
commit;

b)
SELECT VERSIONS_STARTTIME "START_TIME"
, VERSIONS_ENDTIME "END_TIME"
, DEPARTMENT_ID
FROM EMP3 VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE
WHERE LAST_NAME ='Kochhar'
;