Veronika Milovzorova portfolio/ eng

ab aviafirma

1.  Company (ID_comp, name)

CREATE TABLE Company(
    ID_comp int PRIMARY KEY AUTO_INCREMENT,
    name char(10))

2. Trip (trip_no, id_comp, plane, town_from, town_to, time_out, time_in)

CREATE TABLE trip(
    trip_no int PRIMARY KEY AUTO_INCREMENT,
    ID_comp int,
    FOREIGN KEY (ID_comp) REFERENCES company(ID_comp),
    plane char(10),
    town_from char(25),
    town_to char(25),
    time_out datetime,
    time_in datetime)

3. Passenger (ID_psg, name)

CREATE TABLE passenger(
    ID_psg int PRIMARY KEY AUTO_INCREMENT,
    name char(20))

4. Pass_in_trip (trip_no, date, ID_psg, place)

CREATE TABLE pass_in_trip(
    trip_no int,
    FOREIGN KEY (trip_no) REFERENCES trip(trip_no),
    date datetime,
    ID_psg int,
    FOREIGN KEY (ID_psg) REFERENCES passenger(ID_psg),
    place char(10),
    PRIMARY KEY(trip_no, date, ID_psg))
ALTER TABLE pass_in_trip ADD CONSTRAINT fk_trip_no	FOREIGN KEY (trip_no) REFERENCES trip(trip_no)
INSERT INTO company(name)
VALUES ('Airbaltic');
INSERT INTO company(name)
VALUES ('Sportlines');
INSERT INTO company(name)
VALUES ('Chinalines')
INSERT INTO passenger(name)
VALUES ('Aleks Smirnov');
INSERT INTO passenger(name)
VALUES ('Anna Petrova');
INSERT INTO passenger(name)
VALUES ('Pavel Murovec');
INSERT INTO passenger(name)
VALUES ('Aleksandr Aleksandrov');
INSERT INTO passenger(name)
VALUES ('Andrei Galkin')
INSERT INTO trip(ID_comp, plane, town_from, town_to, time_out, time_in)
VALUES(3,'P-17','Barcelona','Tallinn','2022-09-17 05-56','2022-9-30 11-42');
INSERT INTO trip(ID_comp, plane, town_from, town_to, time_out, time_in)
VALUES(2,'F-3','Moskva','Piter','2017-02-11 20-06','2017-02-14 23-56');
INSERT INTO trip(ID_comp, plane, town_from, town_to, time_out, time_in)
VALUES(1,'P-5123','Tokyo','Delhi','2023-01-01 9-02','2023-01-09 20-53');
INSERT INTO trip(ID_comp, plane, town_from, town_to, time_out, time_in)
VALUES(1,'A-47','Kyiv','New York','2019-07-03 12-00','2019-07-14 3-00');
INSERT INTO trip(ID_comp, plane, town_from, town_to, time_out, time_in)
VALUES(3,'U-2','Chicago','Amsterdam','2015-02-09 14-21','2015-02-15 18-12')
INSERT INTO pass_in_trip(trip_no, date, ID_psg, place)
VALUES (1,'2022-09-17 05-56',1,'4b');
INSERT INTO pass_in_trip(trip_no, date, ID_psg, place)
VALUES (2,'2017-02-11 20-06',4,'2r');
INSERT INTO pass_in_trip(trip_no, date, ID_psg, place)
VALUES (3,'2023-01-01 09-02',3,'9c');
INSERT INTO pass_in_trip(trip_no, date, ID_psg, place)
VALUES (4,'2019-07-03 12-00',2,'1b');
INSERT INTO pass_in_trip(trip_no, date, ID_psg, place)
VALUES (5,'2015-02-09 14-21',5,'5r')

1. В таблицу Passenger добавьте поле Age (возраст).

ALTER TABLE passenger ADD COLUMN age int


UPDATE passenger SET age=19
WHERE ID_psg=1;
UPDATE passenger SET age=25
WHERE ID_psg=2;
UPDATE passenger SET age=45
WHERE ID_psg=3;
UPDATE passenger SET age=18
WHERE ID_psg=4;
UPDATE passenger SET age=47
WHERE ID_psg=5

1. Найдите средний возраст пассажиров.

SELECT AVG(age) AS avg_age FROM passenger

2. Составьте запрос на нахождение количества значений в таблице Company.

SELECT COUNT(name) AS kogus FROM company

3. Объясните словами, что выполняет следующий запрос и результат запроса копируйте
ниже:

SELECT plane, COUNT(plane) AS Kogus FROM Trip
GROUP by plane
//Näitab, kui palju igat tüüpi õhusõidukeid on kasutatud.
SELECT town_from, town_to, time_out, time_in, (time_in-time_out) AS Kestvus
FROM trip 
// näitab reisi kestust
en_US
en_US et