Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- \connect hw9
- DROP TABLE Orders CASCADE;
- DROP TABLE Flights CASCADE;
- DROP TABLE Planes CASCADE;
- CREATE TABLE Planes (
- planeId INT NOT NULL,
- capacity INT NOT NULL,
- PRIMARY KEY (planeId)
- );
- CREATE TABLE Flights (
- innerId SERIAL UNIQUE NOT NULL,
- flightId INT NOT NULL,
- flightTime TIMESTAMP NOT NULL,
- planeId INT REFERENCES Planes (planeId) NOT NULL,
- isOver BOOLEAN NOT NULL,
- PRIMARY KEY (flightId, flightTime, planeId)
- );
- CREATE TABLE Orders (
- innerFlightId INT REFERENCES Flights (innerId) NOT NULL,
- seatId INT NOT NULL,
- orderTime TIMESTAMP NOT NULL,
- isBooking BOOLEAN NOT NULL,
- PRIMARY KEY (innerFlightId, seatId)
- );
- CREATE OR REPLACE FUNCTION update_orders () RETURNS TRIGGER AS $$
- DECLARE
- isOver BOOLEAN := FALSE;
- isBooking BOOLEAN := FALSE;
- orderTime TIMESTAMP;
- flightTime TIMESTAMP;
- planeCap INT;
- pId INT;
- BEGIN
- IF (TG_OP = 'DELETE') THEN
- return OLD;
- END IF;
- SELECT Flights.flightTime INTO flightTime FROM Flights where innerId = NEW.innerFlightId;
- IF (NOW () + interval '2 hour' > flightTime AND NEW.isBooking = FALSE) THEN
- RETURN NULL;
- END IF;
- IF (NOW () + interval '1 day' > flightTime AND NEW.isBooking = TRUE) THEN
- RETURN NULL;
- END IF;
- SELECT Orders.isBooking, Orders.orderTime INTO isBooking, orderTime FROM Orders where innerFlightId = NEW.innerFlightId AND seatId = NEW.seatId;
- IF (isBooking = TRUE AND (orderTime + interval '1 day'< NOW () OR NOW () + interval '1 day' < flightTime)) THEN
- DELETE FROM Orders WHERE innerFlightId = NEW.innerFlightId AND seatId = NEW.seatId;
- END IF;
- SELECT Flights.planeId INTO pId FROM flights WHERE NEW.innerFlightId = innerId;
- SELECT Planes.capacity INTO planeCap FROM planes WHERE pId = Planes.planeId;
- if (NEW.seatId > planeCap) THEN
- RETURN NULL;
- END IF;
- IF (TG_OP = 'INSERT') THEN
- SELECT Flights.isOver INTO isOver FROM Flights where innerId = NEW.innerFlightId;
- if (isOver = TRUE) THEN
- RETURN NULL;
- END IF;
- SELECT Orders.isBooking INTO isBooking FROM Orders where innerFlightId = NEW.innerFlightId AND seatId = NEW.seatId;
- if FOUND THEN
- RETURN NULL;
- END IF;
- RETURN NEW;
- END IF;
- IF (TG_OP = 'UPDATE') THEN
- RETURN NEW;
- END IF;
- RETURN NULL;
- END;
- $$
- LANGUAGE plpgsql;
- CREATE TRIGGER trigger_dec
- BEFORE UPDATE OR INSERT OR DELETE ON Orders
- FOR EACH ROW EXECUTE PROCEDURE update_orders ();
- INSERT INTO Planes (planeId, capacity) VALUES (4, 10);
- INSERT INTO Planes (planeId, capacity) VALUES (6, 15);
- INSERT INTO Flights (flightId, flightTime, planeId, isOver) VALUES (777, now () + interval '1000 hour', 4, FALSE);
- INSERT INTO Flights (flightId, flightTime, planeId, isOver) VALUES (777, now () + interval '100 hour', 4, FALSE);
- INSERT INTO Flights (flightId, flightTime, planeId, isOver) VALUES (778, now () + interval '1000 hour', 4, FALSE);
- INSERT INTO Flights (flightId, flightTime, planeId, isOver) VALUES (779, now () + interval '1000 hour', 6, FALSE);
- -- INSERT INTO Orders (innerFlightId, seatId, orderTime, isBooking) VALUES (1, 10, NOW () - interval '1 hour', FALSE);
- -- INSERT INTO Orders (innerFlightId, seatId, orderTime, isBooking) VALUES (1, 10, TO_CHAR(NOW (), 'mon dd yyyy hh:mi:ss') , FALSE);
- -- INSERT INTO Orders (innerFlightId, seatId, orderTime, isBooking) VALUES (1, 10, NOW () + interval '1000 minutes', FALSE);
- INSERT INTO Orders (innerFlightId, seatId, orderTime, isBooking) VALUES (1, 2, NOW () - interval '23 hour', TRUE);
- INSERT INTO Orders (innerFlightId, seatId, orderTime, isBooking) VALUES (1, 3, NOW (), TRUE);
- INSERT INTO Orders (innerFlightId, seatId, orderTime, isBooking) VALUES (2, 7, NOW () - interval '23 hour', TRUE);
- INSERT INTO Orders (innerFlightId, seatId, orderTime, isBooking) VALUES (2, 1, NOW () - interval '23 hour', TRUE);
- INSERT INTO Orders (innerFlightId, seatId, orderTime, isBooking) VALUES (2, 9, NOW () - interval '23 hour', TRUE);
- INSERT INTO Orders (innerFlightId, seatId, orderTime, isBooking) VALUES (3, 8, NOW (), TRUE);
- -- create index on Orders using hash (innerFlightId);
- create index on Flights using btree (innerId);
- -- CURRENT_TIMESTAMP
- -- CURRENT_TIMESTAMP
- select * from Orders;
- select * from Orders;
- SELECT * FROM (Flights INNER JOIN Orders on Flights.innerId = Orders.innerFlightId) NATURAL JOIN planes where flightId = 777;
- SELECT avg(A / B)
- FROM
- (
- SELECT count(*) as A , avg(capacity) as B
- FROM (
- (Flights INNER JOIN Orders on Flights.innerId = Orders.innerFlightId) NATURAL JOIN planes
- )
- where flightId = 777
- GROUP BY innerId
- ) as xx;
- CREATE OR REPLACE FUNCTION FreeSeats(fId Int) RETURNS
- TABLE(seatId INT) AS $$
- DECLARE
- cap INT;
- pId INT;
- BEGIN
- SELECT Flights.planeId INTO pId FROM Flights where innerId = fId;
- SELECT Planes.capacity INTO cap FROM Planes where planeId = pId;
- FOR sId IN 1..cap LOOP
- PERFORM * FROM Orders where Orders.seatId = sId AND innerFlightId = fId;
- IF NOT FOUND THEN
- RETURN QUERY VALUES (sId);
- END IF;
- END LOOP;
- RETURN;
- END;
- $$ LANGUAGE plpgsql;
- -- INSERT INTO Orders (innerFlightId, seatId, orderTime, isBooking) VALUES (3, 8, NOW (), TRUE);
- CREATE OR REPLACE FUNCTION Reserve(fId INT, sId INT) RETURNS BOOLEAN AS $$
- DECLARE
- cap INT;
- pId INT;
- BEGIN
- PERFORM * FROM Orders where Orders.seatId = sId AND innerFlightId = fId;
- IF FOUND THEN
- RETURN FALSE;
- END IF;
- INSERT INTO Orders (innerFlightId, seatId, orderTime, isBooking) VALUES (fId, sId, NOW (), TRUE);
- PERFORM * FROM Orders where Orders.seatId = sId AND innerFlightId = fId;
- IF NOT FOUND THEN
- RETURN FALSE;
- END IF;
- END;
- $$ LANGUAGE plpgsql;
- CREATE OR REPLACE FUNCTION ExtendReservation(fId INT, sId INT) RETURNS BOOLEAN AS $$
- DECLARE
- cap INT;
- pId INT;
- BEGIN
- PERFORM * FROM Orders where Orders.seatId = sId AND innerFlightId = fId AND isBooking = TRUE;
- IF NOT FOUND THEN
- RETURN FALSE;
- END IF;
- UPDATE Orders SET orderTime = NOW() where innerFlightId = fId AND seatId = sId AND isBooking == FALSE;
- RETURN TRUE;
- END;
- $$ LANGUAGE plpgsql;
- CREATE OR REPLACE FUNCTION BuyFree(fId INT, sId INT) RETURNS BOOLEAN AS $$
- DECLARE
- cap INT;
- pId INT;
- BEGIN
- PERFORM * FROM Orders where Orders.seatId = sId AND innerFlightId = fId;
- IF FOUND THEN
- RETURN FALSE;
- END IF;
- INSERT INTO Orders (innerFlightId, seatId, orderTime, isBooking) VALUES (fId, sId, NOW (), FALSE);
- PERFORM * FROM Orders where Orders.seatId = sId AND innerFlightId = fId;
- IF NOT FOUND THEN
- RETURN FALSE;
- END IF;
- END;
- $$ LANGUAGE plpgsql;
- CREATE OR REPLACE FUNCTION BuyReserved(fId INT, sId INT) RETURNS BOOLEAN AS $$
- DECLARE
- cap INT;
- pId INT;
- BEGIN
- PERFORM * FROM Orders where seatId = sId AND innerFlightId = fId AND isBooking = TRUE;
- IF NOT FOUND THEN
- RETURN FALSE;
- END IF;
- DELETE FROM Orders WHERe innerFlightId = fid AND seatId = sid;
- RETURN BuyFree(fid, sid);
- END;
- $$ LANGUAGE plpgsql;
- CREATE OR REPLACE FUNCTION FlightStatistics() RETURNS
- TABLE(seatId INT) AS $$
- DECLARE
- cap INT;
- pId INT;
- BEGIN
- DECLARE tmp CURSOR FOR SELECT innerId FROM Flights;
- LOOP
- END LOOP;
- SELECT Flights.planeId INTO pId FROM Flights where innerId = fId;
- SELECT Planes.capacity INTO cap FROM Planes where planeId = pId;
- FOR sId IN 1..cap LOOP
- PERFORM * FROM Orders where Orders.seatId = sId AND innerFlightId = fId;
- IF NOT FOUND THEN
- RETURN QUERY VALUES (sId);
- END IF;
- END LOOP;
- RETURN;
- END;
- $$ LANGUAGE plpgsql;
- -- ALTER FUNCTION demo ()
- -- RETURNS @rtnTable TABLE
- -- (
- -- -- columns returned by the function
- -- seatId INT NOT NULL,
- -- type INT NOT NULL
- -- )
- -- AS
- -- BEGIN
- -- -- DECLARE @TempTable table (id uniqueidentifier, name nvarchar (255)....)
- -- -- insert into @myTable
- -- -- select from your stuff
- -- --This select returns data
- -- INSERT INTO @rtnTable (seatId, type) VALUES (3, 8);
- -- -- insert into @rtnTable
- -- -- SELECT ID, name FROM @mytable
- -- return;
- -- END
- -- CREATE OR REPLACE FUNCTION new_emp () RETURNS emp AS $$
- -- CREATE OR REPLACE FUNCTION new_emp () RETURNS planes AS $$
- -- BEGIN
- -- set a = SELECT ROW(3, 8)::planes;
- -- return a;
- -- END;
- -- $$ LANGUAGE SQL;
- -- CREATE OR REPLACE FUNCTION add(a INT, b INT) returns INT AS return a + b;
- -- create or replace function add(a int, b int) returns int
- -- as return a + b;
- -- drop table myTable;
- -- create table myTable (
- -- a int,
- -- b int
- -- );
- -- CREATE OR REPLACE FUNCTION test () RETURNS void AS $$
- -- INSERT INTO mytable VALUES (30),(50)
- -- $$ LANGUAGE sql;
- -- -- DROP FUNCTION demo ();
- -- CREATE OR REPLACE FUNCTION demo ()
- -- RETURNS INT AS $$
- -- DECLARE
- -- cap int := 10;
- -- BEGIN
- -- if (TG_OP = 'INSERT') then
- -- return 123;
- -- end if;
- -- select capacity into cap from planes where planeId = 5;
- -- -- SELECT * FROM flights WHERE = fst_name;
- -- -- IF NOT FOUND THEN
- -- -- return 'NOT FOUND';
- -- -- END IF;
- -- return cap;
- -- END;
- -- $$ LANGUAGE plpgsql;
- -- CREATE OR REPLACE FUNCTION TODAY_IS () RETURNS CHAR (22) AS $$
- -- BEGIN
- -- RETURN 'Today is ' || CAST(CURRENT_DATE AS CHAR (100));
- -- END;
- -- $$
- -- LANGUAGE PLPGSQL
- -- CREATE OR REPLACE FUNCTION concat_lower_or_upper (a text, b text, uppercase boolean DEFAULT false)
- -- RETURNS text
- -- AS
- -- $$
- -- SELECT CASE
- -- WHEN $3 THEN UPPER($1 || ' ' || $2)
- -- ELSE LOWER($1 || ' ' || $2)
- -- END;
- -- $$
- -- LANGUAGE SQL IMMUTABLE STRICT;
- -- CREATE OR REPLACE FUNCTION gg (m integer, n integer)
- -- RETURNS TEXT AS $$
- -- BEGIN
- -- RETURN "planeId";
- -- END
- -- $$ LANGUAGE plpgsql;
- -- CREATE TABLE groups (
- -- groupId SERIAL PRIMARY KEY NOT NULL,
- -- groupName VARCHAR(7) NOT NULL
- -- );
- -- CREATE TABLE students (
- -- studentId SERIAL NOT NULL,
- -- studentName VARCHAR(100),
- -- groupId INT REFERENCES groups (groupId) NOT NULL,
- -- PRIMARY KEY(studentId, groupId)
- -- );
- -- CREATE TABLE lecturers (
- -- lecturerId SERIAL PRIMARY KEY NOT NULL,
- -- lecturerName VARCHAR(100)
- -- );
- -- CREATE TABLE courses (
- -- courseId SERIAL PRIMARY KEY NOT NULL,
- -- courseName VARCHAR(100)
- -- );
- -- CREATE TABLE plans (
- -- groupId INT REFERENCES groups (groupId) NOT NULL,
- -- lecturerId INT REFERENCES lecturers (lecturerId) NOT NULL,
- -- courseId INT REFERENCES courses (courseId) NOT NULL,
- -- PRIMARY KEY (groupId, courseId)
- -- );
- -- CREATE TABLE marks (
- -- studentId INT NOT NULL,
- -- groupId INT NOT NULL,
- -- courseId INT NOT NULL,
- -- mark INT NOT NULL,
- -- CHECK (mark BETWEEN 0 AND 100),
- -- PRIMARY KEY (studentId, courseId),
- -- FOREIGN KEY (studentId, groupId) REFERENCES students (studentId, groupId) ON DELETE CASCADE,
- -- FOREIGN KEY (groupId, courseId) REFERENCES plans (groupId, courseId)
- -- );
- -- CREATE TABLE newPoints (
- -- studentId INT NOT NULL,
- -- groupId INT NOT NULL,
- -- courseId INT NOT NULL,
- -- mark INT NOT NULL,
- -- CHECK (mark BETWEEN 0 AND 100),
- -- PRIMARY KEY (studentId, courseId),
- -- FOREIGN KEY (studentId, groupId) REFERENCES students (studentId, groupId) ON DELETE CASCADE,
- -- FOREIGN KEY (groupId, courseId) REFERENCES plans (groupId, courseId)
- -- );
- -- CREATE TABLE losersT (
- -- studentId INT PRIMARY KEY NOT NULL,
- -- countDepts INT NOT NULL
- -- );
- -- -- create view losers as select studentId, count(mark) from (students natural join marks) where mark < 100 group by studentId;
- -- -- select * from losers;
- -- INSERT INTO groups (groupName) VALUES ('M4138');
- -- INSERT INTO groups (groupName) VALUES ('M3439');
- -- INSERT INTO groups (groupName) VALUES ('M3438');
- -- INSERT INTO groups (groupName) VALUES ('M3437');
- -- INSERT INTO students (studentName, groupId) VALUES ('Ваня', 2);
- -- INSERT INTO students (studentName, groupId) VALUES ('Вова', 1);
- -- INSERT INTO students (studentName, groupId) VALUES ('Илья', 2);
- -- INSERT INTO students (studentName, groupId) VALUES ('Женя', 2);
- -- INSERT INTO lecturers (lecturerName) VALUES ('Koхвсь');
- -- INSERT INTO lecturers (lecturerName) VALUES ('Cтанкевич');
- -- INSERT INTO lecturers (lecturerName) VALUES ('Корнеев');
- -- INSERT INTO lecturers (lecturerName) VALUES ('Кудряшёв');
- -- INSERT INTO courses (courseName) VALUES ('Mатан');
- -- INSERT INTO courses (courseName) VALUES ('Дискретная математика');
- -- INSERT INTO courses (courseName) VALUES ('Базы данных');
- -- INSERT INTO courses (courseName) VALUES ('Теория кодирования');
- -- INSERT INTO courses (courseName) VALUES ('Теория формальных языков');
- -- INSERT INTO plans (groupId, lecturerId, courseId) VALUES (2, 1, 1);
- -- INSERT INTO plans (groupId, lecturerId, courseId) VALUES (2, 2, 2);
- -- INSERT INTO plans (groupId, lecturerId, courseId) VALUES (2, 3, 3);
- -- INSERT INTO plans (groupId, lecturerId, courseId) VALUES (2, 2, 5);
- -- INSERT INTO plans (groupId, lecturerId, courseId) VALUES (1, 4, 4);
- -- INSERT INTO marks (studentId, groupId, courseId, mark) VALUES (1, 2, 1, 90);
- -- INSERT INTO marks (studentId, groupId, courseId, mark) VALUES (1, 2, 3, 95);
- -- INSERT INTO marks (studentId, groupId, courseId, mark) VALUES (3, 2, 3, 70);
- -- INSERT INTO marks (studentId, groupId, courseId, mark) VALUES (2, 1, 4, 74);
- -- INSERT INTO newPoints (studentId, groupId, courseId, mark) VALUES (3, 2, 1, 91);
- -- INSERT INTO newPoints (studentId, groupId, courseId, mark) VALUES (4, 2, 1, 92);
- -- INSERT INTO newPoints (studentId, groupId, courseId, mark) VALUES (4, 2, 3, 50);
- -- INSERT INTO marks (studentId, groupId, courseId, mark) VALUES (3, 2, 1, 91);
- -- INSERT INTO marks (studentId, groupId, courseId, mark) VALUES (4, 2, 1, 92);
- -- INSERT INTO marks (studentId, groupId, courseId, mark) VALUES (4, 2, 3, 50);
- -- SELECT COUNT(mark) FROM marks ;
- -- 1
- -- delete from students where studentId in (
- -- select studentId
- -- from students
- -- where not exists (
- -- select *
- -- from marks
- -- where students.studentId = marks.studentId and marks.mark < 60
- -- )
- -- );
- -- 2
- -- delete from students where studentId in (
- -- SELECT studentId FROM marks where mark < 60 group by marks.studentId having count(mark) >= 1
- -- );
- -- 3
- -- delete from groups where groupId in (
- -- select * from (
- -- select groupId from groups
- -- ) as xx
- -- except (
- -- select distinct groupId from students
- -- )
- -- );
- -- 4
- -- create view losers as
- -- select studentId, count(mark) from (students natural join marks) where mark < 100 group by studentId;
- -- -- 5
- -- CREATE OR REPLACE FUNCTION update_loserT () RETURNS TRIGGER AS $emp_audit$
- -- BEGIN
- -- delete from losersT;
- -- insert into losersT select studentId, count(mark) from (students natural join marks) where mark < 60 group by studentId;
- -- return null;
- -- END;
- -- $emp_audit$ LANGUAGE plpgsql;
- -- CREATE TRIGGER trigger_loser
- -- AFTER INSERT OR UPDATE OR DELETE ON marks
- -- FOR EACH ROW EXECUTE PROCEDURE update_loserT ();
- -- -- 6
- -- drop trigger trigger_loser on marks;
- -- -- 7
- -- (select * from marks
- -- union
- -- select * from newPoints);
- -- -- 8
- -- -- Схема моей базы данных не позволяет студентам одной группы изучать разные предметы.
- -- -- 9
- -- CREATE OR REPLACE FUNCTION decrease_points () RETURNS TRIGGER AS $emp_audit$
- -- BEGIN
- -- if (OLD.Mark > New.Mark)
- -- then new.Mark = Old.Mark;
- -- end if;
- -- return new;
- -- END;
- -- $emp_audit$ LANGUAGE plpgsql;
- -- CREATE TRIGGER trigger_dec
- -- before update ON marks
- -- FOR EACH ROW EXECUTE PROCEDURE decrease_points ();
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement