请问如何用SQLHotel(hotelNo,hotelName,hotelAddress)Room(hotelNo,roomNo,type,price)Guest(guestNo,guestName,guestAddress)Booking(hotelNo,guestNo,dateFrom,dateTo,roomNo)1)Howmanyhotelsarethere?2)Whatistheaveragepriceofaroom?3)What

Question

请问如何用SQL
Hotel (hotelNo,hotelName,hotelAddress)
Room (hotelNo,roomNo,type,price)
Guest (guestNo,guestName,guestAddress)
Booking (hotelNo,guestNo,dateFrom,dateTo,roomNo)
1) How many hotels are there?
2) What is the average price of a room?
3) What is the total revenue per night from all double rooms?
4) How many different guests have future bookings in each hotel?
5) List all guests who are currently staying in hotelNo 1.
6) List the number of rooms in each hotel.
7) What is the minimum and maximum price of rooms in each hotel?
8) What is the maximum possible daily revenue for each room size for each hotel?
1.一共多少酒店?
2.房间平均价格是多少?
3.双人房一晚的总共收入是多少?
4.有多少客人预定了酒店?
5.列出一共有多少客人正在hotelNo1.
6.列出每个酒店的房间号码.
7.每个酒店最高和最低价格是多少?
8.每个酒店每种房间一天最高收入是多少?

Answer 1

SQL时间我都忘了,呵呵,时间函数就用Oracle替换了,答题的时候加以说明就行,没问题的.
1) How many hotels are there?那里有多少家宾馆?
2) What is the average price of a room?一间房的平均价格是多少?
3) What is the total revenue per night from all double rooms?所有双人间每晚的总收入是多少?
4) How many different guests have future bookings in each hotel?每家宾馆里有多少客人预定了房间?
5) List all guests who are currently staying in hotelNo 1.罗列目前入住第一宾馆的客人
6) List the number of rooms in each hotel.罗列每家宾馆的房间数
7) What is the minimum and maximum price of rooms in each hotel?每家宾馆的最高和最低房价是多少?
8) What is the maximum possible daily revenue for each room size for each hotel?每家宾馆中每种类型的房间最高日收入是多少?
翻译的差别让结果差很多.
1.select count(*) from Hotel
2.select avg(price) from Room r(跟哪家宾馆没关系,题目没要求)
3.需要提供晚间的时间段,假设是晚8点-早8点
select count(r.price) from Room r
where r.type='double rooms'
and exists
(
select b.roomNo from Booking b
where b.roomNo=r.roomNo
and to_number(to_char(b.dateTo,'hh')) < 8
and to_number(to_char(b.dateFrom,'hh')) > 20
)
4.预定就是没入住,入住时间>当前时间
select count(b.guestNo) from Booking b
group by b.hotelNo
having b.dateFrom > sysdate
5.退房时间>当前时间,就是没退房正在居住的
select g.* from Guest g
where g.guestNo in
(
select b.guestNo from Booking b
where b.hotelNo=1
and b.dateTo > sysdate
)
6.select r.hotelNo,count(r.roomNo) from Room r
group by r.hotelNo
7.select r.hotelNo,max(r.price),min(r.price) from Room r
group by r.hotelNo
8.price单位是多少?一天还是一小时?假设是一天.
select count(r.roomNo)*r.price from Room r
group by r.hotelNo,r.type