Write SQL statements from different tables

Question

0.00/5 (No votes)

See more:

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.

SQL

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.

SQL

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

Posted 8-Mar-15 14:28pm

Member 11508350

Updated 10-Mar-15 1:05am

Magic Wonder

v2

Add a Solution

Comments

CodingLover 8-Mar-15 21:51pm

Do you know about inner/outer join?

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Peter Leow · Answer 1 · 2015-03-08T16:21:00

Some tips to help you solve the problems:
1.

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

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

Quote:
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.

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

_Maxxx_ · Answer 2 · 2015-03-08T16:25:00

Solution 2

Quote:
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

SQL

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

Posted 8-Mar-15 16:25pm

_Maxxx_

Comments

[no name] 8-Mar-15 23:01pm

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.

My two related tables are Guest and Reservation.I can't have the FK GuestNo be one of the attributes.

Thus so far what I have
Hide Copy Code
Select FirstName, LastName, GuestNo FROM GUEST INNER JOIN RESERVATION ON GUEST.firstname = RESERVATION.guestno

I'm not sure about GuestNo.