SQL Questions, Select Statement and Table creation
$30-175 USD
Completed
Posted almost 21 years ago
$30-175 USD
Paid on delivery
Hotel Schema Hotel(hotelNo, hotelName, city) Room(roomNo, hotelNo, type, price) Booking(hotelNo, guestNo, dateFrom, dateTo, roomNo) Guest(guestNo, guestName, guestAddress) Simple Queries 5.7 List full details of all levels SELECT* FROM hotel; 5.8 List full details of all hotels in London SELECT hotelNo, hotelName FROM city; OR SELECT* FROM hotel WHERE city=’London’; 5.9 List the names and address of all guests living in London, alphabetically ordered by name. SELECT guestName, guestAddress FROM Guest WHERE London ORDERED BY guestName ASC; 5.10 List all double or family rooms with a price below $40.00 per night in ascending order of price. SELECT type, price FROM room WHERE type=’double’ OR ‘family’ AND price < 40 ORDER by price; 5.11 List the booking for which no dateTo has been specific SELECT booking FROM date to WHERE Aggregate Functions 5.12 How many hotels are there? SELECT COUNT (*) As my count FROM hotel; 5.13 What is the average price of a room SELECT room AVG (price) FROM Room Group by Price; 5.14 What is the total revenue per night from all double rooms? SELECT count (*) FROM room WHERE type=’double room’ 5.15 How many different guest have made booking for august SELECT Count (*) AS ‘different guests’ FROM booking WHERE date to = ‘August’ Subqueries and Joins 5.16 List the price and type of all rooms at the Grosvenor hotel SELECT room WHERE hotel=’Grosvenor Hotel’ ALL (SELECT room =’type’ AND ‘price’ 5.17 List all guests currently staying at the Grosvenor Hotel SELECT guestNo, guestName, guestAddress FROM Guest WHERE hotel ALL (SELECT hotel FROM Guest WHERE hotel=’Grosvenor’ 5.18 What is the total income from bookings for the Grosvenor Hotel today. SELECT hotelNo, guestNo, dateFrom, dateTo, roomNo (SELECT AVG (income) FROM booking 6.8 Create the Room, Booking, and Guests tables using the integrity enhancement features of SQL with the following constraints. (a) Type must be one of Single, Double, or Family (b) Price must be between $10.00 and $100.00 (c) RoomNo must be between 1 and 100 (d) DateFrom and dateTo must be greater than today’s date (e) The same room cannot be double-booked (f) The same guest cannot have overlapping bookings.
## Deliverables
1) The first assignment have to do with using SQL SELECT statement...Are the answers, I provided correct..If not, can you show me the correct statement and where the mistakes occured 2) Second Questions type has to do with creating a tables(6.8), I have no clue
## Platform
Windows