Question

The following SQL DDL script creates a database for a social network application.

create table userProfile( id char(10)

primary key, firstName varchar(20),

lastName varchar(20),

dob date,

email varchar(30) );

create table foaf( userid char(10),

friendID char(10),

timeEstablished date, constraint pk primary key(userid, friendID),

constraint fk1 foreign key(userid) references userProfile(id),

constraint fk2 foreign key(friendID) references userProfile(id) );

create table activity( actID char(20)

primary key, topic varchar(20),

description varchar(100),

location varchar(20),

ActivityDate date, hostUser char(10),

foreign key(hostUser) references userProfile(id) );

create table activityParticipation( actID char(20),

userID char(10),

commitmentDate date, specialNote varchar(50),

primary key(actID,userID),

foreign key(actID) references activity(actID),

foreign key(userID) references userProfile(id) );

create table friendReferral( userID char(10),

friendID char(10),

referralFriendID char(10),

referralDate Date, primary key(userID,friendID,referralFriendID),

foreign key(userID) references userProfile(id),

foreign key(friendID) references userProfile(id),

foreign key(referralFriendID) references userProfile(id) );

create table post( postID char(50)

primary key, userID char(10),

postDate date, content varchar(150),

foreign key(userID) references userProfile(id) );

create table likePost( userID char(10),

postID char(50),

dateLiked date, primary key(userID,postID,dateLiked),

foreign key(userID) references userProfile(id),

foreign key(postID) references post(postID) );

Please write the following queries in SQL:

1. Find users’ full names who participate in an activity topic CIS9340 Exam 2 held on April 18, 2019 in New York City.

2. Find full names of users who have commitment to participate in activities that are located at different locations on the same date.

3. Find users’ full names who have at least 100 friends.

Answer 1

If you have any doubts, please give me comment...

-- 1

SELECT firstName, lastName

FROM userProfile U, activity A

WHERE U.id = A.hostUser AND topic = 'CIS9340 Exam 2' AND ActivityDate = '2019-04-18' AND location = 'New York';

-- 2

SELECT firstName, lastName

FROM userProfile U, activityParticipation AP, activity A, activityParticipation AP1, activity A1

WHERE U.id = AP.userID AND U.id = AP1.userID AND AP.actID = A.actID AND AP1.actID = A1.actID AND A.location <> A.location AND AP.commitmentDate == AP1.commitmentDate;

-- 3

SELECT firstName, lastName

FROM foaf

GROUP BY userid, firstName, lastName

HAVING COUNT(*)>=100;