using sql (select from where) to answer the question Find the drinkers who like
ID: 3870088 • Letter: U
Question
using sql (select from where) to answer the question
Find the drinkers who like either Bud or Miller, but not both.
Find the names and addresses of bars such that all of their beers are sold less than $ 3.0.
Find the bars such that all of their beers’ prices are lower than all the beers sold in Jos’s Bar. Do not use aggregate functions such as MIN, MAX.
Our Running Example All our SQL queries will be based on the following database schema O Underline indicates key attributes. Beers(name, manf) Bars(name, addr, license) Drinkers(name, addr, phone) Likes(drinker, beer) Sells(bar, beer, price) Frequents(drinker, bar)Explanation / Answer
Find the drinkers who like either Bud or Miller, but not both.
select d.name from drinkers d where
(select count(*) from likes l where l.drinker = d.name) = 1
Find the names and addresses of bars such that all of their beers are sold less than $ 3.0.
select b.name, b,addr from bars b where
(select count(*) sells s where s.bar = b.name and s.price >= 3) = 0
Find the bars such that all of their beers’ prices are lower than all the beers sold in Jos’s Bar. Do not use aggregate functions such as MIN, MAX.
select b.name, b,addr from bars b where
(select count(*) sells s where s.bar = b.name and s.price >=
(
SELECT s1.price FROM sells s1 where s1.bar='Jos' ORDER BY s1.price LIMIT 1
)
) = 0
Here "SELECT s1.price FROM sells s1 where s1.bar='Jos' ORDER BY s1.price LIMIT 1" finds the minimum price from Joe's bar. The we check if the current bar sells any item on more than this price, if yes, that doesn't become part of result set.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.