Lecture 08 - Lecture 08 Comp355 Introduction to SQL Part II...

Info iconThis preview shows pages 1–14. Sign up to view the full content.

View Full Document Right Arrow Icon
Lecture 08 Comp355
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Introduction to SQL Part II
Background image of page 2
SELECT Statement - Aggregates ISO standard defines five aggregate functions: COUNT returns number of values in specified column. SUM returns sum of values in specified column. AVG returns average of values in specified column. MIN returns smallest value in specified column. MAX returns largest value in specified column.
Background image of page 3

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
SELECT Statement - Aggregates Each operates on a single column of a table and returns a single value. COUNT, MIN, and MAX apply to numeric and non-numeric fields, but SUM and AVG may be used on numeric fields only. Apart from COUNT(*), each function eliminates nulls first and operates only on remaining non-null values.
Background image of page 4
SELECT Statement - Aggregates COUNT(*) counts all rows of a table, regardless of whether nulls or duplicate values occur. Can use DISTINCT before column name to eliminate duplicates. DISTINCT has no effect with MIN/MAX, but may have with SUM/AVG.
Background image of page 5

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
SELECT Statement - Aggregates Aggregate functions can be used only in SELECT list and in HAVING clause. If SELECT list includes an aggregate function and there is no GROUP BY clause, SELECT list cannot reference a column out with an aggregate function. For example, the following is illegal: SELECT staffNo, COUNT(salary) FROM Staff;
Background image of page 6
Example of Use of COUNT(*) How many properties cost more than £350 per month to rent? SELECT COUNT(*) AS count FROM PropertyForRent WHERE rent > 350;
Background image of page 7

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Example of Use of COUNT(DISTINCT) How many different properties viewed in May ‘01? SELECT COUNT(DISTINCT propertyNo) AS count FROM Viewing WHERE viewDate BETWEEN ‘1-May-01’ AND ‘31-May-01’;
Background image of page 8
Example of Use of COUNT and SUM Find number of Managers and sum of their salaries. SELECT COUNT(staffNo) AS count, SUM(salary) AS sum FROM Staff WHERE position = ‘Manager’;
Background image of page 9

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Example of Use of MIN, MAX, AVG Find minimum, maximum, and average staff salary. SELECT MIN(salary) AS min, MAX(salary) AS max, AVG(salary) AS avg FROM Staff;
Background image of page 10
SELECT Statement - Grouping Use GROUP BY clause to get sub-totals. SELECT and GROUP BY closely integrated: each item in SELECT list must be single- valued per group , and SELECT clause may only contain: column names aggregate functions constants expression involving combinations of the above.
Background image of page 11

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
SELECT Statement - Grouping All column names in SELECT list must appear in GROUP BY clause unless name is used only in an aggregate function. If WHERE is used with GROUP BY, WHERE is applied first, then groups are formed from remaining rows satisfying predicate. ISO considers two nulls to be equal for purposes of GROUP BY.
Background image of page 12
Find number of staff in each branch and their total salaries. SELECT branchNo,
Background image of page 13

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Image of page 14
This is the end of the preview. Sign up to access the rest of the document.

This note was uploaded on 04/07/2008 for the course COMP 355 taught by Professor Wu during the Spring '08 term at Wentworth Institute of Technology.

Page1 / 57

Lecture 08 - Lecture 08 Comp355 Introduction to SQL Part II...

This preview shows document pages 1 - 14. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online