CREATE TABLE Employee (
    EmployeeCardNumber CHAR(10) PRIMARY KEY, -- Fixed length
    Name VARCHAR(100),
    Role VARCHAR(50),
    AccessToThirdFloor INT CHECK (AccessToThirdFloor IN (0,1)),
    YearsWithShip INT,
    Salary DECIMAL(10,2)
);

CREATE TABLE LoungeLog (
    LogID INT PRIMARY KEY,
    GuestCardNumber VARCHAR(20),
    TimeSpent INT,
    AmountSpent DECIMAL(10,2),
    TotalSpent DECIMAL(10,2)
);

CREATE TABLE EmployeeLog( 
    LogID INT PRIMARY KEY, 
    EmployeeCardNumber VARCHAR(20), 
    RoomAccessed VARCHAR(20),
    Time TIME
    );

INSERT INTO Employee (EmployeeCardNumber, Name, Role, AccessToThirdFloor, YearsWithShip, Salary) VALUES
    ('EMP0000001', 'Alice', 'Engineer', 1, 5, 75000.00),
    ('EMP0000002', 'Bob', 'Technician', 0, 3, 52000.00),
    ('EMP0000003', 'Charlie', 'Navigator', 1, 7, 88000.00),
    ('EMP0000004', 'Diana', 'Cook', 0, 2, 43000.00),
    ('EMP0000005', 'Ethan', 'Security', 1, 4, 61000.00);

INSERT INTO LoungeLog (LogID, GuestCardNumber, TimeSpent, AmountSpent, TotalSpent) VALUES
    (1, 'GC001', 120, 45.50, 50.50),
    (2, 'GC002', 90, 30.00, 1000.00),
    (3, 'GC003', 150, 75.25, 5000.00),
    (4, 'GC004', 60, 20.00, 2319.00),
    (5, 'GC005', 180, 95.00, 1.00);

INSERT INTO EmployeeLog(LogID, EmployeeCardNumber, RoomAccessed, Time) VALUES 
    (1, 'EMP0000001', 'Control Room', '08:15:00'), 
    (2, 'EMP0000002','Engine Bay', '09:30:00'),
    (3, 'EMP0000003', 'Navigation Deck', '10:45:00'), 
    (4, 'EMP0000004', 'Kitchen', '11:20:00'), 
    (5, 'EMP0000005', 'Security Office', '12:05:00'); 


SELECT AVG(AccessToThirdFloor) -- Identify the average
FROM Employee; 

SELECT MAX(AccessToThirdFloor) -- Identify the maximumn number
FROM Employee; 

-- Check for the number of employees making over $100,000 use the alias HighPaidEmployees 
SELECT COUNT(*) AS "HighPaidEmployees"
FROM Employee
WHERE Salary > 100000; -- Greater then


-- How many employees have access to the third floor and how many do not, use the alias EmployeeCount
SELECT AccessToThirdFloor, COUNT(*) AS EmployeeCount -- When you dont know what to group by, group by the first one ie: access to third floor
FROM Employee
GROUP BY AccessToThirdFloor; 

-- Calculate how much each VIP guest has spent during the whole trip. Use alias TotalSpent 
SELECT GuestCardNumber, SUM(AmountSpent) AS TotalSpent 
FROM LoungeLog 
GROUP BY GuestCardNumber;

SELECT GuestCardNumber, TotalSpent 
FROM LoungeLog; 

-- Using the EmployeeLog, the unique rooms last accessed and the total number of employees who accessed them.
-- Sort from the highest EmployeeCount to the Lowest. Use the Alias EmployeeCount
SELECT RoomAccessed, COUNT(*) AS EmployeeCount 
FROM EmployeeLog 
GROUP BY RoomAccessed 
ORDER BY EmployeeCount DESC; -- Highest to lowest 


-- Join is a select statement that combines data from two tables, known as the left table and right table into a single result.