# GATE1998-7-a

3k 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.

recategorized
0
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.
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);

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

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

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

@Arjun
0

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

0

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

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

ago

## Related questions

1
2.7k views
Free disk space can be used to keep track of using a free list or a bit map. Disk addresses require $d$ bits. For a disk with $B$ blocks, $F$ of which are free, state the condition under which the free list uses less space than the bit map.
Which of the following query transformations (i.e., replacing the l.h.s. expression by the r.h.s expression) is incorrect? R1 and R2 are relations, C1 and C2 are selection conditions and A1 and A2 are attributes of R1. A. ... $\pi_{A_1} \left(\sigma_{C_1}\left(R_1\right)\right) \to \sigma_{C_1} \left(\pi_{A_1}\left(R_1\right)\right)$