段江涛《数据库原理》第3章作业.docVIP

The following tables form part of a database held in a relational DBMS: Hotel (hotelNo, hotelName, city) Room (roomNo, hotelNo, type, price) Booking (hotelNo, guestNo, dateFrom, dateTo, roomNo) Guest (guestNo, guestName, guestAddress) where Hotel contains hotel details and hotelNo is the primary key; Room contains room details for each hotel and (hotelNo, roomNo) forms the primary key; Booking contains details of bookings and (hotelNo, guestNo, dateFrom) forms the primary key; Guest contains guest details and guestNo is the primary key. 1. Create tables Create each of the above tables using SQL (create primary keys and foreign keys, where appropriate). 2. Populating tables (1) Insert rows into each of these tables. (2) Update the price of all rooms by 5%. 3. Simple queries (1) List full details of all hotels. (2) List full details of all hotels in Washington. (3) List the names and addresses of all guests living in Washington, alphabetically ordered by name. (4) List all double or family rooms with a price below $40.00 per night, in ascending order of price. (5) List the bookings for which no dateTo has been specified. 4. Aggregate functions (1) How many hotels are there? (2) What is the average price of a room? (3) What is the total revenue per night from all double rooms? 5. Subqueries and joins List the price and type of all rooms at the Hilton Hotel. 6. Grouping (1) List the number of rooms in each hotel. (2) List the number of rooms in each hotel in Washington. (3) What is the most commonly booked room type for each hotel in Washington? 2