-- Remember to ask the question, are we pulling from both tables(INNER) Or just one table

-- Write a query to return the passport holder name and visa type. Unassigned visas should not appear in the results. 
-- Sort the results by PassportHolderName 
CREATE TABLE Passport ( 
    PassportID INT PRIMARY KEY,
    PassportHolderName VARCHAR(100), 
    Nationality VARCHAR(50), 
    IssueDate DATE, 
    ExpiryDate DATE 
    );

CREATE TABLE Visa ( 
    VisaID INT PRIMARY KEY,
    PassportID INT, 
    Country VARCHAR(50), 
    VisaType VARCHAR(50), 
    IssueDate DATE, 
    ExpiryDate DATE,
    FOREIGN KEY (PassportID) REFERENCES Passport(PassportID)
    );

INSERT INTO Passport(PassportID, PassportHolderName, Nationality, IssueDate, ExpiryDate) VALUES
    (1, 'Alice Johnson', 'USA', '2020-03-15', '2030-03-15'),
    (2, 'Mohammed Khan', 'Pakistan', '2019-07-10', '2029-07-10'),
    (3, 'Sofia Martinez', 'Mexico', '2021-01-05', '2031-01-05'),
    (4, 'Liam O''Connor', 'Ireland', '2022-06-20', '2032-06-20'),
    (5, 'Chen Wei', 'China', '2018-11-30', '2028-11-30'),
    (6, 'Yuki Tanaka', 'Japan', '2022-04-12', '2032-04-12'),
    (7, 'Carlos Mendes', 'Brazil', '2017-09-25', '2027-09-25'),
    (8, 'Fatima Al-Farsi', 'UAE', '2020-12-01', '2030-12-01'),
    (9, 'George Smith', 'UK', '2019-02-18', '2029-02-18'),
    (10, 'Anika Patel', 'India', '2021-07-07', '2031-07-07');

INSERT INTO Visa(VisaID, PassportID, Country, VisaType, IssueDate, ExpiryDate) VALUES 
(101, 1, 'France', 'Tourist', '2023-05-01', '2023-11-01'),
(102, 2, 'Canada', 'Work', '2022-08-15', '2024-08-15'),
(103, 3, 'Germany', 'Student', '2021-09-01', '2025-09-01'),
(104, 4, 'Australia', 'Tourist', '2023-12-01', '2024-06-01'),
(105, 5, 'USA', 'Business', '2020-02-20', '2022-02-20'),
(106, 6, 'USA', 'Student', '2022-08-01', '2026-08-01'),
(107, 7, 'Portugal', 'Tourist', '2023-03-10', '2023-09-10'),
(108, 8, 'UK', 'Business', '2021-05-15', '2023-05-15'),
(109, 9, 'New Zealand', 'Work', '2020-11-20', '2024-11-20'),
(110, 10, 'Germany', 'Tourist', '2022-06-01', '2022-12-01');

SELECT p.PassportHolderName, v.VisaType 
FROM Passport p 
INNER JOIN Visa v ON p.PassportID = v.PassportID 
ORDER BY p.PassportHolderName;