영화예매 db를 짜고있다...
test row들..
insert into movie values('m1','미쓰홍당무','코믹','200분','김종환','정말 끝내주게 웃깁니다.','미쓰홍당무',0,'서울시립대');
insert into movie values('m2','킹스맨','액션','150분','강주희','액션 좋아요.','킹스맨',0,'서울시립대');
insert into movie values('m3','살인의추억','스릴러','300분','최윤주','정말 스릴있어요','미쓰홍당무',0,'서울시립대');
insert into movie values('m4','천재','드라마','100분','김종환','이사람 천잰가?','천재',0,'서울시립대');
insert into screen values('s1','1관');
insert into screen values('s2','2관');
insert into screen values('s3','3관');
insert into screen values('s4','4관');
insert into screen values('s5','5관');
insert into screen values('s6','6관');
insert into screen values('s7','7관');
insert into screen values('s8','8관');
insert into screen_movie values('s1','m1','2015-04-01','2015-05-29');
insert into screen_movie values('s1','m2','2015-03-28','2015-04-29');
insert into screen_movie values('s1','m3','2015-04-25','2015-06-02');
insert into screen_movie values('s1','m4','2015-03-17','2015-05-10');
insert into screen_movie values('s2','m1','2015-04-01','2015-05-29');
insert into screen_movie values('s2','m2','2015-03-28','2015-04-29');
insert into screen_movie values('s2','m3','2015-04-25','2015-06-02');
insert into screen_movie values('s2','m4','2015-03-17','2015-05-10');
insert into screen_movie values('s3','m1','2015-04-01','2015-05-29');
insert into screen_movie values('s3','m2','2015-03-28','2015-04-29');
insert into screen_movie values('s3','m3','2015-04-25','2015-06-02');
insert into screen_movie values('s3','m4','2015-03-17','2015-05-10');
insert into screen_movie values('s4','m1','2015-04-01','2015-05-29');
insert into screen_movie values('s4','m2','2015-03-28','2015-04-29');
insert into screen_movie values('s4','m3','2015-04-25','2015-06-02');
insert into screen_movie values('s4','m4','2015-03-17','2015-05-10');
insert into screen_movie values('s5','m1','2015-04-01','2015-05-29');
insert into screen_movie values('s5','m2','2015-03-28','2015-04-29');
insert into screen_movie values('s5','m3','2015-04-25','2015-06-02');
insert into screen_movie values('s5','m4','2015-03-17','2015-05-10');
insert into screen_movie values('s6','m1','2015-04-01','2015-05-29');
insert into screen_movie values('s6','m2','2015-03-28','2015-04-29');
insert into screen_movie values('s6','m3','2015-04-25','2015-06-02');
insert into screen_movie values('s6','m4','2015-03-17','2015-05-10');
insert into screen_movie values('s7','m1','2015-04-01','2015-05-29');
insert into screen_movie values('s7','m2','2015-03-28','2015-04-29');
insert into screen_movie values('s7','m3','2015-04-25','2015-06-02');
insert into screen_movie values('s7','m4','2015-03-17','2015-05-10');
insert into screen_movie values('s8','m1','2015-04-01','2015-05-29');
insert into screen_movie values('s8','m2','2015-03-28','2015-04-29');
insert into screen_movie values('s8','m3','2015-04-25','2015-06-02');
insert into screen_movie values('s8','m4','2015-03-17','2015-05-10');
insert into time values('s1','m1','07:20','09:20','2015-04-01',1);
insert into time values('s1','m2','10:20','12:20','2015-04-01',2);
insert into time values('s1','m3','13:20','15:20','2015-04-01',3);
insert into time values('s1','m4','16:20','18:20','2015-04-01',4);
insert into time values('s2','m1','07:20','09:20','2015-04-01',1);
insert into time values('s2','m2','10:20','12:20','2015-04-01',2);
insert into time values('s2','m3','13:20','15:20','2015-04-01',3);
insert into time values('s2','m4','16:20','18:20','2015-04-01',4);
insert into time values('s3','m1','07:20','09:20','2015-04-01',1);
insert into time values('s3','m2','10:20','12:20','2015-04-01',2);
insert into time values('s3','m3','13:20','15:20','2015-04-01',3);
insert into time values('s3','m4','16:20','18:20','2015-04-01',4);
insert into time values('s4','m1','07:20','09:20','2015-04-01',1);
insert into time values('s4','m2','10:20','12:20','2015-04-01',2);
insert into time values('s4','m3','13:20','15:20','2015-04-01',3);
insert into time values('s4','m4','16:20','18:20','2015-04-01',4);
insert into seat values('s1','a','1');
insert into seat values('s1','a','2');
insert into seat values('s1','a','3');
insert into seat values('s1','a','4');
insert into seat values('s1','a','5');
insert into seat values('s1','a','6');
insert into seat values('s1','a','7');
insert into seat values('s1','a','8');
insert into seat values('s1','a','9');
insert into seat values('s1','a','10');
insert into seat values('s1','a','11');
insert into seat values('s1','a','12');
insert into seat values('s1','a','13');
insert into seat values('s1','a','14');
insert into seat values('s1','a','15');
insert into seat values('s1','a','16');
insert into seat values('s1','a','17');
insert into seat values('s1','a','18');
insert into seat values('s1','b','1');
insert into seat values('s1','b','2');
insert into seat values('s1','b','3');
insert into seat values('s1','b','4');
insert into seat values('s1','b','5');
insert into seat values('s1','b','6');
insert into seat values('s1','b','7');
insert into seat values('s1','b','8');
insert into seat values('s1','b','9');
insert into seat values('s1','b','10');
insert into seat values('s1','b','11');
insert into seat values('s1','b','12');
insert into seat values('s1','b','13');
insert into seat values('s1','b','14');
insert into seat values('s1','b','15');
insert into seat values('s1','b','16');
insert into seat values('s1','b','17');
insert into seat values('s1','b','18');
commit;
부트스트랩 기반으로 영화 리스트 ui를 만들고 이에 필요한 query를 짜야한다.
(1) 특정 상영관과 날짜를 선택했을때 영화목록을 받아오고 (선호도, 개봉일자) 중 하나를 택하여 정렬할수 있는 쿼리
(2) (1)에 더하여 영화를 카테고리별로 받아오는 쿼리
ex)
//스크린코드가 s1인 스크린에 상영중인 영화를 출력하는 쿼리.
select * from movie where moviecode IN (select moviecode from screen_movie where SCREENCODE='s1');
//스크린코드가 s1이고 현재날짜가 해당영화의 시작일과 종료일 사이에 있을때 해당하는 영화를 출력하는 쿼리.
select * from movie where moviecode IN (select moviecode from screen_movie where SCREENCODE='s1' and '2015-05-20' > STARTDATE and '2015-05-20'< ENDDATE);
//스크린코드가 s1이고 현재날짜가 해당영화의 시작일과 종료일 사이에 있고 장르가 코믹인 영화를 출력하는 쿼리.
select * from movie where moviecode IN (select moviecode from screen_movie where SCREENCODE='s1' and '2015-05-20' > STARTDATE and '2015-05-20'< ENDDATE) and genre='코믹';
//스크린코드가 s1이고 현재날짜가 해당영화의 시작일과 종료일 사이에 있고 장르가 코믹인 영화를 선호도가 높은 영화부터 정렬하여 출력하는 쿼리.
select * from movie where moviecode IN (select moviecode from screen_movie where SCREENCODE='s1' and '2015-05-20' > STARTDATE and '2015-05-20'< ENDDATE) and genre='코믹' order by rating DESC;
'활동사항 > db설계(2015.03~2015.06)' 카테고리의 다른 글
최종ERD와 프로시저 단위의 트랜잭션들 (0) | 2015.06.01 |
---|---|
예약 프로시저 생성시 오류 (0) | 2015.05.17 |
oracle 영화관련 테이블 작업하면서...(2) (0) | 2015.05.17 |
oracle 영화관련 테이블 작업하면서..(1) (0) | 2015.03.31 |
node.js oracle 연동 총정리 (3) | 2015.03.27 |
댓글을 달아 주세요