What would be the query if the question was:

Print the students that frequent at least one parlor that serves all ice-creams that they like.

Print the students that frequent at least one parlor that serves all ice-creams that they like.

Dark Mode

23 votes

Suppose we have a database consisting of the following three relations.

- $\text{FREQUENTS (student, parlor)}$ giving the parlors each student visits.
- $\text{SERVES (parlor, ice-cream)}$ indicating what kind of ice-creams each parlor serves.
- $\text{LIKES (student, ice-cream)}$ indicating what ice-creams each student likes.

(Assume that each student likes at least one ice-cream and frequents at least one parlor)

Express the following in SQL:

Print the students that frequent at least one parlor that serves some ice-cream that they like.

0

This is my Attempt of your Query Pratik

Select Student

from frequents F

where NOT EXIST

( Select Student, Parlor, Ice-cream

from F NATURAL JOIN Likes L

EXCEPT

select Student, Parlor, Ice-cream

from Serves NATURAL JOIN L);

0

46 votes

Best answer

SELECT DISTINCT A.student FROM FREQUENTS A, SERVES B, LIKES C WHERE A.parlor=B.parlor AND B.ice-cream=C.ice-cream AND A.student=C.student;

OR

SELECT DISTINCT A.student FROM FREQUENTS A WHERE parlor IN (SELECT parlor FROM SERVES B WHERE B.ice-cream IN (SELECT ice-cream FROM LIKES C WHERE C.student = A.student));

0

1