数据库设计实现管理基础篇第六版第六章习题 6.9到6.28解答

这是书上第四章的习题表格
Hotel (hotelNo, hotelName, city)

Room (roomNo, hotelNo, type, price)

Booking (hotelNo, guestNo, dateFrom, dateTo, roomNo)

Guest (guestNo, guestName, guestAddress)

遵循原则：关键字大写，名称小写

type类型：单人间 Single Room，双人间 Double Room，大床间 King Size & Queen Size Room，标准间 Standard，标准间单人住 TSU(Twin for Sole Use)，三人间 Triple，四人间 Quad，套间 Suite，公寓 Apartment，别墅 Villa

6.9.列出住在伦敦所有客人的姓名和地址，按字母表顺序排序

这是查询，用SELECT,列出的是姓名和客人，先写，再写来自的表，用WHERE过滤条件，最后ORDER BY按姓名排序

SELECT guestName,guestAddress FROM Guest WHERE guestAddress LIKE '%London%''
 ORDER BY guestName;

6.10.列出每晚房价在40英镑以下的所有双人间或套间，按价格升序

SELECT * FROM Room WHERE (type='Double' OR type='Suite' ) AND price<40 ORDER BY price;

6.11. 列出没有指定dateTo的预订情况

    SELECT * FROM Booking WHERE dateTo IS NULL;

聚集函数

就是COUNT,AVG这类函数，放在字段前面，用括号包围字段
6.12. 有多少酒店

    SELECT COUNT(*) FROM Hotel;

6.13. 房间的平均价格是多少

    SELECT AVG(price) FROM Room

6.14. 所有双人间的每晚的总收入是多少

    SELECT SUM(price) FROM Room WHERE type="Double"

6.15. 八月份有多少不同的客人订房

    SELECT COUNT(DISTINCT guestNo) FROM Booking 
    WHERE (dateFrom >=DATE'2018-08-01' AND dateFrom<=DATE'2018-08-31) OR (dateTO>=DATE'2018-08-01');

子查询和连接操作
6.16. 列出格罗夫纳酒店所有的房间的价格和类型

 SELECT price,type FROM Room r,Hotel h WHERE r.hotelNo=h.hotelNo AND HotelName='Grosvenor';

6.17. 列出当前住在格罗夫纳酒店的所有客人的情况
注意此时是当前，酒店名字与客人之间的关系是
Guest->gusetNo->Booking->hotelNo->Hotel->hotelName
然后注意是当前，所以注意dateFrom和DateTo

SELECT g.* FROM guest g,Booking b,Hotel h WHERE 
dateFrom<=now() AND dateTo>=now() AND 
h.hotelName="Grosvenor" AND g.guestNo=b.guestNo AND b.hotelNo=h.hotelNo;

6.18. 列出格罗夫纳酒店所有房间的情况，包括房间中住的客人的名字
找到Room->hotelNo->Hotel->hotelName
以及Room->roomNo->Booking->guestNo->Guest->guestName
同时使用左匹配，因为以房间为主

我发现CURRENT_DATE比now要有逼格一些

SELECT r.* FROM Room r LEFT JOIN 
		(SELECT g.guestName, h.hotelNo, b.roomNo FROM Guest g, Booking b, Hotel h
		WHERE g.guestNo = b.guestNo AND b.hotelNo = h.hotelNo AND
			hotelName= "Grosvenor" AND 
			dateFrom <= CURRENT_DATE AND 
			dateTo >= CURRENT_DATE) AS XXX
	ON r.hotelNo = XXX.hotelNo AND r.roomNo = XXX.roomNo;

6.19. 今天格罗夫纳酒店订房的总收入是多少
需要求这个酒店所有有人住的房间的价格之和，
有酒店名，收入，顾客住的房间，所以和Hotel，Booking，Room三个表相关

所求和的price必须满足rooNo与Booking的相同，日期在订房日期之内，表示有人住，hotelNo与Hotel中HotelName为格罗夫纳的HotelNo相同，表示是格罗夫纳的酒店，

SELECT SUM(price) FROM Booking b,Hotel h,Room r
WHERE hotelName="Grosvenor" AND h.hotelNo=r.hotelNo AND r.roomNo=b.roomNo
AND (dateFrom<=CURRENT_DATE AND dateTo>=CURRENT_DATE);

6.20. 列出格罗夫纳酒店当前没被使用的房间
按照题意，应该列出Room表的所有信息，其中满足HotelNo=格罗夫纳的HotelNo
RoomNo不是Booking中在订房日期范围内的RoomNo

SELECT * FROM Room r,Hotel h 
WHERE h.hotelNo=r.hotelNo AND hotelName="Grosvenor" AND 
roomNo NOT IN (SELECT roomNo FROM Booking WHERE 
dateFrom<=CURRENT_DATE AND dateTo>=CURRENT_DATE);

6.21. 格罗夫纳酒店空房损失是多少
就是计算没被租出去的房的价格之和
所以选出Room中的SUM(price),其中hotelNo与Hotel中HotelName为格罗夫纳HotelNo一致，roomNo不包含Booking中的在订房日期范围内的RoomNo

SELECT SUM(price) FROM Room r,Hotel h WHERE
r.hotelNo=h.hotelNo AND hotelName="Grosvenor" AND
roomNo NOT IN
(SELECT roomNo FROM Booking WHERE
dateFrom>=CURRENT_DATE OR dateTo<=CURRENT_DATE);

分组

6.22. 列出每个酒店的房间数量
列出房间数量，就是计算count房间号的总数，然后根据酒店号分组

SELECT * COUNT(roomNo) FROM Room GROUP BY hotelNo;

6.23. 列出伦敦每个酒店的房间数量
和上一个题目的区别是Hotel的city变成了伦敦

SELECT hotelNo,COUNT(roomNo) FROM Room WHERE hotelNo=
(SELECT hotelNo FROM Hotel WHERE city =='London') GROUP BY hotelNo;

6.24. 八月份每个酒店的平均订房数是多少
平均数用AVG，所以我们将所有酒店在八月份的的总订房数相加，再取平均值

SELECT AVG(x) FROM (SELECT hotelNo,COUNT(RoomNo) AS x FROM Booking WHERE
 (dateFrom <='2018-08-01' AND dateTo >='2018-08-01' ) OR dateFrom>='2018-08-01'  
 AND dateFrom<='2018-08-31') GROUP BY hotelNo);

6.25. 伦敦每个酒店最常定的房间类型是什么
就是相同type数量最多的酒店，以type来分类形成一个表，然后用max找出数量最多的表

SELECT MAX(X) FROM(SELECT type,COUNT(type) AS X FROM Hotel AS h,Room AS r,Booking 
AS b WHERE r.hotelNo=h.hotelNo,h.hotelNo=b.hotelNo 
AND city = 'London' OEDER BY type;

6.26. 今天每个酒店的空房损失是多少
使用SUM算出空房总price即可，所以注意要从总的room中选出那些不属于Booking中在订房时期的roomNo，同时以hotelNo分组

SELECT hoteNo,SUM(price) FROM Room r WHERE roomNo NOT IN 
(SELECT roomNo FROM Booking b, Hotel h WHERE dateFrom>=date(now()) 
AND dateTo<=date(now()) AND b.hotelNo=h.hotelNo) GROUP BY hotelNo;

修改表

6.27. 向每一个表中插入一行

INSERT INTO Hotel (HotelNo,HotelName,city) VALUES (1,"酒店1“,”城市1")
INSERT INTO Room(roomNo,hotelNo,type,price) VALUES (1,1,"D",15)
INSERT INTO Booking(hotelNo,guestNo,dateFrom,dateTo,roomNo) VALUES (1,1,"2018-10-10","2018-10-10",1)
INSERT INTO Guest(guestNo,guestName,guestAddress) VALUES (1,"cs","城市1");

*注意：这里使用" "或是’ '没有本质区别，都可以，当需要嵌套使用引号的时候就交替使用 *

6.28. 所有房间的价格提高5%
这里是用更新关键字UPDATE