Chapter four: simple beer database

Queries:

1. Give the names of all the people who visit to Cheers.
2. Give the name of all the people who love beer.
3. Give all the names of the bars that serve beer.
4. Give all the names of the people who like beer and visit cheers.
5. Give all the names of the people who visit bars that don't sell beer.
6. Give the name(s) of the bar(s) that Paul and Mary visit.
7. Give all the names of the people who like beer and that visit bars that sell beer.
8. Give the names of the bars that serve beers and the names of the people who like beer and visit that particular bar.
9. Give the names of the people who don't like beer and who don't visit California bar.

 

Database:

likes(drinker, beer)
visits(drinker, bar)
serves(bar, beer)

Examples of the tables:

Drinker	Beer		Drinker	Bar		Bar	Beer
Jan	Yes		Jan	Cheers		Cheers	Yes
Jaap	No		Jaap	California		California	No
Peter	Yes		Peter	Old dutch		Old dutch	Yes
Paul	Yes		Paul	Cheers
Mary	No		Mary	Cheers
Sue	Yes		Sue	Old dutch
Jolande	No		Jolande	California
Susan	Yes		Susan	Old dutch

Solutions:

1. Give the names of all the people who visit to Cheers.

 

SELECT drinker
FROM visits
WHERE bar="cheers";

2. Give the name of all the people who love beer.

 

SELECT drinker
FROM likes
WHERE beer="yes";

3. Give all the names of the bars that serve beer.

 

SELECT bar
FROM serves
WHERE beer="yes";

4. Give all the names of the people who like beer and visit cheers.

 

SELECT drinker
FROM likes AS l, visits AS v
WHERE beer="yes" AND l.drinker=v.drinker AND bar="cheers";

5. Give all the names of the people who visit bars that don't sell beer.

 

SELECT drinker
FROM visits AS v, serves AS s
WHERE s.beer="no" AND v.bar=s.bar;

6. Give the name(s) of the bar(s) that Paul and/or Mary visit.

 

SELECT bar
FROM visits AS v
WHERE v.drinker="paul" OR v.drinker="mary";

7. Give all the names of the people who like beer and that visit bars that sell beer.

 

SELECT drinker
FROM likes AS l, visits AS v, serves AS s
WHERE l.beer="yes" AND l.drinker=v.drinker AND s.beer="yes" AND v.bar=s.bar;

8. Give the names of the bars that serve beers and the names of the people who like beer and visit that particular bar.

 

SELECT bar, drinker
FROM likes AS l, visits AS v, serves AS s
WHERE l.drinker=v.drinker AND v.bar=s.bar AND l.beer="yes" AND s.beer="yes";

9. Give the names of the people who don't like beer and who don't visit California bar.

SELECT drinker
FROM likes AS l, visits AS v
WHERE l.drinker=v.drinker AND beer="no" AND drinker not in

(SELECT drinker

FROM visits

WHERE bar="california";