recategorized by
6,026 views
25 votes
25 votes

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 by

4 Answers

Best answer
49 votes
49 votes
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 by
1 votes
1 votes
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)

 

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

 

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

Related questions

26 votes
26 votes
3 answers
3
Kathleen asked Sep 26, 2014
5,177 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...