A. Import Reservation_T.sql. Write queries to extract following information.
1. Extract average daily rates. Name the computed column.
SELECT AVG(DailyRate) AS AvgDailyRate FROM reservation_t;
2. What are average daily rates, average number of guests, and average rate per one guest (average daily rates/average number of guests) by room types? Name the computed columns.
SELECT Room, AVG(DailyRate) AS AvgDailyRate, AVG(NoofGuests) AS AvgNoGuest, AVG(DailyRate)/AVG(NoofGuests) AS AvgRatePerGuest FROM Reservation_t
GROUP BY Room;
3. Retrieve the duration of stay for each reservation. Print the output along with reservation ID and guest names.
SELECT ID, GuestFirstName, GuestLastName, DepartureDate-ArrivalDate AS Duration
FROM Reservation_t;
4. Retrieve all the reservation information if more than 1 guest used a room and daily rate is less than $160.
SELECT *
FROM Reservation_t
WHERE NoofGuests>1 AND DailyRate<160;
5. Retrieve the distinct names of rooms.
SELECT DISTINCT Room FROM Reservation_t;
6. List the three dates when the hotel had the most number of incoming guests. Your output should have the date, and the total number of incoming guests.
SELECT ArrivalDate, SUM(NoOfGuests) FROM Reservation_t
GROUP BY ArrivalDate
ORDER BY SUM(NoOfGuests) DESC
LIMIT 3;
7. Retrieve the dailyrates that were given to guests whose lastname starts with ¡°c¡± or ends
with ¡°l¡±. Print the daily rates with each guest¡¯s name.
SELECT DailyRate, GuestFirstName, GuestLastName
FROM Reservation_t
WHERE GuestLastName LIKE 'c%' OR GuestLastName LIKE '%l';
8. Get the minimum, maximum, and average daily rates for each room type.
SELECT Room, MIN(DailyRate), MAX(DailyRate), AVG(DailyRate) FROM Reservation_t
GROUP BY Room;
9. Retrieve all reservation information of the most popular room type. Popularity of a room type is based on the total number of reservations.
SELECT *
FROM Reservation_t,
(SELECT Room as popular FROM Reservation_t
GROUP BY Room
ORDER BY COUNT(*) DESC LIMIT 1) AS table2
WHERE Room = table2.popular;
B. Import HR.sql. Write queries to extract following information.
1. For each job title, get average, minimum, and maximum salary.
SELECT Title, AVG(Salaries), MIN(Salaries), MAX(Salaries) FROM Employees
Group By Title;
2. Get the first and last names of trainees whose first name starts with j.
SELECT FirstName, LastName
FROM Employees
WHERE Title = ' Trainee' AND FirstName Like ' j% ';
3. Find the last and first name of the employees who stayed with the company more than 20 years. Along with their names, report how long the employees have stayed with the company (in years).
SELECT FirstName, LastName, year(curdate())-year(hired_date) AS Length FROM Employees
WHERE year(curdate())-year(hired_date) > 20;
C. Import product_supplier.sql. Write queries to extract following information.
1. Retrieve all information of products, of which margin is greater than $150.
SELECT *
FROM Product_t
WHERE SellingPrice-PurchaseCost > 150;
2. What is the maximum, minimum, and average purchase cost of the products that should be reordered? The company reorders a product when quantity on hand is equal to or less than reorder level.
SELECT MAX(PurchaseCost), MIN(PurchaseCost), AVG(PurchaseCost) FROM Product_t
WHERE QuantityonHand <= ReorderLevel;
3. Let¡¯s assume that a manager wants to know which companies (SupplierNumber) supply which products (ProductNumber). Order by SupplierNumber and then ProductNumber.
SELECT SupplierNumber, ProductNumber FROM Product_T
Order by SupplierNumber, ProductNumber;
What ifs/he wants to include all companies even though they are not currently supplying any products?
SELECT distinct Supplier_T.SupplierNumber, ProductNumber FROM Supplier_T LEFT JOIN Product_T
ON Supplier_T.SupplierNumber = Product_T.SupplierNumber ORDER by Supplier_T.SupplierNumber, ProductNumber;
4. Retrieve average purchase cost and selling price for each supplier. Present the output with supplier¡¯s company name.
SELECT CompanyName, AVG(PurchaseCost), AVG(SellingPrice) FROM Supplier_T JOIN Product_T
ON Supplier_T.SupplierNumber = Product_T.SupplierNumber GROUP BY Supplier_T.SupplierNumber;
D. Import Electionexpenditure_part.sql. Write queries to extract following information.
1. Which candidate spent most the disbursement? Report the result along with the amount the candidate spent.
select candidate_name,sum(disb_amt) from electionexpenditure_part
group by candidate_name
order by sum(disb_amt) desc
limit 1;
2. How much did Hillary spent for travel-related expenses?
select candidate_name,sum(disb_amt) as travel
from electionexpenditure_part
where candidate_name like '%Hillary%' and disb_desc like '%travel%';
3. What is the expense difference between Trump and Hillary in terms of travel-related expenses?
select HillaryTravel-TrumpTravel from
(select sum(disb_amt) as HillaryTravel from electionexpenditure_part
where candidate_name like '%Hillary%' and disb_desc like '%travel%') as sub1,
(select sum(disb_amt) as TrumpTravel
from electionexpenditure_part
where candidate_name like '%Trump%' and disb_desc like '%travel%') as sub2;
4. Get a distinct list of recipients who are located in Seattle or in Bellevue.
select distinct recipient_name
from electionexpenditure_part
where recipient_city="seattle" or recipient_city="bellevue";
5. Among the recipients who are located in Seattle or in Bellevue, which recipient received the most total distribution? How much was it?
select recipient_name, sum(disb_amt)
from electionexpenditure_part
where recipient_city="seattle" or recipient_city="bellevue" group by recipient_name
order by sum(disb_amt) desc
limit 1;
6. Among the recipients who are located in Seattle or in Bellevue, which recipient received the most distribution from Hillary? How much was it?
select recipient_name, sum(disb_amt)
from electionexpenditure_part
where (recipient_city="seattle" or recipient_city="bellevue") and candidate_name like '%hillary%'
group by recipient_name
order by sum(disb_amt) desc
limit 1;
7. Among the recipients who are located in Seattle or in Bellevue, which recipient received the most distribution from Trump? How much was it?
select recipient_name, sum(disb_amt)
from electionexpenditure_part
where (recipient_city="seattle" or recipient_city="bellevue") and candidate_name like '%trump%'
group by recipient_name
order by sum(disb_amt) desc
limit 1;