1. For the running example Beers-Bars-Drinkers (with a modification on table Bee

Question

1. For the running example Beers-Bars-Drinkers (with a modification on table Beers to have
new schema Beers(name, manf, cost), please write a script file including SQL statements
to answer the following questions.
(To add an attribute to the running example beers table, you can issue the command “alter
table Beers add column cost real;” or you can re-create a new table.)
a. Show the beers, their names and their respective minimum prices from table Sells.
b. Show bars who sells Bud at cheapest price.
c. Show beers and their respective max prices for those beers which are sold in at least 3
bars. (That is, if a beer is sold in 3 or more bars, we want to find it’s name and max
price.)
d. Show the beer names and their respective minimum prices for those beers which are sold
in both Joe’s Bar and Sue’s Bar.
e. Find for those beers such that their costs are lower than their respective average sale
prices.

Our Running Example All our SQL queries will be based on the following database schema I 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

Creating the tables and Inserting the data.

Create table Beers(name varchar(100) NOT NULL, manf varchar(100), cost int);
INSERT INTO Beers VALUES(beername1, 2017, 100);
INSERT INTO Beers VALUES(beername2, 2018, 1200);
Select * from Beers;

CREATE TABLE Bars(name varchar(255) NOT NULL, addr varchar(255), license varchar(255));
INSERT INTO Bars VALUES('bar1','address1','licnese1');
INSERT INTO Bars VALUES('bar2','address2','licnese2');
SELECT * from Bars;

CREATE TABLE Drinkers(name varchar(255) NOT NULL,addr varchar(255), phone int);
INSERT INTO Drinkers VALUES('Drinkername1','drinkeraddress1',1234567893);
INSERT INTO Drinkers VALUES('Drinkername2','drinkeraddress2',1234567894);
SELECT * from Drinkers;

CREATE TABLE Likes(drinker varchar(255) NOT NULL,beer varchar(255) NOT NULL);
INSERT INTO Likes VALUES('Drinkername1','beername1');
INSERT INTO Likes VALUES('Drinkername2','beername2');
SELECT * from Likes;

CREATE TABLE Sells(bar varchar(255) NOT NULL,beer varchar(255) NOT NULL, price int);
INSERT INTO Sells VALUES('bar1','beername1', 100);
INSERT INTO Sells VALUES('bar2','bud', 1200);
INSERT INTO Sells VALUES('bar3','bud', 1100);
INSERT INTO Sells VALUES('Joe's Bar','beername2', 1100);
INSERT INTO Sells VALUES('Sue's Bar','beername3', 1100);
SELECT * from Sells;

CREATE TABLE Frequents(drinker varchar(255) NOT NULL,bar varchar(255) NOT NULL);
INSERT INTO Frequents VALUES('Drinkername1','bar1');
INSERT INTO Frequents VALUES('Drinkername2','bar2');
SELECT * from Frequents;

NOTE: It is assumed that name column from Beers table is same as of beer column in Sells table.

Queries as requested:
a. select beer, min(price) from Sells group by beer;
b. select bar from Sells where beer='bud' and price = (select min(price) from Sells where beer='bud');
c. select beer, max(price) from Sells where beer in (select beer from sells where count(1)>=3);
d. select beer, min(price) from Sells where bar in ('Joe’s Bar','Sue’s Bar') group by beer;
e. select beer from beers a
join (select beer, avg(price) as "price" from sells ) b
on a.beer=b.beer
where cost<price;

Related Questions

Navigate

• Browse (All)
• Browse 1
• Subjects

---

---

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