오렌지 실습 2 - 복습, 정리

오렌지 실습 복습

-------복습, 정리-------
drop table TB_100;
create table APA55353.TB_100
(A number,
B varchar2(10)
);
select * from user_tables where TABLE_NAME ='TB_100';
select * from user_tables where TABLE_NAME =upper('tb_100');
desc TB_100;
select * from user_tab_columns where TABLE_NAME ='TB_100'; -- 테이블 컬럼 구조 확인
insert into TB_100 (a, b) values (1, 'AAA');
commit;
select * from TB_100;
select rowid, a, b from TB_100;
insert into TB_100 (a, b) values (1, 'AAA');
select rowid, a, b from TB_100;
select rowid, a, b from TB_100 where ROWID = 'AAAFw1AAGAAAG2EAAB';

create table e
(a number(4)
, b varchar2(20)
, c date
, d number(7,2)
);
select * from user_tables where table_name = 'E';
select * from user_tab_columns where table_name = 'E';

create table e2
(a number(4)
, b varchar2(20)
, c date default sysdate
, d number(7,2)
);
insert into e2(a, b, d) values(1, 'kkk', 10000.01); -- length 7, point under 2
select * from e2;
commit;

insert into e2(a, b, c, d) values(1, 'kkk', '2021-0101', 10000.01);
select * from e2;
commit;

insert into e2(a, b, c, d) values(1, 'kkk', sysdate, 10000.01);
select * from e2;
commit;

insert into e2(a, b, c, d) values(1, 'kkk', sysdate-1, 10000.01); -- data 1 day before
select * from e2;
commit;

insert into e2(a, b, c, d) values(1, 'kkk', sysdate-1/24, 10000.01); -- data 1 hour before 
select * from e2;
commit;

insert into e2(a, b, c, d) values(1, 'kkk', sysdate, 10000.001); -- data cut error
select * from e2;

drop table TB_100 purge;
drop table TB_A purge;
drop table TB_C purge;
drop table E purge;
drop table E2 purge;

create table D
(dn number(2) primary key
,d varchar2(15)
,l varchar2(15)
);
select * from user_constraints where table_name = 'D';

create table D1
(dn number(2) constraint pk_d1_dn primary key
,d varchar2(15)
,l varchar2(15)
);
select * from user_constraints where table_name LIKE 'D1';

------ PK 제약위배 NN, UK, IDX ------
insert into d1(dn, d, l) values(null, '123456789012345', '123456789012345'); -- NN 위배
insert into d1(dn, d, l) values(0, '123456789012345', '123456789012345');
select * from d1;
commit;
insert into d1(dn, d, l) values(0, '123456789012345', '123456789012345'); -- UK 위배
insert into d1(dn, d, l) values(1, '123456789012345', '123456789012345');
select * from d1;
commit;


-----정보 확인-----
select * from user_constraints where table_name = 'D1';
desc D1; -- NULL 여부(일부 check 제약만 확인됨)
select * from user_cons_columns where table_name = 'D1'; -- 제약조건이 적용된 컬럼 정보
select * from user_tab_columns where table_name = 'D1'; -- 테이블 컬럼 정보
select * from user_ind_columns where table_name = 'D1'; -- 인덱스 컬럼 정보
select * from user_indexes where table_name = 'D1'; -- 인덱스 정보



-------오브젝트에서 정보 확인-------
select * from user_objects where object_name = 'D1';
select * from user_objects;
select distinct OBJECT_TYPE from user_objects; -- 오브젝트 종류
select OBJECT_TYPE, count(*) from user_objects group by OBJECT_TYPE; -- 오브젝트별 갯수
select count(distinct OBJECT_TYPE) from user_objects; -- 오브젝트 종류 카운트
select count(distinct OBJECT_TYPE) as "내맘대로 컬럼명!" from user_objects;
select OBJECT_TYPE, count(*) as "CNT" 
from user_objects 
group by OBJECT_TYPE
order by 2 DESC; -- 2번째 목록 Descend순
select OBJECT_TYPE, count(*) as "CNT" 
from user_objects 
group by OBJECT_TYPE
having count(*) >= 10
order by 2 DESC; -- 오브젝트 10개 이상만 Descend순
select OBJECT_TYPE, count(*) as "CNT" 
from user_objects 
group by OBJECT_TYPE
order by 2 ASC; -- 2번째 목록 Ascend순


-----제약조건 걸기-----
create table D2
(dn number(2)
,d varchar2(15)
,l varchar2(15)
constraint pk_d1_dn primary key
); -- previous PK exists 
create table D2
(dn number(2)
,d varchar2(15)
,l varchar2(15)
constraint pk_d2_dn primary key -- need (dn), constraint for L col
);
select * from user_constraints where table_name = 'D2';
select * from user_cons_columns where table_name = 'D2'; 
alter table D2
drop constraints PK_D2_DN; -- drop for retry
select * from user_cons_columns where table_name = 'D2';
alter table D2
add constraints PK_D2_DN primary key (DN); -- constraint for DN col
select * from user_cons_columns where table_name = 'D2';