SQL - 15. Hotel Database

CREATE DATABASE Hotel

USE Hotel

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

CREATE TABLE Customers (
    AccountNumber INT PRIMARY KEY IDENTITY,
    FirstName NVARCHAR(30) NOT NULL,
    LastName NVARCHAR(30) NOT NULL,
    PhoneNumber INT UNIQUE NOT NULL,
    EmergencyName NVARCHAR(30),
    EmergencyNumber NVARCHAR(20),
    Notes NVARCHAR(MAX)
)

CREATE TABLE RoomStatus (
    RoomStatus NVARCHAR(20) PRIMARY KEY,
    Notes NVARCHAR(MAX)
)

CREATE TABLE RoomTypes (
    RoomType NVARCHAR(20) PRIMARY KEY,
    Notes NVARCHAR(MAX)
)

CREATE TABLE BedTypes (
    BedType NVARCHAR(20) PRIMARY KEY,
    Notes NVARCHAR(MAX)
)

CREATE TABLE Rooms (
    RoomNumber INT PRIMARY KEY IDENTITY,
    RoomType NVARCHAR(20) FOREIGN KEY REFERENCES RoomTypes(RoomType),
    BedType NVARCHAR(20) FOREIGN KEY REFERENCES BedTypes(BedType),
    Rate DECIMAL(3, 2),
    RoomStatus NVARCHAR(20) FOREIGN KEY REFERENCES RoomStatus(RoomStatus),
    Notes NVARCHAR(MAX)
)

CREATE TABLE Payments (
    Id INT PRIMARY KEY IDENTITY,
    EmployeeId INT FOREIGN KEY REFERENCES Employees(Id),
    PaymentDate DATE NOT NULL,
    AccountNumber INT FOREIGN KEY REFERENCES Customers(AccountNumber),
    FirstDateOccupied DATE NOT NULL,
    LastDateOccupied DATE NOT NULL,
    TotalDays INT NOT NULL,
    AmountCharged DECIMAL(10, 2) NOT NULL,
    TaxRate DECIMAL(4, 2),
    TaxAmount DECIMAL(10, 2) NOT NULL,
    PaymentTotal DECIMAL(10, 2) NOT NULL,
    Notes NVARCHAR(MAX)
)

CREATE TABLE Occupancies (
    Id INT PRIMARY KEY IDENTITY,
    EmployeeId INT FOREIGN KEY REFERENCES Employees(Id),
    DateOccupied DATE NOT NULL,
    AccountNumber INT FOREIGN KEY REFERENCES Customers(AccountNumber),
    RoomNumber INT FOREIGN KEY REFERENCES Rooms(RoomNumber),
    RateApplied DECIMAL(3, 2),
    PhoneCharge DECIMAL(10, 2),
    Notes NVARCHAR(MAX)
)

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

INSERT INTO Customers
        (FirstName, LastName, PhoneNumber,
        EmergencyName, EmergencyNumber, Notes)
VALUES
        ('Georgi', 'Ivanov', 9456739, 'Milena', 4676789, NULL),
        ('Kris', 'Angelov', 8758914, 'Stamat', 5678890, NULL),
        ('Martina', 'Vangelova', 9100112, 'Iva', 45678745, NULL)

INSERT INTO RoomStatus(RoomStatus, Notes)
VALUES      ('Free', NULL),
        ('Occupied', NULL),
        ('Reserved', NULL)

INSERT INTO RoomTypes(RoomType, Notes)
VALUES      ('Single', NULL),
        ('Double', NULL),
        ('Studio', NULL)

INSERT INTO BedTypes(BedType, Notes)
VALUES      ('Single', NULL),
        ('Double', NULL),
        ('Triple', NULL)

INSERT INTO Rooms
        (RoomType, BedType, Rate, RoomStatus, Notes)
VALUES
        ('Single', 'Single', 7.25, 'Free', NULL),
        ('Double', 'Double', 8.30, 'Occupied', NULL),
        ('Studio', 'Triple', 9.20, 'Reserved', NULL)

INSERT INTO Payments
            (EmployeeId, PaymentDate, AccountNumber,
            FirstDateOccupied, LastDateOccupied,
            TotalDays, AmountCharged, TaxRate,
            TaxAmount, PaymentTotal, Notes)
VALUES
            (1, '1999-09-09', 1, '1999-09-09', '1999-09-19', 10, 500.00, 20.00, 100.00, 600.00, NULL),
            (2, '2008-07-15', 2, '2008-08-20', '2007-09-01', 11, 660.00, 20.00, 132.00, 792.00, NULL),
            (3, '2019-02-16', 3, '2019-07-02', '2019-07-22', 20, 1500.00, 20.00, 300.00, 1800.00, NULL)

INSERT INTO Occupancies
        (EmployeeId, DateOccupied, AccountNumber,
         RoomNumber, RateApplied, PhoneCharge, Notes)
VALUES
        (1, '1999-09-09', 1, 3, NULL, 50.00, NULL),
        (2, '2008-08-20', 2, 2, NULL, 15.50, NULL),
        (3, '2019-07-02', 3, 1, NULL, 0.00, NULL)