Create Presentation
Download Presentation

Download Presentation
## Relational Algebra & SQL

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -

**Relational Algebra & SQL**Query Formulation Exercise**Exercise 4: Relational Algebra and SQL**Given relational schema: Frequent (D, P) Serves (P, B) Likes (D, B) Attributes: P (pub), B (beer), D (drinker) • The pubs that serve a beer that Jefferson likes. • Drinkers that frequent at least one pub that serves “Bud” or “Becks”. • Drinkers that frequent only pubs that serve some beer they like • Drinkers that frequent only pubs that serve no beer they like.**1) Algebra**• Solution 1: • Solution 2: • Solution 3: • Solution 4:**2) SQL**SELECT p FROM S, L WHERE S.b=L.b AND L.d=‘Jefferson’ • Solution 1: • Solution 2: SELECT d FROM F WHERE F.p in (SELECT p FROM S WHERE b IN (‘Bud”, ‘Becks’))**2) SQL**SELECT d FROM F EXCEPT SELECT d FROM (SELECT* FROM F EXCEPT SELECT d,p FROM S, L WHERE S.b = L.b) • Solution 3:**2) SQL**SELECT d FROM F EXCEPT SELECT d FROM (SELECT* FROM F INTERSECT SELECT d,p FROM S, L WHERE S.b = L.b) • Solution 4:**2) SQL**SELECT d FROM F WHERE (SELECT COUNT (DISTINCT p) FROM S, L WHERE F.p=S.p AND S.b=L.b AND L.d=F.d) = 0 • Solution 4 (alternative solution):