PART A (Writing some of the queries for HW 2 in SQL) Consider a database with th

Question

 PART A  (Writing some of the queries for HW 2 in SQL)  Consider a database with the following schema.          LIKES(drinker,beer);  /* primary key(drinker,beer) */         FREQUENTS(drinker,pub); /* primary key(drinker,pub) */         SERVES(pub,beer,cost); /* primary key(pub,beer) */  Write the following queries in standard SQL, but without using aggregate operators.    1.   Find pubs that serve some beer that Joe likes. 2.   Find drinkers who frequent pubs where they can get a drink for less         than $3 3.   Find drinkers who like at least one expensive (over $8) beer that Joe likes.  4.   Find drinkers who like some beers but do not frequent any pubs. 5.   Find drinkers who frequent pubs that serve either ’Stella Artois’ or ’Molsons’. 6.*  Find pubs that serve every beer that Joe likes. 

Explanation / Answer

1.   

Select pub from SERVES inner join LIKES on SERVES.beer = LIKES.beer where drinker = 'Joe';
2.   

Select drinker from LIKES inner join FREQUENTS on LIKES.drinker = FREQUENTS.drinker inner join SERVES on FREQUENTS.pub = SERVES.pub where cost < 3;
3.   

Select drinker from LIKES inner join SERVES on LIKES.beer = SERVES.beer where cost > 8 ;
4.   

Select drinker from LIKES , SERVES where LIKES.beer != SERVES.beer;
5.   

Select drinker from LIKES inner join FREQUENTS on LIKES.drinker = FREQUENTS.drinker inner join SERVES on SERVES.pub = FREQUENTS.pub where SERVES.beer = 'Stella Artois' or SERVES.beer = 'Molsons';
6.

Select pub from SERVES where beer = ALL (Select beer from LIKES where drinker = 'Joe');

Do ask if there is any doubt

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects

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