Description

Part 5. SQL Homework. Inn ( hotelno , hotelname, city) Room ( roomno , hotelno , sort, value) Booking ( hotelno , guestno , datefrom , dateto, roomno ) Visitor ( guestno , guestname, guestaddress). Basic Inquiries. 7. List full subtle elements of all lodgings. SELECT * FROM inn;.

Transcripts

Part 5 SQL Homework

Hotel ( hotelno , hotelname, city) Room ( roomno , hotelno , sort, value) Booking ( hotelno , guestno , datefrom , dateto, roomno ) Guest ( guestno , guestname, guestaddress)

Simple Queries 7. Rundown full points of interest of all inns. SELECT * FROM inn;

8. Rundown all points of interest of all inns in London. SELECT * FROM inn WHERE city = \'London\';

9. List the names and locations of all visitors in London, one after another in order requested by name. SELECT guestname, guestaddress FROM visitor WHERE guestaddress like âLondonâ ORDER BY guestname;

10. Rundown all twofold or family stays with a cost underneath $40. 00 every night, in climbing request of cost. SELECT * FROM room WHERE cost < 40 AND sort IN (\'Double\', \'Family\') ORDER BY cost;

11. List the bookings for which no dateto has been indicated. SELECT * FROM booking WHERE dateto IS NULL;

Aggregate Functions 12. What number of inns arrive? SELECT COUNT(*) FROM inn; SELECT COUNT(hotelno) FROM lodging;

13. What is the normal cost of a room? SELECT AVG(price) FROM room;

14. What is the aggregate income every night from every twofold room? SELECT SUM(price) FROM room WHERE sort = "Twofold" ;

15. What number of diverse visitors have made bookings for August? SELECT COUNT(DISTINCT guestno) FROM booking WHERE (datefrom <= â8/31/06â AND dateto >= â8/1/06â);

Subqueries and Joins 16. List the value and sort of all rooms at the Grosvenor Hotel. SELECT value, sort FROM room WHERE hotelno = (SELECT hotelno FROM inn WHERE hotelname = \'Grosvenor\');

Another Method 16. List the value and sort of all rooms at the Grosvenor Hotel. SELECT value, sort FROM room, inn WHERE hotel.hotelno = room.hotelno AND hotelname = \'Grosvenor\';

17. Rundown all visitors at present staying at the Grosvenor Hotel. SELECT ( guestno, guestname, guestaddress) FROM visitor, booking, lodging WHERE guest.guestno = booking .guestno AND hotel.hotelno = booking.hotelno AND (datefrom <= âSYSTEM DATEâ AND dateto >= âSYSTEM DATEâ) AND hotelname = âGrosvenorâ;

17. Rundown all visitors presently staying at the Grosvenor Hotel. (another system) SELECT * FROM visitor WHERE guestno IN (SELECT guestno FROM booking WHERE datefrom <= âSYSTEM DATEâ AND dateto >= âSYSTEM DATEâ AND hotelno = (SELECT hotelno FROM inn WHERE hotelname = âGrosvenorâ));

18. List the subtle elements of all rooms at the Grosvenor Hotel, including the visitor\'s name staying in the room, if the room is possessed.

Create a perspective with each room having a visitor CREATE VIEW roomocp (hotelno, roomno, sort, cost, guestname) AS SELECT r.hotelno, r.roomno, r.type, r.price, g.guestname FROM inn h, room r, booking b, visitor g WHERE h.name = âGrosvenorâ AND (b.datefrom <= âSYSTEM DATEâ AND b.dateto >= âSYSTEM DATEâ) AND h.hotelno = r.hotelno AND r.hotelno = b.hotelno AND r.roomno = b.roomno AND b.guestno = g.guestno;

Create a perspective of each room CREATE VIEW roomall (hotelno, roomno, sort, cost) AS SELECT r.hotelno, r.roomno, r.type, r.price FROM lodging h, room r WHERE h.hotelname =\'Grosvenorâ AND h.hotelno = r.hotelno;

Find the answer SELECT r.roomno, r.hotelno, r.type, r.price, p.guestname FROM roomall r LEFT JOIN roomocp p ON r.roomno = p.roomno;

19. What is the aggregate pay from bookings for the Grosvenor Hotel today ? SELECT SUM(price) FROM booking b, room r, lodging 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â;

20. List the rooms which are presently vacant at the Grosvenor Hotel . SELECT ( r.hotelno, r. roomno, r.type, r.price) FROM room r, inn h WHERE r.hotelno = h.hotelno AND h.hotelname = \'Grosvenorâ AND roomno NOT IN (SELECT roomno FROM booking b, inn h WHERE (datefrom <= âSYSTEM DATEâ AND dateto >= âSYSTEM DATEâ) AND b.hotelno=h.hotelno AND hotelname = \'Grosvenor\');

20. List the rooms which are at present empty at the Grosvenor Hotel . SELECT ( r.hotelno, r. roomno, r.type, r.price) FROM room r, lodging h WHERE r.hotelno = h.hotelno AND h.hotelname = \'Grosvenorâ AND NOT EXIST (SELECT * FROM booking b, inn h WHERE (datefrom <= âSYSTEM DATEâ AND dateto >= âSYSTEM DATEâ) AND r.hotelno=b.hotelno AND r.roomno=b.roomno AND r.hotelno=h.hotelno AND hotelname = \'Grosvenor\');

21. What is the lost pay from empty rooms at the Grosvenor Hotel? SELECT SUM(price) FROM room r, lodging h WHERE r.hotelno = h.hotelno AND h.hotelname = \'Grosvenorâ AND roomno NOT IN (SELECT roomno FROM booking b, inn h WHERE (datefrom <= âSYSTEM DATEâ AND dateto >= âSYSTEM DATEâ) AND b.hotelno = h.hotelno AND r.hotelno=b.hotelno AND r.roomno=b.roomno AND h.hotelname = \'Grosvenor\');

Grouping 22. List the quantity of rooms in every inn. SELECT hotelno, COUNT(roomno) FROM room GROUP BY hotelno;

23. List the quantity of room in every inn in London. SELECT r.hotelno, COUNT(roomno) FROM room r, lodging h WHERE r.hotelno=h.hotelno AND city = "London" GROUP BY r.hotelno;

24. What is the normal number of bookings for every inn in August? 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);

25. What is the most ordinarily occupied room sort for all lodgings in London? SELECT sort, MAX(y) FROM (SELECT sort, COUNT(type) AS y FROM booking b, lodging h, room r WHERE r.roomno = b.roomno AND r.hotelno = b.hotelno AND b.hotelno = h.hotelno AND city = "London" GROUP BY sort) GROUP BY sort;

25. What is the most ordinarily occupied room sort for every inn in London? SELECT hotelno, sort, MAX(y) FROM (SELECT hotelno, sort, COUNT(type) AS y FROM booking b, lodging h, room r WHERE r.roomno = b.roomno AND r.hotelno = b.hotelno AND b.hotelno = h.hotelno AND city = "London" GROUP BY hotelno, sort) GROUP BY hotelno, sort;

26. What is the lost wage from empty rooms at every lodging today? 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;

27. Addition columns into each of these tables. Embed INTO inn VALUES (âh11â, âhiltonâ, âsacramentoâ); INSERT IN