create table guests(
GuestID integer unsigned not null,
GName varchar(50) not null,
Surname varchar(50) not null,
Address varchar(100) not null,
City varchar(50) not null,
State varchar(50) not null,
Pcode varchar(10) not null,
PhoneNo varchar(50),
VIP varchar(3) not null,
primary key(GuestID));

insert into guests(GuestID,GName,Surname,Address,City,State,Pcode,PhoneNo,VIP)
values(1,'Steven','Jacobs','3 Felix St','Mt Gravatt','QLD','4122',null,'Yes'),
(2,'Kym','Herston','9 Palm Ct','Albion','QLD','4010','3273-5612','No'),
(3,'Jason','Little','78 Oxley Rd','St Lucia','QLD','4067','3321-9632','No'),
(4,'Lynda','Meyers','18 Agnes St','MacGregor','QLD','4109',null,'No'),
(5,'Rachel','Musgrave','78 Robertson Rd','Sunnybank','QLD','4109','3341-4563','Yes'),
(6,'Gary','Parker','18 Stewart Ave','East Brisbane','QLD','4167','3273-9636','No'),
(7,'Andrew','Pitman','21 Palmdale Dr','Sunnybank','QLD','4109','3276-7832','No'),
(8,'Michael','Fields','21 Queen St','Sunnybank Hills','QLD','4109','3341-7319','Yes'),
(9,'Kathy','Summers','61 Oakleaf St','Eight Mile Plains','QLD','4113','3341-5563','Yes'),
(10,'Liz','Brown','23 Murial Rd','South Brisbane','QLD','4101','3349-7888','No'),
(11,'Tanya','Jones','47 Smith St','Coorparoo','QLD','4151','3356-9886','Yes'),
(12,'Janine','Smith','4 Flinders Dr','Dutton Park','QLD','4102','3349-1122','No'),
(13,'Lin','Wong','8 Main St','Moorooka','QLD','4105','3349-9222','No'),
(14,'Michelle','Wilson','63 Harland Ct','Capalaba','QLD','4157','3273-3661','No'),
(15,'Andrea','Lindsay','12 Peach St','Alex Hills','QLD','4161',null,'No'),
(16,'Roger','Moore','3 Gum St','Mt Gravatt','QLD','4122','3341-5588','No'),
(17,'John','Grant','7 Brandon Rd','Eight Mile Plains','QLD','4113','3341-8787','No'),
(18,'David','Donneley','8 Baker St','Daisy Hill','QLD','4127',null,'No'),
(19,'Judy','Phillips','6 Austral St','Manly','QLD','4179','3233-4511','No'),
(20,'James','Edwards','18 Buckland Ave','Milton','QLD','4064','3245-7447','No');


create table rooms(
RoomNo integer unsigned not null,
Rate decimal(6,2) not null,
Beds integer not null,
View varchar(50) not null,
ColourTV varchar(50) not null,
primary key(RoomNo));

insert into rooms(RoomNo,Rate,Beds,View,ColourTV)
values (101,60.00,2,'None','No'),
(102,70.00,2,'None','No'),
(103,98.00,1,'Ocean','Yes'),
(104,80.00,2,'City','No'),
(105,100.00,2,'None','Yes'),
(106,60.00,1,'None','No'),
(107,110.00,2,'None','Yes'),
(108,80.00,1,'Ocean','Yes'),
(109,90.00,1,'City','Yes'),
(110,60.00,1,'None','No'),
(201,90.00,2,'None','Yes'),
(202,120.00,2,'City','No'),
(203,100.00,2,'Ocean','Yes'),
(204,60.00,1,'None','No'),
(205,90.00,1,'None','Yes'),
(206,90.00,1,'None','Yes'),
(207,100.00,1,'City','Yes'),
(208,90.00,2,'Ocean','Yes'),
(209,60.00,1,'None','No'),
(210,60.00,1,'None','No'),
(301,160.00,3,'City','Yes'),
(302,120.00,1,'City','Yes'),
(303,100.00,3,'Ocean','Yes'),
(304,100.00,2,'Ocean','Yes'),
(305,120.00,2,'City','Yes'),
(306,150.00,3,'City','Yes'),
(307,130.00,2,'Ocean','Yes'),
(308,120.00,2,'Ocean','Yes'),
(309,130.00,1,'City','Yes'),
(310,150.00,3,'Ocean','Yes');


create table bookings(
BookingID integer unsigned not null,
RoomNo integer unsigned not null,
Arrival date not null,
Departure date not null,
GuestID integer unsigned not null,
primary key (BookingID),
foreign key(GuestID) references guests(GuestID),
foreign key(RoomNo) references rooms(RoomNo));

insert into bookings(BookingID,RoomNo,Arrival,Departure,GuestID)
values (1,301,'2014-09-23','2014-09-25',8),
(2,106,'2014-10-01','2014-10-05',3),
(3,308,'2014-10-19','2014-10-22',12),
(4,204,'2014-11-08','2014-11-10',8),
(5,309,'2014-11-17','2014-11-18',5),
(6,309,'2014-11-21','2014-11-23',14),
(7,106,'2014-12-22','2014-12-25',10),
(8,202,'2015-01-02','2015-01-04',1),
(9,108,'2015-01-19','2015-01-20',13),
(10,307,'2015-01-19','2015-01-21',11),
(11,102,'2015-02-20','2015-02-24',6),
(12,306,'2015-02-28','2015-03-01',9),
(13,207,'2015-02-28','2015-03-02',13),
(14,106,'2015-03-05','2015-03-07',2),
(15,301,'2014-03-23','2014-03-25',8),
(16,106,'2014-03-31','2014-04-04',3),
(17,308,'2014-04-18','2014-04-21',12),
(18,204,'2014-05-08','2014-05-10',8),
(19,309,'2014-05-20','2014-05-21',5),
(20,309,'2014-05-24','2014-05-26',14),
(21,107,'2014-06-23','2014-06-26',10),
(22,202,'2014-07-04','2014-07-06',1),
(23,108,'2014-07-22','2014-07-23',13),
(24,307,'2014-07-22','2014-07-24',11);


create table services(
ServiceID integer unsigned not null,
BookingID integer unsigned not null,
Service varchar(50) not null,
ServiceDate date not null,
Charge decimal(6,2) not null,
primary key(ServiceID),
foreign key (BookingID) references bookings(BookingID));

insert into services(ServiceID,BookingID,Service,ServiceDate,Charge)
values (1,1,'Restaurant','2014-09-23',18.00),
(2,1,'Restaurant','2014-09-24',21.00),
(3,1,'Restaurant','2014-09-25',21.00),
(4,1,'Room Service','2014-09-24',16.00),
(5,1,'Phone','2014-09-25',1.00),
(6,8,'Restaurant','2015-01-02',25.00),
(7,8,'Minibar','2015-01-03',24.00),
(8,8,'Restaurant','2015-01-04',18.00),
(9,8,'Phone','2013-01-02',3.00),
(10,22,'Restaurant','2014-07-04',14.00),
(11,22,'Room Service','2014-07-05',4.00),
(12,22,'Restaurant','2014-07-06',18.00),
(13,22,'Room Service','2014-07-06',8.00),
(14,22,'Room Service','2014-07-06',4.00),
(15,14,'Room Service','2015-03-05',6.00),
(16,14,'Room Service','2015-03-05',10.50),
(17,14,'Room Service','2015-03-06',9.00),
(18,14,'Phone','2015-03-07',4.50),
(19,14,'Phone','2015-03-06',2.00),
(20,24,'Restaurant','2014-09-04',25.50),
(21,24,'Restaurant','2014-09-05',22.50),
(22,24,'Restaurant','2014-09-06',28.00),
(23,24,'Phone','2014-09-06',10.00),
(24,24,'Room Service','2015-09-05',18.00),
(25,2,'Room Service','2014-09-01',34.00),
(26,2,'Restaurant','2014-09-02',22.50),
(27,2,'Phone','2014-09-02',4.00),
(28,2,'Restaurant','2014-09-03',31.00),
(29,2,'Minibar','2014-09-04',18.00),
(30,2,'Restaurant','2014-09-04',14.00);