OCP license.E05.130628

ALTER TABLE Statement
Use the ALTER TABLE statement to add, modify, or drop columns:

ALTER TABLE table ADD         (column datatype [DEFAULT expr]                 [, column datatype]...);

ALTER TABLE table MODIFY    (column datatype [DEFAULT expr]                 [, column datatype]...);

ALTER TABLE table DROP      (column);

SET UNUSED Option
• You use the SET UNUSED option to mark one or more columns as unused.
• You use the DROP UNUSED COLUMNS option to remove the columns that are marked as unused.

ALTER TABLE <table_name> SET UNUSED(<column_name>); OR ALTER TABLE <table_name> SET UNUSED COLUMN <column_name>;

ALTER TABLE <table_name> DROP UNUSED COLUMNS;

Adding a Constraint Syntax
Use the ALTER TABLE statement to:
• Add or drop a constraint, but not modify its structure
• Enable or disable constraints
• Add a NOT NULL constraint by using the MODIFY clause

ON DELETE CASCADE : FK 가 참조하는 데이터가 지워지면, 자식테이블의 데이터도 같이 지워진다.

ON DELETE SET NULL : FK 가 참조하는 데이터가 지워지면, 자식테이블의 컬럼 데이터가 NULL로 변경된다.

Types of Multitable INSERT Statements
The different types of multitable INSERT statements are:
  • Unconditional INSERT : 조건 없이 여러개의 table에 value를 넣을 때

    exmp) 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;

 • Conditional ALL INSERT : 조건을 가지고 여러개의 table에 value를 넣을 때 

    exmp ) 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;

 • Conditional FIRST INSERT : FIRST는 ALL 과 달리 값을 대입하되, 위에서 값을 대입하면 삭제

    exmp ) 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;

 • Pivoting INSERT : 축을 변경하여 

    exmp ) 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;

SQL> select * from SALES_SOURCE_DATA; EMPLOYEE_ID    WEEK_ID     SALES_MON  SALES_TUE  SALES_WED SALES_THUR  SALES_FRI ---------------    ----------        ----------         ----------        ----------     ----------        ----------      176                   6                 2000             3000             4000           5000            6000 SQL> SELECT * FROM sales_info; EMPLOYEE_ID       WEEK         SALES -----------           ----------      ----------      176                    6              2000      176                    6              3000      176                    6              4000      176                    6              5000      176                    6              6000

MERGE StatementMERGE Statement
• Provides the ability to conditionally update or insert data into a database table
• Performs an UPDATE if the row exists, and an INSERT if it is a new row:
 – Avoids separate updates
 – Increases performance and ease of use
 – Is useful in data warehousing applications

   * syntax

MERGE INTO table_name table_alias   USING (table|view|sub_query) alias   ON (join condition)   WHEN MATCHED THEN      UPDATE SET      col1 = col_val1,      col2 = col2_val   WHEN NOT MATCHED THEN      INSERT (column_list)      VALUES (column_values);

Flashback Version Query

SQL>  SELECT versions_starttime, versions_endtime, salary         FROM employees         VERSIONS BETWEEN scn minvalue and maxvalue         where employee_id = 100; VERSIONS_STARTTIME       VERSIONS_ENDTIME          SALARY -----------------       -----------------         ---------- 28-JUN-13 12.33.49 PM                                   2000 28-JUN-13 12.33.43 PM  28-JUN-13 12.33.49 PM            1000                        28-JUN-13 12.33.43 PM           24000

ROLLUP Operator
• ROLLUP is an extension to the GROUP BY clause.
• Use the ROLLUP operation to produce cumulative aggregates, such as subtotals

SQL> SELECT department_id, job_id, SUM(salary) FROM employees WHERE department_id < 60 GROUP BY (department_id, job_id) order by 1; DEPARTMENT_ID   JOB_ID       SUM(SALARY) -------------            ----------         -----------         10                AD_ASST            4400         20                MK_MAN            13000         20                MK_REP             6000         30                PU_CLERK          13900         30                PU_MAN             11000         40                HR_REP              6500         50                SH_CLERK          64300         50                ST_CLERK          55700         50                ST_MAN             36400 9 rows selected. SQL> SQL> SELECT department_id, job_id, SUM(salary) FROM employees WHERE department_id < 60 GROUP BY ROLLUP(department_id, job_id); DEPARTMENT_ID         JOB_ID         SUM(SALARY) -------------      ----------       -----------         10           AD_ASST             4400         10                               4400         20            MK_MAN            13000         20            MK_REP             6000         20                              19000         30            PU_MAN            11000         30          PU_CLERK            13900         30                              24900         40            HR_REP             6500         40                               6500         50            ST_MAN            36400         50          SH_CLERK            64300         50          ST_CLERK            55700         50                             156400                                         211200 15 rows selected.

CUBE Operator
• CUBE is an extension to the GROUP BY clause.
• You can use the CUBE operator to produce crosstabulation values with a single SELECT statement.

 GROUPING SETS

 - 동일한 퀴리에 대해서 다양한 그룹핑을 정의 가능

 - 개별로 그룹핑하여 UNION ALL한 경우와 동일하나, 성능적인 면에서 뛰어남

 

Scalar Subquery Expressions
• A scalar subquery expression is a subquery that returns exactly one column value from one row.
• Scalar subqueries can be used in:
– Condition and expression part of DECODE and CASE
– All clauses of SELECT except GROUP BY

WITH Clause : 별칭을 만들어서 재활용함으로써 좀더 효율적으로 쿼리를 관리함.
• Using the WITH clause, you can use the same query block in a SELECT statement when it occurs more than once within a complex query.
• The WITH clause retrieves the results of a query block and stores it in the user’s temporary tablespace.
• The WITH clause improves performance.

* Using the WITH clause, write a query to display the department name and total salaries for those departments whose total salary is greater than the average salary across departments.

WITH dept_costs AS ( SELECT d.department_name, SUM(e.salary) AS dept_total FROM employees e JOIN departments d ON e.department_id = d.department_id GROUP BY d.department_name), 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 department_name;