Database for Management Information System Simple Queries 1List full details of

Question

Database for Management Information System

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%.

Descending Advanced . save 2 TelalhRemo Sort Spelling Replace Anal Detain Ascending YrSelection *Save All . "X Delete , Tope Fier allX DeleteMor Descending Advanced. Spelling Sort & Filter . Records Select BI Find More . Remove Sort Y Toggle Fiter Find Sot & Filter gustNo -gustName-gustAddres-Click to Add hotelNo guestNodateForm dataToroomNo Click to Add 3 M33 5 Y9 22 kail33 23 kail34 3232B 3222C 45 T77 MLT5 Ascendingg Advanced. Selection New Totals Table Tools e Save Retresh X Delete Filter Descending Advanced- · Spelling | Remove Sort YToggie Filter | Retres"X Delete-BMore. Data Database Tools Add-Ins Fields Table Find Selection New Totals Replace Sort & Filtter Records DescendingAdvanced . bRemove Sort YToggie Fiter Delete , 'Spelling Retres"X Delete, Find More . Select " hotelNo hotelNamecity Click to Add Sort & Filter 21 sofotel 22 helton 23 RITZ hotelNo khobar price Click to Add 20 30 50 roomNo type 1 £50.00 6 £60.00 5 £80.00 7

Explanation / Answer

--1List full details of all hotels.

SELECT * FROM hotel;

--2 List full details of all hotels in London.

SELECT * FROM hotel WHERE city = 'London';

--3 List the names and addresses of all guests in London, alphabetically ordered by name.

SELECT guestname,

guestaddress

FROM guest

WHERE guestaddress LIKE 'London'

ORDER BY guestname;

--4List 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 ('Double', 'Family') ORDER BY price;

--5List the bookings for which no dateTo has been specified.

SELECT * FROM booking WHERE dateto IS NULL;

--Aggregate Functions

--6How many hotels are there?

SELECT COUNT(hotelno) FROM hotel;

--7What is the average price of a room?

SELECT AVG(price) FROM room;

--8What is the total revenue per night from all double rooms?

SELECT SUM(price) AS 'total revenue' FROM room WHERE type = 'Double';

--9How many different guests have made bookings for August?

SELECT COUNT (DISTINCT guestno) FROM booking

WHERE (datefrom <= '8/31/year' AND dateto >= '8/1/year');--Please provide year which you are looking into

--Subqueries and Joins

--10List the price and type of all rooms at the Grosvenor Hotel.

SELECT r.price, r.type FROM room r INNER JOIN hotel h ON h.hotelno = r.hotelno WHERE h.hotelname = 'Grosvenor';

--11List all guests currently staying at the Grosvenor Hotel.

SELECT g.guestno, g.guestname, g.guestaddress

FROM guest g

INNER JOIN booking b ON g.guestno =b.guestno

INNER JOIN hotel h ON h.hotelno = b.hotelno WHERE

(datefrom <= '18/03/2018' AND dateto >= '18/03/2018') /*Please provide system date and respective data format*/

AND hotelname = 'Grosvenor';

--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

(SELECT r.hotelno, r.roomno, r.type, r.price FROM hotel h, room r WHERE h.hotelname ='Grosvenor' AND h.hotelno = r.hotelno)

LEFT JOIN

(SELECT r.hotelno, r.roomno, r.type, r.price, g.guestname FROM hotel h, room r, booking b, guest g

WHERE h.name = 'Grosvenor' AND (b.datefrom <= '18/03/2018' AND b.dateto >= '18/03/2018') AND /*Please provide system date and respective data format*/

h.hotelno = r.hotelno AND r.hotelno = b.hotelno AND r.roomno = b.roomno AND b.guestno = g.guestno)

ON r.roomno = b.roomno;

--13What is the total income from bookings for the Grosvenor Hotel today?

SELECT SUM(price) FROM booking b

INNER JOIN room r ON r.roomno = b.roomno

INNER JOIN hotel h ON r.hotelno = b.hotelno

WHERE (b.datefrom <= '18/03/2018' AND b.dateto >= '18/03/2018')/*Please provide system date and respective data format*/

AND h.hotelname = 'Grosvenor';

--14List the rooms that are currently unoccupied at the Grosvenor Hotel.

SELECT r.hotelno , r.roomno, r.type, r.price

FROM room r INNER JOIN hotel h

ON r.hotelno = h.hotelno

WHERE h.hotelname = 'Grosvenor'

AND roomno NOT IN

(SELECT roomno FROM booking b, hotel h

WHERE (datefrom <= '18/03/2018' AND dateto >= '18/03/2018')/*Please provide system date and respective data format*/

AND b.hotelno=h.hotelno AND hotelname = 'Grosvenor');

--Grouping

--15List the number of rooms in each hotel.

SELECT hotelno, COUNT(roomno) FROM room GROUP BY hotelno;

--16List the number of rooms in each hotel in London.

SELECT r.hotelno, COUNT(roomno) FROM room r, INNER JOIN hotel h ON r.hotelno=h.hotelno WHERE city = 'London' GROUP BY r.hotelno;

--17What is the average number of bookings for each hotel in August?

SELECT hotelno, COUNT(hotelno)/31 AS AvgBookings FROM booking

WHERE (datefrom <= '8/31/2018' AND dateto >= '8/1/2018')/*U need to provide receptive year*/

GROUP BY hotelno;

--18What is the most commonly booked room type for each hotel in London?

SELECT type, y FROM

(SELECT 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 type)

WHERE y = (SELECT MAX(y) FROM (SELECT 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 AND city = 'London' GROUP BY type);

--19What is the lost income from unoccupied rooms at each hotel today?

SELECT r.hotelno, SUM(price) FROM room r WHERE NOT EXISTS (SELECT * FROM booking b WHERE r.roomno = b.roomno AND r.hotelno = b.hotelno AND

(datefrom <= '18/03/2018' AND dateto >= '18/03/2018'))/*Please provide system date and respective data format*/

GROUP BY hotelno;

--Populating Tables

--20Update the price of all rooms by 5%.

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.