-- List all guests and their check-in times (if available) from the guest and Banquet check in tables,
-- Check in time from the latest to the earliest. Display the name and check-in time only

CREATE TABLE Guest ( 
    GuestCardNumber VARCHAR(20) PRIMARY KEY, 
    Name VARCHAR(20), 
    VIPStatus INT CHECK (VIPStatus IN (0,1)) -- integer 1 = VIP, 0 = Regul
);

CREATE TABLE BanquetCheckIn ( 
    GuestCardNumber VARCHAR(20) PRIMARY KEY, 
    CheckInTime TIME
);

CREATE TABLE RoomAccess ( 
    GuestCardNumber VARCHAR(20) PRIMARY KEY, 
    
    );

INSERT INTO Guest(GuestCardNumber, Name, VIPStatus) VALUES 
    ('G1001', 'Emily Carter', 1),
    ('G1002', 'James Bennett', 0),
    ('G1003', 'Sophia Wright', 1),
    ('G1004', 'Daniel Foster', 0),
    ('G1005', 'Olivia Turner', 1);


INSERT INTO BanquetCheckIn(GuestCardNumber, CheckInTime) VALUES 
    ('G1001', '18:30:00'),
    ('G1002', '18:45:00'),
    ('G1003', '19:00:00'),
    ('G1004', '19:15:00'),
    ('G1005', '19:30:00');

SELECT G.Name, B.CheckInTime 
FROM Guest G
LEFT JOIN BanquetCheckIn B 
ON G.GuestCardNumber = B.GuestCardNumber 
ORDER BY B.CheckInTime DESC; 


-- Find the names of all guests and the last room they accessed, regardless of whether they have a log or not. Sort values by Time in descending order
SELECT Guest.Name, BanquetCheckIn.CheckInTime
FROM Guest 
LEFT JOIN BanquetCheckIn 
ON Guest.GuestCardNumber = BanquetCheckIn.GuestCardNumber 
ORDER BY BanquetCheckIn.CheckInTime DESC;