CREATE TABLE Books (
               id INTEGER PRIMARY KEY,
               category TEXT,
               price FLOAT,
               promoted BOOLEAN DEFAULT True
);
 
INSERT INTO Books (id, category, price) VALUES(1, 'Dictionary', 100);
INSERT INTO Books (id, category, price) VALUES(2, 'Dictionary', 150);
INSERT INTO Books (id, category, price) VALUES(3, 'Science', 120);
INSERT INTO Books (id, category, price) VALUES(4, 'Science', 190);
INSERT INTO Books (id, category, price) VALUES(5, 'Science', 320);
 
\d
 
SELECT * FROM Books;
 
CREATE VIEW PromotionSummary AS
               SELECT category, MIN(price) AS minprice, MAX(price) AS maxprice
               FROM Books
               WHERE promoted
               GROUP BY category;
 
\d
 
SELECT * FROM PromotionSummary;
              
CREATE OR REPLACE FUNCTION updateStatusBooks() RETURNS TRIGGER AS $$
BEGIN
               UPDATE Books SET promoted = False WHERE category = OLD.category;
               RETURN NULL;
END
$$ LANGUAGE 'plpgsql';
 
CREATE TRIGGER updateStatusBooksTrigger
               INSTEAD OF DELETE ON PromotionSummary
               FOR EACH ROW
               EXECUTE PROCEDURE updateStatusBooks();
              
DELETE FROM PromotionSummary
               WHERE category = 'Dictionary';
 
SELECT * FROM Books;
 
SELECT * FROM PromotionSummary;
 
=====================================================================================
CREATE TABLE Votes (
               cityname TEXT PRIMARY KEY,
               votecount INT
);
 
INSERT INTO Votes (cityname, votecount) VALUES ('Mars_City_One', 34);
INSERT INTO Votes (cityname, votecount) VALUES ('New_Gothenburg', 11);
INSERT INTO Votes (cityname, votecount) VALUES ('Picardia', 1);
UPDATE Votes SET votecount=votecount+3 WHERE cityname='New_Gothenburg';
 
SELECT * FROM Votes;
 
CREATE VIEW VoteSummary AS
               SELECT cityname, 100*votecount/(SELECT SUM(votecount) FROM Votes) AS percentage
               FROM Votes
               ORDER BY percentage DESC;
 
SELECT * FROM VoteSummary;
 
CREATE VIEW VoteSummary1 AS
               SELECT cityname, 100.0*votecount/(SELECT SUM(votecount) FROM Votes) AS percentage
               FROM Votes
               ORDER BY percentage DESC;
 
SELECT * FROM VoteSummary1;
 
CREATE OR REPLACE FUNCTION updateNotVoted() RETURNS TRIGGER AS $$
BEGIN
               IF NOT EXISTS (SELECT * FROM Votes WHERE cityname = '<not voted>')
               THEN
                              INSERT INTO Votes (cityname, votecount) VALUES ('<not voted>', 0);
               END IF;
              
               UPDATE Votes SET votecount = 1337-(SELECT SUM(votecount) FROM Votes WHERE cityname <> '<not voted>') WHERE cityname = '<not voted>';
               RETURN NULL;
END
$$ LANGUAGE 'plpgsql';
 
CREATE TRIGGER updateNotVotedTrigger
               AFTER INSERT OR UPDATE ON Votes
               FOR EACH ROW WHEN (NEW.cityname <> '<not voted>')
               EXECUTE PROCEDURE updateNotVoted();
 
UPDATE Votes SET votecount=votecount+2 WHERE cityname='Picardia';
 
SELECT * FROM Votes;
 
SELECT * FROM VoteSummary1;
 
SELECT * FROM VoteSummary;