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
    );

INSERT INTO Passport(PassportID, PassportHolderName, Nationality, IssueDate, ExpiryDate)VALUES 
    (101, 'Alice Johnson', 'USA', '2020-01-15', '2030-01-15'),
    (102, 'Carlos Mendes', 'Brazil', '2019-06-20', '2029-06-20'),
    (103, 'Fatima Al-Farsi', 'UAE', '2021-03-10', '2031-03-10'),
    (104, 'George Smith', 'UK', '2018-11-05', '2028-11-05'),
    (105, 'Hannah Kim', 'South Korea', '2022-08-01', '2032-08-01'),
    (106, 'Ivan Petrov', 'Russia', '2020-04-18', '2030-04-18'),
    (107, 'Julia Chen', 'China', '2019-09-30', '2029-09-30'),
    (108, 'Liam O’Connor', 'Ireland', '2021-02-14', '2031-02-14'),
    (109, 'Nina Zhang', 'Singapore', '2022-05-22', '2032-05-22'),
    (110, 'Omar Hassan', 'Egypt', '2020-07-11', '2030-07-11');

INSERT INTO Visa (VisaID, PassportID, Country, VisaType, IssueDate, ExpiryDate)VALUES 
(201, 101, 'France', 'Tourist', '2023-05-01', '2023-11-01'),
(202, 101, 'Japan', 'Tourist', '2022-07-15', '2022-12-15'),
(203, 102, 'USA', 'Student', '2021-08-20', '2025-08-20'),
(204, 103, 'Germany', 'Work', '2022-01-10', '2024-01-10'),
(205, 104, 'Canada', 'Work', '2020-06-01', '2023-06-01'),
(206, 105, 'Italy', 'Tourist', '2023-03-05', '2023-09-05'),
(207, 106, 'Australia', 'Tourist', '2021-12-01', '2022-06-01'),
(208, 107, 'USA', 'Student', '2020-09-01', '2024-09-01'),
(209, 108, 'Germany', 'Business', '2022-03-15', '2022-09-15'),
(210, 109, 'UK', 'Tourist', '2023-06-10', '2023-12-10'),
(211, 110, 'UAE', 'Work', '2021-10-01', '2024-10-01'),
(212, 102, 'Canada', 'Tourist', '2023-01-01', '2023-07-01'),
(213, 103, 'USA', 'Business', '2023-04-01', '2023-10-01'),
(214, 104, 'India', 'Tourist', '2022-02-01', '2022-08-01'),
(215, 105, 'Spain', 'Student', '2023-09-01', '2027-09-01');

SELECT VisaType, COUNT(*) AS NumberOfVisas 
FROM Visa 
GROUP BY VisaType;