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;








'STUDY > OCP' 카테고리의 다른 글

OCP license.E07.130702  (0) 2013.07.06
OCP license.E06.130701  (0) 2013.07.06
OCP license.E05.130628  (0) 2013.07.06
OCP license.E04.130627  (0) 2013.07.06
OCP license.E03.130626  (0) 2013.07.06
OCP license.E02.130625  (0) 2013.07.06

Comment

What Is a View?
You can present logical subsets or combinations of data by creating views of tables. A view is a
logical table based on a table or another view. A view contains no data of its own but is like a
window through which data from tables can be viewed or changed. The tables on which a view
is based are called base tables. The view is stored as a SELECT statement in the data dictionary.



l. Controlling User Access

 Objectives
After completing this lesson, you should be able to do the following:
• Differentiate system privileges from object privileges
• Grant privileges on tables
• View privileges in the data dictionary
• Grant roles
• Distinguish between privileges and roles Objectives

 Privileges
• Database security:
     – System security
     – Data security
• System privileges: Gaining access to the database
• Object privileges: Manipulating the content of the database objects
• Schemas: Collection of objects such as tables, views, and sequences

 System Privileges
• More than 100 privileges are available.
• The database administrator has high-level system
  privileges for tasks such as:
     – Creating new users
     – Removing users
     – Removing tables      
     – Backing up tables

Creating Users
The DBA creates users with the CREATE USER statement.
CREATE USER user
IDENTIFIED BY password;

CREATE USER USER1 IDENTIFIED BY USER1;
User created.

conn system/oracle
CREATE USER USER1 IDENTIFIED BY USER1;           // USER1/USER1 으로 계정 생성
conn user1/user1 (x)
conn system/oracle
grant create session to user1;                     // user1에게 'session' 권한을 부여하여 접속할 수 있도록 함
conn user1/user1
create table test (id number); (x)                
conn system/oracle
grant create table to user1;                          // user1에게 'create' 권한 부여
conn user1/user1
create table test (id number); (x)                // user1이 사용하는 공간이 부족하여 불가능
conn system/oracle
alter user user1 quota 10m on users;           // user1에게 10MB의 공간 할당

 User System Privileges
• After a user is created, the DBA can grant specific system privileges to that user.
GRANT privilege [, privilege...]
TO user [, user| role, PUBLIC...];
• An application developer, for example, may have the following system privileges:
     – CREATE SESSION
     – CREATE TABLE
     – CREATE SEQUENCE
     – CREATE VIEW
     – CREATE PROCEDURE

 What Is a Role?



 Object Privileages
Object
Privilege
TableViewSequenceProcedure
ALTERV
V
DELETEVV

EXECUTE


V
INDEXV


INSERTVV

REFERENCESV


SELECTVVV
UPDATEVV



Revoking Object Privileges
• You use the REVOKE statement to revoke privileges granted to other users.
• Privileges granted to others through the WITH GRANT OPTION clause are also revoked
REVOKE {privilege [, privilege...]|ALL}
ON object
FROM {user[, user...]|role|PUBLIC}
[CASCADE CONSTRAINTS];
[CASCADE CONSTRAINTS] :: 다른 table로 foreign key로 쓰고 있을 때 끊어버리고 권한 회수..

user1의 lock 권한 풀고 패스워드 재설정
alter user user1 identified by user1 account unlock;

ROLE_TAB_PRIVS
USER_ROLE_PRIV
USER_TAB_PRIVS_MADE
USER_TAB_PRIVS_RECD
USER_COL_PRIVS_MADE
USER_SYS_PRIVS


'STUDY > OCP' 카테고리의 다른 글

OCP license.E06.130701  (0) 2013.07.06
OCP license.E05.130628  (0) 2013.07.06
OCP license.E04.130627  (0) 2013.07.06
OCP license.E03.130626  (0) 2013.07.06
OCP license.E02.130625  (0) 2013.07.06
OCP license.E01.130624  (0) 2013.07.06

Comment

*UNION Operator




* UNION ALL Operator

* INTERSECT Operator
* MINUS Operator

SQL> select * from a;

        ID
----------
         1
         2
         3

SQL> select * from b;

        ID
----------
         3
         4
         5


SQL> select id  from a
      union all
      select id  from b;

        ID
----------
         1
         2
         3
         3
         4
         5

SQL> select id from a
union
select id from b;
        ID
----------
         1
         2
         3
         4
         5


SQL> select id from a
intersect
select id from b;
        ID
----------
         3

SQL> select id from a
minus
select id from b;
        ID
----------
         1
         2

SQL> select id from b
minus
select id from a;

Set Operator Guidelines
• The expressions in the SELECT lists must match in number and data type.
• Parentheses can be used to alter the sequence of execution.
• The ORDER BY clause:
– Can appear only at the very end of the statement
– Will accept the column name, aliases from the first SELECT statement, or the positional notation

The Oracle Server and Set Operators
• Duplicate rows are automatically eliminated except in UNION ALL.
• Column names from the first query appear in the result.
• The output is sorted in ascending order by default except in UNION ALL.


The iSQL*Plus COLUMN Command
You can use the iSQL*Plus COLUMN command to customize column headings.
Syntax:
      COL[UMN] [{column|alias} [option]]
Where OPTION is:       
      CLE[AR]: Clears any column formats       
      HEA[DING] text: Sets the column heading
      FOR[MAT] format: Changes the display of the column using a format model       
      NOPRINT | PRINT: Suppresses or displays the column heading and data
      NULL
The following statement suppresses the column data and title heading for the column named
A_DUMMY. Notice that the first SELECT clause in the previous slide creates a dummy column
named A_DUMMY.
      COLUMN a_dummy NOPRINT

• DataBases Objects
objectDscription
TableBasic unit of storage; composed of rows
ViewLogically represents subsets of data from one or more tables
SequenceGenerates numeric values
IndexImproves the performance of some queries
Sysnonymgives alternative names to objects


Including Constraints
• Constraints enforce rules at the table level.
• Constraints prevent the deletion of a table if there are dependencies.
• The following constraint types are valid:
     – NOT NULL
     – UNIQUE
     – PRIMARY KEY
     – FOREIGN KEY
     – CHECK


'STUDY > OCP' 카테고리의 다른 글

OCP license.E05.130628  (0) 2013.07.06
OCP license.E04.130627  (0) 2013.07.06
OCP license.E03.130626  (0) 2013.07.06
OCP license.E02.130625  (0) 2013.07.06
OCP license.E01.130624  (0) 2013.07.06
oracle TRUNCATE  (0) 2013.06.26

Comment



티스토리 툴바