Oracle statement 笔记

1.  multiple-column subqueries.

     where (manager_Id, department_Id) in (select managerid, departmentid from t)

2.  outter join

      SELECT t1.stor_id, t1.stor_name, t2.qty

      FROM stores t1, sales t2

      WHERE t1.stor_id = t2.stor_id(+)         // left join

      WHERE t1.stor_id(+) = t2.stor_id         // right join

3.  using a subquery  in the from clause

    select  t1.stor_id, t1.qty, t2.avgqtyy

     from sales t1, (select storid, avg(qty) avgqty from sales group by stor_id) t2

    where t1.stor-id = t2.stor_id

    and t1.qty > t2.avgqty;

4. Correlated Subqueries

    SELECT last_name, salary, department_id

    FROM employees outer

    WHERE salary >

                            ( SELECT AVG(salary)

                               FROM employees

                               WHERE department_id = outer.department_id)

5. With clause

    Use the same query block in a SELECT statement wheneit occurs more than once withiin a complex query. The WITH clause retrieves the results of a query block and stores it in the user's temporary tablespace. And it improves performance.

6. insert all

    INSERT ALL

    INTO emp_name(emp_id, lname, fname) VALUES( employeeid, lastname, firstname)

    INTO emp_addr(emp_id, address) VALUES(employeeid, address)

   SELECT employeeid, lastname, firstname, address

   FROM employees

   WHERE employeeid > 3

7. conditioanal  insert all

    INSERT ALL

    WHEN lastname like 'A%' THEN

    INTO emp_name(emp_id, lname, fname) VALUES( employeeid, lastname, firstname)

   WHEN lastname like 'B%' THEN

   INTO emp_addr(emp_id, address) VALUES(employeeid, address)

   SELECT employeeid, lastname, firstname, address

   FROM employees

   WHERE employeeid > 3

7. insert first

    INSERT FIRST

    WHEN lastname like 'A%' THEN

    INTO emp_name(emp_id, lname, fname) VALUES( employeeid, lastname, firstname)

   WHEN lastname like 'B%' THEN

   INTO emp_addr(emp_id, address) VALUES(employeeid, address)

   ELSE INTO emp_name2 values(employeeid, lastname,firstname)

   SELECT employeeid, lastname, firstname, address

   FROM employees

   WHERE employeeid > 3

8. External table .  external tables are read-only tables in which the data is stored outside the database in flat files.

9 SET Operatiors

    Union/Union All

    Intersect

    Minus

10 General Functions

      NVL(expr1, expr2)  if expr1 is null, return exper2

      NVL2(expr1, expr2, expr3)  if expr1 is null , return exper2, else return expr3

     NullIF(expr1, expr2) if expr1 = expr2, return null  

11. Using explicit default valuses

    Insert into departments(department_id, department_name, manager_id) values(300, 'Enineering', DEFAULT);

    UPDATE  departments SET manager_id = DEFAULT WHERE department_id = 10 

12. Merge Statement: Perfomrs an UPDATE if the row exists, and an INSERT if it is a new row.

      MERGE INTO copy_emp c

               USING employees e

              ON(c.employee_id = e.employee_id)

     WHEN MATCHED THEN

              UPDATE SET

               col1 = e.col1,

               col2  = e.col2

    WHEN NOT MATCHED THEN

              INSERT (colm_list) VALUES(column_values);