Lecture 7- Aggregation and Grouping

Question 1

What is an aggregate?

Answer 1

Aggregate function that take a collection (a set or multiset) of values as input and return a single value

Question 2

What are the five standard built in aggregate functions?

Answer 2

Question 3

Describe these operations on sets:

1. COUNT ([DISTINCT] A)
2. SUM ([DISTINCT] A)
3. AVG ([DISTINCT] A)
4. MAX (A)
5. MIN (A)

Answer 3

1. The number of (unique) values in the A column
2. The sum of all (unique) values in the A column
3. The average of all (unique) values in the A column
4. The maximum value in the A column
5. The minimum value in the A column

Question 4

What is basic aggregation?

Answer 4

• Count is frequently used to count the number of tuples in a relation
• The notation for this function in SQL is count(* )

Question 5

Describe the use of basic aggregation and the use of DISTINCT

Answer 5

• It is not allowed in SQL to use DISTINCT with COUNT(asterix)
• If count does not include DISTINCT then COUNT(asterix) = COUNT(x), where x is any set of attributes
• Retaining duplicates is important in computing an average

Question 6

What is this query doing?

customer (cname, street, city)

Answer 6

Find the number of customers in Edmonton

Question 7

What is this query doing?

branch (bname, address, city, assets)

Answer 7

Find the total assets of branches in Edmonton

Question 8

What is this query doing?

deposit (accno, cname, bname, balance)

Answer 8

Find the number of different branches where John Doe has a deposit account

Question 9

What is this query doing?

loan (accno, cname, bname, amount)

Answer 9

Find the name of the customer with the higest loan amount

Question 10

What is this query doing?

customer (cname, street, city)
deposit (accno, cname, bname, balance)

Answer 10

Find the number of customers who have deposit accounts in at least 3 different branches

Question 11

What is this query doing?

branch (bname, address, city, assets)

Answer 11

Find the names of branches which have assets greater than the average assets of all branches

Question 12

How to compute the number of customers per city?

Answer 12

1. Fire off a seperate query for each city
2. Define a special grouping operator

SELECT COUNT(asterix)
FROM customer
WHERE city = ‘Edmonton’

SELECT city, COUNT(cname)
FROM customer
GROUP BY city

Question 13

What is GROUP BY used for?

Answer 13

• The GROUP BY clause is used in SQL to group rows in a relation based on give attributes
• The attributes given in the GROUP BY clause are used to form groups called subgroups

Question 14

If there is a ________ in the SELECT statement, then the GROUP BY clause must be placed after it

Answer 14

WHERE clause

Question 15

What type of query is this?

Answer 15

GROUP BY with aggragate functions

Question 16

What is the rule of GROUP BY with aggregate functions?

Answer 16

Every column name in the SELECT clause must appear in the GROUP BY clause (this is not required if the column name is only used in aggregate function)

Question 17

What is the HAVING clause used for?

Answer 17

• Used to specify a filter condition for the group generated by the GROUP BY clause
• It is used in SQL statements to specify a filter conditionfor groups of rows or aggregates

Question 18

What does HAVING clause do?

Answer 18

HAVING is applied to each group seperately to determine if the group as a whole qualifies

Question 19

What is this query doing?

branch (bname, address, city, assets)

Answer 19

• Find cities with more than two bank branches
• GROUP BY city partitions tuples into groups where all tuples in a group have the same values in city column
• HAVING is applied to each group separately to determine if the group as a whole qualifies

Question 20

What is this query doing?

loan (accno, cname, bname, amount)

Answer 20

For every branch, list the branch name and the name of every customer who has more than 3 loans each over $100,000 in that branch

Question 21

Describe the evaluation of GROUP BY with HAVING

Answer 21