SQL - 14. Car Rental Database

CREATE DATABASE CarRental

USE CarRental

CREATE TABLE Categories (
    Id INT PRIMARY KEY IDENTITY,
    CategoryName NVARCHAR(20) UNIQUE NOT NULL,
    DailyRate DECIMAL(3, 1) NOT NULL,
    WeeklyRate DECIMAL(3, 1) NOT NULL,
    MonthlyRate DECIMAL(3, 1) NOT NULL,
    WeekendRate DECIMAL(3, 1) NOT NULL
)

CREATE TABLE Cars (
    Id INT PRIMARY KEY IDENTITY,
    PlateNumber NVARCHAR(10) UNIQUE NOT NULL,
    Manufacturer NVARCHAR(30) NOT NULL,
    Model NVARCHAR(20) NOT NULL,
    CarYear DATE NOT NULL,
    CategoryId INT FOREIGN KEY REFERENCES Categories(Id),
    Doors INT NOT NULL,
    Picture VARBINARY,
    Condition NVARCHAR(20),
    Available BIT NOT NULL
)

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

CREATE TABLE Customers (
    Id INT PRIMARY KEY IDENTITY,
    DriverLicenceNumber INT NOT NULL,
    FullName NVARCHAR(60) NOT NULL,
    [Address] NVARCHAR(100),
    City NVARCHAR(30),
    ZIPCode INT,
    Notes NVARCHAR(MAX)
)

CREATE TABLE RentalOrders (
    Id INT PRIMARY KEY IDENTITY,
    EmployeeId INT FOREIGN KEY REFERENCES Employees(Id),
    CustomerId INT FOREIGN KEY REFERENCES Customers(Id),
    CarId INT FOREIGN KEY REFERENCES Cars(Id),
    TankLevel DECIMAL(4, 2),
    KilometrageStart INT NOT NULL,
    KilometrageEnd INT NOT NULL,
    TotalKilometrage INT NOT NULL,
    StartDate DATETIME NOT NULL,
    EndDate DATETIME NOT NULL,
    TotalDays INT NOT NULL,
    RateApplied DECIMAL(2, 1),
    TaxRate DECIMAL(2, 1),
    OrderStatus NVARCHAR(20),
    Notes NVARCHAR(MAX)
)

INSERT INTO Categories
            (CategoryName, DailyRate, WeeklyRate, MonthlyRate, WeekendRate)
VALUES
            ('First', 8.8, 7.7, 6.6, 9.9),
            ('Second', 4.4, 5.5, 6.6, 7.7),
            ('Third', 1.1, 2.2, 3.3, 4.4)

INSERT INTO Cars
            (PlateNumber, Manufacturer, Model, CarYear, CategoryId, Doors, Picture, Condition, Available)
VALUES
            ('7777', 'Porsche', '911', '2019', 1, 3, NULL, 'Perfect', 1),
            ('8888', 'BMW', 'I8', '2019', 2, 3, NULL, 'Excelent', 1),
            ('1111', 'Audi', 'TT', '2016', 3, 3, NULL, 'Very good', 0)

INSERT INTO Employees
            (FirstName, LastName, Title, Notes)
VALUES
            ('Gabriela', 'Stantcheva', 'CEO', NULL),
            ('Petar', 'Marinov', 'Manager', NULL),
            ('Liliya', 'Markova', 'HR', NULL)

INSERT INTO Customers
            (DriverLicenceNumber, FullName, [Address], City, ZIPCode, Notes)
VALUES
            ('89346', 'Georgi Ivanov', 'Sofia', 'Sofia', '1000', NULL),
            ('14589', 'Kris Angelov', 'Plovdiv', 'Plovdiv', '4000', NULL),
            ('96434', 'Martina Vangelova', 'Varna', 'Varna', '5000', NULL)

INSERT INTO RentalOrders
            (EmployeeId, CustomerId, CarId, TankLevel, KilometrageStart, KilometrageEnd,
            TotalKilometrage, StartDate, EndDate, TotalDays, RateApplied, TaxRate,
            OrderStatus, Notes)
VALUES
            (1, 1, 1, 80, 0, 550, 589367, '2000-07-10', '2000-07-20', 10, NULL, NULL, 'Completed', NULL),
            (2, 2, 2, 80, 100, 999, 465789, '2018-05-31', '2018-06-30', 30, NULL, NULL, 'Completed', NULL),
            (3, 3, 3, 80, 420, 820, 345678, '2019-08-08', '2019-09-09', 31, NULL, NULL, NULL, NULL)