C20'0046: Database Management Systems Lecture

1
C20.0046 Database Management SystemsLecture 13

• M.P. Johnson
• Stern School of Business, NYU
• Spring, 2005

2
Agenda

• Review GA
• RegExps
• Live examples
• HW2 due next Tuesday
• MT next Thursday

3
General form of GA

• NB Any attribute of relations in the FROM
  clause may be aggregated in the HAVING clause,
  but only those attributes that are in the GROUP
  BY list may appear unaggregated in the HAVING
  clause (the same rule as for the SELECT clause)
  (Ullman, p283).

SELECT S FROM R1,,Rn WHERE C1 GROUP
BY As HAVING C2
Why?

• S may contain attributes As and/or any
  aggregates but no other attributes
• C1 condition on the attributes in R1,,Rn
• C2 condition on aggregations or attributes from
  As

Why?
4
Evaluation of GA
SELECT S FROM R1,,Rn WHERE C1 GROUP
BY a1,,ak HAVING C2

• Evaluation steps
• Compute the FROM-WHERE part as usual to obtain a
  table with all attributes in R1,,Rn
• Group by the attributes a1,,ak
• Compute the aggregates in C2 and keep only groups
  satisfying C2
• Compute aggregates in S and return the result

5
Summary SQL queries

• Only SELECT, FROM required
• Cant have HAVING without GROUP BY
• Can have GROUP BY without HAVING
• Any clauses used must appear in this order

SELECT LFROM Rs WHERE s GROUP
BY L2 HAVING s2 ORDER BY L3
6
Web page examples

• Find all authors who wrote at least 10 documents
• Authors(login, name)
• Webpages(url, title, login)
• Attempt 1 with nested queries

Bad!
SELECT DISTINCT name FROM Authors WHERE
(SELECT count(url) FROM Webpages
WHERE Authors.loginWebpages.login)
gt 10
7
Web page examples

• Find all authors who wrote at least 10 documents
• Attempt 2 Simplify with GROUP BY

Good!
SELECT name FROM Authors, Webpages WHERE
Authors.login Webpages.login GROUP BY
name HAVING count(Webpages.url) gt 10
No need for DISTINCT - get for free from GROUP BY
8
Web page examples

• Find all authors who have a vocab gt 10000 words
• Authors(login, name)
• Webpages(url, title, login)
• Mentions(url, word)

SELECT name FROM Authors, Webpages,
Mentions WHERE Authors.loginWrote.login AND
Webpages.urlMentions.url GROUP BY
name HAVING count(distinct word) gt 10000
9
Live examples

• Inner joins require an ON clause
• Like a where clause
• Arbitrary boolean expression
• If always true (11), reduces to cross join
• New compar op BETWEEN
• a between 5 and 10 ? a gt 5 and a lt 10
• Q produce a list of employees with their salary
  grades
• emp, salgrade

10
Live examples

• Q produce list of bosses and underling-counts,
  for bosses with gt1 underling
• Ignoring non-bosses
• Including them
• Ignoring them again

11
sqlzoo exercises

• Select 2a http//sqlzoo.net/1.htm
• Subquery 1d with a subquery http//sqlzoo.net/1a.
  htm
• Subquery 1d with a join/cross product
  http//sqlzoo.net/1a.htm

12
Finally R.A./SQL has limitations

• Can easily find Alices direct subordinates
• But find all of Kings underlings
• Cannot compute transitive closure
• Cannot express in R.A./SQL!
• SQL is not Turing-Complete

13
Live examples

• Examples from sqlzoo.net

SELECT L FROM R1, , Rn WHERE C
PL(sC(R1 x Rn)