Sample SQL queries demoed in the class

CREATE TABLE Beers(
	name CHAR(64) PRIMARY KEY, 
	manf CHAR(64))

CREATE TABLE Bars(
	name    CHAR(64) PRIMARY KEY,
	address CHAR(128),
	license CHAR(64))

CREATE TABLE Drinkers(
	name    CHAR(64) PRIMARY KEY,
	address CHAR(128),
	phone   CHAR(16))

CREATE TABLE Likes(
	drinker CHAR(64),
	beer	   CHAR(64), 
	PRIMARY KEY (drinker, beer), 
	FOREIGN KEY (drinker) REFERENCES Drinkers,
	FOREIGN KEY (beer) REFERENCES Beers)

CREATE TABLE Sells(
	bar    CHAR(64),
	beer	   CHAR(64), 
	price   REAL,
	PRIMARY KEY (bar, beer), 
	FOREIGN KEY (bar) REFERENCES Bars,
	FOREIGN KEY (beer) REFERENCES Beers)

CREATE TABLE Frequents(
	drinker CHAR(64),
	bar	CHAR(64), 
	PRIMARY KEY (drinker, bar), 
	FOREIGN KEY (drinker) REFERENCES Drinkers,
	FOREIGN KEY (bar) REFERENCES Bars)


1. select * from Beers where manf = 'busch'

2. select name as beer, manf as company from Beers

3. select beer, price*120 as Yen  from Sells where beer = 'bud light'

4.select * from drinkers where address like '%first%'

5. select * from drinkers where phone like '%-11__'

6. select * from sells where price < 3 or price >= 3

7. select * from sells where price < 3 or price >= 3 or price is null

8. select beer from Likes L, Frequents F where F.bar='joe bar' and F.drinker=L.drinker

9. select bar from sells where beer = 'bud' and price = (  select price from sells where bar = 'joe bar' and beer = 'bud')

10. select bar from sells where beer = 'bud light' and price < (select price from sells where bar = 'emily bar' and beer = 'bud light')

11. select bar from sells where beer = 'bud light' and price <= all (select price from sells where beer = 'bud light')

12. select bar from sells where beer = 'bud light' and price < any (select price from sells where beer = 'bud light')

13. select name, manf from beers b1 where not exists (select * from beers b2 where b1.manf=b2.manf and b1.name <> b2.name)

14. select price from sells where bar = 'joe bar' union select price from sells where bar = 'emily bar'

15. select price from sells where bar='joe bar' or bar='emily bar'

16. select beer, avg(price) from sells group by beer

17. select drinker, avg(price) from frequents f, sells s where f.bar=s.bar group by drinker

18. select beer, avg(price) from sells group by beer having count(price) >= 2