4,865 views

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.

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.

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));
by

First one is simplest to write !
But Sir, how can u use correlation in 'IN' function? Only EXISTS and UNIQUE is used with correlation.
Where is that condition given?
In my notes...but I got to know it's wrong....thanks for correcting me :)
well explanation sir .
SELECT DISTINCT student FROM LIKES NATURAL JOIN SERVES NATURAL JOIN FREQUENTS;

@Arjun sir won't this query work?
Can someone please explain me how the 2nd query will work, I didn't understand its working correctly.

@Arjun

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);

For those who face a problem in understanding the 2nd solution, they should visit the correlated subquery concept first.

SELECT DISTINCT F.STUDENT FROM FREQUENTS F WHERE F.PARLOUR IN (SELECT PARLOUR FROM SERVES JOIN LIKES)

Is this correct??

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 ;

by
Query using EXIST

SELECT Student from FREQUENTS F where EXIST ( SELECT * from SERVES S where F.PARLOR = S.PARLOR AND EXIST (SELECT * FROM LIKES L where S.ICECREAM = L.ICECREAM AND F.STUDENT= L.STUDENT))