Suppose we have data table BARS(name,licese,city,phone,addr), BEERS(name,manf),
ID: 3751736 • Letter: S
Question
Suppose we have data table BARS(name,licese,city,phone,addr), BEERS(name,manf), DRINKERS(name, city, phone,addr), FREQUENTS(drinker, bar), LIKE(drinker, beer) and SELLS(bar,beer,price) using mySQL queries:
Queries [DO NOT USE AGGREGATES LIKE COUNT etc in any of the queries], feel free to use outer joins.
1)Drinkers who do not like any beer
2) Bars which serve no beer(s) which Joe likes (you can use one of the outer joins if you want)
3) Beers which are liked by at most one drinker who frequents Cabana
Comment: Beers which are liked by none of the drinkers who frequent Cabana should be included. This is the meaning of “at most”
4) Drinkers who frequent no bars that Tom frequents
5) Bars which serve all beers that Cabana serves and possibly more
6) Pairs of drinkers who frequent the same bar (and possibly other bars)
Comment: Order pairs so you do not return <Bob, Ann>, <Ann, Bob>
Explanation / Answer
Answer)
1) select name from DRINKERS where name not in (
select drinker from LIKE
);
2) select bar from SELLS where beer not in (
select beer from LIKE where drinker='Joe'
);
3) select beer from LIKE where drinker in (
select drinker from FREQUENTS where bar ='Cabana'
);
4) select drinker from FREQUENTS where bar not in (
select bar from FREQUENTS where drinker='Tom'
);
5) select bar from SELLS where beer in (
select beer from SELLS where bar = 'Cabana'
);