第一天

一  Insert ------Add  new row

1. insert  into departments(department_id,departments_name,manager_id,location_id)

      values(10086,'winit','jinyang',10000)

2.Unconditional  insert all

   insert ALL

           into sal_history values(EMPID,HIREDATE,SAL)

           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;

3. Conditional insert all

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_idEMPID,hire_date HIREDATE, 

           salary SAL, manager_id MGR

    FROM   employees
    WHERE  employee_id > 200;

4.Conditional First Insert

INSERT FIRST
   WHEN SAL  > 25000          THEN
    INTO special_sal VALUES(DEPTID, SAL)
  WHEN HIREDATE like ('%00%') THEN
    INTO hiredate_history_00 VALUES(DEPTID,HIREDATE)
  WHEN HIREDATE like ('%99%') THEN
    INTO hiredate_history_99 VALUES(DEPTID, HIREDATE)
  ELSE
  INTO hiredate_history VALUES(DEPTID, HIREDATE)

  SELECT department_id DEPTID, SUM(salary) SAL,
         MAX(hire_date) HIREDATE
  FROM   employees

  GROUP BY department_id;

5. Pivoting Insert

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;

6. 

 

 

 

2. update employees 

     set department_id=10086

     where employee_id=42