PLEASE HELP ME ANSWER ALL OF MY ITM-315(DATABASE SYSTEMS) questions. PLEASE USE
ID: 3710423 • Letter: P
Question
PLEASE HELP ME ANSWER ALL OF MY ITM-315(DATABASE SYSTEMS) questions. PLEASE USE Microsoft Access to answer the questions.
For some queries, the save button may not appear after running the query. Use file>save where necessary to save the queries.
Create and save the following queries (save as query 1 through query 14) in the provided database: hotel spring 18 ch 10. Rename the database as hotel spring 18 ch 10lastname or hotel spring 18 ch10lastnameandlastname and submit via Blackboard by the due date/time.
Query 7. How many bookings have there been in each city?
Query 8: List each employee’s first name, last name, their hotelNo, their manager’s first name and their manager’s last name.
Query 9 which guests have yahoo email accounts?
Query 10. which hotel (by name) has the most expensive room ?
query11 which guests (by name) have not booked rooms in hotel 1?
Query 12. what is the average price of hotel rooms in Stillwater?
Query 13. List the count of the single rooms for each hotel for any hotel that has less than 2 single rooms.
query 14: drop the standard price column from the room table.
HOTEL SPRING DATABASE
All Access Obje...og employee guest l hotelE room guestNo dateFrom dateTo roomNo ratebooked Click to Add Tables 8/3/2016 2/1/2015 8/4/2016 2/6/2015 13/13/2016 3/15/2016 8/9/2014 8/10/2014 2/8/2015 7/5/2016 3/1/2015 2/4/2015 6/4/2014 3/7/2015 23/10/2015 3/15/2015 2/6/2015 2/4/2014 9/8/2015 9/15/2015 3/9/2016 3/14/2016 2/2/2015 015 3/6/2016 3/26/2016 3/31/2016 13 5/12/2014 5/13/2014 2/8/2015 11 5/11/2014 5/12/2014 8 4/15/20164/18/2016 4/4/2015 4/4/2016 4 3/11/2016 3/15/2016 2/6/2015 2/7/2015 2/6/20152/10/2015 9 4/15/20164/18/2016 3 3/22/2016 3/25/2016 10 2/22/2015 2/24/2015 10 3/12/20143/13/2014 401 72 booking 101 E employee E guest 2/3/2015 7/3/2016 1 2/28/2015 2/3/2015 11 5/31/2014 3/5/2015 hotel room 101 212 91 100 100 100 100 100 110 110 2 2/4/2015 3 1/31/2014 401 2/1/2015 25/18/2015 5/23/2 2 3/4/2016 210 421 130 2/5/2015 135 4/1/2015 4/1/2016 160 160 160 212 214 2/5/2015 2/1/2015 412 411 321 410 312 312 4 190Explanation / Answer
FInd the query as below
Query 7. How many bookings have there been in each city?
SELECT guestCity, Count(*) as BookingNumber
FROM booking INNER JOIN guest
ON booking.guestNo = guest.guestNo
GROUP BY guestCity
Query 8: List each employee’s first name, last name, their hotelNo, their manager’s first name and their manager’s last name.
SELECT e1.empID EmployeeId, e1.empFirst EmployeeFirstName, e1.empLast EmployeeLastName,
e1.managerID ManagerId, e2.empFirst ManagerFirstName, e2.empLast ManagerLastName
FROM [employee] e1
LEFT JOIN [employee] e2
ON e1.managerID = e2.empID
Query 9 which guests have yahoo email accounts?
select * from guest
where guest.guestEmail like '%@yahoo.com%'
Query 10. which hotel (by name) has the most expensive room ?
SELECT top 1 hotel.hotelName
FROM hotel INNER JOIN room
ON hotel.hotelNo= room.hotelNO
order by room.standardprice desc
query11 which guests (by name) have not booked rooms in hotel 1?
SELECT guest.*
FROM booking INNER JOIN guest
ON booking.guestNo = guest.guestNo
INNER JOIN hotel
ON booking.hotelNo = hotel.hotelNO
where booking.hotelNo <> 1
order by guest.guestFirst desc
Query 12. what is the average price of hotel rooms in Stillwater?
SELECT Avg(room.standardprice ) AS AveragePrice
FROM hotel INNER JOIN room
ON hotel.hotelNo= room.hotelNO
where hotel.hotelCity='Stillwater'
Query 13. List the count of the single rooms for each hotel for any hotel that has less than 2 single rooms.
SELECT count(*) as singleroomcount , hotel.hotelname
FROM hotel INNER JOIN room
ON hotel.hotelNo= room.hotelNO
group by hotel.hotelname
having COUNT(*) < 2
query 14: drop the standard price column from the room table.
ALTER TABLE room DROP COLUMN standardprice
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.