영화예매 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;