야놀자 한방쿼리~

우선 AQueryTool은 다음과 같다

URL : https://aquerytool.com/aquerymain/index/?rurl=d422880c-c105-48f5-a01d-395e644759bc&
Password : 04u855

 

1번째 한방쿼리

select A.name as 숙소명, RR.rating as 별점, U.nickname as 닉네임, R.roomName as 객실명,RR.comment as 후기내용,
       case
            when TIMESTAMPDIFF(hour, RR.writeDate, current_timestamp) < 24
            then concat(TIMESTAMPDIFF(hour, RR.writeDate, current_timestamp), '시간전')
            else concat(TIMESTAMPDIFF(day, RR.writeDate, current_timestamp), '일전')
       end as  작성일
from RoomReview RR
join Room R on R.id = RR.roomId
join Accommodation A on R.accommodationId = A.id
join Reservation R2 on RR.reservationId = R2.id
join User U on R2.userId = U.id
where A.id = 2;

 

2번째 한방쿼리

 

select A.rating as 등급, A.name as 호텔명, avg(RR.rating) as 별점, count(RR.id) as 후기개수,
       R.checkInDate as 체크인, R.checkOutDate as 체크아웃, 
       R.roomName as 객실명, R.accommodationStatus as 숙박여부,
       R.standardPeople as 기준인원, R.maximumPeople as 최대인원, 
       date_format(R.startTime, '%H:%i') as 숙박시작시간, 
       R.costPrice as 원가, R.salePrice as 판매가
from Accommodation A
join RoomReview RR on A.id = RR.accommodationId
join Room R on A.id = R.accommodationId
where A.id=2
group by R.roomName;

 

3번째 한방쿼리

select S1.name as 출발지, S2.name as 도착지, K.date as 날짜, K.ktxNumber as ktx번호,
       date_format(K.departureTime, '%H : %i') as 출발시간,
       date_format(K.arrivalTime, '%H : %i') as 도착시간,
       date_format(K.requiredTime, '%H : %i') as 소요시간,
       case
           when K.leftSeats = 'Y'
               then '예매가능'
           when K.leftSeats = 'N'
               then '좌석부족'
       end as 남은좌석,
       case
           when K.leftSeats = 'Y'
               then K.price
           when K.leftSeats = 'N'
               then '-'
       end as 가격
       from Ktx K
join Station S1 on K.departure = S1.id
join Station S2 on K.destination = S2.id
where S1.name = '서울' and S2.name = '전주'
;

 

4번째 한방쿼리

삽입을 위한 이미지1

삽입을 위한 이미지2

select C.detailType as 지역이용권,
       LARCI.image as 대표이미지,
       LAR.name as 상품이름,
       case
            when LAR.useToday = 0
            then 'O'
            when LAR.useToday = 1
            then 'X'
       end as 당일사용,
       case
            when LAR.fullRefund = 0
            then 'O'
            when LAR.fullRefund = 1
            then 'X'
       end as 미사용전액환불,
       T.costPrice as 원가,
       T.salePrice as 할인가
from LeisureAndRentalCar LAR
join Category C on C.id = LAR.categoryId
join LeisureAndRentalCarImage LARCI on LARCI.leisureAndTrafficId = LAR.id
join Ticket T on LAR.id = T.typeId
where C.id = 23
group by LAR.name;

 

5번째 한방쿼리

삽입을 위한 이미지3

select L.name as 레저명, L.description as 소개, L.validity as 유효기간,
       case
           when L.useToday = 0
           then '가능'
           else '불가능'
       end as 당일이용,
       L.closedDay as 휴무일,
       LARCI.image as 레저이미지
       from LeisureAndRentalCar L
join LeisureAndRentalCarImage LARCI on L.id = LARCI.leisureAndTrafficId
where L.id = 3;

 

6번째 한방쿼리

select C.subType as 카테고리, L.name as 레저명, T.name as 티켓명,
       L.validity as 유효기간, B.quantity as 수량, (T.salePrice * B.quantity) as 가격
from Basket B
join Category C on B.categoryId = C.id
join Ticket T on B.ticketId = T.id
join LeisureAndRentalCar L on T.typeId = L.id;

 

7번째 한방쿼리

 

select C.name as 지역, C2.detailType as 카테고리, L.name as 레저명, L.description as 레저소개,
       case
            when L.useToday = 0
            then 'O'
            else 'X'
       end as 당일사용,
       case
            when L.fullRefund = 0
            then 'O'
            else 'X'
       end as 당일사용,
       min(T.costPrice) as 원가, min(T.salePrice) as 판매가
from LeisureAndRentalCar L
join City C on C.id = L.cityId
join Category C2 on L.categoryId = C2.id
join Ticket T on L.id = T.typeId
where C.name = '경기'
group by T.typeId;

 

8번째 한방쿼리

select A.name as 숙소명, S.representative as 대표자명, S.brandName as 상호명, S.address as 주소,
       S.email as 이메일, S.phoneNumber as 연락처, S.businessNumber as 사업자등록번호
from Seller S
join Accommodation A on S.id = A.sellerId
where A.name = '경주 다솔강펜션(풀빌라)';

 

9번째 한방쿼리

 

select A1.name as 출발지, A2.name as 도착지, F.date as 날짜, F.airline as 항공사,
       date_format(F.departureTime, '%H : %i') as 출발시간, date_format(F.arrivalTime, '%H : %i') as 도착시간, F.requiredTime as 소요시간,
       case
            when F.direct = 'D'
            then '직항'
            when F.direct = 'I'
            then '경유'
       end as 직항,
       F.price as 가격 from Flight F
join Airport A1 on F.departure = A1.id
join Airport A2 on F.destination = A2.id;

 

10번째 한방쿼리

 

select C.subType as 카테고리, A.name as 숙소명, R.roomName as 객실명, R.roomInfo as 객실정보,
       R.standardPeople as 기준인원, R.maximumPeople as 최대인원, R.accommodationStatus as 숙박가능여부,
       case
            when R.status = 'N'
            then '예약마감'
            when R.status = 'Y'
            then R.salePrice
       end as 판매가
from Room R
join Accommodation A on R.accommodationId = A.id
join Category C on A.categoryId = C.id
where A.name = '켄싱턴리조트 설악비치';