-- Write an SQL query to display all visas and the name of the passport holder (if available). Display the VisaID and the Passport Holder Name. Order by VisaID.

CREATE TABLE Passport ( 
    PassportID INT UNIQUE PRIMARY KEY,
    PassportHolderName VARCHAR(100), 
    Nationality VARCHAR(50), 
    IssueDate DATE, 
    ExpiryDate DATE
    ); 

CREATE TABLE Visa( 
    VisaID INT UNIQUE 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 
    (101, 'Alice Johnson', 'USA', '2020-05-15', '2030-05-14'),
    (102, 'Rajesh Kumar', 'India', '2019-08-20', '2029-08-19'),
    (103, 'Maria Gonzalez', 'Mexico', '2021-03-10', '2031-03-09'),
    (104, 'Chen Wei', 'China', '2022-11-01', '2032-10-31'),
    (105, 'Fatima Al-Mansoori', 'UAE', '2023-01-25', '2033-01-24');

INSERT INTO Visa(VisaID, PassportID, Country, VisaType, IssueDate, ExpiryDate) VALUES 
    (201, 101, 'France', 'Tourist', '2023-06-01', '2023-12-01'),
    (202, 102, 'Germany', 'Student', '2022-09-01', '2026-08-31'),
    (203, 103, 'Canada', 'Work', '2021-04-15', '2024-04-14'),
    (204, 104, 'Australia', 'Tourist', '2023-12-10', '2024-06-10'),
    (205, 105, 'United Kingdom', 'Business', '2024-02-01', '2024-08-01');

CREATE VIEW Visas_2023 AS 
SELECT * 
FROM Visa 
WHERE Year(IssueDate) = 2023;