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

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

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

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

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.

Explanation / Answer

1.

The SQL query to display the names of the beers and their prices which are sold by the bar “Blue Angel” can be shown as follows:

SELECT beer, price FROM Sells WHERE bar = ‘Blue Angel’

The table Sells stores the information of each beer, its price, and the name of the bar which sold that beer. The name of the beer and its price can be displayed by selecting the attributes beer and price of the table Sells.

The condition in the where clause will only select the beers sold by the “Blue Angel” bar.

2.

The SQL query to display the names and phone numbers of the drinker who likes the beer “Budweiser” can be shown as follows:

SELECT name, phone FROM Drinkers, Likes WHERE drinker = name AND beer = ‘Budweiser’

The table Drinker stores the information about a drinker which is as follows:

·       Drinker’s name

·       Drinker’s city

·       Drinker’s address

·       Drinker’s phone number

The table Likes stores the name of the drinkers and the beers liked by them. The name and phone number of a drinker can be selected from the table Drinker.

The condition in the where clause will select the names of the drinkers which is present in the table Likes and like the beer “Budweiser”.

3.

The SQL query to display the bars frequented by “Vince” and “Herb” can be shown as follows:

SELECT bar FROM Frequents freqDrinker1, Frequents freqDrinker2 WHERE freqDrinker1.bar = freqDrinker2.bar AND freqDrinker1.drinker = ‘Vince’ AND freqDrinker2.drinker = ‘Herb’

The table Frequents stores the information about the bars frequented by a drinker. The bar can be selected from the table Frequents.

The condition in the where clause will select the drinkers freqDrinker1 and freqDrinker2 who are “Vince” and “Herb” and checks whether the bars corresponding to these two drinkers are same.

4.

The query to display the bars in the “Chicago” whether the address of the bar is known or phone number of the bar is known can be shown as follows:

SELECT * FROM Bars WHERE city = ‘Chicago’ AND ((addr IS NOT NULL AND phone IS NULL) OR (addr IS NULL AND phone IS NOT NULL))

The table Bars stores the information of the bars. The attributes of the bars can be selected from the table Bars.

The condition in the where clause will select the bar which is in the city “Chicago” and checks if the value in the attribute addr is NULL, then the value in the attribute phone is NOT NULL.

If the value in the attribute addr is NOT NULL, then the value in the attribute phone is NULL.

The condition in the where clause ensures that the selected bar is in the city “Chicago” and if the corresponding address is known, then the phone number should not be known or if the corresponding phone number of the bar is known, then the address of the bar should not be known.

Related Questions

Navigate

• Browse (All)
• Browse B
• Subjects

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