Lecture 8

Question 1

SELECT & aggregation

Answer 1

SELECT statement aggregates

• Count,
• sum,
• average,
• min & max

Question 2

aggregate functions

Answer 2

• aggregate functions perform a calculation on a set of values, and returns a single value
  
  • 4+2+3+1+7=16
• Each aggregate function operates on a single column of a table and returns a single value
• used with the GROUP BY clause of the SELECT statement
• except for COUNT, aggregate functions ignore null values

ISO standard defines five aggregate functions:

• COUNT
• SUM (numeric only)
• AVG (numeric only)
• MIN
• MAX

Question 3

COUNT(*)

Answer 3

COUNT(*) counts all rows of a table, regardless of whether nulls or duplicate values occur

Question 4

DISTINCT

Answer 4

use DISTINCT before column name to eliminate duplicates

Question 5

GROUP BY & aggregate functions

Answer 5

The GROUP BY statement is often used with aggregate functions (COUNT, MAX, MIN, SUM, AVG) to group the result set by one or more columns

SELECT column_name(s)

FROM Table_name

WHERE condition

GROUP BY column_name(s)

ORDER BY column_name(s);

Question 6

HAVING & aggregrate functions

Answer 6

enables you to specify conditions that filter which group results appear in the results

Similar to WHERE, but WHERE filters individual rows whereas HAVING filters groups

(the HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions)

Question 7

where can aggregate functions be used

Answer 7

Only in SELECT list and in HAVING claus

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

Question 8

example use of COUNT(*)

Answer 8

Question 9

Example of Having

Answer 9

Question 10

JOIN

Answer 10

They are used to join two or more different tables on a point in which both the tables match the same value and property

Question 11

inner, outer joins, etc. check the ppt

Answer 11

inner, outer joins, etc. check the ppt