oracle sql

insert into pMember values('1','test1','1111','홍길동','010-2333-0100','서울','M');

insert into pMember values('2','test2','1111','김민수','010-2333-3333','부산','M');

insert into pMember values('3','test3','1111','박찬호','010-2314-0100','서울','M');

insert into pMember values('4','test4','1114','이영희','010-2333-0100','대구','F');

insert into pMember values('5','test5','1115','이진아','010-2551-5120','서울','F');

insert into pReserve values('1','test1','2015-09-01','2015-09-02','2','1','Y','2015-08-15 12:30:25' );

insert into pReserve values('2','test1','2015-09-17','2015-09-18','3','2','N','2015-09-15 12:30:25' );

insert into pReserve values('3','test2','2015-09-15','2015-09-16','3','3','Y','2015-08-15 12:30:25' );

insert into pReserve values('4','test3','2015-09-15','2015-09-16','4','1','Y','2015-08-16 12:30:25' );

insert into pReserve values('5','test4','2015-10-15','2015-10-16','4','1','Y','2015-08-16 12:30:25' );

insert into pRmInfo values('1','JAVA방','5','70000');

insert into pRmInfo values('2','ORACLE방','10','100000');

insert into pRmInfo values('3','HTML방','3','50000');

insert into pRmInfo values('4','MYSQL방','2','60000');

1.pMember 테이블에서 최근 등록한 회원순으로 출력 (unq)기준

select * from pMember order by unq desc;

2.아이디, 이름, 전화번호, 예약시작일, 예약종료일 출력(join)

select a.id,a.name,a.hp,b.sDate,b.eDate from pMember a, pReserve b  where a.id=b.id;;

3.아이디, 이름, 전화번호, 예약시작일, 예약종료일 출력(날짜포맷: 2015/09/10)

select a.id,a.name,a.hp,to_char(b.sDate,'YYYY/MM/DD'),to_char(b.eDate,'YYYY/MM/DD') from pMember a, pReserve b where a.id=b.id;

4.아이디, 이름, 전화번호, 예약시작일, 예약종료일 출력(날짜포맷: 2015/09/10,숙박예정자)

select a.id,a.name,a.hp,to_char(b.sDate,'YYYY/MM/DD'),to_char(b.eDate,'YYYY/MM/DD'),'숙박예정자' from pMember a, pReserve b where a.id=b.id and b.sDate>=sysdate;

5.아이디, 이름, 전화번호, 예약시작일, 예약종료일 출력(날짜포맷: 2015/09/10,숙박했던 사람)

select a.id,a.name,a.hp,to_char(b.sDate,'YYYY/MM/DD'),to_char(b.eDate,'YYYY/MM/DD'),'숙박했던사람' from pMember a, pReserve b where a.id=b.id and  b.eDate < sysdate;

6.pMember 테이블에서 특정 아이디를 아래와 같이 출력

test1 김만수(남) 서울

select id,name||'('||decode(gender,'M','남','여')||')'as 정리 ,addr from pMember where id='test1';

7.pMember 테이블의 아이디, 이름, 주소 출력(아이디 기준으로 역준정령하여 2~4행까지 출력)

select rm,id,name,addr from (select rownum rm,id, name,addr from (select id,name,addr from pMember order by id desc)) where rm>=2 and rm<=4;

8.pReserve에서 아이디별 예약건수 출력

select count(*),id from pReserve group by id;

9.아이디, 이름, 전화번호, 방이름,예약시작일,예약종료일(특정아이디의)

select a.id,a.name,a.hp,c.rName,b.sDate,b.eDate from pMember a, pReserve b, pRmInfo c where a.id=b.id and b.rNum=c.rNum;

10.특정 방의 ->방 이름,예약시작일,예약종료일 출력

select c.rName,b.sDate,b.eDate from pReserve b,pRmInfo c where b.mCnt=c.mCnt;

10.가장 가격이 비싼 방을 예약한 사람들 출력

select a.name from pMember a, pReserve b where a.id=b.id 

and b.rNum = 

(select rNumfrom pRmInfo where money=(select max(money) from pRmInfo) );

11. oracle방의 ->방이름,예약실작일,예약종료일 출력

select c.rName,b.sDate,b.eDate from pReserve b,pRmInfo c where b.rNum=c.rNum and c.rName like '%HTML방%';

12.아이디, 이름,예약시작일 예약종료일,결재여부(완료/미결재) 출력

select a.id,a.name,b.sDate,b.eDate,decode(b.app,'Y','완료','N','미결재') from pMember a,pReserve b where a.id=b.id;

13.다음 달에 예약걸려 있는 사람의 아이디,이름,예약기간 출력

select a.id,a.name,b.sDate,b.eDate from pMember a, pReserve b where a.id=b.id and to_char(sDate,'YYYY/MM')=to_char(add_months(sysdate,1),'YYYY/MM');

14.이번달 총 수입금액

select sum(c.money) from pReserve b,pRmInfo c where b.rNum=c.rNum and to_char(sDate,'YYYY/MM')=to_char(sysdate,'YYYY/MM') and to_char(eDate,'YYYY/MM')=to_char(sysdate,'YYYY/MM');

15.월 예약건수,수입금액 출력

select count(unq),sum(c.money) from pReserve b, pRmInfo c where b.rNum=c.rNum and  to_char(sDate,'YYYY/MM')=to_char(sysdate,'YYYY/MM') and to_char(eDate,'YYYY/MM')=to_char(sysdate,'YYYY/MM');