-- Maman 02
-- Q1
create table if not exists users (
uid integer not null,
name varchar (30) not null,
email varchar (50) not null,
password varchar (50) not null,
descr varchar (255) not null,
country varchar (50)not null,
primary key (uid)
);
create table if not exists post (
pid integer not null,
uid integer not null,
content varchar (255) not null,
imageURL varchar (255) not null,
pdate date not null,
ptime time not null,
primary key (pid),
foreign key (uid) references users(uid)
);
create table if not exists comment (
pid integer not null,
cdate date not null,
ctime time not null,
uid integer not null,
content varchar (255) not null,
primary key (pid,cdate, ctime),
foreign key (pid) references post(pid)
);
create table if not exists likes (
uid integer not null,
pid integer not null,
ldate date not null,
ltime time not null,
primary key (uid, pid),
foreign key (pid) references post (pid),
foreign key (uid) references users(uid)
);
create table if not exists follow (
fuid integer not null,
uid integer not null,
primary key (fuid,uid),
foreign key (uid) references users(uid)
);
--Q2
create or replace function trigf1() returns trigger as
$$
declare
post_date date;
post_time time;
begin
select pdate, ptime into post_date, post_time /* שמירה של המשתנים בפונקציה לצורך השוואה*/
from post
where pid = new.pid;
if not found then /* סעיף 5.2 בספר, בדיקה אם השאילתא מצאה שורה*/
raise exception ' פוסט עם PID לא קיים';
end if;
if (new.cdate< post_date) or (new.cdate = post_date and new.ctime< post_time)then
raise exception 'לא ניתן להגיב על פוסט שטרם פורסם';
end if;
return new;
end;
$$
LANGUAGE plpgsql;
create trigger checkcommentdate
before insert
on comment
for each row
execute procedure trigf1();
-- Q3
insert into users (uid, name, email, password, descr, country) values
(1, 'alice', 'alice@example.com', 'pass1', 'photographer', 'israel'),
(2, 'bob','bob@example.com', 'pass2','traveler', 'usa'),
(3, 'carol', 'carol@example.com', 'pass3', 'chef', 'italy'),
(4, 'david', 'david@example.com', 'pass4', 'musician', 'israel'),
(5, 'eve', 'eve@example.com', 'pass5', 'techie', 'canada'),
(6, 'frank', 'frank@example.com', 'pass6', 'gamer', 'japan'),
(7, 'grace', 'grace@example.com', 'pass7', 'reader', 'uk'),
(8, 'hank', 'hank@example.com', 'pass8', 'blogger', 'france'),
(9, 'tamar', 'tam@example.com', 'pass9', 'volley', 'israel');
insert into post (pid, uid, content, imageURL, pdate, ptime) values
(101, 1, 'sunset in tel aviv', 'sun.jpg', '2025-05-05', '18:30'),
(102, 2, 'hiking the rockies', 'rockies.jpg', '2025-04-20', '10:00'),
(103, 3, 'best pasta recipe', 'pasta.jpg', '2025-04-22', '12:15'),
(104, 4, 'new song release', 'song.jpg', '2025-05-01', '15:45'),
(105, 1, 'morning coffee', 'coffee.jpg', '2025-03-15', '08:20'),
(106, 5, 'tech trends 2025', 'tech.jpg', '2025-05-03', '09:00'),
(107, 6, 'gaming marathon', 'game.jpg', '2025-04-25', '21:40'),
(108, 2, 'cherry blossoms', 'sakura.jpg', '2025-05-04', '07:50'),
(109, 3, 'street food adventures', 'street.jpg', '2025-05-06', '11:00'),
(110, 4, 'guitar tutorial', 'guitar.jpg', '2025-02-18', '17:10'),
(111, 3, 'city tour', 'oldcity.jpg', '2025-03-01', '10:00'),
(116, 4, 'new song', 'song.bb', '2025-08-01', '15:25');
insert into comment (pid, cdate, ctime, uid, content) values
(101, '2025-05-05', '19:00', 2, 'beautiful!'),
(101, '2025-06-05', '19:05', 3, 'love the colors.'),
(102, '2025-04-21', '14:00', 1, 'awesome hike!'),
(102, '2025-04-22', '16:00', 8, 'nice view.'),
(103, '2025-04-22', '13:00', 5, 'yummy!'),
(104, '2025-05-02', '10:15', 6, 'great beat!'),
(106, '2025-05-03', '11:30', 2, 'interesting insights.'),
(107, '2025-04-26', '22:00', 4, 'good luck!'),
(108, '2025-05-04', '08:10', 3, 'so pretty!'),
(109, '2025-05-06', '12:10', 5, 'delicious.'),
(110, '2025-02-19', '18:00', 1, 'helpful tutorial.'),
(102, '2025-04-22', '16:25', 9, 'nice!.'),
(102, '2025-04-22', '16:15', 5, 'cool!'),
(102, '2025-04-22', '17:15', 6, 'cool!!'),
(102, '2025-04-22', '22:15', 3, 'wow');
insert into likes (uid, pid, ldate, ltime) values
(1, 101, '2025-05-05', '21:00:00'),
(1, 102, '2025-03-21', '14:05:00'),
(1, 103, '2025-04-22', '14:10:00'),
(1, 104, '2025-05-02', '11:00:00'),
(1, 105, '2025-03-15', '09:05:00'),
(2, 111, '2025-03-02', '11:00:00'),
(2, 101, '2025-05-05', '18:50:00'),
(2, 103, '2025-04-22', '12:30:00'),
(2, 105, '2025-03-15', '09:00:00');
insert into follow (fuid, uid) values
(2, 1),
(3, 1),
(4, 1),
(6, 1),
(1, 2),
(1, 3),
(2, 6),
(7, 1),
(7, 9),
(7, 4),
(4, 8),
(4, 6);
-- Q4
select uid, pid, content
from post
where date_part ('month', pdate)=5;
--Q5
select uid, name, country
from users natural join post
where content like '%city %'
--Q6
select post.pid, post.content
from post
join users ON post.uid = users.uid
where users.country = 'usa'
and post.pid in (
select pid
from comment
group by comment.pid , comment.cdate
having count(*) >= 4)
-- Q7
select likes.uid
from users
join likes on users.uid = likes.uid
join post on likes.pid = post.pid
where users.uid != post.uid and not exists (
select *
from follow
where follow.fuid =users.uid and follow.uid = post.uid)
--Q8
select users.uid, name
from users
where not exists (
select *
from comment
where comment.uid = users.uid) and (
select count (*)
from follow
join users u2 on follow.uid = u2.uid
where follow.uid = users.uid and u2.country = 'israel')>=3
--Q9
select *
from users
join (
select follow.fuid, count(*) as num_following
from follow
group by follow.fuid)
as follow_count on user.uid = follow_count.fuid
where users.country = 'israel' and (
select count(*)
from post
where post.uid =users.uid)>= 3 and follow_count.num_following = (
SELECT u.*
FROM users u
JOIN (
SELECT fuid, COUNT(*) AS num_following
FROM follow
GROUP BY fuid
) fc ON u.uid = fc.fuid
WHERE u.country = 'israel'
AND (
SELECT COUNT(*) FROM post p WHERE p.uid = u.uid
) >= 3
AND fc.num_following = (
SELECT MAX(cnt)
FROM (
SELECT COUNT(*) AS cnt
FROM follow f
JOIN users u2 ON f.fuid = u2.uid
WHERE u2.country = 'israel'
AND (
SELECT COUNT(*) FROM post p2 WHERE p2.uid = u2.uid
) >= 3
GROUP BY f.fuid
) sub
);
Telegram: DB developers channel