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;executescalar方法
4) 2019年8⽉，有多少不同的客⼈订房？
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.guest
No 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
’2019-09-29’ AND b.dateTo > ‘2019-09-29’;
12) 昨天(2019-09-29)位于长沙的华天宾馆，哪些房间空闲⽆⼈住。
No AS roomNo FROM Room AS r, Hotel AS h WHERE r.hotelNo = h. hotelNo AND h.
name = ‘华天宾馆’No NOT IN(No AS roomNo FROM Booking AS b, Hotel AS h, Room AS r WHERE b.hotelNo =
h.hotelNo AND b.hotelNo = r.hotelNo No = 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⼊住位于长沙的华天宾馆，他想预订⼀个双⼈间，请为其列出所有可预订的双⼈间的房间号，价格。
No AS roomNo, r.price AS price FROM Room AS r, Hotel AS h WHERE h.hotelNo = r.hotelNo pe =
论坛网站制作教程
‘双⼈间’ AND h.name = ‘华天宾馆’ No NOT IN (No AS roomNo FROM Room AS r, Hotel AS h, Booking AS b WHERE h.hotelNo = b.hotelNo No = 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，对位于长沙的宾馆，它们最常订的房间分别是哪种类型？（是指⼊住的房间中，哪种类型的房间数最多）；
pe 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 No =
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 (dateT
o > 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 No 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
No AS roomNo, r.type AS type, r.price AS price FROM Room AS r, Hotel AS h WHER
E h.hotelNo = @hotelNo AND r.hotelNo = @hotelNo No NOT IN
(No FROM Booking AS b, Hotel AS h, Room AS r WHERE b.hotelNo = @hotelNo AND h.hotelNo = @hotelNo AND r.hotelNo = @hotelNo No = 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 ) );
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论