Company (ID_comp, name)
–tabeli loomine
CREATE TABLE Company (
ID_comp int primary key identity(1,1),
name char(10)
);
SELECT * from Company;
INSERT INTO Company(name)
VALUES (‘Aviaseils’);
INSERT INTO company(name)
VALUES (‘Pegasus’);
INSERT INTO company(name)
VALUES (‘Nordica’);
INSERT INTO company(name)
VALUES (‘Airbaltic’);
INSERT INTO company(name)
VALUES (‘Ryanair’);
Select * from Company
Trip (trip_no, id_comp, plane, town_from, town_to, time_out, time_i
CREATE TABLE Trip (
trip_no int primary key identity(1,1),
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
);
select * from Trip;
INSERT INTO Trip(ID_comp, plane, town_from, town_to, time_out, time_in)
VALUES(3,’G-26′,’Moskva’,’Tallinn’,’2022-08-29′,’2022-08-30′);
INSERT INTO Trip(ID_comp, plane, town_from, town_to, time_out, time_in)
VALUES(2,’F-7′,’Paris’,’Dubai’,’2019-05-13′,’2019-05-14′);
INSERT INTO Trip(ID_comp, plane, town_from, town_to, time_out, time_in)
VALUES(4,’P-512′,’Orissa’,’Jilin’,’2022-09-01′,’2022-09-01′);
INSERT INTO Trip(ID_comp, plane, town_from, town_to, time_out, time_in)
VALUES(1,’A-63′,’Kyiv’,’Columbia’,’2021-04-17′,’2021-04-17′);
INSERT INTO Trip(ID_comp, plane, town_from, town_to, time_out, time_in)
VALUES(5,’M-6′,’Rio Branco’,’Asaka’,’2023-02-10′,’2023-02-10′)
Passenger (ID_psg, name)
CREATE TABLE passenger(
ID_psg int PRIMARY KEY identity(1,1),
name char(20))
INSERT INTO passenger(name)
VALUES (‘Kolja’);
INSERT INTO passenger(name)
VALUES (‘Petja’);
INSERT INTO passenger(name)
VALUES (‘Vasja’);
INSERT INTO passenger(name)
VALUES (‘Alex’);
INSERT INTO passenger(name)
VALUES (‘Kostja’)
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))
INSERT INTO pass_in_trip(trip_no, date, ID_psg, place)
VALUES (4,’2021-04-17′,1,’2b’);
INSERT INTO pass_in_trip(trip_no, date, ID_psg, place)
VALUES (1,’2022-08-29′,4,’5a’);
INSERT INTO pass_in_trip(trip_no, date, ID_psg, place)
VALUES (2,’2019-05-13′,3,’7c’);
INSERT INTO pass_in_trip(trip_no, date, ID_psg, place)
VALUES (5,’2023-02-10′,2,’9b’);
INSERT INTO pass_in_trip(trip_no, date, ID_psg, place)
VALUES (3,’2022-09-01′,5,’1d’)
В таблицу Passenger добавьте поле Age (возраст)
ALTER TABLE passenger ADD age int
Заполните столбец возраст данными
UPDATE passenger SET age=18
WHERE ID_psg=1;
UPDATE passenger SET age=10
WHERE ID_psg=2;
UPDATE passenger SET age=20
WHERE ID_psg=3;
UPDATE passenger SET age=17
WHERE ID_psg=4;
UPDATE passenger SET age=12
WHERE ID_psg=5
Продумайте и добавьте подходящий произвольный столбец в таблицу Company, заполните его. Год начала работы компании.
ALTER TABLE Company ADD aasta int;
UPDATE Company SET aasta=2004
WHERE ID_comp=1;
UPDATE Company SET aasta=2000
WHERE ID_comp=2;
UPDATE Company SET aasta=1990
WHERE ID_comp=3;
UPDATE Company SET aasta=1895
WHERE ID_comp=4;
UPDATE Company SET aasta=1960
WHERE ID_comp=5
Найдите средний возраст пассажиров.
SELECT AVG(age) AS avg_age FROM passenger
Составьте запрос на нахождение количества значений в таблице Company.
SELECT COUNT(*) AS kogus FROM Company
Объясните словами, что выполняет следующий запрос и результат запроса копируйте ниже:
SELECT plane, COUNT(plane) AS Kogus FROM Trip
GROUP by plane
(Количество путешествий с группировкой по названию самолета.)
SELECT town_from, town_to, time_out, time_in, CAST(([time_in]-[time_out])as int) AS Kestvus
FROM trip
Продолжительность путешествия в днях
–Запрос на основании двух таблиц
Select c.name, t.town_from
from Company as c, Trip as t
where c.ID_comp=t.ID_comp
Найди все компании, вылетающие из одного произвольного города. Запрос на основе таблиц trip, company и выводящий поля company.name, town_from.
–INNER JOIN
Select c.name, t.town_from
from Company as c INNER JOIN Trip as t
on c.ID_comp=t.ID_comp
where t.town_from like ‘Moskva’
Найти все города, в которые путешествует один произвольно выбранный пассажир. Запрос на основе таблиц trip, pass_in_trip, passenger и выводящий поля name, place, town_to.
Select pa.name, p.place, t.town_to
from Trip as t, pass_in_trip as p, passenger as pa
where t.trip_no=p.trip_no and p.ID_psg=pa.ID_psg and
pa.name like ‘Kolja%’
Добавь свою таблицу к базе данных, свяжи ее с имеющейся таблицей.
Цена билетов для конкретного человека.
CREATE TABLE Pilet (
pilet_id int primary key identity(1,1),
piletMaksa int,
ID_psg int,
Foreign key (ID_psg) references Passanger(ID_psg),
);
Select*from Pilet;
INSERT INTO Pilet (piletihind, Id_psg)
VALUES (’88’);
INSERT INTO Pilet (piletihind, Id_psg)
VALUES (’85’);
INSERT INTO Pilet (piletihind, Id_psg)
VALUES (’78’);
INSERT INTO Pilet (piletihind, Id_psg)
VALUES (‘157’);
INSERT INTO Pilet (piletihind, Id_psg)
VALUES (‘123’);
Select*from Pilet;
Select p.name, pl.piletihind
from Passanger as p, Pilet as pl
Where p.Id_psg = pl.Id_psg and
p.name like ‘Kolja%’;
