Lets Learn Database: SQL Exercise (Part 2)

1. Normalization
An agency called InstantCover supplies part-time/temporary staff to hotels throughout Scotland. The table shown in Figure 2 lists the time spent by agency staff working at two hotels. The National Insurance Number (NIN) is unique for employee.in
 InstantCover(NIN, ContractNo, HoursPerWeek, eName, HotelNo, HotelLocation)
i. Provide examples of insertion, deletion, and modification anomalies.

INSERTION: a. To insert the details of new members for example

INSERT INTO InstantCover (NIN, ContractNo, HoursPerWeek, eName, HotelNo, HotelLocation) 
             VALUES (872342, 23, 40, ‘Johnny Yes Papa’, 32, ‘London’); 

DELETION
b. we delete a row from InstantCover relation that represents the last member
 DELETE FROM  InstantCover 
       
c. If we want to change the value of one attributes of a particular hotel in the InstantCover         relation
            ALTER TABLE InstantCover 
          MODIFY  HotelLocation  VARCHAR(20) NOT NULL 

b. Describe and illustrate the process of normalizing the table shown in Figure 2 to 3NF. State any assumptions you make about the data shown in this table.

1NF 
FD1- NIN, ContractNo      -->        HoursPerWeek, eName, HotelNo, HotelLocation (Primary Key) 
FD2- NIN       -->                   HoursPerWeek, eName, HotelNo, HotelLocation (Partial Dependency) 
FD3-ContractNo    -->          HoursPerWeek, eName (Partial Dependency) 
FD4-HotelNo         -->          HotelLocation (Transitive Dependency} 
InstantCover (NIN, ContractNo, HoursPerWeek, eName, HotelNo, HotelLocation)
2NF 
FD1- NIN, ContractNo     -->           HoursPerWeek, eName, HotelNo, HotelLocation (Primary Key)
FD2- NIN       -->                   HoursPerWeek, eName, HotelNo, HotelLocation (Partial Dependency)
FD4-HotelNo    -->               HotelLocation (Transitive Dependency}
InstantCover (NIN, ContractNo, HoursPerWeek, eName, HotelNo, HotelLocation)
Contract ( ContractNo ,HoursPerWeek, eName)
3NF
FD1- NIN, ContractNo  -->              HoursPerWeek, eName, HotelNo, HotelLocation (Primary Key) 
FD2- NIN                 -->         HoursPerWeek, eName, HotelNo, HotelLocation (Partial Dependency) 
InstantCover (NIN, ContractNo, HoursPerWeek, eName, HotelNo, HotelLocation) 
Contract ( ContractNo ,HoursPerWeek, eName) 
Hotel ( HotelNo,HotelLocation) 

2. SQL 
Hotel (Hotel_No, Name, Address) 
Room (Room_No, Hotel_No, Type, Price) 
Booking (Hotel_No, Guest_No, Date_From, Date_To, Room_No) 
Guest (Guest_No, Name, Address)

Simple Queries
 
1. List full details of all hotels
SELECT *      
FROM Hotel 


2.List full details of all hotels in London. 

SELECT *      
FROM Hotel WHERE  Address= ‘London’ 

3. List the names and addresses of all guests in London, alphabetically ordered by name.
SELECT * 
FROM Guest 
WHERE Address = ‘London’ ORDER BY Name ASC;


4. List all double or family rooms with a price below £40.00 per night, in ascending order of price. 

SELECT *  
FROM Room  
WHERE Price < 40 
 AND type IN (‘D’, ‘F’) 
 ORDER BY Price; 


5. List the bookings for which no date_to has been specified.
 SELECT *    
FROM Booking  
WHERE Date_To IS NULL;



Aggregate Functions  

1. How many hotels are there?
SELECT COUNT(*)  
 FROM Hotel;

2. What is the average price of a room?
 SELECT AVG(Price)  
FROM Room; 


3. What is the total revenue per night from all double rooms? 
SELECT SUM(price)  
FROM Room  
WHERE type = ‘D’; 


4. How many different guests have made bookings for August?
SELECT COUNT(Guest_No)
 FROM Booking 
WHERE(Date_From <='08/31/2019 ' AND Date_To >='08/01/2019') 


Subqueries and Joins 
1. List the price and type of all rooms at the Grosvenor Hotel.
SELECT Price, Type    
FROM Room    
WHERE Hotel_No =
 (SELECT Hotel_No  
  FROM Hotel    
WHERE Name = 'Grosvenor Hotel'); 



2. List all guests currently staying at the Grosvenor Hotel. 
SELECT * 
FROM Guest  
WHERE Guest_No = 
 (SELECT Guest_No 
 FROM Booking  
WHERE Date_From <= CURRENT_DATE AND  Date_To >= CURRENT_DATE AND  Hotel_No =  (SELECT Hotel_No FROM Hotel  WHERE Name = '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.
  SELECT Room.*, Guest.Name
 FROM Room 
INNER JOIN Hotel on Hotel.Hotel_No = Room.Hotel_No 
INNER JOIN Booking on Booking.Hotel_No = Hotel.Hotel_No 
INNER JOIN Guest on Booking.Guest_No = Guest.Guest_No 
WHERE Hotel.Name = 'Grosvenor Hotel'; 

4. What is the total income from bookings for the Grosvenor Hotel today?
  SELECT SUM(Price)  
FROM Booking b, Room r, Hotel h 
WHERE (Date_From <= CURRENT_DATE AND Date_To>= CURRENT_DATE) 
 AND r.Hotel_No = h.Hotel_No  
AND r.Room_No = b.Room_No  
AND Name = 'Grosvenor Hotel'


5. List the rooms that are currently unoccupied at the Grosvenor Hotel. 
SELECT * 
FROM Room r 
WHERE Room_No 
NOT IN  (SELECT Room_No  FROM Booking b, Hotel h 
WHERE (Date_From<= CURRENT_DATE AND Date_To>= CURRENT_DATE)  
AND b.Hotel_No = h.Hotel_No 
 AND Name= 'Grosvenor Hotel'); 


6. What is the lost income from unoccupied rooms at the Grosvenor Hotel?

SELECT SUM(Price)  
FROM Room r 
WHERE Room_No NOT IN  
(SELECT Room_No  
FROM Booking b,Hotel h 
WHERE (Date_From <= CURRENT_DATE AND Date_To>= CURRENT_DATE)  
AND b.Hotel_No = h.Hotel_No  
AND Name= 'Grosvenor Hotel'); 


Grouping 
1. List the number of rooms in each hotel. 
SELECT Hotel_No, 
COUNT(Room_No) 
AS count FROM Room GROUP BY Hotel_No; 


2. List the number of rooms in each hotel in London.

SELECT r.Hotel_No, 
COUNT(Room_No)  
FROM Room r, Hotel h  
WHERE r.Hotel_No=h.Hotel_No 
AND          Address = 'London'  GROUP BY r.Hotel_No; 


3. What is the average number of bookings for each hotel in August?
  SELECT Hotel_No, y/31 
 FROM (SELECT Hotel_No, COUNT(Hotel_No) AS y  
FROM Booking       
WHERE (Date_From <= '8/31/2019' AND   Date_To >= '8/1/2019') GROUP BY Hotel_No);


4. What is the most commonly booked room type for each hotel in London? 
SELECT MAX(X)
FROM  ( SELECT Type, COUNT(Type) AS X 
FROM Booking b, Hotel h, Room r 
WHERE r.Room_No = b.Room_No  
AND b.Hotel_No = h.Hotel_No  
AND Address= 'London' 
GROUP BY Type);

5. What is the lost income from unoccupied rooms at each hotel today?
SELECT Hotel_No, SUM(Price)  
FROM Room r  
WHERE Room_No NOT IN 
 (SELECT Room_No 
FROM Booking b, Hotel h 
WHERE (Date_From<= CURRENT_DATE AND Date_To>= CURRENT_DATE) 
 AND b.Hotel_No = h.Hotel_No) GROUP BY Hotel_No;

 








Post a Comment

0 Comments