Question

In: Computer Science

SQL tables(like pinterest) should have those tables user board pictures, likes follow pins comments Please write...

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.)

Solutions

Expert Solution

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.


Related Solutions

SQL- Trigger I have two tables (below) I need to write a trigger that would delete...
SQL- Trigger I have two tables (below) I need to write a trigger that would delete everything for a pid from the Appt table if the pid is deleted from the patient table. Create table Appt(                 pid numeric Foreign Key references patient(pid),                 ptname varchar(50) Foreign Key references patient(name),                 dob date Foreign Key references patient(dob),                 dr varchar(20),                 appdate date,                 apptime time, ); and Create table Patient(                 pid numeric primary key,                 name varchar(50),                ...
Write a C++ program performing the rot13 cipher, The code should perform like this: The user...
Write a C++ program performing the rot13 cipher, The code should perform like this: The user should be able to input any letter or words, or even sentences where once they have inputted the particular word, each letter goes 13 letters ahead, so an 'A' becomes an 'N', a 'C' becomes 'P', and so on. If rot13 cipher is tested a second time, the original plantext should be restored: 'P' becomes 'C', 'N' becomes 'A'. The 13 letters go in...
Write an SQL command that creates a SHOP table. The table should have a shop_id, a shop_name, a shop_location
Write an SQL command that creates a SHOP table. The table should have a shop_id, a shop_name, a shop_location
Please answer these question briefly. Dont have to write a lot. like 2-3 sentences or less...
Please answer these question briefly. Dont have to write a lot. like 2-3 sentences or less as long as get answer the questions. Please answer all thanks 1) Aspects of protein translational regulation that are key for protein folding 2) Embryonic stem cells      a)Derivation (from fertilized eggs that would be thrown away normally) 3) What is “Pluripotent” and what are some main criteria for pluripotency 4) What are applications of pluripotent cells? 5) What are some problems with pluripotent cells?
Write 5 questions about Object Oriented Programming in Python. Each question should have 7 options. Please...
Write 5 questions about Object Oriented Programming in Python. Each question should have 7 options. Please provide 7 answer options for EACH question and the select answer for EACH question
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT