In: Computer Science
SQL tables(like pinterest)
should have those tables
user
board
pictures,
likes
follow
pins
comments
Please write SQL for those questions
1. two users are friends if each has liked at least three pictures from the other user’s boards. Output all pairs of friends.
2. For each picture, output the number of times it has been re-pinned.
3. names of all users who follow a board containing a picture with the description “cactus at sunset”.
IV. names of all users who posted more than ten comments in the last 24 hours. (You may use “currenttime()” to refer to the current time in your query.)
Assuming the functionalities required below design for database is feasible. All the queries made henceforth are based on this proposed structure.
1. SELECT A.name , B.name FROM user A, user B INNER JOIN likes ON B.userID=likes.userID INNER JOIN picture ON picture.picID=likes.picID WHERE picture.userID='A.name';
2.SELECT picture.picID, count(pins.picID) FROM picture INNER JOIN pins ON picture.picID=pins.picID;
3. SELECT name FROM user WHERE userID IN ( SELECT follow.userID FROM follow INNER JOIN board ON follow.boardID=board.boardID INNER JOIN picture ON picture.boardID=board.boardID WHERE picture.description='Cactus at sunset' );
4. SELECT name FROM user INNER JOIN comments ON user.userID=comments.userID WHERE comments.time >= getdate()-1;
alternatively you may use any of the following:
comments.time >= DATEADD(dd,-1,getdate()); //1 DAY PREVIOUS
comments.time >= DATEADD(hh,-24,getdate()); //24 HOUR PREVIOUS
If any doubt remains, mention them in the comments. If you are satisfied with the answer please leave a thumbs up, it really matters.
Thank You.