day 0619

<< 참조키(foreign key)의 설정 >>

 

references 부모테이블(주식별자)

 

create table newbook(
	bookid number primary key,
	bookname varchar2(50) not null,
	publisher varchar2(20),
	price number
);

create table newcustomer(
	custid number primary key,
	name varchar2(20),
	addr varchar2(50),
	phone varchar2(20)
);

create table neworders(
	orderid number primary key,
	custid number references newcustomer(custid),						
	bookid number references newbook(bookid),						
	saleprice number,
	orderdate date
);

insert into newbook select bookid, bookname, publisher, price from book;
insert into newcustomer select * from customer;

insert into neworders values(1,2,5,7000,sysdate);

insert into neworders values(2,10,5,7000,sysdate);
-- ORA-02291: 무결성 제약조건(C##MADANG.SYS_C008416)이 위배되었습니다- 부모 키가 없습니다
-- 추가하려는 고객번호 10번은 newcustomer 테이블에 없기 때문에 오류가 발생

newbook 

newcustomer 

neworders의 부모는 newbook, newcustomer

  참조하고 있는 자식테이블이 있으면 부모테이블을 삭제할 수 없다

 

-- 참조키로 설정하는 칼럼은 반드시 부모테이블의 "주 식별자"여야 한다

 

<< 기본키와 참조키의 설정 >>

- 칼럼레벨의 설정  :  칼럼을 적어 줄 때 설정

create table newbook(
    bookid number primary key,
    bookname varchar2(50) not null,
    price number,
    publisher varchar2(20)
);

insert into newbook values(1, '재밌는 자바', '코스타', 30000);

create table newcustomer(
	custid number ,
	name varchar2(20),
	addr varchar2(50),
	phone varchar2(20),
	primary key(custid)
);

create table neworders(
	orderid number primary key,
	custid number references newcustomer(custid),						
	bookid number references newbook(bookid),						
	saleprice number,
	orderdate date
);

 

- 테이블레벨의 설정  :  칼럼을 다 적고 맨 마지막에 키를 설정

create table newbook(
    bookid number,
    bookname varchar2(50),
    price number,
    publisher varchar2(20),
    primary key (bookid)
);

create table newcustomer(
    custid number,
    name varchar2(20),
    addr varchar2(50),
    phone varchar2(20),
    primary key(custid)
);

create table neworders(
    orderid number,
    custid number,
    bookid number,
    saleprice number,
    orderdate date,
    primary key(orderid),
    foreign key(custid) references newcustomer(custid),
    foreign key(bookid) references newbook(bookid)
);

 

<< 제약의 종류 >>

null  :  해당 칼럼 값이 null을 허용

not null  :  해당 칼럼 값을 생략할 수 없다

unique  :  해당 칼럼 값이 유일해야 한다

default  :  해당 칼럼 값을 생략하면 기본값 설정

check  :  해당 칼럼 값의 범위(조건) 설정

primary key  :  테이블에서 다른 레코드와 구별하기 위한 식별자 설정

foreign key  :  부모테이블과 관계를 설정(참조키 설정)

 

오류) "개체무결성에 위배됩니다."

primary key 오류

pk로 설정한 칼럼의 값이 null이거나 중복 될 때

 

오류) "참조무결성에 위배됩니다."

foreign key 참조 오류

fk로 설정한 칼럼의 값이 부모 테이블에 존재하지 않을 때

 

<< 두개 이상의 칼럼이 합쳐져서 pk 설정하기 >>

 

-- newbook 테이블 만들어보기

도서명과 출판사를 합쳐서 pk로 설정하기

create table newbook(
    bookname varchar2(50),
    publisher varchar2(50),
    price number,
    primary key(bookname, publisher)
);

insert into newbook values('자바', '코스타', 50000);
insert into newbook values('자바', '한빛', 50000);
insert into newbook values('오라클', '코스타', 40000);

insert into newbook values('자바', '코스타', 50000);
-- ORA-00001: 무결성 제약 조건(C##MADANG.SYS_C008394)에 위배됩니다

 

-- newcustomer 테이블 만들어보기

create table newcustomer(
    custid number,
    name varchar2(20),
    addr varchar2(50),
    phone varchar2(20),
    primary key(custid)
);

insert into newcustomer values(1, '가', '서울', 1111);
insert into newcustomer values(2, '나', '서울', 1111);

 

-- neworders 테이블 만들어보기

주문번호를 pk, newbook의 pk를 참조키로 설정, newcustomer의 pk를 참조키로 설정

create table neworders(
    orderid number,
    custid number,
    bookname varchar2(50),
    publisher varchar2(50),
    saleprice number,
    orderdate date,
    primary key(orderid),
    foreign key(bookname, publisher) references newbook(bookname, publisher),
    foreign key(custid) references newcustomer(custid)
);


-- neworders에 제약 조건을 만족하는 레코드를 추가하기
insert into neworders values(1, 1, '자바', '코스타', 40000, sysdate);
insert into neworders values(2, 2, '자바', '한빛', 40000, sysdate);

 

-- 영화 가격은 20,000원을 넘지 않아야 한다

-- 상영관번호는 1부터 10 사이다

create table 극장(
    극장번호 number primary key,
    극장이름 varchar2(20),
    극장위치 varchar2(20)
);

insert into 극장 values(1, '롯데', '잠실');
insert into 극장 values(2, '메가', '강남');
insert into 극장 values(3, '대한', '잠실');

create table 상영관(
    극장번호 number references 극장(극장번호),
    상영관번호 number check (상영관번호 between 1 and 10),
    영화제목 varchar2(50),
    가격 number check (가격 <= 20000),
    좌석수 number,
    primary key(극장번호, 상영관번호)
);

insert into 상영관 values(1, 1, '어려운 영화', 15000,48);
insert into 상영관 values(3, 1, '멋진 영화', 7500,120);
insert into 상영관 values(3, 2, '재밌는 영화', 8000,110);

create table 고객(
    고객번호 number primary key,
    이름 varchar2(20),
    주소 varchar2(20)
);

insert into 고객 values(3, '홍길동', '강남');
insert into 고객 values(4, '김철수', '잠실');
insert into 고객 values(9, '박영희', '강남');

create table 예약(
    극장번호 number,
    상영관번호 number,
    고객번호 number,
    좌석번호 number,
    날짜 date,
    primary key(극장번호, 상영관번호, 고객번호),
    foreign key(극장번호, 상영관번호) references 상영관(극장번호, 상영관번호),
    foreign key(고객번호) references 고객(고객번호)
);

insert into 예약 values(3,2,3,15,'2014/09/01');
insert into 예약 values(3,1,4,16,'2014/09/01');
insert into 예약 values(1,1,9,48,'2014/09/01');

 

ed ff

column 극장번호 format 9999;
column 극장이름 format a10;
column 위치 format a10;

column 상영관번호 format 9999;
column 영화제목 format a10;
column 가격 format 999,999;
column 좌석수 format 9999;

column 고객번호 format 9999;
column 이름 format a10;
column 주소 format a10;

column 좌석번호 format 9999;
column 날짜 format a15;

SQL> @@ff
SP2-0158: 알 수 없는 COLUMN 옵션 "999,999"
SP2-0158: 알 수 없는 COLUMN 옵션 "9999"

 

1) 모든 극장의 이름과 위치를 보이시오

select 극장이름, 위치 from 극장;

 

2) '잠실'에 있는 극장을 보이시오

select * from 극장 where 위치 = '잠실';

 

3) '잠실'에 사는 고객의 이름을 오름차순으로 보이시오

select 이름 from 고객
where 주소 = '잠실'
order by 이름;

 

4) 가격이 8000원 이하인 영화의 극장번호, 상영관번호, 영화제목을 보이시오

select 극장번호, 상영관번호, 영화제목
from 상영관
where 가격 <= 8000;

 

5) 극장 위치와 고객의 주소가 같은 고객을 보이시오

select 고객.* 
from 고객, 극장, 예약,상영관 
where 극장.극장번호 = 상영관.극장번호 and 
상영관.극장번호 = 예약.극장번호 and 
상영관.상영관번호 = 예약.상영관번호 and 
예약.고객번호 = 고객.고객번호 and 
극장.위치 = 고객.주소;

 

6) 극장의 수는 몇개인가?

select count(*) from 극장;

 

7) 상영되는 영화의 평균 가격은 얼마인가?

select avg(가격) from 상영관;

 

8) 2014년 9월 1일에 영화를 관람한 고객의 수는 얼마인가?

select count(*) from 예약 
where 날짜 = '2014/09/01';

 

9) '대한' 극장에서 상영된 영화제목을 출력

select 영화제목
from 극장, 상영관
where 극장.극장번호 = 상영관.극장번호 and
극장이름 = '대한';

select 영화제목 
from 상영관 
where 극장번호 = (select 극장번호 from 극장 where 극장이름 = '대한');

 

10) '대한' 극장에서 영화를 본 고객의 이름을 출력

select 이름
from 고객
    where 고객번호 in (  select 고객번호 
                        from 예약 
                        where 극장번호 = (select 극장번호 
                                        from 극장
                                        where 극장이름 = '대한')  );

 

11) '대한' 극장의 전체 수입을 출력

- 예약으로 부터 극장번호가 3번인것 중에서 상영관번호별로 예약건수를 조회

select 상영관번호, count(*)
from 예약
where 극장번호 = 3
group by 상영관번호;

- 예약으로 부터 극장이름이 대한극장인 것 중에서 상영관번호별로 예약건수를 조회

select 상영관번호, count(*)
from 예약
where 극장번호 = ( select 극장번호 from 극장 where 극장이름 = '대한' )
group by 상영관번호;

- 예약으로 부터 극장이름이 대한극장인 것 중에서 상영관번호별로 예약건수와 영화가격을 조회

select 극장번호, 상영관번호, 
count(*) cnt
from 예약 a
where 극장번호 = ( select 극장번호 from 극장 where 극장이름 = '대한' )
group by 극장번호, 상영관번호;

 

-- 전체

select 극장번호, 상영관번호, cnt, 
	(select 가격 from 상영관 b where a.극장번호 = b.극장번호 and a.상영관번호 = b.상영관번호) 가격
	from (select 극장번호, 상영관번호, 
		count(*) cnt
		from 예약 a
		where 극장번호 = ( select 극장번호 from 극장 where 극장이름 = '대한' )
		group by 극장번호, 상영관번호) a;

  select  sum(cnt* 
	(select 가격 from 상영관 b where a.극장번호 = b.극장번호 and a.상영관번호 = b.상영관번호)) sum
	from (select 극장번호, 상영관번호, 
		count(*) cnt
		from 예약 a
		where 극장번호 = ( select 극장번호 from 극장 where 극장이름 = '대한' )
		group by 극장번호, 상영관번호) a;

 

12) 극장별 상영관 수를 출력

select 극장번호, count(*)
	from 상영관
	group by 극장번호;
    
	select (select 극장이름 from 극장 a where a.극장번호 = b.극장번호) 극장이름, count(*)
	from 상영관 b
	group by 극장번호;

 

13) '잠실'에 있는 극장의 상영관 출력

select * from 상영관
where 극장번호 in ( select 극장번호 from 극장 where 위치 = '잠실' );

 

14) 2014년 9월 1일의 극장별 평균 관람 고객수 출력

select 극장번호, count(*) 
from 예약 
where 날짜 = '2014/09/01'
group by 극장번호;

select 극장번호, count(*), count( distinct 상영관번호) 
from 예약 
where 날짜 = '2014/09/01'
group by 극장번호;

select 극장번호, count(*)/count( distinct 상영관번호) avg 
from 예약 
where 날짜 = '2014/09/01'
group by 극장번호;

select 
(select 극장이름 from 극장 a where a.극장번호 = b.극장번호) 극장이름, 
count(*)/count( distinct 상영관번호) avg 
from 예약 b
where 날짜 = '2014/09/01'
group by 극장번호;

select 극장번호, count(*) 
from 예약
group by 극장번호;

 

15) 2014년 9월 1일에 가장 많은 고객이 관람한 영화를 출력

- 극장번호별로, 상영관번호별로 관람수를 출력 A

select 극장번호,상영관번호,count(*) 
from 예약
group by 극장번호,상영관번호;

- B

select max(count(*)) 
from 예약
group by 극장번호,상영관번호;

- A중에 cnt가 B인 것의 극장번호, 상영관번호

select 극장번호,상영관번호
from ( A )
where cnt = ( B )

- C

select 극장번호,상영관번호
from ( select 극장번호,상영관번호,count(*) cnt 
from 예약
group by 극장번호,상영관번호)
where cnt = ( select max(count(*)) 
from 예약
group by 극장번호,상영관번호);

- 상영관으로부터 극장번호와 상영관번호가 C인것을 조회

select * from 상영관
where (극장번호,상영관번호) = ( C );

select * from 상영관
where (극장번호,상영관번호) = ( select 극장번호,상영관번호
from ( select 극장번호,상영관번호,count(*) cnt 
from 예약
group by 극장번호,상영관번호 )
where cnt = ( select max(count(*)) 
from 예약
group by 극장번호,상영관번호 ) );

select * from 상영관
where (극장번호,상영관번호) in ( select 극장번호,상영관번호
from ( select 극장번호,상영관번호,count(*) cnt 
from 예약
group by 극장번호,상영관번호 )
where cnt = ( select max(count(*)) 
from 예약
group by 극장번호,상영관번호 ) );

-- 두개의 상영관 모두 max값이 총관람수가 4명이서 2개의 영화 출력이 됩니다

 

16) 각 테이블에 데이터를 삽입하는 insert문을 하나씩 실행 하시오

insert into 극장 values(4,'코스타','종로');
insert into 상영관 values(4,1,'재미있는자바',10000,50);
insert into 고객 values(10,'홍석영','종로');
insert into 예약 values(4,1,10,7,'2024/06/19');

 

17) 영화의 가격을 10%씩 인상하시오

 update 상영관 set 가격 = 가격 * 1.1 ;

 

<< DML >>

insert  :  레코드의 생성

update  :  레코드의 수정

delete  :  레코드의 삭제

 

<< 데이터 정의어 DDL >>

create  :  테이블 생성

alter  :  테이블 구조 변경(수정)

drop  :  테이블 삭제

 

<< 테이블 구조 변경하기 >>

alter table 테이블명 ~

- 칼럼 추가

- 칼럼 삭제

- 칼럼 속성 변경

 

<< 칼럼 추가 하기 >>

alter table 테이블명 add 칼럼명 자료형;

 

<< 칼럼 삭제 하기 >>

alter table 테이블명 drop column 칼럼명;

 

<< 칼럼 속성 수정하기 >>

alter table 테이블명 modify 칼럼명 새로운자료형;

 

** 칼럼 구조 변경하기 실습

create table newbook(
    bookid number,
    bookname varchar2(20),
    publisher varchar2(20),
    price number
);

 

-- newbook 테이블에 varchar2(13)의 자료형을 가진 칼럼 isbn을 추가하시오

alter table newbook add isbn varchar2(13);

 

이미 레코드가 있는 경우에서 칼럼 추가할 경우

레코드가 이미 존재하는 테이블에 새로운 칼럼을 추가 할 때는 not null 제약을 설정할 수 없다

insert into newbook values(1, '자바', '코스타', 5000);

alter table newbook add isbn varchar2(13) not null;

 

-- 칼럼 속성 수정하기

-- newbook테이블의 isbn 칼럼의 자료형을 number로 변경하시오

(만약, 변경하려는 자료형에 맞지 않는 레코드가 이미 있다면 변경할 수 없다)

alter table newbook modify isbn number;

 

-- newbook 테이블의 isbn 칼럼을 삭제 해 봅니다

alter table newbook drop column isbn;

 

-- 오류 해결해보기

insert into newbook values(1, '재미있는 자바', '코스타', 40000);
insert into newbook values(2, '재미있고 신나는 스프링', '코스타', 45000);
-- ORA-12899: "C##MADANG"."NEWBOOK"."BOOKNAME" 열에 대한 값이 너무 큼(실제: 32,
최대값: 20)

alter table newbook modify bookname varchar2(50);

 

-- newbook 테이블에 bookname에 not null 설정

alter table newbook modify bookname varchar2(50) not null;

 

<< 이미있는 테이블에 pk 설정하기 >>

alter table 테이블명 add primary key([칼럼명]);

 

-- newbook 테이블 bookid에 대하여 pk를 설정하시오

bookid가 중복된 값이 있거나 null 값이 있으면 불가능

alter table newbook add primary key (bookid);

 

-- newbook 테이블 price에 대하여 1000원 이상이어야 하도록 제약 추가하기

alter table newbook add check(price >= 1000);

 

제약 : contraint

사용자가 설정한 제약의 정보를 갖고 있는 데이터 사전  :  user_constraints

desc user_constraints;

 

select constraint_name, constraint_type, table_name, status 
from user_constraints

 

ed ff

column constraint_name format a20;
column constraint_type format a10;
column table_name format a10;
column status format a10;

ff@@

뭐요 ..... 왜요.........

 

-- newbook 테이블에 설정된 제약 정보를 조회 (제약명)

(테이블 이름이 값으로 들어갈때는 대문자로 처리)

select constraint_name, constraint_type, table_name, status 
from user_constraints
where table_name = 'NEWBOOK';

데이터베이스 명령어 자체는 대소문자를 구별하지 않는다

하지만 테이블명이 값으로 판별될때는 대소문자를 구별한다

데이터사전인 user_ constraints 에 테이블 이름은 대문자로 들어간다

 

<< 제약을 비활성화 시키기 >>

alter table 테이블명 disable constraint 제약명;

 

<< 제약 활성화 시키기

alter table 테이블명 enable constraint 제약명;

 

-- newbook에 pk 제약을 비활성화 시키기

alter table newbook disable constraint SYS_C008433;

-- 비활성화 시키고 테스트 해 보기

-- 제약이 비활성화 되어서 중복된 도서번호 데이터를 허용한다

insert into newbook values(1,'자바','코스다',50000);

 

제약이 비활성화되어 중복된 도서번호를 허용합니다

 

-- newbook에 pk 제약을 다시 활성화 시키기

-- pk제약에 따른 만족하지 않는 레코드가 있기 때문에 pk제약을 활성화 할 수 없다

alter table newbook enable constraint SYS_C008433;

 

-- check제약이 2개다 있어서 어떤것이 가격에 대한 체크제약인지 알 수 없다

select constraint_name, constraint_type, table_name, status
from user_constraints
where table_name = 'NEWBOOK';

 

-- SEARCH_CONDITION 칼럼을 이용하여 좀 더 상세한 정보를 알 수 있다

 

select constraint_name, constraint_type, table_name, status,SEARCH_CONDITION
from user_constraints
where table_name = 'NEWBOOK';