Consider the following database consisting of six relations: Beers(name, manf):

Question

Consider the following database consisting of six relations:

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).

Attribute types: All attributes are of string type (e.g., Drinkers.name can be 'John McDonald'), except Sells.price is a number (e.g., Sells.price can be 2.25).

Write SQL queries to answer the following:

Find all bars frequented by both 'Vince' and 'Herb'.

this is what I have:

select distinct bar

from Frequents

where (Frequents.drinker = 'Vince' or Frequents.drinker = 'Herb');

Example Database:

Table: Frequents

Result of your SQL statement:

Correct Result:

drinker bar Bob Coconut Willie's Cocktail Lounge Erik Blue Angel Herb Seven Bamboo Jesse Blue Angel John Caravan Justin Gecko Grill Mike A.P. Stump's Mike The Shark and Rose Rebecca Coconut Willie's Cocktail Lounge Rebecca Gecko Grill Tom Blue Angel Tom Caravan Vince Blue Angel Vince Seven Bamboo

Explanation / Answer

1. Find the names of all beers, and their prices, served by the bar 'Blue Angel'.

select Beers.name, Sells.price

from Beers

inner join Sells

where Sells.bar = 'Blue Angel';

2. Find the name and phone number of every drinker who likes the beer 'Budweiser'.

select Drinkers.name, Drinkers.phone

from Drinkers

inner join Likes beer = 'Budweiser';

3. Find all bars frequented by both 'Vince' and 'Herb'.

select bar

from Frequents freq1, Frequents freq2

where drinker = 'Vince' and drinker = 'Herb';

4. Find all bars in 'Chicago' (and display all attributes) for which we know either the address (i.e., addr in our schema) or the phone number but not both.

select * from Bars

where ((addr is NULL and phone is NOT NULL) or (addr if NOT NULL and phone is NULL)) and

city = 'Chicago';

4. Find all bars in 'Chicago' (and display all attributes) for which we know either the address (i.e., addr in our schema) or the phone number but not both.

select * from Bars

where ((addr is NULL and phone is NOT NULL) or (addr if NOT NULL and phone is NULL)) and

city = 'Chicago';

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

---

---

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