VF8UkiQs

1\connect hw9
2
3
4DROP TABLE Orders CASCADE;
5DROP TABLE Flights CASCADE;
6DROP TABLE Planes CASCADE;
7
8
9CREATE TABLE Planes (
10    planeId INT NOT NULL,
11    capacity INT NOT NULL,
12    PRIMARY KEY (planeId)
13);
14
15CREATE TABLE Flights (
16    innerId SERIAL UNIQUE NOT NULL,
17    flightId INT NOT NULL,
18    flightTime TIMESTAMP NOT NULL,
19    planeId INT REFERENCES Planes (planeId) NOT NULL,
20    isOver BOOLEAN NOT NULL,
21    PRIMARY KEY (flightId, flightTime, planeId)
22);
23
24CREATE TABLE Orders (
25    innerFlightId INT REFERENCES Flights (innerId) NOT NULL,
26    seatId INT NOT NULL,
27    orderTime TIMESTAMP NOT NULL,
28    isBooking BOOLEAN NOT NULL,
29    PRIMARY KEY (innerFlightId, seatId)
30);
31
32CREATE OR REPLACE FUNCTION update_orders () RETURNS TRIGGER AS $$
33    DECLARE
34        isOver BOOLEAN := FALSE;
35        isBooking BOOLEAN := FALSE;
36        orderTime TIMESTAMP;
37        flightTime TIMESTAMP;
38        planeCap INT;
39        pId INT;
40    BEGIN
41        IF (TG_OP = 'DELETE') THEN
42            return OLD;
43        END IF;
44
45        SELECT Flights.flightTime INTO flightTime FROM Flights where innerId = NEW.innerFlightId;
46        IF (NOW () +  interval '2 hour' > flightTime AND NEW.isBooking = FALSE) THEN
47            RETURN NULL;
48        END IF;
49        IF (NOW () +  interval '1 day' > flightTime AND NEW.isBooking = TRUE) THEN
50            RETURN NULL;
51        END IF;
52
53        SELECT Orders.isBooking, Orders.orderTime INTO isBooking, orderTime FROM Orders where innerFlightId = NEW.innerFlightId AND seatId = NEW.seatId;
54        IF (isBooking = TRUE AND (orderTime + interval '1 day'<  NOW () OR NOW () + interval '1 day' < flightTime)) THEN
55            DELETE FROM Orders WHERE innerFlightId = NEW.innerFlightId AND seatId = NEW.seatId;
56        END IF;
57
58        SELECT Flights.planeId INTO pId FROM flights WHERE NEW.innerFlightId = innerId;
59        SELECT Planes.capacity INTO planeCap FROM planes WHERE pId = Planes.planeId;
60
61        if (NEW.seatId > planeCap) THEN
62            RETURN NULL;
63        END IF;
64
65        IF (TG_OP = 'INSERT') THEN
66            SELECT Flights.isOver INTO isOver FROM Flights where innerId = NEW.innerFlightId;
67            if (isOver = TRUE) THEN
68                RETURN NULL;
69            END IF;
70            SELECT Orders.isBooking INTO isBooking FROM Orders where innerFlightId = NEW.innerFlightId AND seatId = NEW.seatId;
71            if FOUND THEN
72                RETURN NULL;
73            END IF;
74            RETURN NEW;
75        END IF;
76
77        IF (TG_OP = 'UPDATE') THEN
78            RETURN NEW;
79        END IF;
80        RETURN NULL;
81    END;
82$$
83LANGUAGE plpgsql;
84
85CREATE TRIGGER trigger_dec
86BEFORE UPDATE OR INSERT OR DELETE ON Orders
87    FOR EACH ROW EXECUTE PROCEDURE update_orders ();
88
89
90INSERT INTO Planes (planeId, capacity) VALUES (4, 10);
91INSERT INTO Planes (planeId, capacity) VALUES (6, 15);
92
93INSERT INTO Flights (flightId, flightTime, planeId, isOver) VALUES (777, now () + interval '1000 hour', 4, FALSE);
94INSERT INTO Flights (flightId, flightTime, planeId, isOver) VALUES (777, now () + interval '100 hour', 4, FALSE);
95INSERT INTO Flights (flightId, flightTime, planeId, isOver) VALUES (778, now () + interval '1000 hour', 4, FALSE);
96INSERT INTO Flights (flightId, flightTime, planeId, isOver) VALUES (779, now () + interval '1000 hour', 6, FALSE);
97-- INSERT INTO Orders (innerFlightId, seatId, orderTime, isBooking) VALUES (1, 10, NOW () - interval '1 hour', FALSE);
98-- INSERT INTO Orders (innerFlightId, seatId, orderTime, isBooking) VALUES (1, 10, TO_CHAR(NOW (),  'mon dd yyyy hh:mi:ss') , FALSE);
99-- INSERT INTO Orders (innerFlightId, seatId, orderTime, isBooking) VALUES (1, 10, NOW () + interval '1000 minutes', FALSE);
100INSERT INTO Orders (innerFlightId, seatId, orderTime, isBooking) VALUES (1, 2, NOW () - interval '23 hour', TRUE); 
101INSERT INTO Orders (innerFlightId, seatId, orderTime, isBooking) VALUES (1, 3, NOW (), TRUE);
102INSERT INTO Orders (innerFlightId, seatId, orderTime, isBooking) VALUES (2, 7, NOW () - interval '23 hour', TRUE); 
103INSERT INTO Orders (innerFlightId, seatId, orderTime, isBooking) VALUES (2, 1, NOW () - interval '23 hour', TRUE); 
104INSERT INTO Orders (innerFlightId, seatId, orderTime, isBooking) VALUES (2, 9, NOW () - interval '23 hour', TRUE); 
105INSERT INTO Orders (innerFlightId, seatId, orderTime, isBooking) VALUES (3, 8, NOW (), TRUE);
106
107
108-- create index on Orders using hash (innerFlightId);
109create index on Flights using btree (innerId);
110
111-- CURRENT_TIMESTAMP
112-- CURRENT_TIMESTAMP
113
114select * from Orders;
115select * from Orders;
116
117SELECT * FROM (Flights INNER JOIN Orders on Flights.innerId = Orders.innerFlightId) NATURAL JOIN planes where flightId = 777;
118SELECT avg(A / B)
119FROM
120(
121    SELECT count(*) as A , avg(capacity) as B
122    FROM (
123        (Flights INNER JOIN Orders on Flights.innerId = Orders.innerFlightId) NATURAL JOIN planes
124    )
125    where flightId = 777
126    GROUP BY innerId
127) as xx;
128
129
130
131CREATE OR REPLACE FUNCTION FreeSeats(fId Int) RETURNS
132TABLE(seatId INT) AS $$
133DECLARE
134    cap INT;
135    pId INT;
136BEGIN
137    SELECT Flights.planeId INTO pId FROM Flights where innerId = fId;
138    SELECT Planes.capacity INTO cap FROM Planes where planeId = pId;
139    FOR sId IN 1..cap LOOP
140        PERFORM * FROM Orders where Orders.seatId = sId AND innerFlightId = fId;
141        IF NOT FOUND THEN
142            RETURN QUERY VALUES (sId);
143        END IF;
144    END LOOP;
145    RETURN;
146END;
147$$ LANGUAGE plpgsql;
148
149-- INSERT INTO Orders (innerFlightId, seatId, orderTime, isBooking) VALUES (3, 8, NOW (), TRUE);
150
151CREATE OR REPLACE FUNCTION Reserve(fId INT, sId INT) RETURNS BOOLEAN AS $$
152DECLARE
153    cap INT;
154    pId INT;
155BEGIN
156    PERFORM * FROM Orders where Orders.seatId = sId AND innerFlightId = fId;
157    IF FOUND THEN
158        RETURN FALSE;
159    END IF;
160
161    INSERT INTO Orders (innerFlightId, seatId, orderTime, isBooking) VALUES (fId, sId, NOW (), TRUE);
162
163    PERFORM * FROM Orders where Orders.seatId = sId AND innerFlightId = fId;
164    IF NOT FOUND THEN
165        RETURN FALSE;
166    END IF;
167END;
168$$ LANGUAGE plpgsql;
169
170
171CREATE OR REPLACE FUNCTION ExtendReservation(fId INT, sId INT) RETURNS BOOLEAN AS $$
172DECLARE
173    cap INT;
174    pId INT;
175BEGIN
176    PERFORM * FROM Orders where Orders.seatId = sId AND innerFlightId = fId AND isBooking = TRUE;
177    IF NOT FOUND THEN
178        RETURN FALSE;
179    END IF;
180
181    UPDATE Orders  SET orderTime = NOW() where innerFlightId = fId AND seatId = sId AND isBooking == FALSE;
182    RETURN TRUE;
183END;
184$$ LANGUAGE plpgsql;
185
186
187CREATE OR REPLACE FUNCTION BuyFree(fId INT, sId INT) RETURNS BOOLEAN AS $$
188DECLARE
189    cap INT;
190    pId INT;
191BEGIN
192    PERFORM * FROM Orders where Orders.seatId = sId AND innerFlightId = fId;
193    IF FOUND THEN
194        RETURN FALSE;
195    END IF;
196
197    INSERT INTO Orders (innerFlightId, seatId, orderTime, isBooking) VALUES (fId, sId, NOW (), FALSE);
198
199    PERFORM * FROM Orders where Orders.seatId = sId AND innerFlightId = fId;
200    IF NOT FOUND THEN
201        RETURN FALSE;
202    END IF;
203END;
204$$ LANGUAGE plpgsql;
205
206
207
208
209
210CREATE OR REPLACE FUNCTION BuyReserved(fId INT, sId INT) RETURNS BOOLEAN AS $$
211DECLARE
212    cap INT;
213    pId INT;
214BEGIN
215    PERFORM * FROM Orders where seatId = sId AND innerFlightId = fId AND isBooking = TRUE;
216    IF NOT FOUND THEN
217        RETURN FALSE;
218    END IF;
219
220    DELETE FROM Orders WHERe innerFlightId = fid AND seatId = sid;
221    RETURN BuyFree(fid, sid);
222END;
223$$ LANGUAGE plpgsql;
224
225
226CREATE OR REPLACE FUNCTION FlightStatistics() RETURNS
227TABLE(seatId INT) AS $$
228DECLARE
229    cap INT;
230    pId INT;
231BEGIN
232    DECLARE tmp CURSOR FOR SELECT innerId FROM Flights; 
233    LOOP 
234        
235    END LOOP;
236    SELECT Flights.planeId INTO pId FROM Flights where innerId = fId;
237    SELECT Planes.capacity INTO cap FROM Planes where planeId = pId;
238    FOR sId IN 1..cap LOOP
239        PERFORM * FROM Orders where Orders.seatId = sId AND innerFlightId = fId;
240        IF NOT FOUND THEN
241            RETURN QUERY VALUES (sId);
242        END IF;
243    END LOOP;
244    RETURN;
245END;
246$$ LANGUAGE plpgsql;
247
248
249
250
251
252
253
254
255
256-- ALTER FUNCTION demo ()
257-- RETURNS @rtnTable TABLE
258-- (
259    -- -- columns returned by the function
260    -- seatId INT NOT NULL,
261    -- type INT NOT NULL
262-- )
263-- AS
264-- BEGIN
265-- -- DECLARE @TempTable table (id uniqueidentifier, name nvarchar (255)....)
266
267-- -- insert into @myTable
268-- -- select from your stuff
269
270-- --This select returns data
271-- INSERT INTO @rtnTable (seatId, type) VALUES (3, 8);
272-- -- insert into @rtnTable
273-- -- SELECT ID, name FROM @mytable
274-- return;
275-- END
276
277
278-- CREATE OR REPLACE FUNCTION new_emp () RETURNS emp AS $$
279-- CREATE OR REPLACE FUNCTION new_emp () RETURNS planes AS $$
280
281-- BEGIN
282    -- set a = SELECT ROW(3, 8)::planes;
283    -- return a;
284-- END;
285-- $$ LANGUAGE SQL;
286
287-- CREATE OR REPLACE FUNCTION add(a INT, b INT) returns INT AS return a + b;
288
289
290-- create or replace function add(a int, b int) returns int
291    -- as return a + b; 
292
293-- drop table myTable;
294
295-- create table myTable (
296    -- a int,
297    -- b int
298-- );
299
300-- CREATE OR REPLACE FUNCTION test () RETURNS void AS $$
301-- INSERT INTO mytable VALUES (30),(50)
302-- $$ LANGUAGE sql;
303
304
305-- -- DROP FUNCTION demo ();
306
307-- CREATE OR REPLACE FUNCTION demo ()
308-- RETURNS INT AS $$
309-- DECLARE
310    -- cap int := 10;
311-- BEGIN
312    -- if (TG_OP = 'INSERT') then
313        -- return 123;
314    -- end if;
315    -- select capacity into cap from planes where planeId = 5;
316    -- -- SELECT * FROM flights  WHERE = fst_name;
317    -- -- IF NOT FOUND THEN
318        -- -- return 'NOT FOUND';
319    -- -- END IF;
320    -- return cap;
321-- END;
322-- $$ LANGUAGE plpgsql;
323
324-- CREATE OR REPLACE FUNCTION TODAY_IS () RETURNS CHAR (22) AS  $$
325-- BEGIN
326  -- RETURN 'Today  is ' || CAST(CURRENT_DATE AS CHAR (100));
327-- END;
328-- $$
329-- LANGUAGE PLPGSQL
330
331
332-- CREATE OR REPLACE FUNCTION concat_lower_or_upper (a text, b text, uppercase boolean DEFAULT false)
333-- RETURNS text
334-- AS
335-- $$
336 -- SELECT CASE
337        -- WHEN $3 THEN UPPER($1 || ' ' || $2)
338        -- ELSE LOWER($1 || ' ' || $2)
339        -- END;
340-- $$
341-- LANGUAGE SQL IMMUTABLE STRICT;
342
343
344-- CREATE OR REPLACE FUNCTION gg (m integer, n integer)
345-- RETURNS TEXT AS $$
346        -- BEGIN
347                -- RETURN  "planeId";
348        -- END
349-- $$ LANGUAGE plpgsql;
350
351
352
353-- CREATE TABLE groups (
354    -- groupId SERIAL PRIMARY KEY NOT NULL,
355    -- groupName VARCHAR(7) NOT NULL
356-- );
357
358-- CREATE TABLE students (
359    -- studentId SERIAL NOT NULL,
360    -- studentName VARCHAR(100),
361    -- groupId INT REFERENCES groups (groupId) NOT NULL,
362    -- PRIMARY KEY(studentId, groupId)
363-- );
364
365-- CREATE TABLE lecturers (
366    -- lecturerId SERIAL PRIMARY KEY NOT NULL,
367    -- lecturerName VARCHAR(100)
368-- );
369
370-- CREATE TABLE courses (
371    -- courseId SERIAL PRIMARY KEY NOT NULL,
372    -- courseName VARCHAR(100)
373-- );
374-- CREATE TABLE plans (
375    -- groupId INT REFERENCES groups (groupId) NOT NULL,
376    -- lecturerId INT REFERENCES lecturers (lecturerId)  NOT NULL,
377    -- courseId INT REFERENCES courses (courseId) NOT NULL,
378    -- PRIMARY KEY (groupId, courseId)
379-- );
380
381-- CREATE TABLE marks (
382    -- studentId INT NOT NULL,
383    -- groupId INT NOT NULL,
384    -- courseId INT NOT NULL,
385    -- mark INT NOT NULL,
386    -- CHECK (mark BETWEEN 0 AND 100),
387    -- PRIMARY KEY (studentId, courseId),
388    -- FOREIGN KEY (studentId, groupId) REFERENCES students (studentId, groupId) ON DELETE CASCADE,
389    -- FOREIGN KEY (groupId, courseId) REFERENCES plans (groupId, courseId)
390-- );
391
392
393
394-- CREATE TABLE newPoints (
395    -- studentId INT NOT NULL,
396    -- groupId INT NOT NULL,
397    -- courseId INT NOT NULL,
398    -- mark INT NOT NULL,
399    -- CHECK (mark BETWEEN 0 AND 100),
400    -- PRIMARY KEY (studentId, courseId),
401    -- FOREIGN KEY (studentId, groupId) REFERENCES students (studentId, groupId) ON DELETE CASCADE,
402    -- FOREIGN KEY (groupId, courseId) REFERENCES plans (groupId, courseId)
403-- );
404
405
406
407-- CREATE TABLE losersT (
408    -- studentId INT PRIMARY KEY NOT NULL,
409    -- countDepts INT NOT NULL
410-- );
411
412
413
414-- -- create view losers as select studentId, count(mark) from (students natural join marks) where mark < 100 group by studentId;
415
416-- -- select * from losers;
417
418
419
420
421
422
423
424-- INSERT INTO groups (groupName) VALUES ('M4138');
425-- INSERT INTO groups (groupName) VALUES ('M3439');
426-- INSERT INTO groups (groupName) VALUES ('M3438');
427-- INSERT INTO groups (groupName) VALUES ('M3437');
428
429-- INSERT INTO students (studentName, groupId) VALUES ('Ваня', 2);
430-- INSERT INTO students (studentName, groupId) VALUES ('Вова', 1);
431-- INSERT INTO students (studentName, groupId) VALUES ('Илья', 2);
432-- INSERT INTO students (studentName, groupId) VALUES ('Женя', 2);
433
434-- INSERT INTO lecturers (lecturerName) VALUES ('Koхвсь');
435-- INSERT INTO lecturers (lecturerName) VALUES ('Cтанкевич');
436-- INSERT INTO lecturers (lecturerName) VALUES ('Корнеев');
437-- INSERT INTO lecturers (lecturerName) VALUES ('Кудряшёв');
438
439-- INSERT INTO courses (courseName) VALUES ('Mатан');
440-- INSERT INTO courses (courseName) VALUES ('Дискретная математика');
441-- INSERT INTO courses (courseName) VALUES ('Базы данных');
442-- INSERT INTO courses (courseName) VALUES ('Теория кодирования');
443-- INSERT INTO courses (courseName) VALUES ('Теория формальных языков');
444
445-- INSERT INTO plans (groupId, lecturerId, courseId) VALUES (2, 1, 1);
446-- INSERT INTO plans (groupId, lecturerId, courseId) VALUES (2, 2, 2);
447-- INSERT INTO plans (groupId, lecturerId, courseId) VALUES (2, 3, 3);
448-- INSERT INTO plans (groupId, lecturerId, courseId) VALUES (2, 2, 5);
449-- INSERT INTO plans (groupId, lecturerId, courseId) VALUES (1, 4, 4);
450
451-- INSERT INTO marks (studentId, groupId, courseId, mark) VALUES (1, 2, 1, 90);
452-- INSERT INTO marks (studentId, groupId, courseId, mark) VALUES (1, 2, 3, 95);
453-- INSERT INTO marks (studentId, groupId, courseId, mark) VALUES (3, 2, 3, 70);
454-- INSERT INTO marks (studentId, groupId, courseId, mark) VALUES (2, 1, 4, 74);
455
456-- INSERT INTO newPoints (studentId, groupId, courseId, mark) VALUES (3, 2, 1, 91);
457-- INSERT INTO newPoints (studentId, groupId, courseId, mark) VALUES (4, 2, 1, 92);
458-- INSERT INTO newPoints (studentId, groupId, courseId, mark) VALUES (4, 2, 3, 50);
459
460-- INSERT INTO marks (studentId, groupId, courseId, mark) VALUES (3, 2, 1, 91);
461-- INSERT INTO marks (studentId, groupId, courseId, mark) VALUES (4, 2, 1, 92);
462-- INSERT INTO marks (studentId, groupId, courseId, mark) VALUES (4, 2, 3, 50);
463
464
465-- SELECT COUNT(mark) FROM marks ;
466
467-- 1
468
469-- delete from students where studentId in (
470    -- select studentId
471        -- from students
472        -- where not exists (
473            -- select *
474            -- from marks
475            -- where students.studentId = marks.studentId and marks.mark < 60
476        -- )
477-- );
478
479-- 2
480
481-- delete from students where studentId in (
482    -- SELECT studentId FROM marks where mark < 60 group by marks.studentId having count(mark) >= 1
483-- );
484
485
486-- 3
487
488
489-- delete  from groups where  groupId in (
490    -- select * from (
491            -- select groupId from groups
492        -- ) as xx
493        -- except  (
494            -- select distinct groupId from  students
495        -- )
496-- );
497
498-- 4
499
500-- create view losers as
501-- select studentId, count(mark) from (students natural join marks) where mark < 100 group by studentId;
502
503
504-- -- 5
505
506-- CREATE OR REPLACE FUNCTION update_loserT () RETURNS TRIGGER AS $emp_audit$
507    -- BEGIN
508        -- delete from losersT;
509        -- insert into losersT select studentId, count(mark) from (students natural join marks) where mark < 60 group by studentId;
510        -- return null;
511    -- END;
512-- $emp_audit$ LANGUAGE plpgsql;
513
514-- CREATE TRIGGER trigger_loser
515-- AFTER INSERT OR UPDATE OR DELETE ON marks
516    -- FOR EACH ROW EXECUTE PROCEDURE update_loserT ();
517
518
519
520-- -- 6
521
522-- drop trigger trigger_loser on marks;
523
524
525-- -- 7 
526
527
528-- (select * from marks
529-- union
530-- select * from newPoints);
531
532-- -- 8
533
534-- -- Схема моей базы данных не позволяет студентам одной группы изучать разные предметы.
535
536
537-- -- 9 
538
539
540-- CREATE OR REPLACE FUNCTION decrease_points () RETURNS TRIGGER AS $emp_audit$
541    -- BEGIN
542        -- if (OLD.Mark > New.Mark)
543        -- then  new.Mark = Old.Mark;
544        -- end if;
545        -- return new;
546    -- END;
547-- $emp_audit$ LANGUAGE plpgsql;
548
549-- CREATE TRIGGER trigger_dec
550-- before update ON marks
551    -- FOR EACH ROW EXECUTE PROCEDURE decrease_points ();