Create Table Customer
(CustomerNo integer not null primary key,
CSurname text(25) Not Null,
CForenames text(25) Not Null,
CStreet text(25) Not Null,
CArea text(20),
CTown text(20) not Null,
CPostCode text(8) Not Null,
CTelNo text(12),
CLicenceNo text(16) Not Null,
CMaxRentalPD Currency)

Create Table Staff
(StaffNo integer Not Null primary key,
BranchNo integer Not Null,
SSurname text(25) Not Null,
SForenames text(25) Not Null,
SStreet text(25) Not Null,
SArea text(20),
STown text(20) not Null,
SPostCode text(8) Not Null,
STelNo text(12),
SGender text(1),
SSalary currency)

Create Table Branch
(BranchNo integer Not Null primary Key,
BStreet text(25) Not Null,
BArea text(20),
BTown text(20) not Null,
BPostCode text(8) Not Null,
BTelNo text(12))

Create Table VehRent
(VehicleNo integer Not Null primary key,
VehReg text (8) Not Null,
VehMake text(12) Not Null,
VehModel text(12) Not Null,
VehCC text(8) Not Null,
VehGearType text(1) Not Null,
VehColour text(12) Not Null,
VehSeats integer Not Null,
VehRentPD currency Not Null,
VehInsPD currency,
StaffNo integer Not Null references Staff(StaffNo),
BranchNo integer Not Null references Branch(BranchNo),
Available text(1),
RComments text(150))

Create Table RentalAgreement
(AgreeNo integer Not Null primary key,
VehicleNo integer Not Null references VehRent(VehicleNo),
CustomerNo integer Not Null references Customer(CustomerNo),
RentPD currency,
PayMethod text(1) Not Null,
DepAmount currency,
DepPaid text(1),
StartDt Datetime Not Null,
EndDate DateTime)

Create Table TestDrive
(VehicleNo integer Not Null,
CustomerNo integer Not Null references Customer(CustomerNo),
TestDate datetime,
DComments text(50),
primary key (VehicleNo, CustomerNo))

Create Table VehService
(VehicleNo integer Not Null references VehRent(VehicleNo),
StaffNo integer Not Null references Staff(StaffNo),
ServiceDate datetime Not Null,
SComments text(150),
primary key(VehicleNo, StaffNo, ServiceDate))

insert into Customer
values (201, 'Black', 'David', '27 Breckon Drive', 'Leachmere', 'Sunderland', 'SR5 9TR', '01916744567', 'BLACK671234DE8NB', 20)

insert into Customer
values (202, 'Stephenson', 'Arthur', '12 Davis Street', 'Heaton', 'Newcastle', 'NE1 8TY', '01912324564', 'STEPH783421AW9DS', 15)

insert into Customer
values (203, 'Lewis', 'Emma', '13 Hope Street', 'Denton', 'Newcastle', 'NE4 7GH', '01913452332', 'LEWIS563567EJ9YT', 17)

insert into Customer
values (204, 'Alexander', 'Jessica', '51 High Street', NULL, 'Whitley Bay', 'NE26 5TR', '01912534545', 'ALEXA66567JY8TY', 18)

insert into Customer
values (205, 'Doyle', 'David', '78 Larbert Drive', 'Roker', 'Sunderland', 'SR6 0FG', '01915156789', 'DOYLE72878DH978', 17)

insert into customer
values (206, 'Griffen', 'James', '22 Nile Street', NULL, 'Penshaw', 'SR7 8SD', '01914567834', 'GRIFF77456JH878', 16)

insert into customer
values (207, 'Payne', 'Helen', '32 Sunny Drive', 'Redhouse', 'Sunderland', 'SR4 5WR', '01914678762', 'PAYNE78673HG786', 20)

insert into customer
Values (208, 'Saunders', 'William', '5 Brook Drive', NULL, 'Penshaw', 'SR7 3TS', '01914563478', 'SAUND67784WR876', 50)

insert into staff
values (101, 11, 'Downey', 'Carol', '21 Regent Terrace', 'Grangetown', 'Sunderland', 'SR2 9YR', '01915670976', 'F', 23000)

insert into staff
values (102, 11, 'Smith', 'James', '11 Mill Lane', 'Fulwell', 'Sunderland', 'SR6 8EW', '01915489867', 'M', 19000)

insert into staff
values (103, 12, 'Harvey', 'David', '19 Burnhope Way', NULL, 'Peterlee', 'SR8 2GH', '01915865543', 'M', 28000)

insert into staff
values (104, 12, 'Marshall', 'June', '35 York Road', NULL, 'Hartlepool', 'TS26 9SD', '01429274245', 'F', 18000)

insert into staff
values (105, 13, 'James', 'Veronica', '23 Victoria Square', 'Jesmond', 'Newcastle', 'NE2 3RE', '01912816856', 'F', 29000)

insert into staff
values (106, 13, 'Tully', 'Peter', '12 Osborne Road', NULL, 'Newcastle', 'NE2 2CB', '01912122300', 'M', 24000)

insert into staff
values (107, 14, 'Times', 'Ian', '32 Leazes Crescent', NULL, 'Hexham', 'NE46 7YT', '01434601976', 'M', 32000)

insert into branch
values (11, '56 High Street', 'Fulwell', 'Sunderland', 'SR4 8UT', '01915143432')

insert into branch
values (12, '32 Wessington Lane', NULL, 'Peterlee', 'SR8 2NY', '01915180292')

insert into branch
values (13, '23 Peters Street', 'Heaton', 'Newcastle', 'NE1 9TR', '01912328345')

insert into branch
values (14, '11 Wright Way', NULL, 'Hexham', 'NE47 5RT', '01434684325')

insert into vehrent
values (1001, 'S51 NGH', 'Ford', 'Mondeo', '2000', 'M', 'Red', 4, 23, 9, 101, 11, 'Y', NULL)

insert into vehrent
values (1002, 'T76 HGH', 'Ford', 'Fiesta', '1600', 'M', 'Blue', 4, 16, 7, 102, 11, NULL, NULL)

insert into vehrent
values (1003, 'R78 RTD', 'BMW', '5-Series', '2500', 'A', 'Silver', 5, 37, 17, 101, 11, 'Y', NULL)

insert into vehrent
values (1004, 'S56 TSG', 'Fiat', 'Brava', '1600', 'M', 'Brown', 4, 19, 9, 102, 11, NULL, NULL)

insert into vehrent
values (1005, 'W67 YTD', 'Nissan', 'Primera', '2000', 'A', 'Silver', 4, 22, 12, 103, 12, 'Y', NULL)

insert into vehrent
values (1006, 'W72 YTD', 'Nissan', 'Almera', '1400', 'M', 'Blue', 4, 16, 6, 103, 12, NULL, NULL)

insert into vehrent
values (1007, 'S67 YFG', 'Peugeot', '406', '1800', 'M', 'Yellow', 4, 21, 12, 104, 12, NULL, NULL)

insert into vehrent
values (1008, 'T89 HJK', 'Proton', 'Persona', '1500', 'M', 'Black', 4, 16, 6, 104, 12, 'Y', NULL)

insert into vehrent
values (1009, 'S45 KLR', 'Mazda', 'Xedos 9', 2300, 'A', 'Grey', 4, 34, 15, 105, 13, 'Y', NULL)

insert into vehrent
values (1010, 'T23 TRD', 'MercedesBenz', 'SLK', '2300', 'A', 'Silver', 2, 30, 14, 105, 13, NULL, NULL)

insert into vehrent
values (1011, 'W56 YFG', 'Audi', 'RS4', '2550', 'M', 'Red', 4, 25, 12, 106, 13, NULL, NULL)

insert into vehrent
values (1012, 'S45 HTR', 'Seat', 'Leon', '1600', 'M', 'Yellow', 4, 16, 8, 106, 13, 'Y', NULL)

insert into vehrent
values (1013, 'T34 URT', 'Skoda', 'Felicia', '1900', 'M', 'Red', 4, 15, 9, 106, 13, NULL, NULL)

insert into vehrent
Values (1014, 'W45 WDF', 'Lexus', 'IS200', '2000', 'A', 'Silver', 4, 25, 11, 107, 14, NULL, NULL)

insert into vehrent
values (1015, 'S42 TJP', 'Porsche', 'Boxster', '2700', 'M', 'White', 2, 38, 19, 107, 14, 'Y', NULL)


insert into rentalagreement
values (2001, 1002, 203, 16, 'P', 50, 'Y', #03/12/2002#, #03/17/2002#)

insert into rentalagreement
values (2002, 1007, 208, 21, 'C', 20, 'Y', #03/15/2002#, #03/16/2002#)

insert into rentalagreement
values (2003, 1001, 205, 19, 'A', 0, 'N', #03/15/2002#, #04/14/2002#)

insert into rentalagreement
values (2004, 1006, 202, 16, 'P', 20, 'Y', #03/19/2002#, #03/25/2002#)

insert into rentalagreement
values (2005, 1008, 203, 16, 'C', 25, 'Y', #03/25/2002#, #03/26/2002#)

insert into rentalagreement
values (2006, 1002, 207, 15, 'A', 0, 'N', #03/26/2002#, #04/30/2002#)

insert into rentalagreement
values (2007, 1010, 206, 25, 'P', 50, 'Y', #03/28/2002#, #03/30/2002#)

insert into rentalagreement
values (2008, 1005, 203, 22, 'P', 20, 'Y', #04/01/2002#, #04/08/2002#)

insert into rentalagreement
values (2009, 1015, 204, 38, 'C', 20, 'Y', #04/02/2002#, #04/04/2002#)

insert into rentalagreement
values (2010, 1002, 201, 16, 'P', 0, 'N', #04/06/2002#, #04/10/2002#)

insert into rentalagreement
values (2011, 1002, 201, 16, 'P', 0, 'N', #04/12/2002#, #04/15/2002#)

insert into rentalagreement
values (2012, 1003, 204, 37, 'P', 50, 'Y', #04/12/2002#, #04/14/2002#)

insert into rentalagreement
values (2013, 1005, 203, 22, 'P', 20, 'Y', #04/15/2002#, #04/20/2002#)

insert into rentalagreement
values (2014, 1007, 202, 21, 'C', 20, 'Y', #04/18/2002#, #04/25/2002#)

insert into rentalagreement
values (2015, 1002, 205, 16, 'P', 20, 'Y', #04/20/2002#, #04/22/2002#)


insert into testdrive
values (1003, 205, #14/03/2002#, 'Nice car but far too expensive')

insert into testdrive
values (1001, 205, #15/03/2002#, 'Good performance')

insert into testdrive
values (1004, 201, #17/03/2002#, 'Not enough leg room in the back')

insert into testdrive
values (1014, 203, #21/03/2002#, 'Nice car, shame about the price')

insert into testdrive
values (1011, 204, #01/04/2002#, 'Do not like the colour')

insert into testdrive
values (1002, 206, #02/04/2002#, 'Car too small')

insert into vehservice
values (1007, 104, #17/03/2002#, 'Routine service')

insert into vehservice
values (1002, 102, #18/03/2002#, 'Check clutch and brake fluid')

insert into vehservice
values (1010, 105, #22/03/2002#, 'Some paintwork scratches to be removed from previous hire')

insert into vehservice
values (1006, 103, #27/03/2002#, 'Major service - check washers')

insert into vehservice
values (1012, 106, #01/04/2002#, 'Routine maintenance')