编写来自不同表的SQL语句 [英] Write SQL statements from different tables

问题描述

我被要求创建几个SQL语句来检索有关豪华酒店数据库的信息。



列出每个酒店中从未有过的所有房间按照酒店编号按顺序保留。

我有SQL语句检索房间已保留但我的问题是检索那些尚未保留的。

I'm being asked to create a couple of SQL statements to retrieve information about a Luxury Hotel Database.

List all the rooms in each hotel that have never been reserved in order by hotel number.
I have the SQL statement retrieved the rooms what have been reserved but my problem is retrieving those that have not been reserved.

SELECT RoomNo FROM RESERVATION WHERE RoomNo ORDER BY HotelNo

按名称列出客人，并在每家酒店预订客房的次数。按照从最常见到最不常见的客人的顺序排列清单。



以下是我们正在处理的表格。

List the guests by name and the number of times each has reserved a room at one of our hotels. Arrange the list in order from most-frequent to least-frequent guest.

Here are the tables we are working on.

HOTEL (HotelNo, HotelName, City)

ROOM_TYPE (RoomType, Descr, RoomRate)

ROOM (HotelNo, RoomNo, RoomType, PhoneExt)
FK1: Foreign key HotelNo references HOTEL
FK2: Foreign key RoomType references ROOM_TYPE
GUEST (GuestNo, FirstName, LastName, Address, City, State, ZipCode)
GUEST_PHONE (PhoneNumber, GuestNo, PhoneType)
FK: Foreign key GuestNo references GUEST

RESERVATION (ResNum, HotelNo, RoomNo, GuestNo, ArrivalDate, DepartureDate, NumPersons)
FK1: Foreign Key (HotelNo, RoomNo) references ROOM
FK2: Foreign Key GuestNo references GUEST

推荐答案

帮助您解决问题的一些提示：

1.

Some tips to help you solve the problems:
1.

引用：

列出每个酒店中从未按照酒店编号预订的所有房间

List all the rooms in each hotel that have never been reserved in order by hotel number

如何=> ;使用 NOT EXISTS [ ^ ]和ORDER BY（您应该已经知道，否则请问Google）

2.

How to => use NOT EXISTS[^] and ORDER BY (you should already know, else ask Google)
2.

引用：

按姓名列出客人，并在每家酒店预订房间的次数。按照从最常见到最不常见的客人的顺序排列列表。

List the guests by name and the number of times each has reserved a room at one of our hotels. Arrange the list in order from most-frequent to least-frequent guest.

如何=> 内部联接 [< a href =https://support.office.com/en-ca/article/Join-tables-and-queries-3f5838bd-24a0-4832-9bc1-07061a1478f6#bminner\"target =_ blanktitle =新窗口> ^ ]通过主键和外键的2个相关表，使用 COUNT函数 [ ^ ]和ORDER BY。

How to => Inner join[^] 2 related tables through primary key and foreign key, use COUNT Function[^], and ORDER BY.

引用：

I有SQL语句检索房间已保留但我的问题是检索那些尚未保留的。选择RoomNo FROM RESERVATION在哪里RoomNo ORDER BY HotelNo

I have the SQL statement retrieved the rooms what have been reserved but my problem is retrieving those that have not been reserved. SELECT RoomNo FROM RESERVATION WHERE RoomNo ORDER BY HotelNo

SELECT ROOM.HotelNo, ROOM.RoomNo 
from Room 
where ROOM.RoomNo Not In  (Select RESERVATION.RoomNo from RESERVATION)
Order by ROOM.HotelNo

我认为

would do you I think

这篇关于编写来自不同表的SQL语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！