practice -1

Some Practical Examples which i have done : 
 

1) Develop Physical schema for following relations:- 

1. Table name : - Hotel hotelNo hotelName city
2. Table name : -Room roomNo hotelNo type price
3. Table name :- Booking hotelNo guestNo dateFrom dateTo roomNo
4. Table name :-Guest guestNo guestName guestAddress

For the above schema's following primary keys are used:-

1. Table name Primary key
2. Hotel hotelNo
3. Room (roomNo,hotelNo)
4. Booking (hotelNo,guestNo,dateFrom)
5. Guest guestNo

Identify the foreign keys in the schema. 

 2 ) Answers the following queries :

1. Populating tables:
2. Insert minimum 10 rows into each of these tables.
3. Update the price of all room by 5%.

Simple queries :

1. List full details of all hotels.
2. List full details of all hotels in London.
3. List the names and addresses of all guests living in London, 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.

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?
4. How many different guests have made bookings for August?

Grouping :

1. List the number of rooms in each hotel.
2. List the number of rooms in each hotel in London.
3. What is the average number of bookings for each hotel in August?
4. What is the most commonly booked room type for each hotel in London?
5. What is the lost income unoccupied rooms at each hotel today?

 Subqueries and joins :

1. List the price and type of all rooms at the Grosvenor Hotel.
2. List all guests currently staying at the Grosvenor Hotel.
3. List the details of all rooms at the Grosvenor Hotel, including the name of the guest staying in the room, if the room is occupied.
4. What is the total income from bookings for the Grosvenor Hotel today?
5. List the rooms that are currently unoccupied at the Grosvenor Hotel.
6. What is the lost income from unoccupied rooms at the Grosvenor Hotel?

Answers will be posted Soon..............