Question

Language SQL

Need help with SQL Question

Create a view vHW1_8_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 to pay.

Here is my code:

select guestname, hotelname, datediff(dateto,datefrom), sum(datediff(dateto,datefrom)*price)
from Guest g, Hotel h, Room r, Booking b
where h.hotelno=b.hotelno and r.roomno=b.roomno and g.guestno=b.guestno
and dateto is not null group by guestname,hotelname;

and here is the output:

+--------------+--------------+---------------------------+--------------------------------------+

| guestname | hotelname | datediff(dateto,datefrom) | sum(datediff(dateto,datefrom)*price) |

+--------------+--------------+---------------------------+--------------------------------------+

| Carol Farrel | Grosvenor | 5 | 50.00 |

| Carol Farrel | Watergate | 9499 | 1719224.01 |

| John Kay | Grosvenor | 7 | 70.00 |

| Mary Tregear | Watergate | 2190 | 258398.10 |

| Mike Ritchie | Grosvenor | 25 | 250.00 |

| Mike Ritchie | Phoenix Park | 11 | 165.00 |

| Tony Shaw | Latham | 2 | 235.98 |

+--------------+--------------+---------------------------+--------------------------------------+

Here are the tables:

Hotel

+---------+---------------+--------+

| hotelno | hotelname | city |

+---------+---------------+--------+

| ch01 | Omni Shoreham | London |

| ch02 | Phoenix Park | London |

| dc01 | Latham | Berlin |

| fb01 | Grosvenor | London |

| fb02 | Watergate | Paris |

+---------+---------------+--------+

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 |

+---------+--------------+-----------------------+

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 |

| 2222 | ch02 | new | 86.00 |

+--------+---------+--------+-------+

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 |

+---------+---------+------------+------------+--------+

Answer 1

SELECT * FROM id,guestname,guestaddress,roomno,hostelno,hostelname,city,datediff(dateto,datefrom),um(datediff(dateto,datefrom)*price) WHERE guestno=1001

SELECT * FROM id,guestname,guestaddress,roomno,hostelno,hostelname,city,datediff(dateto,datefrom),um(datediff(dateto,datefrom)*price) WHERE guestno=1002

SELECT * FROM id,guestname,guestaddress,roomno,hostelno,hostelname,city,datediff(dateto,datefrom),um(datediff(dateto,datefrom)*price) WHERE guestno=1003

SELECT * FROM id,guestname,guestaddress,roomno,hostelno,hostelname,city,datediff(dateto,datefrom),um(datediff(dateto,datefrom)*price) WHERE guestno=1004

SELECT * FROM id,guestname,guestaddress,roomno,hostelno,hostelname,city,datediff(dateto,datefrom),um(datediff(dateto,datefrom)*price) WHERE guestno=1005

SELECT * FROM id,guestname,guestaddress,roomno,hostelno,hostelname,city,datediff(dateto,datefrom),um(datediff(dateto,datefrom)*price) WHERE guestno=1006

SELECT * FROM id,guestname,guestaddress,roomno,hostelno,hostelname,city,datediff(dateto,datefrom),um(datediff(dateto,datefrom)*price) WHERE guestno=1007