This preview shows page 1. Sign up to view the full content.
Unformatted text preview: nk of the EMPLOYEE table as two tables, one for employees and one for
Fname Minit Lname Ssn Bdate Address Sex Salary Super_ssn Dno Minit Lname Ssn Bdate Address Sex Salary Super_ssn Dno M
Fname 5 SELECT E.Lname, E.Salary, M.Lname, M.Salary
FROM EMPLOYEE E, EMPLOYEE M
WHERE E.Super_ssn = M.Ssn and E.Salary > M.Salary; AGGREGATE FUNCTIONS Used to accumulate information from multiple tuples, forming a singletuple summary Built-in aggregate functions
• COUNT, SUM, MAX, MIN, and AVG Used in the SELECT clause
How many movies were directed by Steven Spielberg?
WHERE director='Steven Spielberg‘;7
• All tuples in result are counted, with duplicates!
• COUNT(title) or COUNT(director) give same result!
• COUNT(DISTINCT year) would include each year only once!
What was the total movie profit since 2010, across how many directors?
SELECT SUM(gross - budget), COUNT(DISTINCT director)
WHERE year >= 2010; 6 Examples: GROUPING BEFORE AGGREGATION How can we answer a query such as
“How many films were directed by each director after 2001?”
• Need to...
View Full Document
This document was uploaded on 02/07/2014.
- Spring '14