GATE CSE 1998 | Question: 7-a

Question

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.

Comments

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

Answer 1

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

Comments

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

 

---

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

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.

---

Sir can't we directly join 3 tables and just put one condition that frequents.student = likes.student ?

---

@ash_khola after natural join u can directly select distinct students.. 

see this ans  https://gateoverflow.in/1721/gate-cse-1998-question-7-a?show=364808#a364808

---

Yes thats what i was looking for, thanks.

Answer 2

SELECT DISTINCT STUDENT
FROM FREQUENT NATURAL JOIN SERVES NATURAL JOIN LIKES ;

 

Answer 3

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)

 

Answer 4

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

Answer 5

My Idea behind this Query:

Select those Ice-Creams liked by the student which are served at Parlour frequently visited by  the student.

 

SELECT DISTINCT student

FROM FREQUENTS AS F

WHERE EXISTS ( SELECT ice-cream

                               FROM LIKES AS L

WHERE EXISTS ( SELECT parlor

FROM SERVES AS L

WHERE L.parlor = F.parlor AND L.ice-cream = S.ice-cream 

)

);

Answer 6

Select Distinct Student

From Frequent ⋈ Serves  ⋈  Like ;

OR

Select Distinct S.Student FROM 
    FREQUENTS P, SERVES Q, LIKES R
    WHERE
        P.Parlor=Q.Parlor 
        AND
        Q.ice-cream=R.ice-cream
        AND
        P.student=R.student;