Database for management course, please use Microsoft Access to solve these quest

Question

Database for management course, please use Microsoft Access to solve these questions. Simple Queries 1List full details of all hotels. 2 List full details of all hotels in London. 3 List the names and addresses of all guests in London, alphabetically ordered by name. 4List all double or family rooms with a price below £40.00 per night, in ascending order of price. 5List the bookings for which no dateTo has been specified. Aggregate Functions 6How many hotels are there? 7What is the average price of a room? 8What is the total revenue per night from all double rooms? 9How many different guests have made bookings for August? Subqueries and Joins 10List the price and type of all rooms at the Grosvenor Hotel. 11List all guests currently staying at the Grosvenor Hotel. 12List the details of all rooms at the Grosvenor Hotel, including the name of the guest staying in the room, if the room is occupied 13What is the total income from bookings for the Grosvenor Hotel today? 14List the rooms that are currently unoccupied at the Grosvenor Hotel. Grouping 15List the number of rooms in each hotel. 16List the number of rooms in each hotel in London. 17What is the average number of bookings for each hotel in August? 18What is the most commonly booked room type for each hotel in London? 19What is the lost income from unoccupied rooms at each hotel today? Populating Tables 20Update the price of all rooms by 5%. find ka02 22 kal33 23 kai34 L4 5 Y9 45 177 32328 3222C AscendingSeleton A. Sort& Fiter Find cty Clck to Add 21 sofotel roomNohotelNo 1 22 hetanmanarna 23 RITZ type price Click lo Add E50 00 6 E60 00 5 E80 00 7 30 50

Explanation / Answer

1)
SELECT * FROM Hotel;
2)
SELECT * FROM Hotel WHERE city = ‘London’;
3)
SELECT guestName, guestAddress FROM Guest WHERE address LIKE ‘%London%’
ORDER BY guestName;
4)
SELECT * FROM Room WHERE price < 40 AND type IN (‘D’, ‘F’)
ORDER BY price;
Note: ASC(Asending) is the default setting.
5)
SELECT * FROM Booking WHERE dateTo IS NULL;

6)
SELECT COUNT(*) FROM Hotel;
7)
SELECT AVG(price) FROM Room;
8)
SELECT SUM(price) FROM Room WHERE type = ‘D’;
9)
SELECT COUNT(DISTINCT guestNo) FROM Booking
WHERE (dateFrom <= DATE’2004-08-01’ AND dateTo >= DATE’2004-08-01’) OR
(dateFrom >= DATE’2004-08-01’ AND dateFrom <= DATE’2004-08-31’);

10)
SELECT price, type FROM Room
WHERE hotelNo =
(SELECT hotelNo FROM Hotel
WHERE hotelName = ‘Grosvenor Hotel’);
11)
SELECT * FROM Guest
WHERE guestNo =
(SELECT guestNo FROM Booking
WHERE dateFrom <= CURRENT_DATE AND
dateTo >= CURRENT_DATE AND
hotelNo =
(SELECT hotelNo FROM Hotel
WHERE hotelName = ‘Grosvenor Hotel’));
12)

SELECT r.* FROM Room r LEFT JOIN
(SELECT g.guestName, h.hotelNo, b.roomNo FROM Guest g, Booking b, Hotel h
WHERE g.guestNo = b.guestNo AND b.hotelNo = h.hotelNo AND
hotelName= ‘Grosvenor Hotel’ AND
dateFrom <= CURRENT_DATE AND
dateTo >= CURRENT_DATE) AS XXX
ON r.hotelNo = XXX.hotelNo AND r.roomNo = XXX.roomNo;

13)

SELECT SUM(price)
FROM booking b, room r, hotel h
WHERE (b.datefrom <= ‘SYSTEM DATE’
AND b.dateto >= ‘SYSTEM DATE’)   
AND r.hotelno = h.hotelno
AND r.hotelno = b.hotelno
AND r.roomno = b.roomno
AND h.hotelname = ‘Grosvenor’;

14)

SELECT (r.hotelno, r.roomno, r.type, r.price)
FROM room r, hotel h
WHERE r.hotelno = h.hotelno AND
h.hotelname = 'Grosvenor’ AND
roomno NOT IN
(SELECT roomno
FROM booking b, hotel h
WHERE (datefrom <= ‘SYSTEM DATE’
AND dateto >= ‘SYSTEM DATE’)
AND b.hotelno=h.hotelno
AND hotelname = 'Grosvenor');

15)
SELECT hotelNo, COUNT(roomNo) AS count FROM Room
GROUP BY hotelNo;
16)
SELECT hotelNo, COUNT(roomNo) AS count FROM Room r, Hotel h
WHERE r.hotelNo = h.hotelNo AND city = ‘London’
GROUP BY hotelNo;
17)

SELECT hotelno, y/31
FROM
(SELECT hotelno, COUNT(hotelno) AS y
FROM booking
WHERE (datefrom <= ‘8/31/06’ AND
dateto >= ‘8/1/06’
GROUP BY hotelno);

18)

SELECT hotelno, type, MAX(y)
FROM
(SELECT hotelno, type, COUNT(type) AS y
FROM booking b, hotel h, room r
WHERE r.roomno = b.roomno AND
r.hotelno = b.hotelno AND
b.hotelno = h.hotelno AND
city = 'London'
GROUP BY hotelno, type)
GROUP BY hotelno, type;

19)

SELECT r.hotelno, SUM(price)
FROM room r
WHERE NOT EXIST
(SELECT *
FROM booking b
WHERE
r.roomno = b.roomno AND
r.hotelno = b.hotelno AND
(datefrom <= ‘SYSTEM DATE’ AND
dateto >= ‘SYSTEM DATE’))
GROUP BY hotelno;

20)

UPDATE room
SET price = price*1.05;

Related Questions

Navigate

• Browse (All)
• Browse D
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.