Summary SQL part 3

UNIT III – DATABASE MANAGEMENT
STRUCTURED QUERY LANGUAGE(SQL)
SQL : PART 3

GROUP FUNCTIONS/AGGREGATE FUNCTIONS
Group functions are also called aggregate functions.They work upon group of rows and return one result for
the complete set of rows
Table : EMPL




1. SUM( ) : To find the sum of the values under the specified column. If distinct keyword is
specified then duplicate values are ignored.
Syntax : SELECT SUM(column name)
FROM <table name> WHERE <search condition>;




SELECT SUM(sal) FROM EMPL WHERE job = ‘SALESMAN’;

2. AVG( ) : To find the average value in a column
Syntax : SELECT AVG(column name)
FROM <table name> WHERE <search condition>;




1)Write a query to display the average salary of employees in
department number 20
SELECT AVG(sal) FROM EMPL WHERE deptno =20;
2)Write a query to display the average salary of all salesman.
SELECT AVG(SAL) FROM EMPL WHERE job =”SALESMAN”;

3. MIN( ) :To find the smallest value in a column
Syntax : SELECT MIN(column name)
FROM <table name> WHERE <search condition>;

, Eg:
Write a query to display the minimum salary of employees
SELECT MIN(SAL) FROM EMPL

4. MAX( ) : To find the largest value in a column
Syntax : SELECT MAX(column name)
FROM <table name> WHERE <search condition>;




SELECT MAX(SAL) FROM EMPL WHERE job = ‘SALESMAN’;

5. COUNT – This function is used to counts the number of values in a column.
i)COUNT(column name): To count the number of non NULL values in a column ( excludes
NULL values)
ii)COUNT(*): To count the total number of rows in a table including NULL values.