Andmebaas Avia Firma

andmebaasisusteemide-pohimsisted

--Tabeli loomine
CREATE TABLE Company (
ID_comp int primary key identity(1,1),
name char(10)
);
SELECT * FROM Company;
INSERT INTO Company(name)
VALUES ('BOENG');
INSERT INTO Company(name)
VALUES ('F-15');
INSERT INTO Company(name)
VALUES ('BOENG-13');
INSERT INTO Company(name)
VALUES ('AeroPlan');
INSERT INTO Company(name)
VALUES ('NordPlan');
DELETE FROM Company
WHERE ID_comp=3;
SELECT*FROM Company

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
);
INSERT INTO Trip (ID_comp, plane, town_from, town_to, time_out, time_in)
VALUES(1, 'AiroPlan', 'China', 'Moskva', '2023-12-10', '2023-12-11');
INSERT INTO Trip (ID_comp, plane, town_from, town_to, time_out, time_in)
VALUES(2, 'proAiroPolution', 'Tokio', 'London', '2021-12-9', '2023-12-13');
INSERT INTO Trip (ID_comp, plane, town_from, town_to, time_out, time_in)
VALUES(4, 'AiroKeyUI', 'Luxemburg', 'Tallinn', '2019-12-10', '2023-12-11');
INSERT INTO Trip (ID_comp, plane, town_from, town_to, time_out, time_in)
VALUES(5, 'AiroNord', 'Volga', 'Valga', '2023-12-10', '2023-12-11');
INSERT INTO Trip (ID_comp, plane, town_from, town_to, time_out, time_in)
VALUES(6, 'SportAir', 'Washington', 'Jõhvi', '2023-12-10', '2023-12-11');
SELECT * FROM Trip

--Tabeli loomine
CREATE TABLE Passenger (
ID_pvg int primary key identity(1,1),
name char(20)
);
INSERT INTO Passenger(name)
VALUES('Bodyan')
INSERT INTO Passenger(name)
VALUES('Petya')
INSERT INTO Passenger(name)
VALUES('Egor')
INSERT INTO Passenger(name)
VALUES('Kirill')
INSERT INTO Passenger(name)
VALUES('Kristina')
SELECT * FROM Passenger;

SELECT * FROM Passenger;
UPDATE Passenger SET age=25
WHERE ID_pvg=1;
UPDATE Passenger SET age=19
WHERE ID_pvg=2;
UPDATE Passenger SET age=13
WHERE ID_pvg=3;
UPDATE Passenger SET age=53
WHERE ID_pvg=4;
UPDATE Passenger SET age=73
WHERE ID_pvg=5;
SELECT * FROM Passenger;

ALTER TABLE Company ADD income int;
SELECT * FROM Company
UPDATE Company SET income=200
where ID_comp=1;
UPDATE Company SET income=3000
where ID_comp=2;
UPDATE Company SET income=2500
where ID_comp=3;
UPDATE Company SET income=14200
where ID_comp=4;
UPDATE Company SET income=1000
where ID_comp=5;
select * from Company

Select * from Passenger
SELECT AVG(age) AS keskmineVanus From Passenger

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 

--запрос на основе 2 таблиц
SELECT c.name, t.plane from Company as c, Trip as t
where c.ID_comp=t.ID_comp

--Inner join
SELECT c.name, t.plane
from Company as c inner join Trip as t
on c.ID_comp=t.ID_comp

--Inner join
SELECT c.name, t.plane
from Company as c inner join Trip as t
on c.ID_comp=t.ID_comp
WHERE t.town_from like 'China'

SELECT * FROM Trip AS t, Pass_in_trip as p, Passenger as pa

Select t.town_to, p.place, pas.name
FROM Trip AS t, Pass_in_trip as p, Passenger as pas
WHERE t.trip_no=p.trip_no and ID_pvg=pas.ID_pvg and pas.name like '%D%'

INSERT INTO price(hinde)
VALUES(500);
INSERT INTO price(hinde)
VALUES(300);
INSERT INTO price(hinde)
VALUES(100);
INSERT INTO price(hinde)
VALUES(342);
INSERT INTO price(hinde)
VALUES(480);

SELECT t.plane, p.hinde
from price as p inner join Trip as t on p.ID_hind=t.ID_comp
WHERE p.hinde>200