The Gateway to Computer Science Excellence
First time here? Checkout the FAQ!
x
+15 votes
1.1k 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.

asked in Databases by Veteran (59.7k points)
recategorized by | 1.1k views
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);

2 Answers

+28 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));
answered by Veteran (367k points)
selected by
+6
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 .
+4
SELECT DISTINCT student FROM LIKES NATURAL JOIN SERVES NATURAL JOIN FREQUENTS;

@Arjun sir won't this query work?
0 votes
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))
answered by Active (1.6k points)

Related questions



Quick search syntax
tags tag:apple
author user:martin
title title:apple
content content:apple
exclude -tag:apple
force match +apple
views views:100
score score:10
answers answers:2
is accepted isaccepted:true
is closed isclosed:true

44,073 questions
49,595 answers
162,959 comments
65,789 users