Question

Create a view vHW1_7_xxxx listing the guest name, number of days and the amount each guest needs to pay to the hotels for their stays. Your result should not include any unknown value in the dateto. Your result should include guest name, hotel name, number of days, and amount

This is for SQL/Putty.

SELECT * FROM Hotel;
+---------+--------------- +--------+
| hotelno | hotelname   | city |
+---------+--------------- +--------+
| ch01 | Omni Shoreham | London |
| ch02 | Phoenix Park | London |
| dc01 | Latham | Berlin |
| fb01 | Grosvenor | London |
| fb02 | Watergate | Paris |
+---------+--------------- +--------+

SELECT * FROM Booking ;
+--------- +--------- +------------ +------------ +--------+
| hotelno | guestno | datefrom | dateto | roomno |
+--------- +--------- +------------ +------------ +--------+
| ch01 | 10006 | 2004-04-21 | NULL | 1101 |
| ch02 | 10002 | 2004-04-25 | 2004-05-06 | 801 |
| dc01 | 10003 | 2004-05-20 | NULL | 1001 |
| dc01 | 10007 | 2004-05-13 | 2004-05-15 | 1001 |
| fb01 | 10001 | 2004-04-01 | 2004-04-08 | 501 |
| fb01 | 10001 | 2004-05-01 | NULL | 701 |
| fb01 | 10002 | 2004-05-04 | 2004-05-29 | 601 |
| fb01 | 10004 | 2004-04-15 | NULL | 601 |
| fb01 | 10005 | 2004-05-02 | 2004-05-07 | 501 |
| fb02 | 10003 | 2004-04-05 | 2010-04-04 | 1001 |
| fb02 | 10005 | 2004-05-01 | 2030-05-04 | 1101 |
+---------+---------+------------+------------+--------+

mysql> SELECT * FROM Guest;
+---------+--------------+-----------------------+
| guestno | guestname | guestaddress |
+---------+--------------+-----------------------+
| 10001 | John Kay | 56 High St, London |
| 10002 | Mike Ritchie | 18 Tain St, London |
| 10003 | Mary Tregear | 5 Tarbot Rd, Aberdeen |
| 10004 | Joe Keogh | 2 Fergus Dr, Aberdeen |
| 10005 | Carol Farrel | 6 Achray St, Glasgow |
| 10006 | Tina Murphy | 63 Well St, Glasgow |
| 10007 | Tony Shaw | 12 Park Pl, Glasgow |
+---------+--------------+-----------------------+

mysql> SELECT * FROM Room;
+--------+---------+--------+-------+
| roomno | hotelno | type | price |
+--------+---------+--------+-------+
| 501 | fb01 | single | 10.00 |
| 601 | fb01 | double | 10.00 |
| 701 | ch02 | single | 10.00 |
| 701 | fb01 | family | 39.00 |
| 801 | ch02 | double | 15.00 |
| 901 | dc01 | single | 18.00 |
| 1001 | ch01 | single | 29.99 |
| 1001 | dc01 | double | 30.00 |
| 1001 | fb02 | single | 58.00 |
| 1101 | ch01 | family | 59.99 |
| 1101 | dc01 | family | 35.00 |
| 1101 | fb02 | double | 86.00 |
+--------+---------+--------+-------+

These are all the tables that I am given. How do I solve this?

Answer 1

Hi,

Below is the query to create a views which pulls the given columns from the tables.

Columns : guest name, hotel name, number of days, and amount

View Definition:

CREATE VIEW vHW1_7_xxxx AS

SELECT guestname, hotelname,

dateto-datefrom as numberofdays,

numberofdays*price

from booking b

inner join guest g

on b.guestno=g.guestno

inner join hotel h

on b.hotelno=h.hotelno

inner join room r

on b.hotelno=r.hotelno

where b.dateto is not NULL