Question

AdminDetails(adminid,fname,lname,email)

Manages(adminid,categoryid)

Category(categoryid,categoryname)

Comment(commentid,postid,userid,comment)

Post(postid,post,date,body,subject)

PostAdmin(adminid,userid,postid)

Posttag(postid,tagid)

Topic(topicid,categoryid,topicname)

User(userid,emailid,fname,lname,username,dob,age)

Userfeedback(feedbacked, userid, date)

PostbyUser(postid,userid)

Tags(tagid,tagname)

Polls(postid,pollid,polltype,pollname)

Feedbacks(feedbacked,rating,feedback)

Write any rough 10 sql queries (with the values in the tables imagined) which are a bit complex.

Answer 1

1. Print the Posts made on the Subject of Physics by school students between ages 15 to 20.

SELECT POST
   FROM POST
   WHERE SUBJECT= 'PHYSICS' AND POSTID IN
   (SELECT POSTID FROM POSTADMIN
       WHERE USERID IN
   (SELECT USERID FROM USER
   WHERE AGE BETWEEN 15 AND 20));

2. Print the names of the managers who manages the highest number of topics.

SELECT FNAME, LNAME
FROM ADMINDETAILS
WHERE ADMINID IN
(SELECT ADMINID
FROM MANAGES
GROUP BY ADMINID
WHERE COUNT(CATEGORYID) =
(SELECT MAX(CATEGORYID)
FROM MANAGES
GROUP BY ADMINID));

3. Print Poll names that has received the maximum negative feedback.

SELECT POLLNAME
FROM POLLS
WHERE POSTID IN
(SELECT POSTID FROM POSTBYUSER
WHERE USERID IN
(SELECT USERID FROM USERFEEDBACK
WHERE FEEDBACKED IN
(SELECT FEEDBACKED
FROM FEEDBACKS
WHERE FEEDBACK IN
(SELECT MIN(FEEDBACK)
FROM FEEDBACKS))));

4. Print the Comment made by Users from the Senior Citizen category.

SELECT COMMENT
FROM COMMENT
WHERE USERID IN
(SELECT USERID
FROM USER
WHERE AGE>=60);

5. Count the number of topics managed by Admin named 'Bradley Cooper'.

SELECT COUNT(TOPICID)
FROM TOPIC
WHERE CATEGORYID IN
(SELECT CATEGORYID IN
MANAGES WHERE ADMINID IN
(SELECT ADMINID FROM ADMINDETAILS
WHERE FNAME="Bradley" AND LNAME="Cooper"));

6. Count the Posts that have received ratings lower than 5.

SELECT COUNT(POSTID)
FROM POSTADMIN
WHERE USERID IN
(SELECT USERID IN USERFEEDBACK
WHERE FEEDBACKED IN
(SELECT FEEDBACKED IN FEEDBACKS
WHERE RATING<5));

7. Display the name of the admin who manages the number of categories of topics greater than 2.
SELECT FNAME, LNAME
FROM ADMINDETAILS
WHERE ADMINID IN
(SELECT ADMINID FROM MANAGES
GROUP BY ADMINID
WHERE COUNT(CATEGORYID) >2.

8. Display the number of topics under the name-'Water Cycle' falling under the category 'School Subjects'.
SELECT COUNT(TOPICID)
FROM TOPIC T, CATEGORY C
WHERE T. CATEGORYID= C.CATEGORYID AND TOPICNAME="WATER CYCLE" AND CATEGORYNAME="SCHOOL SUBJECTS";

9. Display the email ids of the admin whose first names start with A.
SELECT EMAIL
FROM ADMINDETAILS
WHERE FNAME LIKE 'A%';

10. Display the subject of the post tagged the maximum number of times.

SELECT SUBJECT
FROM POST
WHERE POSTID IN
(SELECT POSTID FROM POSTTAG
GROUP BY POSTID
WHERE COUNT(TAGID)=
(SELECT MAX(COUNT)
FROM(SELECT COUNT(TAGID)
FROM POSTTAG
GROUP BY POSTID)));