Using Microsoft Access. SQL entries booking (hotelNo, guestNo, dateFrom, dateTo,
ID: 3707422 • Letter: U
Question
Using Microsoft Access. SQL entries
booking (hotelNo, guestNo, dateFrom, dateTo, roomNo, rateBooked)
employee (empID, empFirst, empLast, empDateHired, empEmail, managerID, hotelNo)
guest (guestNo, guestFirst, guestLast, guestStreet, guestCity, guestState, guestPhone, guestEmail)
hotel (hotelNo, hotelName, hotelCity, hotelPhone)
room (roomNo, hotelNo, type, standardprice)
--------------------------------------------------------------------------------------------------
Query1: Add an Amenity table to the database with the following columns: hotel_id and amenity_name (assume amenity names are unique). hotel_id should be designated as a foreign key referencing the hotel table. Hint: use integer as the data type for hotelno. Make sure to designate the primary key-which is the combination of hotel_ID and Amenity name.
Query 2: Add a column to the appropriate table to record the state the hotel is in.
Query 3: change the name of the Holiday Inn in Tulsa to Holiday Inn Deluxe
Query 4: delete room 123 in hotel 6 from the database
Query 5: Add the following amenity to the database: hotel 6 has a swimming pool.
Query 6: Create a query that shows all of the guests and the bookings of those rooms. The result should include all guests, even those guests that have not booked any rooms. Show the guestNo, guestFirst, guestLast, HotelNo aand roomNo in the result.
Query 7: How many bookings have there been in each city?
Query 8: List each employee’s first name, last name, their hotelNo, their manager’s first name and their manager’s last name.
Query 9: which guests have yahoo email accounts?
Query 10: which hotel (by name) has the most expensive room ?
Query11: which guests (by name) have not booked rooms in hotel 1?
Query 12: what is the average price of hotel rooms in Stillwater?
Query 13: List the count of the single rooms for each hotel for any hotel that has less than 2 single rooms.
Query 14: drop the standard price column from the room table.
Explanation / Answer
Solution:
Query 1:
CREATE TABLE Amenity(
hote_id int(20) NOT NULL,
amenity_name varchar(40) NOT NULL UNIQUE,
PRIMARY KEY(hotel_id, amenity_name)
CONSTRAINT FK_hotel_id FOREIGN KEY (hotel_id)
REFERENCES hotel(hotelNo)
);
Query 2:
ALTER TABLE hotel
ADD COLUMN status boolean;
Query 3:
UPDATE hotel
SET hotelName= 'Holiday Inn Delux'
WHERE hotelName = 'Holiday Inn';
Query 4:
DELETE FROM hotel
WHERE WHERE roomNo= 123 AND hotleNo= 6;
I hope this helps if you find any problem. Please comment below. Don't forget to give a thumbs up if you liked it. :)
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.