introducton to oracle9i : sql 연습문제 8,9,10,11,12장

소개글

introducton to oracle9i : sql 연습문제 8,9,10,11,12장에 대한 보고서 자료입니다.

목차

없음

본문내용

om my_employee
8.18
savepoint lab8_18
8.19
delete my_employee
8.20
select * from my_employee
8.21
rollback to lab8_18
8.22
select * from my_employee
8.23
commit
9.1
create table dept
(id number(7),name varchar2(25));
desc dept
9.2
insert into dept
select department_id, department_name from departments
9.3
create table emp
(id number(7), last_name varchar2(25), first_name varchar2(25),
dept_id number(7));
desc emp
9.4
alter table emp
modify (last_name varchar2(50));
desc emp
9.5
select table_name
from user_tables
9.6
create table employee2
as select employee_id id, first_name, last_name, salary, department_id dept_id from employees
desc employee2
9.7
drop table emp;
desc emp
9.8
rename employee2 to emp
9.9
comment on table dept is 'It is departments';
comment on table emp is 'It is employees';
select * from user_tab_comments
9.10
alter table emp drop column first_name;
desc emp
9.11
alter table emp
set unused (dept_id);
desc emp
9.12
alter table emp
drop unused columns;
desc emp
10.1
alter table emp
add constraint my_emp_id_pk primary key (id)
10.2
alter table dept
add constraint my_dept_id_pk primary key (id)
10.3
alter table emp add (dept_id NUMBER(7));
alter table emp add constraint my_emp_dept_id_fk
foreign key (dept_id)references dept(id);
10.4
select constraint_name, constraint_type from user_constraints
where constraint_name = 'MY_DEPT_ID_PK'
or constraint_name ='SYS_C002541'
or constraint_name ='MY_EMP_ID_PK'
or constraint_name ='MY_EMP_DEPT_ID_FK'
*본인에게는 ‘SYS_C002541'이란 제약조건이름은 없습니다.
10.5
select object_name, object_type from user_objects
where object_name = 'DEPT' or object_name = 'EMP'
10.6
alter table emp add commission number(2,2)
constraint my_emp_com_ch check (commission >= 0);
desc emp
11.1
create view employees_vu
as select employee_id, last_name employee, department_id from employees
11.2
select * from employees_vu
11.3
select view_name, text from user_views
* 저는 하나만 나옵니다.
11.4
select employee,department_id from employees_vu
11.5
create view dept50
as select employee_id empno, last_name employee, department_id deptno from employees where department_id = 50
11.6
desc dept50;
select * from dept50
11.7
update dept50 set deptno = 80 where employee = 'Matos'
11.8
create view salary_vu
as select e.last_name "Employee", d.department_name "Department", e.salary "Salary", j.grade_level "Grade" from employees e, departments d, job_grades j where e.department_id = d.department_id and e.salary between j.lowest_sal and j.highest_sal
select * from salary_vu
12.1
create sequence dept_id_seq increment by 10 start with 200 maxvalue 1000
12.2
select sequence_name, max_value, increment_by, last_number from user_sequences
12.3
insert into dept values(dept_id_seq.nextval, 'Education');insert into dept alues(dept_id_seq.nextval, 'Administration'); select * from dept
12.4
create index emp_dept_id_idx on emp (dept_id)
12.5
select index_name, table_name, uniqueness from user_indexes where index_name = 'EMP_DEPT_ID_IDX' or index_name = 'MY_EMP_ID_PK'

키워드

oracle,   sql,   introducton to oracle9i : sql