Question

My SQL -----database----

What restrictions apply to the use of the aggregate functions within the SELECT statement? How do nulls affect the aggregate functions? Give a complete example, Explain how the GROUP BY clause works. What is the different between the WHERE and HAVING clauses? Give an example

Answer 1

Following are the restrictions that apply to the aggregate functions within the select statement :

1. Some of the aggregate functions(Sum and avg) can only be applied to numeric data.
2. An aggregate function with a non-aggregate can't be used simultaneously in a single sql query.
3. It usually ignores null values.

Null values are all ignored by the aggregate functions except in the case of count function.

Example: consider a table instructors as follows

Name	Department	salary	Age
Arun Prakash	cs	40000	45
sam allans	ee	30000	52
charlie white	cs	20000	57
joseph	ee	null	35
piyush	cs	null	30
ram mohan	it	20000	40

joseph and piyush are newly joined so there salary are null.

if we want the salary of all the instructors paid by the college we can write the query :

select sum(salary) from instructor;

output is:

sum(salary)

110000

Group by clause is used when we have to apply aggregate functions for a group of set of tuples, it first groups the table according to some attribute and then apply the aggregate function on each group.

The where clause is used when we have to apply condition to tuples while the having clause is used when we have to apply condition to a group.

Example : if we want to find the average salary of all the departments where average salary is greater then 25000 we write the following query:

select department, avg(salary) from instructor group by department having avg(salary) > 25000;

output:

department	avg(salary)
cs	30000
ee	30000