Question

mySQL database question.. I have a database that has the following tables:

User (Id, Name, Gender) Primary key = Id

Friends (Id1, Id2, Startdate) Primary key = (Id1, Id2) Foreign keys are also Id1, Id2 pointing to User(Id)

Comments (CommentId, Poster, Recipient, Text, PostDate) Primary key = (CommentId) Foreign Keys are Poster, Recipient pointing to User(Id)

I need to answer the following queries:

5. List Users who have posted comments to all female users

6. List User(s) who have received comments from the most number of users

When finding users, all I need to list is the Id of the user.

Answer 1

QUERY SOLVED USING SQL SERVER 2012

ANSWER 1 :

select * from [User] where
   id in
   (
       select poster from Comments
           where Recipient in
               (select id from [user] where Gender='Female')
           group by poster
           having count(*)=(select count(*) from [User] where Gender='Female')

   )

ANSWER 2 :

select * from [User] where
   id in
   (
       select top 1 Recipient from Comments group by Recipient order by count(*) desc       

   )

SCREENSHOT OF SAMPLE DATA

SCREENSHOT OF QUERY EXECUTION AND OUTPUT

ANSWER 1:

ANSWER 2: