search
Log In
21 votes
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.

in Databases
recategorized by
3k 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);

3 Answers

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

selected by
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. 

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

 

ago

Related questions

22 votes
3 answers
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.
asked Sep 26, 2014 in Operating System Kathleen 2.7k views
27 votes
10 answers
2
4k views
Consider the following relational database schemes: COURSES (Cno, Name) PRE_REQ(Cno, Pre_Cno) COMPLETED (Student_no, Cno) COURSES gives the number and name of all the available courses. PRE_REQ gives the information about which courses are pre-requisites for a ... following using relational algebra: List all the courses for which a student with Student_no 2310 has completed all the pre-requisites.
asked Sep 26, 2014 in Databases Kathleen 4k views
45 votes
5 answers
3
10.6k views
Consider the following database relations containing the attributes Book_id Subject_Category_of_book Name_of_Author Nationality_of_Author With Book_id as the primary key. What is the highest normal form satisfied by this relation? Suppose the attributes Book_title and Author_address are ... is changed to {Name_of_Author, Book_title}, what will be the highest normal form satisfied by the relation?
asked Sep 26, 2014 in Databases Kathleen 10.6k views
67 votes
6 answers
4
9.8k views
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)$
asked Sep 26, 2014 in Databases Kathleen 9.8k views
...