Забыли?

?

# hotelno

код для вставкиСкачать
```Chapter 5
SQL Homework
вЂў Hotel (hotelno, hotelname, city)
вЂў Room (roomno, hotelno, type, price)
вЂў Booking (hotelno, guestno, datefrom,
dateto, roomno)
Simple Queries
вЂў 7. List full details of all hotels.
вЂў SELECT *
вЂў
FROM hotel;
вЂў 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
ordered by name.
вЂў
FROM guest
вЂў
вЂў
ORDER BY guestname;
вЂў 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.
вЂў SELECT *
вЂў
FROM booking
вЂў
WHERE dateto IS NULL;
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 AVG(price)
вЂў
FROM room;
вЂў 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
вЂў
вЂў 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 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
Grosvenor Hotel.
вЂў SELECT price, type
вЂў FROM room, hotel
вЂў WHERE hotel.hotelno = room.hotelno
вЂў
AND hotelname = 'Grosvenor';
вЂў 17. List all guests currently staying at
the Grosvenor Hotel.
вЂў
FROM guest, booking, hotel
вЂў
WHERE guest.guestno =booking.guestno AND
вЂў
hotel.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)
вЂў 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 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 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, 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;
вЂў 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 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.
вЂў
вЂў
вЂў
вЂў
вЂў
вЂў
вЂў
вЂў
вЂў
вЂў
вЂў
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.
вЂў
вЂў
вЂў
вЂў
вЂў
вЂў
вЂў
вЂў
вЂў
вЂў
вЂў
вЂў
вЂў
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 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, COUNT(roomno)
вЂў
FROM room
вЂў
GROUP BY hotelno;
вЂў 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 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 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 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 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
вЂў VALUES (вЂ�h11вЂ™, вЂ�hiltonвЂ™, вЂ�sacramentoвЂ™);
вЂў INSERT INTO room
вЂў VALUES (вЂ�hr1111вЂ™, вЂ�h11вЂ™, вЂ�singleвЂ™, 120);
вЂў 28. Update the price of all room by 5%.
вЂў
вЂў
UPDATE room
вЂў
SET price = price*1.05;
```
###### Документ
Категория
Презентации
Просмотров
50
Размер файла
60 Кб
Теги
1/--страниц
Пожаловаться на содержимое документа