CODEWITHSUNDEEP
sql
daniel
daniel

Reputation: 587

SQL Query Problems (Correctness check)

Tables:

– Beers (name, manufacturer)
– Bars (name, address, license)
– Sells (bar,beer, price)
– Drinkers (name, address, phone)
– Likes (drinker, beer)
– Frequents (drinker, bar)
– Friends (drinker1, drinker2)

Tasks:

  1. Find all beers liked by two or more drinkers
  2. Find all beers liked by three or more drinkers
  3. Find all beers liked by friends of Anna
  4. Find all bars that sell a beer that is cheaper than all beers sold by the bar "99 Bottles"

Query #1:

SELECT x name  
FROM Beers x, Drinkers y, Likes s    
WHERE y.name=s.drinker and s.beer=x.name
GROUP BY x.name    
HAVING COUNT(s.drinker) >= 2;

Query #2:

Same as the previous

Query #3. 

SELECT x name
FROM Beers x, Friends y, Drinkers z, Likes p  
WHERE z.name = "Anna" 
  AND z.name = y.drinker1   
  AND y.drinker2 = p.drinker 
  AND p.beer = x.name;

Query #4:

SELECT x.name
FROM Bars x, Sells y
WHERE x.name = y.bar 
  AND SOME(SELECT y.price) < ALL(SELECT t.price
                                 FROM Sells t
                                 WHERE t.bar = "99 bottles");

I am a little bit hesitant about the last one. Am I allowed to use SOME in that way? Can I put create a sub-query by using outer variables in the select clause?

Upvotes: 0

Views: 394

Answers (1)

Serge
Serge

Reputation: 4036

3. Find all beers liked by friends of Anna:

There are 2 main issues with your answer:

  • The query may return the same beer name more than once in certain scenarios
  • You will miss some cases, because "Anna" may appear in either drinker1 or drinker2 column of the Friends table

Here'a sample solution using explicit JOIN's:

SELECT x.name
FROM Beers
WHERE b.name IN (
    SELECT l.beer
    FROM Likes l
        INNER JOIN Friends f
            ON l.drinker = f.drinker1
    WHERE f.drinker2 = "Anna"

    UNION

    SELECT l.beer
    FROM Likes l
        INNER JOIN Friends f
            ON l.drinker = f.drinker2
    WHERE f.drinker1 = "Anna"
)

4. Find all bars that sell a beer that is cheaper than all beers sold by the bar "99 Bottles":

You are using SOME and ALL incorrectly;

  • We never use these keywords on both sides of a comparison operator
  • The expression inside SOME or ALL must be a complete sub-query (SELECT y.price is not a valid expression)

Here'a sample solution using IN AND MIN:

SELECT b.name
FROM Bars b
WHERE b.name IN (

    SELECT s.bar
    FROM Sells s
    WHERE s.price <
    (
        SELECT MIN(price)
        FROM Sells
        WHERE bar = "99 bottles"
    )

)

Upvotes: 1

Related Questions