Suppose we have a database consisting of the following three relations.
(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.
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);
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));
Will my query work:
select distinct F.student from Frequent F, Serves S where F.parlour=S.parlour and S.ice-cream=(Select ice-cream from Likes L, Frequents F where F.student=L.student);
SELECT DISTINCT F.STUDENT FROM FREQUENTS F WHERE F.PARLOUR IN (SELECT PARLOUR FROM SERVES JOIN LIKES)
Is this correct??
@Gupta731 no it does not work because you are applying where condition incorrectly.
S.ice-cream=(Select ice-cream from Likes L, Frequents F where F.student=L.student);
S.ice-cream=(Select ice-cream
from Likes L, Frequents F
where F.student=L.student);
the inner query return set of records and you are comparing with single record with ‘=’ that would not possible, it is possible with function IN/NOT IN.
select distinct student from FREQUENTS as f natural join SERVES as s where icecream in (select icecream from LIKES as l where f.student = l.student)
SELECT DISTINCT STUDENT FROM FREQUENT NATURAL JOIN SERVES NATURAL JOIN LIKES ;
64.3k questions
77.9k answers
244k comments
80.0k users