CREATE TABLE port (
    id INT AUTO_INCREMENT PRIMARY KEY, 
    country VARCHAR(100),
    name VARCHAR(100),
    category VARCHAR(50),
    daily_rate DECIMAL(10, 2) 
)


CREATE TABLE ship (
    id INT AUTO_INCREMENT PRIMARY KEY, 
    name VARCHAR(100),
    displacement DECIMAL(10, 2), 
    home_port_id INT, 
    type VARCHAR(50),
    captain VARCHAR(100),
    FOREIGN KEY (home_port_id) REFERENCES port(id) 
);


CREATE TABLE visit (
    id INT AUTO_INCREMENT PRIMARY KEY, 
    berth_number INT,
    purpose VARCHAR(100),
    arrival_date DATE,
    departure_date DATE,
    ship_id INT, 
    port_id INT, 
    FOREIGN KEY (ship_id) REFERENCES ship(id), 
    FOREIGN KEY (port_id) REFERENCES port(id) 
);



INSERT INTO port (country, name, category, daily_rate) VALUES
('USA', 'Port of Los Angeles', 'Commercial', 150.00),
('USA', 'Port of New York', 'Commercial', 200.00),
('Canada', 'Port of Vancouver', 'Commercial', 180.00),
('UK', 'Port of London', 'Commercial', 220.00),
('Germany', 'Port of Hamburg', 'Commercial', 170.00),
('Australia', 'Port of Sydney', 'Commercial', 160.00),
('Japan', 'Port of Tokyo', 'Commercial', 190.00);

INSERT INTO ship (name, displacement, home_port_id, type, captain) VALUES
('Titanic', 52310.0, 1, 'Cruise', 'Edward Smith'),
('Queen Mary 2', 151400.0, 2, 'Cruise', 'Captain Nick Bates'),
('Oasis of the Seas', 226000.0, 3, 'Cruise', 'Captain Johnny F.'),
('Harmony of the Seas', 226000.0, 4, 'Cruise', 'Captain A. M.'),
('Norwegian Epic', 155873.0, 5, 'Cruise', 'Captain E. J.'),
('Carnival Vista', 133500.0, 6, 'Cruise', 'Captain R. M.'),
('MSC Meraviglia', 171598.0, 7, 'Cruise', 'Captain A. C.');


INSERT INTO visit (berth_number, purpose, arrival_date, departure_date, ship_id, port_id) VALUES
(1, 'Tourism', '2023-06-01', '2023-06-05', 1, 1),
(2, 'Cargo', '2023-06-10', '2023-06-12', 2, 2),
(3, 'Tourism', '2023-06-15', '2023-06-20', 3, 3),
(4, 'Cargo', '2023-06-25', '2023-06-27', 4, 4),
(5, 'Tourism', '2023-07-01', '2023-07-05', 5, 5),
(6, 'Cargo', '2023-07-10', '2023-07-12', 6, 6),
(7, 'Tourism', '2023-07-15', '2023-07-20', 7, 7),
(8, 'Cargo', '2023-07-25', '2023-07-27', 1, 2),
(9, 'Tourism', '2023-08-01', '2023-08-05', 2, 3),
(10, 'Cargo', '2023-08-10', '2023-08-12', 3, 4),
(11, 'Tourism', '2023-08-15', '2023-08-20', 4, 5),
(12, 'Cargo', '2023-08-25', '2023-08-27', 5, 6),
(13, 'Tourism', '2023-09-01', '2023-09-05', 6, 7),
(14, 'Cargo', '2023-09-10', '2023-09-12', 7, 1),
(15, 'Tourism', '2023-09-15', '2023-09-20', 1, 2),
(16, 'Cargo', '2023-09-25', '2023-09-27', 2, 3),
(17, 'Tourism', '2023-10-01', '2023-10-05', 3, 4),
(18, 'Cargo', '2023-10-10', '2023-10-12', 4, 5),
(19, 'Tourism', '2023-10-15', '2023-10-20', 5, 6),
(20, 'Cargo', '2023-10-25', '2023-10-27', 6, 7),
(21, 'Tourism', '2023-11-01', '2023-11-05', 7, 1),
(22, 'Cargo', '2023-11-10', '2023-11-12', 1, 2),
(23, 'Tourism', '2023-11-15', '2023-11-20', 2, 3),
(24, 'Cargo', '2023-11-25', '2023-11-27', 3, 4),
(25, 'Tourism', '2023-12-01', '2023-12-05', 4, 5);

Запрос 1:
select name, displacement,home_port_id, type, captain from ship where captain="Captain R. M."