CarRentalDB

CREATE TABLE Categories (
Id INT NOT NULL PRIMARY KEY,
Category VARCHAR(MAX) NOT NULL,
DailyRate FLOAT,
WeeklyRate FLOAT,
MonthlyRate FLOAT,
WeekendRate FLOAT
)

CREATE TABLE Cars (
Id INT NOT NULL PRIMARY KEY,
PlateNumber VARCHAR(MAX) NOT NULL,
Make INT,
Model VARCHAR(MAX) NOT NULL,
CarYear INT,
CategoryId INT NOT NULL,
Doors INT,
Picture VARCHAR(MAX),
Condition VARCHAR(MAX),
Available bit
)

CREATE TABLE Employees (
Id INT NOT NULL PRIMARY KEY,
FirstName VARCHAR(MAX) NOT NULL,
LastName VARCHAR(MAX) NOT NULL,
Title VARCHAR(MAX),
Notes VARCHAR(MAX)
)


CREATE TABLE Customers (
Id INT NOT NULL PRIMARY KEY,
DriverLicenceNumber INT NOT NULL,
FullName VARCHAR(MAX) NOT NULL,
Address VARCHAR(MAX),
City VARCHAR(MAX),
ZIPCode INT,
Notes VARCHAR(MAX)
)


CREATE TABLE RentalOrders (
Id INT NOT NULL PRIMARY KEY IDENTITY,
EmployeeId INT NOT NULL,
CustomerId INT NOT NULL,
CarId INT NOT NULL,
CarCondition VARCHAR(MAX),
TankLevel FLOAT,
KilometrageStart INT,
KilometrageEnd INT,
TotalKilometrage INT,
StartDate DATE NOT NULL,
EndDate DATE NOT NULL,
TotalDays INT,
RateApplied FLOAT NOT NULL,
TaxRate FLOAT NOT NULL,
OrderStatus VARCHAR(MAX) NOT NULL,
Notes VARCHAR(MAX)
)




INSERT INTO Categories
VALUES (1, 'Excellent', 12.4, 32.5, 99.2, 109.20),(2, 'Good', 10.10, 20.20, 30.30, 60.60),(3, 'Bad',9.20,109.90,21.43,10.10)

INSERT INTO Cars
VALUES (1,'SA1234OA',2,'Porche',2004,1,2,300,'Good',1),(2,'AB0920AB',1,'Mazda',2010,2,4,5000,'Very Good',0),(3,'NM0911NM',10,'Ferrari',1999,3,4,10,'Bad',1)

INSERT INTO Employees
VALUES (1,'James','Arthur','Mr','None'),(2,'Jessica','Alba','Ms','Sexy'),(3,'Ronald','Weesley','Ms','Weird')

INSERT INTO Customers
VALUES (1, 123456,'Jon Snow','The North','Winterfell',1000,'Ice'), (2,987654,'Deaneris Targarien','The East','DragonStone',2000,'Fire'), (3,129032,'Tyrion Lannister','The West','Casterly Rock', 3000,'Rock')

INSERT INTO RentalOrders
VALUES (1,2,3,'Good',0.5,1230,2345,10000,'2017-09-15','2017-09-20',5,45.90,0.25,'Active','None'),(2,3,1,'Alright',0.55,10,20,10,'2010-11-23','2010-12-24',31,1,1,'In Progress','One - hot'),(3,1,2,'Bad',0.24,100,400,500,'2004-10-01','2004-10-10',10,20.20,0.75,'Expired','OMG no')