7wFR0aBt

1DROP TABLE IF EXISTS Comments;
2DROP TABLE IF EXISTS Votes;
3DROP TABLE IF EXISTS Post;
4DROP TABLE IF EXISTS "User";
5DROP TRIGGER if exists user_increment on "User";
6
7
8CREATE TABLE "User" (
9    User_ID integer,
10    Nickname varchar(20) NOT NULL,
11    PRIMARY KEY(User_ID)
12);
13
14CREATE TABLE Post (
15    Post_ID integer GENERATED ALWAYS AS identity,
16    User_ID integer,
17    Bookmarks integer NOT NULL,
18    Title varchar(100) NOT NULL,
19    PRIMARY KEY(Post_ID),
20    foreign key (User_ID) references "User"(User_ID)
21);
22CREATE TABLE Comments (
23    Comment_ID integer GENERATED ALWAYS AS identity,
24    User_ID integer,
25    Post_ID integer,
26    Writing_Date date NOT NULL,
27    Votes integer NOT NULL,
28    Text varchar(255) NOT NULL,
29    PRIMARY KEY(Comment_ID),
30    foreign key (Post_ID) references Post(Post_ID),
31    foreign key (User_ID) references "User"(User_ID)
32);
33CREATE TABLE Votes (
34    Votes_ID integer GENERATED ALWAYS AS identity,
35    User_ID integer,
36    Post_ID integer,
37    Amount integer NOT NULL ,
38    PRIMARY KEY(Votes_ID),
39    foreign key (User_ID) references "User"(User_ID),
40    foreign key (Post_ID) references Post(Post_ID)
41);
42
43CREATE OR REPLACE FUNCTION auto_increment()
44    RETURNS TRIGGER
45    LANGUAGE plpgsql
46AS $$
47    DECLARE
48        x int;
49    BEGIN
50        x = (SELECT COUNT(*)
51        FROM "User");
52        if x is null then
53            x = 1;
54            new.User_ID = 1;
55        else
56            new.User_ID = x+1 ;
57        end if;
58        RETURN new;
59    END;
60$$;
61CREATE TRIGGER user_increment
62    BEFORE INSERT ON "User"
63    FOR EACH ROW EXECUTE FUNCTION auto_increment();
64
65INSERT INTO "User"(Nickname) VALUES('Craboteam');
66INSERT INTO "User"(Nickname) VALUES('amidolteam');
67INSERT INTO "User"(Nickname) VALUES('Artem.Buts');
68INSERT INTO "User"(Nickname) VALUES('Artem.Petrenko');
69INSERT INTO "User"(Nickname) VALUES('Loloshka228');
70INSERT INTO Post(User_ID,Bookmarks, Title) VALUES(1,12,'Cannot simply use PostgreSQL table name (“relation does not exist”)');
71INSERT INTO Post(User_ID,Bookmarks, Title) VALUES(2,23,'Postgres: How to view contents of a table? [duplicate]');
72INSERT INTO Post(User_ID,Bookmarks, Title) VALUES(1,31,'How can I drop all the tables in a PostgreSQL database?');
73INSERT INTO Post(User_ID,Bookmarks, Title) VALUES(4,47,'How do you wipe a Postgresql database?');
74INSERT INTO Post(User_ID,Bookmarks, Title) VALUES(5,9281,'PostgreSQL: Show tables in PostgreSQL');
75INSERT INTO Comments(User_ID, Post_ID, Writing_Date, Votes, Text)  VALUES (1,1,'2021-03-21',221,'Cool table');
76INSERT INTO Comments(User_ID, Post_ID, Writing_Date, Votes, Text)  VALUES (2,1,'2021-05-6',11,'Lorem ipsum');
77INSERT INTO Comments(User_ID, Post_ID, Writing_Date, Votes, Text)  VALUES (3,2,'2021-04-6',16,'Dolor sir amet');
78INSERT INTO Comments(User_ID, Post_ID, Writing_Date, Votes, Text)  VALUES (2,2,'2021-04-2',7,'Lorem ipsum');
79INSERT INTO Comments(User_ID, Post_ID, Writing_Date, Votes, Text)  VALUES (2,4,'2021-04-1',9,'Lorem ipsum');
80INSERT INTO Comments(User_ID, Post_ID, Writing_Date, Votes, Text)  VALUES (2,1,'2021-04-5',1,'Test Comment');
81INSERT INTO Votes(User_ID, Post_ID, Amount) VALUES (1,1,10);
82INSERT INTO Votes(User_ID, Post_ID, Amount) VALUES (1,2,100);
83INSERT INTO Votes(User_ID, Post_ID, Amount) VALUES (1,3,123);
84INSERT INTO Votes(User_ID, Post_ID, Amount) VALUES (1,4,321);
85INSERT INTO Votes(User_ID, Post_ID, Amount) VALUES (1,5,28012);
86/*
87/*2 pohľady s jednoduchým netriviálnym selectom nad jednou tabuľkou*/
88--Vyberie všetko z tabuľky POST, kde je počet záložiek v rozmedzí [25; 1000]
89SELECT *
90FROM Post
91WHERE Bookmarks > 25 AND Bookmarks < 1000;
92--Vyberie všetko z tabuľky POST, kde User_ID rovna sa ID s nicknamemo'm Craboteam
93SELECT *
94FROM Post
95WHERE User_ID = (SELECT User_ID FROM "User" WHERE Nickname = 'Craboteam');
96
97/*3 pohľady so spájaním tabuliek (1x spojenie aspoň 2 tabuliek, 1x spojenie aspoň 3 tabuliek, 1x outer join)*/
98--Vyberie komentáre a nickname, ktoré boli napísané za posledných 5 dní
99SELECT  C.Text, C.Writing_Date, U.Nickname
100FROM Comments C
101INNER JOIN "User" U on U.User_ID = C.User_ID
102WHERE C.Writing_Date < date(now()) AND C.Writing_Date > date(now())-5
103ORDER BY C.Writing_Date DESC;
104--Vyberá komentáre, ID príspevku, kde v nicknam'e autora komentára je časť „team“
105SELECT post.Post_ID, C.Text, C.Votes, U.Nickname
106FROM Post
107INNER JOIN "User" U on U.User_ID = Post.User_ID
108INNER JOIN Comments C on Post.Post_ID = C.Post_ID
109WHERE U.Nickname like '%team%';
110--Vyberie komentáre a nickname,kde je User_ID v rozmedzí [2; 5]
111SELECT c.Text,c.Writing_Date,p.User_ID
112FROM Comments C
113LEFT OUTER JOIN Post P on C.Post_ID = P.Post_ID
114WHERE p.User_ID >2 AND p.User_ID <5;
115/*2 pohľady s použitím agregačných funkcií a/alebo zoskupenia*/
116--Zobrazí počet komentárov, ktoré používateľ zanechal
117SELECT c.User_ID, U.Nickname, COUNT(*) as comments_count
118FROM Comments c
119Inner Join "User" U on U.User_ID = c.User_ID
120GROUP BY c.User_ID, U.Nickname;
121--Priemerný počet Votes u commenatariev, ktoré mal commentar posledné dva dni
122SELECT avg(C.Votes)
123from Comments c
124WHERE C.Writing_Date < date(now()) AND C.Writing_Date > date(now())-2;
125*/
126SELECT * FROM Comments;
127SELECT * FROM Post;
128SELECT * FROM "User";
129SELECT * from Votes;
130
131--1 pohľad s použitím množinových operácií. Vyberie komentáre, ktoré boli napísané skôr ako dnes, s id používateľov viac ako 2
132SELECT c.User_ID, U.Nickname, c.Text, c.Writing_Date
133FROM Comments c
134INNER JOIN "User" U on U.User_ID = c.User_ID
135WHERE c.Writing_date < date(now())
136INTERSECT
137SELECT c.User_ID, U2.Nickname, c.Text, c.Writing_Date
138FROM Comments c
139INNER JOIN "User" U2 on U2.User_ID = c.User_ID
140WHERE c.User_ID > 2
141ORDER BY User_ID ASC;
142
143--1. pohľad s použitím vnorených poddopytov. Vyberte názvy príspevkov s počtom komentárov > 2
144SELECT p.Title, pocet_kommentov
145FROM (
146    SELECT COUNT(*) as pocet_kommentov,Post_ID
147    FROM Comments
148    GROUP BY Post_ID
149    ) c
150INNER JOIN Post p on p.Post_ID = c.Post_ID
151WHERE pocet_kommentov >= 2
152GROUP BY p.Title, pocet_kommentov;
153
154--1. pohľad s použitím vnorených poddopytov. Vyberie príspevky, ktorých autormi sú používatelia, ktorí majú časť 'team' vo svojem niku
155SELECT p.Post_ID, U.Nickname, p.Title, p.Bookmarks
156FROM Post p
157INNER JOIN "User" U on U.User_ID = P.User_ID
158WHERE p.User_ID IN (SELECT User_ID FROM "User" WHERE Nickname like '%team%');