--tabeli loomine
CREATE TABLE Company (
ID_comp int primary key identity(1,1),
name char(10)
);
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
)
select * from Trip
CREATE TABLE Passenger(
ID_psg int PRIMARY KEY identity(1,1),
name char(20))
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 Company(name) VALUES ('Имя компании');
INSERT INTO trip(plane,town_from,town_to,time_out,time_in)
VALUES("Самолет","Из какого города","В какой город","Время отлета","Время прилета");
INSERT INTO passenger(name) VALUES("Имя пассажира");
INSERT INTO pass_in_trip(trip_no,date_,ID_psg,place) VALUES(Номер путешествия,"Дата отлета",Номер пассажира,"Место пассажира")
ALTER TABLE Passenger ADD age int
UPDATE Passenger SET age=Возраст пассажира
WHERE ID_psg=Номер пассажира
SELECT * from Company;
ALTER TABLE Company add Country char(20);
SELECT * from Company
UPDATE Company set Country=Страна компании
WHERE ID_comp=Номер компании;
SELECT avg(age) as keskVanus
From Passenger
SELECT count(*) AS Kogus
From Company
SELECT town_from, town_to, time_out, time_in, CAST(([time_in]-[time_out]) as int) AS Kestvus
FROM trip
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
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 'Tartu'
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 'Ivan%'
CREATE TABLE Quantity_of_fuel (
ID_qof int primary key identity(1,1),
trip_no int,
FOREIGN KEY (trip_no) REFERENCES Trip(trip_no),
Fuel char(20)
);
SELECT * from Quantity_of_fuel
INSERT INTO Quantity_of_fuel(trip_no,Fuel)
Values(номер путешествия,'количество топлива на путешествие')
SELECT t.plane, q.Fuel
from Trip as t,Quantity_of_fuel as q
Where t.trip_no=q.trip_no
