SQLize Online / PHPize Online  /  SQLtest Online

create table Model ( bort_number serial NOT NULL PRIMARY KEY, kolvo_hours integer, vmestimost integer, created_at timestamp default current_timestamp, last_obsluzh_date date, vozrast_samoleta integer ); create type request_status as enum ('V_puti', 'V_aeroportu'); create table Reis ( reis_number serial NOT NULL PRIMARY KEY, bort_number_r integer REFERENCES Model(bort_number), created_at timestamp default current_timestamp, captain varchar(30), /*vmestimost integer REFERENCES Model(vmestimost),*/ people integer, dlitelnost_min integer, status request_status, cost numeric ); create type request_type as enum ('Planovy_osm', 'Neispravnost'); create table Spisok_rashodov ( id_rashoda serial NOT NULL PRIMARY KEY, type request_type, created_at timestamp default current_timestamp, stoimost_rash integer, bort_number integer REFERENCES Model(bort_number) ); create table Remont ( id_rashoda integer REFERENCES Spisok_rashodov(id_rashoda), bort_number integer REFERENCES Model(bort_number) ); create table Client ( id serial NOT NULL PRIMARY KEY, name varchar(30), Fam varchar(30), created_at timestamp default current_timestamp, kolvo_mill integer ); /*create type request_VIP as enum ('Yes', 'No');*/ create table Usluga ( id_uslugi serial NOT NULL PRIMARY KEY, id_client integer REFERENCES Client(id), /*VIP_cost numeric,*/ date_tranz date, created_at timestamp default current_timestamp, wasted_mill integer ); INSERT INTO Model (kolvo_hours,vmestimost,last_obsluzh_date, vozrast_samoleta) VALUES (54, 100, '2019-09-05',30), (111, 200, '2010-11-04',40), (453, 154, '2012-07-27',50), (342, 202, '2008-12-13',60), (21, 64, '2000-01-01',70), (99, 132, '2017-05-05',80), (543, 86, '2021-10-14',90), (98, 164, '2023-03-18',100); select * from Model; INSERT INTO Reis (bort_number_r,captain,people, dlitelnost_min, status) VALUES (1, 'Alex', 24,180,'V_aeroportu'), (2, 'Krisha', 43 ,210,'V_puti'), (3, 'Philip', 11,240,'V_aeroportu'), (4, 'Dane', 8 ,80,'V_puti'), (5, 'Daria', 47 ,370,'V_aeroportu'), (6, 'Jhonson', 4, 60,'V_aeroportu'), (7, 'Nick', 70, 120, 'V_puti'), (8, 'Debby', 121, 86,'V_aeroportu'); Select * from Reis; CREATE OR REPLACE FUNCTION Sp_rash_insert_trigger_fnc() RETURNS trigger AS $$ BEGIN INSERT INTO Remont ( id_rashoda, bort_number) VALUES(NEW.id_rashoda,NEW.bort_number); RETURN NEW; END; $$ LANGUAGE 'plpgsql'; CREATE TRIGGER Sp_rash_insert_trigger AFTER INSERT ON Spisok_rashodov FOR EACH ROW EXECUTE PROCEDURE Sp_rash_insert_trigger_fnc(); INSERT INTO Spisok_rashodov (type,stoimost_rash, bort_number) VALUES ('Planovy_osm', 6000, 1), ('Planovy_osm', 6000, 2), ('Planovy_osm', 6000, 3), ('Planovy_osm', 6000, 4), ('Neispravnost', 100000, 2), ('Planovy_osm', 6000, 6), ('Planovy_osm', 6000, 7), ('Planovy_osm', 6000, 8); select * from Spisok_rashodov; select * from Remont; CREATE FUNCTION Func_vmest () returns TABLE(bort_number integer, kolvo_hours integer, vmestimost integer, last_obsluzh_date date, vozrast_samoleta integer) language plpgsql AS $$ begin return query(SELECT model.bort_number, model.kolvo_hours, model.vmestimost, model.last_obsluzh_date, model.vozrast_samoleta FROM Model WHERE model.vmestimost>100); end; $$; /*Создать, например, табличную функцию, которая выводит список моделей самолетов, у которых вместимость > 100*/ select * from Func_vmest (); CREATE function perev(price integer, course integer) returns numeric language plpgsql as $$ begin return round((price/course), 2); end; $$; /*Create VIEW V_director AS Select * from model INNER JOIN Reis ON model.bort_number=Reis.bort_number_r INNER JOIN Spisok_rashodov ON Reis.Bort_number_r=Spisok_rashodov.bort_number; Create VIEW V_Spisok_rashodov AS Select * from Spisok_rashodov; Create VIEW V_Reis AS Select * from Reis; Select * from V_director;*/ CREATE PROCEDURE insert_Model( Kolvo_hours integer, vmestimost integer, last_obsluzh_date date, vozrast_samoleta integer ) LANGUAGE SQL AS $$ INSERT INTO Model (kolvo_hours, vmestimost, last_obsluzh_date, vozrast_samoleta) VALUES ( kolvo_hours, vmestimost, last_obsluzh_date, vozrast_samoleta); $$; CREATE PROCEDURE insert_Reis( bort_number integer, captain varchar(30),people integer, dlitelnost_min integer, status request_status, cost numeric ) LANGUAGE SQL AS $$ INSERT INTO Reis (bort_number_r, captain, people, dlitelnost_min, status, cost) VALUES (bort_number, captain, people, dlitelnost_min, status, cost); $$; CREATE PROCEDURE insert_Client( name varchar(30), Fam varchar(30), kolvo_mill integer) LANGUAGE SQL AS $$ INSERT INTO Client (name, Fam, kolvo_mill) VALUES (name, Fam, kolvo_mill); $$; CREATE PROCEDURE insert_Usluga( id_client integer, date_tranz date, wasted_mill integer) LANGUAGE SQL AS $$ INSERT INTO Usluga (id_client, date_tranz, wasted_mill) VALUES (id_client, date_tranz , wasted_mill); $$; INSERT INTO Client(name, Fam, kolvo_mill) VALUES ('Ivan', 'Ivanov',10000); INSERT INTO Usluga(id_client,date_tranz,wasted_mill) VALUES (1, '2022-01-11',10), (1, '2023-03-18',10); SELECT id_client, date_tranz, AVG(wasted_mill) OVER (PARTITION BY id_client ORDER BY date_tranz) AS Wasted FROM Usluga; CREATE function reis_vyruchka(people integer, cost numeric) returns numeric language plpgsql as $$ begin return round((people*cost), 2); end; $$; select * from reis_vyruchka(2, 100); create or replace procedure Model_delete(_bort_number integer) as $$ begin delete from Model where number = _bort_number; end $$ language plpgsql; create or replace procedure Reis_delete(_reis_number integer) as $$ begin delete from Reis where reis_number = _reis_number; end $$ language plpgsql; create or replace procedure Spisok_rashodov_delete(_id_rashoda integer) as $$ begin delete from Spisok_rashodov where id_rashoda = _id_rashoda; end $$ language plpgsql; create or replace procedure Client_delete(_id integer) as $$ begin delete from Client where id= _id; end $$ language plpgsql; create or replace procedure Usluga_delete(_id_uslugi integer) as $$ begin delete from Usluga where id_uslugi = id_uslugi; end $$ language plpgsql;