SQL ülesanded: AB Aviafirma

Company (ID_comp, name)
Trip (trip_no, id_comp, plane, town_from, town_to, time_out, time_in)
Passenger (ID_psg, name)
4. Pass_in_trip (trip_no, date, ID_psg, place)

CREATE TABLE Company(
ID_comp int primary key auto_increment,
name char(10)
);
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
)

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

CREATE TABLE Passenger(
ID_psg int primary key auto_increment,
name char(20)
);

INSERT INTO trip(plane,town_from,town_to,time_out,time_in)
VALUES(“Airbaltik”,”Tallinn”,”Tokyo”,2023-10-12,2023-10-13;
INSERT INTO trip(plane,town_from,town_to,time_out,time_in)
VALUES(“Airbaltik”,”Tallinn”,”Tokyo”,2023-10-12,2023-10-13;
INSERT INTO trip(plane,town_from,town_to,time_out,time_in)
VALUES(“Airbaltik”,”Tallinn”,”Tokyo”,2023-10-12,2023-10-13;
INSERT INTO trip(plane,town_from,town_to,time_out,time_in)
VALUES(“Airbaltik”,”Tallinn”,”Tokyo”,2023-10-12,2023-10-13;
INSERT INTO trip(plane,town_from,town_to,time_out,time_in)
VALUES(“Airbaltik”,”Tallinn”,”Tokyo”,2023-10-12,2023-10-13;
SELECT * from trip

Select c.name, t.plane
FROM Company AS c, Trip AS t
WHERE c.ID_Comp=t.ID_Comp

Select c.name, t.plane
FROM Company AS c INNER JOIN Trip AS t
ON c.ID_Comp=t.ID_Comp

Select c.name, t.plane
FROM Company AS c, Trip AS t
WHERE c.ID_Comp=t.ID_Comp

Select c.name, t.plane
FROM Company AS c INNER JOIN Trip AS t
ON c.ID_Comp=t.ID_Comp

ДОБАВЬ СВОЮ ТАБЛИЦУ К БАЗЕ ДАННЫХ, СВЯЖИ ЕЕ С ИМЕЮЩЕЙСЯ ТАБЛИЦЕЙ.

Для начала я создал новую таблицу по название “info”

добавил туда 2 столбца, название Груза”Name” и Вес груза”KG”.

Далее я добавил значения в стоблец “Name”

INSERT INTO info(
name)
values
('Kohver')

Далее я ввел данные стоблцу “KG”

UPDATE info SET kg=6
WHERE ID_pa=7;

Далее я создал новый стоблец в таблице “Pass_in_trip” под названием “infoArv”

ALTER TABLE pass_in_trip add infoArv int;

Обновил данные в таблице

UPDATE pass_in_trip SET infoArv=1
WHERE ID_psg=1;
UPDATE pass_in_trip SET infoArv=2
WHERE ID_psg=2;
UPDATE pass_in_trip SET infoArv=1
WHERE ID_psg=3;
UPDATE pass_in_trip SET PagasiArv=2
WHERE ID_psg=4;
UPDATE pass_in_trip SET PagasiArv=1
WHERE ID_psg=5;

Далее я связал две таблицы, я хотел чтоб мне показывало ID пассажира, место, кол-во багажа, вес багажа и что за багаж

SELECT pt.ID_psg, pt.place, pt.infoArv, pa.kg, pa.name
FROM pass_in_trip AS pt, infoArv AS pa
WHERE pa.kg=pt.infoArv