PLEASE DO ***NOT*** READ THESE SOLUTIONS BEFORE YOU MAKE A SERIOUS
ATTEMPT TO SOLVE THE QUESTIONS YOURSELF.

These problems are optional, and are made available at the request of
some students wha wanted to practice more SQL.  I do not plan to post
the solutions, or to discuss these problems in class or in the quiz
sections.  If you need help with any of them, please come to my office
hours, or make a separate appointment.

Schema:

Likes(drinker, beer)
Frequents(drinker, bar)
Serves(bar, beer)

AGGREGATE QUERIES

* for each bar, count the number of drinkers that frequent that bar
  and that like both 'heineken' and 'bud'


select x.bar, count(distinct x.drinker)
from frequents x, likes y, likes z
where x.drinker = y.drinker and x.drinker = z.drinker
  and y.beer = 'heineken' and z.beer = 'bud'
group by x.bar


* for each bar that serve both 'heineken' and 'bud' count the number
  of drinkers that frequent that bar

select x.bar, count(distinct x.drinker)
from frequents x, serves y, serves z
where x.bar = y.bar and x.bar = z.bar
  and y.beer = 'heineken' and z.beer = 'bud'
group by x.bar


* for each bar that serve 'heineken' count the number of drinkers that
  frequent that bar and that like 'bud'

select x.bar, count(distinct x.drinker)
from frequents x, serves y, likes z
where x.bar = y.bar and x.drinker = z.drinker
  and y.beer = 'heineken' and z.beer = 'bud'
group by x.bar


EXISTNENTIAL AND UNIVERSAL QUANTIFIER

* find all drinkers that frequent some bar that serves some beer that
  they like

select distinct x.drinker
from frequents x, serves y, likes z
where x.bar = y.bar and y.beer = z.beer and x.drinker = z.drinker


* find all drinkers that frequent only bars that serve some beer that
  they like

{d | forall bar. frequents(d,bar) => exists beer.serves(bar,beer) and
      likes(d,beer)}
=
{d | "d is a drinker"} -
{d | exists bar.frequents(d,bar) 
            and not (exists beer.(serves(bar,beer)  and likes(d,beer)))}


select x.drinker
from frequents x  -- any collection that returns all drinkers
where x.drinker not in
        (select y.drinker
         from frequents y
         where not exists (select * from serves z, likes u
                           where y.bar=z.bar and y.drinker=u.drinker
                             and z.beer = u.beer))


* find all drinkers that frequent some bar that serves only beer that
  they like

{d | exists bar.frequents(d,bar) and 
            forall beer.(serves(bar,beer)=>likes(d,beer))}
=
{d | exists bar.frequents(d,bar) and 
            not exists beer.(serves(bar,beer) and not likes(d,beer))}

select x.drinker
from frequents x
where not exists(select * from serves y
                 where x.bar = y.bar
                   and not exists (select * from likes z
                                   where z.drinker=x.drinker
                                     and y.beer = z.beer))


* find all drinkers that frequent some bar that serves some beer that
  they don't like

{d | exists bar.exists beer.frequents(d,bar) and serves(bar,beer) and not likes(d,beer)}

select x.drinker
from frequents x, serves y
where x.bar = y.bar
  and not exists (select * from likes z
                  where x.drinker=z.drinker and y.beer = z.beer)


* find all drinkers that ferquent some bar that serves only beer that
  they don't like


{d | exists bar.frequents(d,bar) 
            forall beer.(serves(bar,beer)=>not likes(d,beer))}

=

{d | exists bar.frequents(d,bar) 
            not exists beer. serves(bar,beer) and likes(d,beer)}

select x.drinker
from frequents x
where not exists (select * from serves y, likes z 
                  where x.bar=y.bar 
                    and y.beer = z.beer
                    and z.drinker = x.drinker)


* find all drinkers that frequent only bars that serve some beer that
  they don't like

{d | "d is a dinker" and
     forall bar.frequents(d,bar) ==>  
              exists beer. serves(bar,beer) and not likes(d,beer)}

=
{d | "d is a dinker" and
     not exists bar.( frequents(d,bar) 
                      and not exists beer. serves(bar,beer) and not likes(d,beer))}

select x.drinker
from frequents x  -- any relation that gives all drinkers
where not exists
        (select * from frequents y
         where x.drinker = y.drinker
          and not exists (select * from serves z
                          where z.bar = y.bar and
                            not exists (select * from likes u
                                        where u.drinker = y.drinker
                                        and u.beer = z.beer)))


* find all drinkers that frequent only bars that serve only beer that
  they don't like

{d | "d is a drinker" and
     forall bar.frequents(d,bar) ==> 
              forall beer. serves(bar,beer) ==> not likes(d,beer)}
=
{d | "d is a drinker" and
     not exists bar.(frequents(d,bar) and
          not(forall beer. serves(bar,beer) ==> not likes(d,beer)))}
=
{d | "d is a drinker" and
     not exists bar.(frequents(d,bar) and
          exists beer. serves(bar,beer) and likes(d,beer)))}

select x.drinker
from frequents x
where not exists (select * from frequents y, serves z, likes u
                  where x.drinker=y.drinker
                   and  y.bar = z.bar and z.beer = u.beer
                   and y.drinker = u.drinker)