Think of the employee table as two tables one for

Info iconThis preview shows page 1. Sign up to view the full content.

View Full Document Right Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: nk of the EMPLOYEE table as two tables, one for employees and one for managers. E 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? SELECT COUNT(*) FROM Film 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) FROM Film 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.

Ask a homework question - tutors are online