Beers(name, manf): stores information about beers, including the manufacturer of

Question

Beers(name, manf): stores information about beers, including the manufacturer of each beer.

Bars(name, city, addr, license, phone): stores information about bars including their city, street address, phone number and their operating license.

Drinkers(name, city, addr, phone): stores information about drinkers, including their city, street address and phone number.

Likes (drinker, beer): indicates which drinker likes which beers (note that a drinker may like many beers and many drinkers may like the same beer).

Sells (bar, beer, price): indicates the price of each beer sold at each bar (note that each bar can sell many beers and many bars can sell the same beer, at possibly different prices).

Frequents (drinker, bar): indicates which drinker frequents which bars (note that each drinker may frequent many bars and many drinkers may frequent the same bar).

What is the average price of 'Budweiser' in 'Chicago'?

How many bars in 'Chicago' sell a beer liked by 'John' for less than 5.00?

Find the names of the bars in 'Boston' that sell 'Budweiser' for the cheapest price (compared with other bars in 'Boston').

Explanation / Answer

Let me know if you have any doubt.

#1
select avg(S.price)
from Bars as B join Sells as S
on B.name=S.bar
where B.name='Budweiser' and B.city='Chicago';

#2
select count(S.bar)
from Sells as S join Bars as B
on S.bar=B.name join Likes as L
on B.name=L.beer
where L.drinker='John' and B.city='Chicago' and S.price <5.00;

#3
select S.bar
from Sells as S join Bars as B
on S.bar=B.name
where B.name='Budweiser' and B.city='Boston'
and S.price= ( select min(S.price) from Sells);

Related Questions

Navigate

• Browse (All)
• Browse B
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.