Lecture 4 - Groupings

Question 1

The process of grouping is?

Answer 1

Partition a relation into groups based on grouping attribute, i.e.,
clustering tuples having the same value in the grouping attribute.

Question 2

What is the statement that can be used for grouping?

Answer 2

GROUP BY

Question 3

Example of grouping by departement and working out the average salary?

Answer 3

SELECT DNO, COUNT (*), AVG (Salary)
FROM EMPLOYEE
GROUP BY DNO;

Question 4

The grouping attribute must appear…

Answer 4

in SELECT

Question 5

What happens if a grouping attribute has NULL values?

Answer 5

There is a seperate group created for NULLS.

Question 6

Is grouping computationally heavy?

Answer 6

yes

Question 7

What should we avoid doing?

Answer 7

Grouping by PK as this doesn’t do anything

Question 8

EXAMPLE : How many employees are working in each project? Include the
project name at the results

Answer 8

SELECT P.PNAME, COUNT (*) //employees per project
FROM PROJECT AS P, WORKS_ON AS W
WHERE P.PNUMBER = W.PNO
GROUP BY P.PNAME;

Question 9

Is it possible to group by more than 1 attribute ?

Answer 9

YEs

Question 10

Example of multi-attribute grouping?

Answer 10

SELECT COUNT(*), E.DNO, E.SALARY
FROM EMPLOYEE AS E
GROUP BY E.DNO, E.SALARY
ORDER BY E.DNO