Manipulating Data

■DML:

  insert:

    ・The syntax : use either the VALUES keyword or a subquery, but not both

    ×　insert into t1 (c1,c2) values(select c1,c2 from t1);

    ・insert rows into several tables with one statement

     eg:

INSERT ALL

WHEN order_total < 100000 THEN

INTO small_orders

WHEN order_total > 100000 AND order_total < 200000 THEN

INTO medium_orders

ELSE

INTO large_orders

SELECT order_id, order_total, sales_rep_id, customer_id

FROM orders;

  update

  delete

  merge

    eg:

   MERGE INTO bonuses D
    USING (SELECT employee_id, salary, department_id FROM employees
    WHERE department_id = 80) S
    ON (D.employee_id = S.employee_id)
    WHEN MATCHED THEN UPDATE SET D.bonus = D.bonus + S.salary*.01
     DELETE WHERE (S.salary > 8000)
    WHEN NOT MATCHED THEN INSERT (D.employee_id, D.bonus)
     VALUES (S.employee_id, S.salary*0.1)
     WHERE (S.salary <= 8000);

■Truncate

■For update

■Transactions control statements

  ・ACID

　・COMMIT; ROLLBACK;

  SAVEPOINT A;

  ROLLBACK TO A;
■Data Control Language (DCL) statements

  GRANT

  REVOKE