오라클 복습

      

      

      

    
    

      <sql />
    
create table 극장(
    극장번호 number(1),
    극장이름 varchar2(10),
    위치 varchar2(20),
    primary key(극장번호)
);
drop table 극장;

create table 상영관(
    극장번호 number(1),
    상영관번호 number(1),
    영화제목 varchar2(30),
    가격 number(10),
    좌석수 number(10),
    primary key(극장번호, 상영관번호)--상영관번호만으로는 프라이머리키가 될 수 없다
);

drop table 상영관;
--같은 사람이 같은 좌석번호를 두 번 예약하지 않는다고 가정함

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

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

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

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

insert into 예약 values(3, 2, 3, 15, '20200901');
insert into 예약 values(3, 1, 4, 16, '20200901');
insert into 예약 values(1, 1, 9, 48, '20200901');

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

select * from 극장;
select * from 상영관;
select * from 예약;
select * from 고객;

--Q1.모든 극장의 이름과 위치를 보이시오.
select 극장이름, 위치 
from 극장;

--Q2. 잠실에 있는 극장을 보이세요
--보이세요는 극장의 모든 정보를 보여달라는 것이니 select *
select *
from 극장
where 위치 like '잠실';

--Q3. 잠실에 사는 고객 이름을 오름차순으로 보이시오
select 이름
from 고객
where 주소 like '잠실'
group by 이름;
--Q4. 가격이 8천원 이하인 영화의 극장번호, 상영관번호, 영화제목을 보이시오
select 극장번호, 상영관번호, 영화제목
from 상영관
where 가격<=8000;

--Q5. 극장 위치와 고객의 주소가 같은 고객들을 보이시오.
--보이세요는 극장의 모든 정보를 보여달라는 것이니 select *
select *
from 고객, 극장
where 고객.주소=극장.위치;

select *
from 고객, 극장
where 주소 like 위치;

commit;

      

      

      

    
    

      <sql />
    
--테이블 안에 데이터를 넣을 껀데 데이터의 제약조건을 지정할 수 있다.
--예를 들어 영화 가격은 2만원이 넘지 않아야 한다. 라는 제약조건을 건다면
--insert 데이터를 할 때 2만원 초과의 데이터를 넣으면 오류가 뜬다.
create table 극장(
    극장번호 number(2),
    극장이름 varchar2(10) not null,
    위치 varchar2(20),
    primary key(극장번호)
);                 
drop table 극장;   
   
--check/unique constraint (C##MADANG.SYS_C007255) violated 이러한 오류 메시지가 뜬다면 테이블을 만들때 사용한 제약조건을 수정할 것
--Constraint 는 제약조건, violated는 위배되었다.
--속성 값의 범위를 도메인이라고 칭한다.
--제약조건에는 check와 unique, not null 세 가지가 있다.
--제약조건은 insert할 때 적용됨.
--check 제약조건(=도메인 제약조건)은 속성의 도메인을 넘어선 걸 입력받으려고 하면 체크 제약조건에 위배 됨
--unique 제약조건(=기본키 제약조건)은 지정된 primary키가 중복되었을 때


--상영관 번호는 1부터 10 까지만 존재하도록 제약조건
--가격은 20000원 이하만 존재
create table 상영관(
    극장번호 number(2),
    상영관번호 number(2) check(상영관번호 between 1 and 10),
    영화제목 varchar2(30),
    가격 number(10) check(가격<=20000),
    좌석수 number(10),
    primary key(극장번호, 상영관번호)--상영관번호만으로는 프라이머리키가 될 수 없다
);

drop table 상영관;
--같은 사람이 같은 좌석번호를 두 번 예약하지 않는다고 가정함

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

create table 고객(
    고객번호 number(38),
    이름 varchar2(20),
    주소 varchar2(20),
    primary key(고객번호)
);
drop table 고객;
--check constraint (C##MADANG.SYS_C007255) violated 이러한 오류 메시지가 뜬다면 테이블을 만들때
--사용한 제약조건을 수정할 것

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

insert into 예약 values(3, 2, 3, 15, '20200901');
insert into 예약 values(3, 1, 4, 16, '20200901');
insert into 예약 values(1, 1, 9, 48, '20200901');

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

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

select * from 극장;
select * from 상영관;
select * from 예약;
select * from 고객;

--Q1. 극장의 수는 몇개인가
--count 괄호 안에 null값이 허용되지 않는 속성을 넣어야한다.
--어떤 속성이 null이 허용되는지 확인하기 귀찮을 수 있다. 그래서 왠만하면 *을 넣는 것이 일반적이다.
select count(위치)
from 극장;

select count(*)
from 극장;

--만약에 문제에서 '위치를 가지고 있는 극장'찾아야 한다면 count(위치) 하면 됨
--위 예시처럼 특정한 값을 넣을 때 말고는 괄호안에 *

--프라이머리키의 기능 
--1. 중복 데이터 허용X > 삽입시 유니크 제약조건 걸림
--2. null값 못 들어감(insert 때), 프라이머리키로 적용안된건 null 들어갈 수 있음

--또 다른 제약조건 not null(테이블 만들때 적용시키고 싶은 속성 옆에 적으면 됨), 프라이머리키로 적용안된 것도
--null 안 들어가게 하고 싶다면 사용
--not null 도 제약조건이다

--Q2. 상영되는 영화의 평균 가격은? 
select round(avg(가격), 1)
from 상영관;
--반올림 하고 싶으면 round 
--TRUNC(숫자,버릴 자릿수) - 숫자를 버릴 자릿수 아래로 버림.

--위의 결과값을 소수점 첫째자리까지 나타내시오

--Q3. 2020년 9월 1일에 영화 관람한 고객수는?
--to_char()는 날짜 타입의 데이터를 문자열로 바꾸어 주는 함수
select count(*)
from 예약
where to_char(날짜, 'yymmdd') like '200901';
--y가 2개면 20처럼 2개, y가 4개면 2020처럼 4로 적기
commit;

      

      

      

    
    

      <sql />
    
select * from 극장;
select * from 상영관;
select * from 예약;
select * from 고객;

update 극장
set 위치 ='잠실'
where 극장이름 ='롯데';

--update, insert, delete 문법을 사용한 후에는 반드시 commit을 해줘야 데이터베이스에 최종 반영이 된다.

--update 문법 사용
--Q1. 상영관 테이블에서 어려운 영화 가격을 16000원으로 수정하시오

update 상영관
set 가격 = 16000
where 영화제목 ='어려운 영화';
--숫자에는 ' ' 어퍼스트로피 안적음
commit;
--다한 후에 반드시 커밋
--delete 문법을 활용하여 박영희 고객 정보를 삭제

delete
from 고객
where 이름 ='박영희';

commit;

--삭제한 거 다시 삽입하려면
insert into 고객 values(5, '박영희', '강남');

--삽입한 거 다시 되돌리려면

rollback;

commit;
--커밋을 하기 전에는 버퍼에 정보가 저장이 되는데(롤백 가능), 커밋을 해서 최종반영이 된 정보는 롤백이 안 됨
--예를 들자면 한글 프로그램에서 커밋은 컨트롤+S와 같고 롤백은 컨트롤+Z와 같다.

--drop, create는 롤백이 불가능하다.(바로 데이터베이스에 입력이 됨)
--update, insert, delete 문법을 사용한 후에는 반드시 commit을 해줘야 데이터베이스에 최종 반영이 된다.

--Q2. '대한'극장에서 상영된 영화제목을 보이시오
select 영화제목
from 극장 , 상영관
where 극장.극장번호=상영관.극장번호 and 극장이름 like '대한';
 
--Q3. '대한' 극장에서 영화를 본 고객의 이름은?(조인으로 풀이)
select 이름
from 예약, 고객, 극장
where 예약.고객번호=고객.고객번호 and 극장.극장번호=예약. 극장번호 and 극장이름 like '대한';

--Q3. '대한' 극장에서 영화를 본 고객의 이름은?(중첩질의문으로 풀이)
--1단계. 극장테이블에서 '대한'극장의 극장번호 뽑아내기
select 극장번호
from 극장
where 극장이름 like '대한';
--2단계. 위에서 뽑아낸 극장번호랑 예약테이블에서의 극장번호랑 일치하는 조건의 고객번호 뽑아내기
select 고객번호
from 예약
where 극장번호 in(select 극장번호
                    from 극장
                        where 극장이름 like '대한');
--3단계. 위에서 뽑아낸 고객번호랑 고객테이블에서의 고객번호랑 일치하는 조건의 고객 이름 뽑아내기
select 이름
from 고객
where 고객번호 in(select 고객번호
                    from 예약
                        where 극장번호 in(select 극장번호
                            from 극장
                                where 극장이름 like '대한'));
                                
--Q5. '대한'극장의 전체 수입을 구하시오
select sum(가격)
from 극장, 상영관
where 극장.극장번호=상영관.극장번호 and 극장이름 like '대한';

select * from 극장;
select * from 상영관;
select * from 예약;
select * from 고객;

--Q6. 극장별 상영관 수를 보이시오
select 극장번호, count(상영관번호) 상영관수
from 상영관
group by 극장번호;

--Q7. 2020년 9월 1일의 극장별 관람객 수를 보이시오
--날짜는 = 글자는 like
select 극장번호, count(고객번호)
from 예약
where 날짜 = '20/09/01'
group by 극장번호;

--날짜타입을 글로 변환시키기
select 극장번호, count(고객번호)
from 예약
where to_char(날짜, 'yyyymmdd') like '20200901'
group by 극장번호;

commit;