Part 5.


160 views
Uploaded on:
Category: People / Lifestyle
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
Slide 1

Part 5 SQL Homework

Slide 2

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

Slide 3

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

Slide 4

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

Slide 5

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;

Slide 6

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;

Slide 7

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

Slide 8

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

Slide 9

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

Slide 10

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

Slide 11

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’);

Slide 12

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\');

Slide 13

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\';

Slide 14

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

Slide 15

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’));

Slide 16

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.

Slide 17

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;

Slide 18

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;

Slide 19

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;

Slide 20

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

Slide 21

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\');

Slide 22

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\');

Slide 23

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\');

Slide 24

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

Slide 25

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;

Slide 26

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

Slide 27

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;

Slide 28

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;

Slide 29

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;

Slide 30

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

Recommended
View more...