MySQL系列（六）：SQL课后作业（以连锁酒店数据库为例）

希尔顿是一家经营宾馆的集团公司。它在全世界各地都开有宾馆。其住宿业务数据库中的四个表是：

• Hotel (hotelNo, name, city)
• Room (roomNo, hotelNo, type, price)
• Booking (hotelNo, guestNo, dateFrom, dateTo, roomNo)
• Guest (guestNo, name, city, creditNo)

房间类型有：单人间，双人间，套间，商务间；Guest中的city为户籍地。每个宾馆有客房，客户入店住宿时要预定或者现场登记，要求客户提供的信息有姓名，身份证号码，户籍地，信用卡号。

用SQL表达如下操作：

一、 初级：

1) 列出位于长沙的所有旅馆的基本情况，包括宾馆名称，房间类型，房间数量；

SELECT h.name, r.type, COUNT(*) AS roomCount FROM Hotel AS h, Room AS r WHERE h.hotleNo = r.hotelNo GROUP BY h.name, r. type;

2) 列出2019-09-29这天，住在位于长沙的华天宾馆的所有客人的姓名，所住房间号，按姓名排序；

SELECT g.name AS name, b.roomNo AS roomNo FROM Guest AS g, Booking AS b, Hotel AS h WHERE h.city = ‘长沙’ AND h.name = ‘华天宾馆’ AND b.dateFrom <= DATE ’2019-09-29’ AND b.dateTo > DATE ‘2019-09-29’ AND g. guestNo = b.guestNo AND r.hotelNo = h.hotelNo ORDER BY name;

3) 列出位于长沙的宾馆，每晚房价在140以下的所有双人间或套间，按宾馆名称，价格升序排序；

SELECT h.name AS hotelName, r.type AS roomType, r.price AS price FROM Hotel AS h, Room AS r WHERE h.city = ‘长沙’ AND roomType IN (‘双人间’, ‘套间’) AND price<140 ORDER BY hotelName, price;

4) 2019年8月，有多少不同的客人订房？

SELECT COUNT(DISTINCT guestNo) FROM Booking WHERE (dateFrom >= DATE ‘2019-08-01’ AND dateFrom < DATE ‘2019-09-01’) OR (dateTo > DATE ‘2019-08-01’ AND dateTo <= DATE ‘2019-09-01’);

5) 列出没有指定dataTo的预订记录；

SELECT hotelNo, guestNo, dateFrom, dateTo, roomNo FROM Booking WHERE dataTo IS NULL;

6) 集团公司共有多少旅馆？

SELECT COUNT(*) AS hotelCount FROM Hotel;

7) 以城市分组，求出其宾馆的房间的平均价格是多少？

SELECT h.city AS city, AVG(r.price) AS avgPrice FROM Hotel AS h, Room AS r WHERE h.hotelNo = Room.hotelNo GROUP BY city;

8) 列出华天宾馆在不同城市，其包含的房间类型及其平均价格。输出所在城市名称，类型，平均价格；

SELECT h.city AS city, r.type AS type, AVG(r.price) AS avgPrice FROM Hotel AS h, Room AS r WHERE h.name = ‘华天宾馆’ AND h.hotelNo = r.hotelNo GROUP BY city, type;

9) 列出在(2019-09-30)这天，户籍地为北京的住客情况，包含宾馆所在城市，宾馆名称，房间号，姓名，身份证号;

SELECT h.city AS city, h.name AS hotelName, b.roomNo AS roomNo, g.name AS name, g.creditNo AS creditNo FROM Hotel, Booking, Guest WHERE h.hotleNo = b. hotleNo AND g.guestNo = b.guestNo AND g.city = ‘北京’ AND b.dateFrom <= DATE’2019-09-30’ AND b.dateTo > DATE ‘2019-09-30’;

10) 对于位于长沙的华天宾馆，它的所有房间如果都住满了，一天的总收入是多少？

SELECT SUM(r.price) AS idealEarning FROM Hotel AS h, Room AS r WHERE h.hotelNo = r.hotelNo AND h.city = ‘长沙’ AND h.name = ‘华天宾馆’;

11) 昨天(2019-09-29) 位于长沙的华天宾馆，因房间有客户入住了，其实际总收入是多少？

SELECT SUM(r.price) AS realEarning FROM Hotel AS h, Room AS r, Booking AS b WHERE h.hotelNo = b.hotelNo AND r.roomNo = b.roomNo AND r.hotelNo = b.hotelNo AND h.city = ‘长沙’ AND h.name = ‘华天宾馆’ AND b.dateFrom <= ’2019-09-29’ AND b.dateTo > ‘2019-09-29’;

12) 昨天(2019-09-29)位于长沙的华天宾馆，哪些房间空闲无人住。

SELECT r.roomNo AS roomNo FROM Room AS r, Hotel AS h WHERE r.hotelNo = h. hotelNo AND h.name = ‘华天宾馆’ AND r.roomNo NOT IN(SELECT r.roomNo AS roomNo FROM Booking AS b, Hotel AS h, Room AS r WHERE b.hotelNo = h.hotelNo AND b.hotelNo = r.hotelNo AND r.roomNo = b.roomNo AND b.dateFrom <= DATE ‘2019-09-29’ AND b.dateTo > DATE ‘2019-09-29’);

13) 昨天2019-09-29，每个旅馆的房间入住率是多少？输出字段为：城市名称，宾馆名称，入住率

SELECT h.city AS city, h.name AS name, ( (SELECT COUNT(*) FROM Booking AS b WHERE b.hotelNo = h.hotelNo AND b.dateFrom <= DATE ’2019-09-29’ AND b.dateTo > DATE ‘2019-09-29’) / (SELECT COUNT(*) FROM Room AS r WHERE r.hotelNo = h.hotelNo) ) AS rate FROM Hotel AS h;

14) 今天是2019-09-30，一客户想在2019-10-01至2019-10-03入住位于长沙的华天宾馆，他想预订一个双人间，请为其列出所有可预订的双人间的房间号，价格。

SELECT r.roomNo AS roomNo, r.price AS price FROM Room AS r, Hotel AS h WHERE h.hotelNo = r.hotelNo AND r.type = ‘双人间’ AND h.name = ‘华天宾馆’ AND r.roomNo NOT IN (SELECT r.roomNo AS roomNo FROM Room AS r, Hotel AS h, Booking AS b WHERE h.hotelNo = b.hotelNo AND b.roomNo = r.roomNo AND b.hotelNo = r.hotelNo AND ( (b.dateFrom >= DATE ‘2019-10-01’ AND b.dateFrom < DATE ‘2019-10-03’) OR (b.dateTo > DATE ‘2019-10-01’ AND b.dateTo <= DATE ‘2019-10-03’) );

15) 昨天2019-09-29，对位于长沙的宾馆，它们最常订的房间分别是哪种类型？（是指入住的房间中，哪种类型的房间数最多）；

SELECT r.type AS type, MAX(COUNT(*)) FROM Booking AS b, Room AS r, Hotel AS h WHERE b.dateFrom <= DATE ‘2019-09-29’ AND b.dateTo > DATE ‘2019-09-29’ AND h.city = ‘长沙’ AND h.hotelNo = b.hotelNo AND r.roomNo = b.roomNo AND r.hotelNo = b.hotelNo GROUP BY type;

16) 请列出2019年8月，预订宾馆次数大于5次的客户，包括姓名，身份证号，户籍地。

SELECT g.name AS name, g.city AS city, g.creditNo AS creditNo FROM Guest AS g, Booking AS b WHERE b.guestNo = g.guestNo AND ( (dateFrom >= DATE ‘2019-08-01’ AND dateFrom < DATE ‘2019-09-01’) OR (dateTo > DATE ‘2019-08-01’ AND dateTo <= DATE ‘2019-09-01’) ) GROUP BY b.guestNo HAVING COUNT(*) > 5;

17) 往每个表中插入一行数据；

INSERT INTO Hotel (hotelNo) VALUES (2019109);

INSERT INTO Room (roomNo, hotelNo) VALUES (208, 2019109);

INSERT INTO Guest (guestNo) VALUES (2019100);

INSERT INTO Booking VALUES (2019109, 2019100, DATE ’2019-10-01’, DATE ’2019-10-02’, 208);

18) 将所有房间的价格提高5%。

UPDATE Room SET price = price + price / 20;

19) 创建与Booking表具有相同结构的表archival_booking，用于保存历史记录。用INSERT语句，将表Booking中2019年1月1日前的预订数据转入到表archival_booking中，在表Booking不再保留。

CREATE TABLE archival_booking (hotelNo CHAR(10) NOT NULL, guestNo CHAR(10) NOT NULL, dateFrom DATE NOT NULL, dateTo DATE, roomNo CHAR(10) NOT NULL,

PRIMARY KEY (hotelNo, guestNo, dateFrom, roomNo), FOREIGN KEY (hotelNo) REFERENCES Hotel(hotelNo), FOREIGN KEY (guestNo) REFERENCES Guest(guestNo), FOREIGN KEY (hotelNo, roomNo) REFERENCES Room(hotelNo, roomNo) );

INSERT INTO archival_booking(hotelNo, guestNo, dateFrom, dateTo, roomNo) (SELECT hotelNo, guestNo, dateFrom, dateTo, roomNo FROM Booking WHERE dateTo < DATE ‘2019-01-01‘);

DELETE FROM Booking WHERE dateTo < DATE ‘2019-01-01‘;

20) 求出2019年，对位于长沙的每个宾馆，都有过预订的客户，列出其姓名，身份证号，户籍地。

SELECT g.guestNo AS guestNo, g.name AS name, g.city AS city, g.creditNo, COUNT(DISTINCT b.hotelNo) AS creditNo FROM Guest AS g, Booking AS b, Hotel AS h WHERE b. guestNo = g. guestNo AND b.hotelNo = h.hotelNo AND h.city = ‘长沙’ AND ( (dateFrom >= DATE ‘2019-01-01’ AND dateFrom < DATE ‘2020-01-01’) OR (dateTo > DATE ‘2019-01-01’ AND dateTo <= DATE ‘2020-01-01’) ) GROUP BY guestNo HAVING COUNT(DISTINCT b.hotelNo) = (SELECT COUNT(*) FROM Hotel WHERE h.city = ‘长沙’);

二、 高级：

1）创建一个视图，列出2019-09-30这天，入住在编号为'H0001'的宾馆，输出字段包括客人名字，房间号，房间类型，价格；

CREATE VIEW H000120190930 (name, roomNo, type, price) AS SELECT g.name AS name, b.roomNo AS roomNo, r.type AS type, r.price AS price FROM Guest AS g, Room AS r, Booking AS b, Hotel AS h WHERE g.guestNo=Booking.guestNo AND b.hotelNo=h.hotelNo AND b.roomNo=r.roomNo AND r.hotelNo=h.hotelNo AND h.hotelNo=’H0001’ AND b.dateFrom <= DATE ’2019-09-30’ AND b.dateTo > DATE ‘2019-09-30’;

2）创建一个存储过程，以起始日期，结束日期，hotelNo为输入参数，求出所指预定期间，这个酒店可以预定的房间列表，包括房间号，类型，价格；按价格升序排列；

CREATE PROCEDURE available (@dateFrom IN DATE, @dataTo IN DATE, @hotelNo IN VARCHAR) AS

BEGIN

SELECT r.roomNo AS roomNo, r.type AS type, r.price AS price FROM Room AS r, Hotel AS h WHERE h.hotelNo = @hotelNo AND r.hotelNo = @hotelNo AND r.roomNo NOT IN

(SELECT r.roomNo FROM Booking AS b, Hotel AS h, Room AS r WHERE b.hotelNo = @hotelNo AND h.hotelNo = @hotelNo AND r.hotelNo = @hotelNo AND r.roomNo = b.roomNo AND ( (b.dataFrom >= @dateFrom AND b.dataFrom < @dataTo) OR (b.dataTo > @dateFrom AND b.dataTo <=@dateTo) OR (b.dataFrom < @dateFrom AND b.dataTo > @dateTo) ) )

ORDER BY price;

END;

3）创建一个触发器，不允许发生一个客户在两个不同的宾馆，有起始日期相同的预订；

CREATE TRIGGER single_order

BEFORE INSERT ON Booking

REFERENCING NEW ROW AS new

FOR EACH ROW

BEGIN

WHEN(EXIST SELECT hotelNo, guestNo, dateFrom FROM Booking AS b WHERE b.hotelNo <> @new.hotelNo AND b.guestNo = @new.guestNo AND b.dataFrom = @new.dataFrom)

raise_application_error(20000, ‘这个预定与已有预定冲突’);

END;

4）对于如下数据库，用SQL语句表达如下操作：列出公司所有的领导（部门经理的上司）。

SELECT e_no,name,bdate,title,salary FROM emp WHERE emp.e_no IN ( SELECT super_eno FROM emp WHERE emp.e_no IN ( SELECT mrg_no FROM dept ) );