|
SQL, SQL*PLUS #2
원본 출처 : http://sh.hanarotel.co.kr/~ggola/telecom/ocp/sql2.htm
>>Control User Access
* Control User Access
- provide access to the oracle server
- provide and remove access on user object, such as tables and sequences
- confirm given and received privileges with the data dictionary
- create synonyms, or alternate names, for database objects
1.System privilege
- session : create session
- table : create table
select any table, alter any table ...
=> privilege는 grant를 통해 제공되고 revoke를 통해 제거된다
grant system_privileges ... (or role) <= privilege 나 role을
to user (or role or public) <= 지정된 user나 role 또는 모든user(public)에게
(with admin option) <= 이 option을 받은 user만이 grant를 할 수 있다
(role을 with admin option으로 grant하면 grantee user가 그 role에 대해 alter, drop할 수 있다)
=> 관련View : dba_sys_privs (grantee, privilege, admin_option)
2.Object privilege
- table : alter, delete, index, insert, references, select, update
- view : delete, insert, select, update
- sequence : alter, select
- procedure : exec(ute)
- snapshots : select
=> procedure execute privilege의 경우
stand-alone인 경우만 해당
(즉, 해당 Procedure가 다른 Object와 연관없이 독립적인 기능을 수행하는 경우)
=> table에 대한 index, references의 경우 role에 grant 할 수 없다
(즉, user에게 직접 grant하는 경우만 해당된다)
=> alter privilege : alter object, create trigger on object
delete privilege : delete from, truncate
execute : execute stored_procedure
index : create index on
references : create or alter table .... foreign key ....
..
..
=> privilege는 grant를 통해 제공되고 revoke를 통해 제거된다
grant object_privileges ... (or all) <= 특정 privilege 나 object의 전체권한(all)을
(columns....) <= grant할 column list (insert, references, update privilege인 경우만)
on object <= grant할 object에 대하여
to user (or role or public) <= 지정된 user나 role 또는 모든user(public)에게
(with grant option) <= 이 option을 받은 user만이 grant를 할 수 있다 (role에는 grant할 수 없다)
(role은 with grant option으로 grant할 수 없다)
revoke privilege ...
on object_name
from user1, user2 ... (public, role)
(cascade constraints) <= references privilege 로 만들어진 모든 FK References를 강제로 제거
EX) grant select, update on emp to user1, user2 ;
grant select, insert (id, first_name, last_name), update (last_name) on emp to username ;
=> grant command의 사용은 object privilege가 자신의 own schema이던지 아니면
with grant option을 통해 grantee를 갖고 있는경우에 한한다
=> 관련View : user_tab_privs_made (grantee, table_name, grantor, privilege, grantable)
-- grantable means 'with grant option'
user_tab_privs (grantee, owner, table_name, grantor, privilege, grantable)
user_tab_privs_recd (owner, table_name, grantor, privilege, grantable)
3.Synonym : convenient access (Object의 편리한 사용을 위해 만드는 Object의 다른 이름)
=> limit : the object cannot be contained in a package, => ?
a priviate synonym name must be distinct from all other objects owned by the user,
only the DBA can drop a PUBLIC synonym
=> create (public) synonym synonym_name for object_name ;
drop synonym synonym_name ;
4.Role
- system privilege와 object privilege의 구분없이 만들어진다
- role은 user에의해 소유되지 않고 그 자체로도 schema가 아니다
- role은 그 자신만 제외하고 다른 어떤 role이나 user에게 grant될 수 있다
- roles can be enabled or disabled for each authorized user
- roles may require authorization (passwords) to enable
=> create role role_name
(not) <= role을 grant시 enable하는데 있어 Orace Server의 검증이 필요없다
identified <= role을 grant시 enable하는데 있어 Orace Server의 검증이 필요하다
by password <= role을 enable할 시에 지정하는 pasword
externally <= password를 통하지 않고 OS인증을 통한다
(Externally : Oracle Server verifies user access to the role using an operation system utility)
- index, references privileges cannot be granted to a role
>>Computation with data
1.Arithmetic Operators
- add(+), subtract(-), multiply(*), divide(/)
=> 계산식에서 ()를 표현하지 않으면 *,/와 +,-의 순으로 계산된다
EX) select col1 * col2 + 20, col3 * (col4 - 40), col_price * .03 from table where ....... ;
2.Calculation Operators and Function
- round, trunc, mod
=> round(col1, n) : 주어진 n의 자리까지 반올림해서 표시
EX) select round( (col1 / col3) * 100 , 3 ) from table where ....... ;
=> col1을 col3으로 나눈 백분율에서 소숫점 4자리에서 반올림하여 소수점이하 3자리까지 표시
EX) select round( 257 , -2 ) from table where ....... ;
=> 257의 십단위로 반올림 : 260
=> trunc(col1, n) : 주어진 n의 자리까지 표시 그 이하 버림
EX) select trunc( (col1 / col3) * 100 , 1 ) from table where ....... ;
=> col1을 col3으로 나눈 백분율에서 소숫점 이하 2자리부터 버리고 1자리까지 표시
=> mod(col1, n) : col1을 주어진 n으로 나누어 나머지를 표시
EX) select mod( 47.84 , 4 ) from table where ....... ; --> 3.84
=> 47.84를 4로 나눈 나머지를 표시 : 3.84 (소수점 이하는 계산하지 않는다)
- NULL, NVL
=> data가 없는 경우 NULL로 표시되고, NVL(col1, 0)는 col1이 NULL인 경우 0으로 표시
=> 예상문제 : NVL 의 사용시 가능한 형태는 ?
NVL(expr1, expr2)
datatype은 date, character, number
datatype must match between expr1 and expr2
return the datatype of expr1
=> 예상문제 : NULL value의 특징은 ?
unavailable, unasigned, unknown, inapplicable
not the same zero or space
take up one byte of internal storage overhead
arithmetic expression containing a null value evaluate to NULL
select시 order by 가 ascending인 경우 displayed LAST
=> 예상문제 : operator - rules of precedence
1. all comparison operators (=,<,>,in,like.....)
2. and
3. or
EX) select col1, NVL(col1, 0) from table ;
---> col1 NVL(col1, 0)
(NULL) 0
- date계산
=> date 계산은 숫자를 사용할 수 있고 숫자는 일단위 (days)를 나타낸다
EX) select sysdate, sysdate + 1, sysdate -1, sysdate - sysdate
from dual ;
---> sysdate ,
add_month(date, n), last_day(date), next_day( sysdate + 7, 'WEDNESDAY'),
months_between(sysdate+ 1000, sysdate)
- to_char(data, format_mask)
---> format_mask (9, 0, $, L, MI, '.', ',', PR, EEEE, V, B)
- to_date(character_value, format_mask)
=> 예상문제 : operation with dates datatype ?
database stores dates as number
default date display : 'DD-MON-YY'
January 1, 4712 B.C ~ December 31, 4712 A.D
arithmetic operations
(date +- number ,
date - date ,
date +- number/24 )
=> 예상문제 : round(date[,fmt]) ? (return date with the time set to midnight if no format)
select round(sysdate, 'MONTH') , sysdate from dual -- 01-MAR-99 18-FEB-99
select round(sysdate, 'YEAR') , sysdate from dual -- 01-JAN-99 18-FEB-99
select round(sysdate) , sysdate from dual -- 18-FEB-99 18-FEB-99
=> 예상문제 : trunc(date[,fmt]) ? (the date of first day of the month contained in date when no format)
select trunc(sysdate, 'MONTH') , sysdate from dual -- 01-FEB-99 18-FEB-99
select trunc(sysdate, 'YEAR') , sysdate from dual -- 01-JAN-99 18-FEB-99
select trunc(sysdate) , sysdate from dual -- 18-FEB-99 18-FEB-99
=> 예상문제 : 다음중 date type을 return하지 않는것은 ?
date -1, date + 1 , date - date, date + date, date - 14/24
('date - date'는 number return, 'date + date'는 error occurs)
=> 예상문제 : '10 of October 1990' 의 표현식은?
to_char(date, 'fmdd "of" month yyyy')
3.Manipulate Character and Summary Computations
- concatenation : select col1 || col2, CONCAT(col1, col2), CONCAT('A', 'B') .......
- initcap(userid) : 'Userid'
upper('SeOul'), lower('sEoUL'), substr(char, m, [,n]), length(data)
- avg(salary), max(salary), min(salary), sum(salary),
count(*), count( (distinct) salary)
- Group Rows Together :
select ....... group by dept_id
select title, count(*) from emp group by title having count(*) > 2 ;
=> 예상문제 : group function을 사용하지 않은 select lists 는
must be in the GROUP BY
must include a GROUP BY clause that specifies the individual items
=> 예상문제 : group and having의 처리 step
rows are grouped
the group function is applied to the group
groups matching the HAVING condition are displayed
=> 예상문제 : having의 역할(to specify which groups are to be displayed)
The HAVING clause may precede the GROUP BY clause, but it is recommended
that you place the GROUP BY clause first because it is more logical.
Groups are formed and group functions are calculated before the HAVING
clause is applied to the groups in the SELECT list.
(일반적으로 group by가 having에 선행하지만, having이 group by앞에 올 수도 있다.
그러나 group by의 처리순서를 고려할 때 having이 나중에 오는것이 좋을 것이다.
having의 condition처리가 제일 나중이기 때문이다.)
>>Manipulate Multiple Tables
1.Display Data from Related Tables
- EquiJoin :
select a.id, b.dept_name
from emp a, dept b
where a.id = b.id and a.dept_id = b.dept_id and a.name like 'JOHN%' .... ;
=> 예상문제 : 위 문장에서 from절의 a, b의 alias는 select 전체에서 사용해야된다.
즉, select a.id, dept.dept_name 이런식의 alias와 real table name의 혼용은 안된다.
=> 예상문제 : equijoin의 정의
Values in the col_name on the both tables must be equal.
WHERE 절에서 join condition 으로 equal(=) 만 사용
=> 예상문제 : Non-Equijoin
WHERE 절에서 join condition 으로 equal(=) 이외의 operator 사용
- Non-EquiJoin :
result when no column in one table corresponds directly to a column in the other table
join condition contains an operation other than equal(=)
- Outer Join : (+)side에는 없고 반대편에만 있어도 유효한 Data
select a.id, b.dept_name
from emp a, dept b
where a.id = b.id and a.dept_id(+) = b.dept_id and a.name like 'JOHN%' .... ;
select a.id, b.dept_name
from emp a, dept b
where a.id = b.id(+) and a.dept_id = b.dept_id and a.name like 'JOHN%' .... ;
=> 예상문제 : Outer Join의 영문정의
missing rows can be returned if an outer join operator is used. The operator is a
plus sign enclosed in parentheses (+), and is placed on the "side" of the join that
is deficient in information. The operator has the effect of creating one or more NULL
rows, to which one or more rows from the non-deficient table can be joined.
=> 예상문제 : outer joins restrictions
outer join operator (+) can only appear on one side of the expression
a condition involving an outer join may not use IN operation
may not be linked to another condition by OR operation
- Self-Join : 1개의 Table에 대해서 join
(join a table to itself by using table aliases to simulate as if the table were two separate tables)
select a.id, b.last_name
from emp a, emp b
where a.id = b.manager_id a.name like 'JOHN%' .... ;
=> 예상문제 : 아래 문장은 self-join 이다 (그러나 결과는 join을 하나 안하나 같다)
select patient.name, doctor.name
from people patient, people doctor
where patient.id = doctor.id;
- 카티션 프로덕트 (cartesian product) = A x B
Relation이 없는 join인 즉, A와 B의 Table에 대해 join condition 없이 select 해오는 경우
총 returned row 는 A + B 한 만큼의 Result Set을 가질 것이다
=> 예상문제 :
How many join conditions are needed in a join query to avoid a Cartesian Product ?
답은 : number of tables minus one (a minimum of the number of join conditions)
즉, cartesian product를 발생 안토록하는 최소의 join수는 전체 from tables의 수에서
-1 만큼이 필요하다.
=> 예상문제 : to avoid a cartesian product
always include a valid join condition in a WHERE clause
=> 예상문제 : Cartesian Product 상황
join condition is ommited
join condition is invalid
(예, join condition is a number of tables -2 이상인 경우)
all rows in the first table are joined to all rows in the second table
- View : Related Tables에서 원하는 Data를 가지는 가상의 Table (사실 Data가 아니라 Query Statement)
(drop view view_name ;)
create view vw_emp as
select a.id, b.last_name, b.dept_name
from emp a, dept b
where a.dept_id = b.dept_id .... ;
(with check option : 생성된 view의 range밖의 value로 reassigned를 허락하지 않는다)
2.Display Data from Related Tables using
('union, union all, intersect, minus)
- union : 합집합 (교집합은 제외한 합) = A |_| B
(교집합이 없는경우의 union은 즉, 교집합을 제외할 필요가 없으므로 union all을 사용하는것이
union 보다 더 빠르다)
- union all : 합집합 + 교집합 = (A |_| B + A |-| B)
- intersect : 교집합 = A |-| B
- minus : 차집합 = (A - B) OR (B - A)
=> 예상문제 : 두 table A, B사이의 관계가 1:1, 1:M, M:1을 모두 허용한다면 어떤 relationships가
발생하는가 => many-to-many , recursive
(Relationship Types : One-to-One, Many-to-One, One-to-Many, Many-to-Many)
>>Pass Values Between Queries
1.Display Data from Related Tables
- Nested Selects = subqueries :
select name, title from emp where title = ( select title from emp where name = 'SMITH') ;
select name, dept_id, title from emp where (dept_id, title) (not) in
( select dept_id, title from dept where rep_dept_id = '15') ;
select name, title, salary from emp where salary < ( select avg(salary) from emp ) ;
>>Dictionary 관련추가사항
1.dictionary : lists all data dictionary tables, views, synonyms (현 User가 접근가능한 dictionary)
2.table_privileges : table과 관련된 모든 권한
(data IDUS, index, grant, alter, references..)
3.ind : synonym for user_indexes
4.dict_columns : description of each column in data dictionary tables and views
|
