description: The sql schema we used here is a bar-beer-drinker relational schema. Bars can sell beers, which are from different manufactories have different types. Drinkers frequents bars and like beers.

1.List the number of beers that bars starting with B serve(all bars)
2.The set of bars and manf that have average price for the beers from the same manufacturer more than 5 bucks
3.Unusual beer (A beer that is sold in the least number of bars)
4.Bars that serve all beers
5.Bars that have a beer Mike likes that is cheaper than in any bars Mike frequents
6.Bars that have maximum variety
7.Unusual manf per bar (A manufacturer that the current bar has the least number of beers from)

Q&A

  1. The set of bars and manf that have average price for the beers from the same manufacturer more than 5 bucks

    1
    2
    3
    4
    5
    SELECT s.bar, b.manf, AVG(s.price) as avg_price
    FROM sells s
    JOIN beers b ON s.beer = b.name
    GROUP BY s.bar, b.manf
    HAVING avg_price > 5

    – Question: would the result set change if s.bar and b.manf switch places? No.

    1
    2
    3
    4
    5
    SELECT s.bar, b.manf, AVG(s.price) as avg_price
    FROM sells s
    JOIN beers b ON s.beer = b.name
    GROUP BY b.manf, s.bar
    HAVING avg_price > 5

    – Question: can we shift the condition to WHERE? No.
    – The search condition can use aggregate and nonaggregate expressions in having clause, but only nonaggregate expressions in where clause.

    1
    2
    3
    4
    SELECT s.bar, b.manf, AVG(s.price) as avg_price
    FROM sells s JOIN beers b ON s.beer = b.name
    WHERE avg_price > 5
    GROUP BY s.bar, b.manf

    – Question: which field(s) can be used in having clause?
    – Errors Maybe:

    1
    2
    3
    4
    SELECT s.bar FROM sells s
    JOIN beers b ON b.name = s.beer
    GROUP BY s.bar, b.manf
    HAVING s.beer = 'Budweiser'
    1
    2
    3
    4
    5
    SELECT b.name
    FROM sells s
    JOIN beers b ON b.name = s.beer
    GROUP BY s.bar, b.manf
    HAVING b.manf = 'Anheuser-Busch'
  2. List the number of beers that bars starting with B serve(all bars)

    1
    2
    3
    4
    SELECT count(*) as beerCount
    FROM( SELECT distinct sells.beer
    FROM sells
    WHERE (sells.bar>"B" or sells.bar="B") and sells.bar<"C") as distinct_beers

    Alternative

    1
    2
    3
    4
    SELECT count(*) as beerCount
    FROM( SELECT distinct sells.beer
    FROM sells
    WHERE substring(sells.bar,1,1)="B") as distinct_beers

    Alternative

    1
    2
    3
    4
    SELECT count(*) as beerCount
    FROM( SELECT distinct sells.beer
    FROM sells
    WHERE LEFT(sells.bar,1)="B") as distinct_beers

    Then if we want the beers number of every bar starting with “B”

    1
    2
    3
    4
    SELECT s.bar, COUNT(s.beer) 
    FROM sells s
    WHERE s.bar LIKE 'B%'
    GROUP BY s.bar

    – Question: Can we move the selection condition in the where clause to having clause? Yes.

    1
    2
    3
    4
    SELECT s.bar, COUNT(s.beer) 
    FROM sells s
    GROUP BY s.bar
    HAVING s.bar LIKE 'B%'
  3. Unusual beer (A beer that is sold in the least number of bars)

    1
    2
    3
    4
    5
    SELECT s.beer FROM sells s
    GROUP BY s.beer
    HAVING COUNT(s.beer) <= ALL(
    SELECT COUNT(s1.beer) FROM sells s1
    GROUP BY s1.beer)
  4. Bars that serve all beers

    1
    2
    3
    4
    5
    6
    7
    SELECT ba.name 
    FROM bars as ba
    WHERE NOT EXISTS( SELECT be.name
    FROM beers as be
    WHERE NOT EXISTS(SELECT ba.name,be.name
    FROM sells as s
    WHERE s.beer=be.name and s.bar=ba.name))

    OR

    1
    2
    3
    4
    5
    SELECT s.bar 
    FROM sells s
    WHERE (SELECT COUNT(s1.beer) FROM sells s1 WHERE s1.bar = s.bar)
    =
    (SELECT COUNT(*) FROM beers)

    OR

    – Do it with a GROUP BY

    1
    2
    3
    4
    SELECT s.bar, COUNT(s.beer)
    FROM sells s
    GROUP BY s.bar
    HAVING COUNT(s.beer) = (SELECT COUNT(*) FROM beers)
  5. Bars that have a beer Mike likes that is cheaper than in any bars Mike frequents

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    SELECT s.bar FROM sells s
    JOIN (SELECT s1.beer AS beer, MIN(s1.price) AS price
    FROM sells s1
    JOIN frequents f ON f.bar = s1.bar
    JOIN likes l ON l.drinker = f.drinker
    AND l.beer = s1.beer
    WHERE f.drinker = 'Mike'
    GROUP BY s1.beer) AS Existing
    ON s.beer = Existing.beer
    AND s.price < Existing.price
  6. Bars that have maximum variety

    1
    2
    3
    4
    5
    6
    SELECT s1.bar FROM sells s1
    GROUP BY s1.bar
    HAVING COUNT(s1.beer) >=
    ALL(SELECT COUNT(s.beer)
    FROM sells s
    GROUP BY s.bar)

    OR

    1
    2
    3
    4
    5
    6
    SELECT s1.bar FROM sells s1
    GROUP BY s1.bar
    HAVING COUNT(s1.beer) = (
    SELECT MAX(temp.cnt) FROM (SELECT s.bar, COUNT(s.beer) AS cnt
    FROM sells s
    GROUP BY s.bar) as temp)
  7. Unusual manf per bar (A manufacturer that the current bar has the least number of beers from)

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    SELECT s.bar, b.manf
    FROM sells s
    JOIN beers b ON s.beer = b.name
    GROUP BY s.bar, b.manf
    HAVING COUNT(s.beer) <= ALL(
    SELECT COUNT(s1.beer)
    FROM sells s1
    JOIN beers b1 ON s1.beer = b1.name
    GROUP BY s1.bar, b1.manf
    HAVING s1.bar = s.bar)

Comments

2015-09-19