Chapter 5 SQL Homework Hotel hotelno hotelname city
![Chapter 5 SQL Homework Chapter 5 SQL Homework](https://slidetodoc.com/presentation_image_h/f76c157325d0ecb58262042f59d37608/image-1.jpg)
![• Hotel (hotelno, hotelname, city) • Room (roomno, hotelno, type, price) • Booking • Hotel (hotelno, hotelname, city) • Room (roomno, hotelno, type, price) • Booking](https://slidetodoc.com/presentation_image_h/f76c157325d0ecb58262042f59d37608/image-2.jpg)
![Simple Queries • 7. List full details of all hotels. • SELECT * • Simple Queries • 7. List full details of all hotels. • SELECT * •](https://slidetodoc.com/presentation_image_h/f76c157325d0ecb58262042f59d37608/image-3.jpg)
![• 8. List all details of all hotels in London. • SELECT * • 8. List all details of all hotels in London. • SELECT *](https://slidetodoc.com/presentation_image_h/f76c157325d0ecb58262042f59d37608/image-4.jpg)
![• 9. List the names and addresses of all guests in London, alphabetically • 9. List the names and addresses of all guests in London, alphabetically](https://slidetodoc.com/presentation_image_h/f76c157325d0ecb58262042f59d37608/image-5.jpg)
![• 10. List all double or family rooms with a price below $40. • 10. List all double or family rooms with a price below $40.](https://slidetodoc.com/presentation_image_h/f76c157325d0ecb58262042f59d37608/image-6.jpg)
![• 11. List the bookings for which no dateto has been specified. • • 11. List the bookings for which no dateto has been specified. •](https://slidetodoc.com/presentation_image_h/f76c157325d0ecb58262042f59d37608/image-7.jpg)
![Aggregate Functions • 12. How many hotels are there? • • SELECT COUNT(*) • Aggregate Functions • 12. How many hotels are there? • • SELECT COUNT(*) •](https://slidetodoc.com/presentation_image_h/f76c157325d0ecb58262042f59d37608/image-8.jpg)
![• 13. What is the average price of a room? • • SELECT • 13. What is the average price of a room? • • SELECT](https://slidetodoc.com/presentation_image_h/f76c157325d0ecb58262042f59d37608/image-9.jpg)
![• 14. What is the total revenue per night from all double rooms? • 14. What is the total revenue per night from all double rooms?](https://slidetodoc.com/presentation_image_h/f76c157325d0ecb58262042f59d37608/image-10.jpg)
![• 15. How many different guests have made bookings for August? • • • 15. How many different guests have made bookings for August? • •](https://slidetodoc.com/presentation_image_h/f76c157325d0ecb58262042f59d37608/image-11.jpg)
![Subqueries and Joins • 16. List the price and type of all rooms at Subqueries and Joins • 16. List the price and type of all rooms at](https://slidetodoc.com/presentation_image_h/f76c157325d0ecb58262042f59d37608/image-12.jpg)
![Another Method • 16. List the price and type of all rooms at the Another Method • 16. List the price and type of all rooms at the](https://slidetodoc.com/presentation_image_h/f76c157325d0ecb58262042f59d37608/image-13.jpg)
![• 17. List all guests currently staying at the Grosvenor Hotel. • SELECT • 17. List all guests currently staying at the Grosvenor Hotel. • SELECT](https://slidetodoc.com/presentation_image_h/f76c157325d0ecb58262042f59d37608/image-14.jpg)
![• 17. List all guests currently staying at the Grosvenor Hotel. (another method) • 17. List all guests currently staying at the Grosvenor Hotel. (another method)](https://slidetodoc.com/presentation_image_h/f76c157325d0ecb58262042f59d37608/image-15.jpg)
![• 18. List the details of all rooms at the Grosvenor Hotel, including • 18. List the details of all rooms at the Grosvenor Hotel, including](https://slidetodoc.com/presentation_image_h/f76c157325d0ecb58262042f59d37608/image-16.jpg)
![Create a view with every room having a guest • • • CREATE VIEW Create a view with every room having a guest • • • CREATE VIEW](https://slidetodoc.com/presentation_image_h/f76c157325d0ecb58262042f59d37608/image-17.jpg)
![Create a view of every room • • CREATE VIEW roomall (hotelno, roomno, type, Create a view of every room • • CREATE VIEW roomall (hotelno, roomno, type,](https://slidetodoc.com/presentation_image_h/f76c157325d0ecb58262042f59d37608/image-18.jpg)
![Find the answer • • SELECT r. roomno, r. hotelno, r. type, r. price, Find the answer • • SELECT r. roomno, r. hotelno, r. type, r. price,](https://slidetodoc.com/presentation_image_h/f76c157325d0ecb58262042f59d37608/image-19.jpg)
![• 19. What is the total income from bookings for the Grosvenor Hotel • 19. What is the total income from bookings for the Grosvenor Hotel](https://slidetodoc.com/presentation_image_h/f76c157325d0ecb58262042f59d37608/image-20.jpg)
![• 20. List the rooms which are currently unoccupied at the Grosvenor Hotel. • 20. List the rooms which are currently unoccupied at the Grosvenor Hotel.](https://slidetodoc.com/presentation_image_h/f76c157325d0ecb58262042f59d37608/image-21.jpg)
![• 20. List the rooms which are currently unoccupied at the Grosvenor Hotel. • 20. List the rooms which are currently unoccupied at the Grosvenor Hotel.](https://slidetodoc.com/presentation_image_h/f76c157325d0ecb58262042f59d37608/image-22.jpg)
![• 21. What is the lost income from unoccupied rooms at the Grosvenor • 21. What is the lost income from unoccupied rooms at the Grosvenor](https://slidetodoc.com/presentation_image_h/f76c157325d0ecb58262042f59d37608/image-23.jpg)
![Grouping • 22. List the number of rooms in each hotel. • SELECT hotelno, Grouping • 22. List the number of rooms in each hotel. • SELECT hotelno,](https://slidetodoc.com/presentation_image_h/f76c157325d0ecb58262042f59d37608/image-24.jpg)
![• 23. List the number of room in each hotel in London. • • 23. List the number of room in each hotel in London. •](https://slidetodoc.com/presentation_image_h/f76c157325d0ecb58262042f59d37608/image-25.jpg)
![• 24. What is the average number of bookings for each hotel in • 24. What is the average number of bookings for each hotel in](https://slidetodoc.com/presentation_image_h/f76c157325d0ecb58262042f59d37608/image-26.jpg)
![• 25. What is the most commonly booked room type for all hotels • 25. What is the most commonly booked room type for all hotels](https://slidetodoc.com/presentation_image_h/f76c157325d0ecb58262042f59d37608/image-27.jpg)
![• 25. What is the most commonly booked room type for each hotel • 25. What is the most commonly booked room type for each hotel](https://slidetodoc.com/presentation_image_h/f76c157325d0ecb58262042f59d37608/image-28.jpg)
![• 26. What is the lost income from unoccupied rooms at each hotel • 26. What is the lost income from unoccupied rooms at each hotel](https://slidetodoc.com/presentation_image_h/f76c157325d0ecb58262042f59d37608/image-29.jpg)
![• 27. Insert rows into each of these tables. • INSERT INTO hotel • 27. Insert rows into each of these tables. • INSERT INTO hotel](https://slidetodoc.com/presentation_image_h/f76c157325d0ecb58262042f59d37608/image-30.jpg)
![• 28. Update the price of all room by 5%. • • UPDATE • 28. Update the price of all room by 5%. • • UPDATE](https://slidetodoc.com/presentation_image_h/f76c157325d0ecb58262042f59d37608/image-31.jpg)
- Slides: 31
![Chapter 5 SQL Homework Chapter 5 SQL Homework](https://slidetodoc.com/presentation_image_h/f76c157325d0ecb58262042f59d37608/image-1.jpg)
Chapter 5 SQL Homework
![Hotel hotelno hotelname city Room roomno hotelno type price Booking • Hotel (hotelno, hotelname, city) • Room (roomno, hotelno, type, price) • Booking](https://slidetodoc.com/presentation_image_h/f76c157325d0ecb58262042f59d37608/image-2.jpg)
• Hotel (hotelno, hotelname, city) • Room (roomno, hotelno, type, price) • Booking (hotelno, guestno, datefrom, dateto, roomno) • Guest (guestno, guestname, guestaddress)
![Simple Queries 7 List full details of all hotels SELECT Simple Queries • 7. List full details of all hotels. • SELECT * •](https://slidetodoc.com/presentation_image_h/f76c157325d0ecb58262042f59d37608/image-3.jpg)
Simple Queries • 7. List full details of all hotels. • SELECT * • FROM hotel;
![8 List all details of all hotels in London SELECT • 8. List all details of all hotels in London. • SELECT *](https://slidetodoc.com/presentation_image_h/f76c157325d0ecb58262042f59d37608/image-4.jpg)
• 8. List all details of all hotels in London. • SELECT * • FROM hotel • WHERE city = 'London';
![9 List the names and addresses of all guests in London alphabetically • 9. List the names and addresses of all guests in London, alphabetically](https://slidetodoc.com/presentation_image_h/f76c157325d0ecb58262042f59d37608/image-5.jpg)
• 9. 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;
![10 List all double or family rooms with a price below 40 • 10. List all double or family rooms with a price below $40.](https://slidetodoc.com/presentation_image_h/f76c157325d0ecb58262042f59d37608/image-6.jpg)
• 10. List 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;
![11 List the bookings for which no dateto has been specified • 11. List the bookings for which no dateto has been specified. •](https://slidetodoc.com/presentation_image_h/f76c157325d0ecb58262042f59d37608/image-7.jpg)
• 11. List the bookings for which no dateto has been specified. • SELECT * • FROM booking • WHERE dateto IS NULL;
![Aggregate Functions 12 How many hotels are there SELECT COUNT Aggregate Functions • 12. How many hotels are there? • • SELECT COUNT(*) •](https://slidetodoc.com/presentation_image_h/f76c157325d0ecb58262042f59d37608/image-8.jpg)
Aggregate Functions • 12. How many hotels are there? • • SELECT COUNT(*) • FROM hotel; • SELECT COUNT(hotelno) • FROM hotel;
![13 What is the average price of a room SELECT • 13. What is the average price of a room? • • SELECT](https://slidetodoc.com/presentation_image_h/f76c157325d0ecb58262042f59d37608/image-9.jpg)
• 13. What is the average price of a room? • • SELECT AVG(price) • FROM room;
![14 What is the total revenue per night from all double rooms • 14. What is the total revenue per night from all double rooms?](https://slidetodoc.com/presentation_image_h/f76c157325d0ecb58262042f59d37608/image-10.jpg)
• 14. What is the total revenue per night from all double rooms? • • SELECT SUM(price) • FROM room • WHERE type = 'Double' ;
![15 How many different guests have made bookings for August • 15. How many different guests have made bookings for August? • •](https://slidetodoc.com/presentation_image_h/f76c157325d0ecb58262042f59d37608/image-11.jpg)
• 15. How many different guests 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 price and type of all rooms at Subqueries and Joins • 16. List the price and type of all rooms at](https://slidetodoc.com/presentation_image_h/f76c157325d0ecb58262042f59d37608/image-12.jpg)
Subqueries and Joins • 16. List the price and type of all rooms at the Grosvenor Hotel. • • • SELECT price, type FROM room WHERE hotelno = (SELECT hotelno FROM hotel WHERE hotelname = 'Grosvenor');
![Another Method 16 List the price and type of all rooms at the Another Method • 16. List the price and type of all rooms at the](https://slidetodoc.com/presentation_image_h/f76c157325d0ecb58262042f59d37608/image-13.jpg)
Another Method • 16. List the price and type of all rooms at the Grosvenor Hotel. • • SELECT price, type FROM room, hotel WHERE hotelno = room. hotelno AND hotelname = 'Grosvenor';
![17 List all guests currently staying at the Grosvenor Hotel SELECT • 17. List all guests currently staying at the Grosvenor Hotel. • SELECT](https://slidetodoc.com/presentation_image_h/f76c157325d0ecb58262042f59d37608/image-14.jpg)
• 17. List all guests currently staying at the Grosvenor Hotel. • SELECT (guestno, guestname, guestaddress) • FROM guest, booking, hotel • WHERE guestno =booking. guestno AND • hotelno = booking. hotelno AND • (datefrom <= ‘SYSTEM DATE’ • AND dateto >= ‘SYSTEM DATE’) AND • hotelname = ‘Grosvenor’;
![17 List all guests currently staying at the Grosvenor Hotel another method • 17. List all guests currently staying at the Grosvenor Hotel. (another method)](https://slidetodoc.com/presentation_image_h/f76c157325d0ecb58262042f59d37608/image-15.jpg)
• 17. List all guests currently staying at the Grosvenor Hotel. (another method) • • • SELECT * FROM guest WHERE guestno IN (SELECT guestno FROM booking WHERE datefrom <= ‘SYSTEM DATE’ AND dateto >= ‘SYSTEM DATE’ AND hotelno = (SELECT hotelno FROM hotel WHERE hotelname = ‘Grosvenor’));
![18 List the details of all rooms at the Grosvenor Hotel including • 18. List the details of all rooms at the Grosvenor Hotel, including](https://slidetodoc.com/presentation_image_h/f76c157325d0ecb58262042f59d37608/image-16.jpg)
• 18. List the details of all rooms at the Grosvenor Hotel, including the name of the guest staying in the room, if the room is occupied.
![Create a view with every room having a guest CREATE VIEW Create a view with every room having a guest • • • CREATE VIEW](https://slidetodoc.com/presentation_image_h/f76c157325d0ecb58262042f59d37608/image-17.jpg)
Create a view with every room having a guest • • • CREATE VIEW roomocp (hotelno, roomno, type, price, guestname) AS 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 <= ‘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 view of every room CREATE VIEW roomall hotelno roomno type Create a view of every room • • CREATE VIEW roomall (hotelno, roomno, type,](https://slidetodoc.com/presentation_image_h/f76c157325d0ecb58262042f59d37608/image-18.jpg)
Create a view of every room • • CREATE VIEW roomall (hotelno, roomno, type, price) AS SELECT r. hotelno, r. roomno, r. type, r. price FROM hotel h, room r WHERE h. hotelname ='Grosvenor’ AND h. hotelno = r. hotelno;
![Find the answer SELECT r roomno r hotelno r type r price Find the answer • • SELECT r. roomno, r. hotelno, r. type, r. price,](https://slidetodoc.com/presentation_image_h/f76c157325d0ecb58262042f59d37608/image-19.jpg)
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 total income from bookings for the Grosvenor Hotel • 19. What is the total income from bookings for the Grosvenor Hotel](https://slidetodoc.com/presentation_image_h/f76c157325d0ecb58262042f59d37608/image-20.jpg)
• 19. What is the total income from bookings for the Grosvenor Hotel today ? • • 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’;
![20 List the rooms which are currently unoccupied at the Grosvenor Hotel • 20. List the rooms which are currently unoccupied at the Grosvenor Hotel.](https://slidetodoc.com/presentation_image_h/f76c157325d0ecb58262042f59d37608/image-21.jpg)
• 20. List the rooms which are currently unoccupied at the Grosvenor Hotel. • • • 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');
![20 List the rooms which are currently unoccupied at the Grosvenor Hotel • 20. List the rooms which are currently unoccupied at the Grosvenor Hotel.](https://slidetodoc.com/presentation_image_h/f76c157325d0ecb58262042f59d37608/image-22.jpg)
• 20. List the rooms which are currently unoccupied at the Grosvenor Hotel. • • • • SELECT (r. hotelno, r. roomno, r. type, r. price) FROM room r, hotel h WHERE r. hotelno = h. hotelno AND h. hotelname = 'Grosvenor’ AND NOT EXIST (SELECT * FROM booking b, hotel 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 income from unoccupied rooms at the Grosvenor • 21. What is the lost income from unoccupied rooms at the Grosvenor](https://slidetodoc.com/presentation_image_h/f76c157325d0ecb58262042f59d37608/image-23.jpg)
• 21. What is the lost income from unoccupied rooms at the Grosvenor Hotel? • • • SELECT SUM(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 r. hotelno=b. hotelno AND r. roomno=b. roomno AND h. hotelname = 'Grosvenor');
![Grouping 22 List the number of rooms in each hotel SELECT hotelno Grouping • 22. List the number of rooms in each hotel. • SELECT hotelno,](https://slidetodoc.com/presentation_image_h/f76c157325d0ecb58262042f59d37608/image-24.jpg)
Grouping • 22. List the number of rooms in each hotel. • SELECT hotelno, COUNT(roomno) • FROM room • GROUP BY hotelno;
![23 List the number of room in each hotel in London • 23. List the number of room in each hotel in London. •](https://slidetodoc.com/presentation_image_h/f76c157325d0ecb58262042f59d37608/image-25.jpg)
• 23. List the number of room in each hotel in London. • SELECT r. hotelno, COUNT(roomno) • FROM room r, hotel h • WHERE r. hotelno=h. hotelno AND • city = 'London' • GROUP BY r. hotelno;
![24 What is the average number of bookings for each hotel in • 24. What is the average number of bookings for each hotel in](https://slidetodoc.com/presentation_image_h/f76c157325d0ecb58262042f59d37608/image-26.jpg)
• 24. What is the average number of bookings for each hotel 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 commonly booked room type for all hotels • 25. What is the most commonly booked room type for all hotels](https://slidetodoc.com/presentation_image_h/f76c157325d0ecb58262042f59d37608/image-27.jpg)
• 25. What is the most commonly booked room type for all hotels in London? • • SELECT type, 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 • city = 'London' • GROUP BY type) • GROUP BY type;
![25 What is the most commonly booked room type for each hotel • 25. What is the most commonly booked room type for each hotel](https://slidetodoc.com/presentation_image_h/f76c157325d0ecb58262042f59d37608/image-28.jpg)
• 25. What is the most commonly booked room type for each hotel in London? • • 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;
![26 What is the lost income from unoccupied rooms at each hotel • 26. What is the lost income from unoccupied rooms at each hotel](https://slidetodoc.com/presentation_image_h/f76c157325d0ecb58262042f59d37608/image-29.jpg)
• 26. What is the lost income from unoccupied rooms at each hotel 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 Insert rows into each of these tables INSERT INTO hotel • 27. Insert rows into each of these tables. • INSERT INTO hotel](https://slidetodoc.com/presentation_image_h/f76c157325d0ecb58262042f59d37608/image-30.jpg)
• 27. Insert rows into each of these tables. • INSERT INTO hotel • VALUES (‘h 11’, ‘hilton’, ‘sacramento’); • INSERT INTO room • VALUES (‘hr 1111’, ‘h 11’, ‘single’, 120);
![28 Update the price of all room by 5 UPDATE • 28. Update the price of all room by 5%. • • UPDATE](https://slidetodoc.com/presentation_image_h/f76c157325d0ecb58262042f59d37608/image-31.jpg)
• 28. Update the price of all room by 5%. • • UPDATE room • SET price = price*1. 05;
Hotel.hotelno=room.hotelno(hotel room)
Homework oh homework
Homework oh homework i hate you you stink
Jack prelutsky homework oh homework
Homework i love you poem
Homework oh homework i hate you you stink
Example of literal language
Status kepemilikan hotel
Hyper-themed hotel: fantasyland hotel, canada
Difference between sql and pl/sql
Pl/sql unit testing
Hyatt regency hotel missouri
Guest bill
Lotte city hotel mapo
Latin american city model examples
Inner city suburbs
Is mexico city a primate city
Geometry homework answers
Chapter 4 homework accounting
Chapter 5 hotel
Chapter 14 driving in city traffic
Why is city driving more difficult
Chapter 25 america moves to the city
Lesson 2 the greek city-states
Chapter 4 the rise of sumerian city-states
Chapter 25 america moves to the city
Chapter 25 america moves to the city
Yesterday homework
Yoga and reza ... do their homework last night.
Homework 3 multiplying binomials and trinomials
Congruent chords example
Unit 8 rational functions homework 1