数据库设计实现管理基础篇第六版第六章 习题 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

UPDATE Room SET price=price*1.05