Functions In SQL

Ms. Qurat-ul-Ann

1
Functions
• Count
• AVG
• Sum
• MAX
• MIN
• GROUP By Clause
• Having Clause
• Other Clause

2
3
Functions
 Functions are used to manipulate data items.

 They accepts one or more values and return one value.

 An argument is user supplied constant , variable or

column reference .
 The FUNCTION NAME (argument1, argument2,……)

4
Aggregate Functions
 Aggregate functions generate summary value.

 They can be supplied to all the rows in a table or the rows

specified by the WHERE clause.

 Aggregate functions generate a single value from set of

rows.
 Aggregate functions such as SUM, COUNT, AVG, MAX,

MIN generate a summary value.

5
Count(*)
 The count(*) is used to count all rows including rows

containing duplicates and the null values.

 Count function is used to count the number of rows.

 EAXMPLE

Write a query that displays total columns in EMP table

WHEN DEPTNO=20.

SELECT COUNT(*) FROM EMP WHERE DEPTNO=20;

6
7
Count(Column_Name)
 The Count(Column_Name) is used to count the values in

the column specified excluding any null values.

 EXAMPLE

Write a query that displays total records in COMM column

of EMP table.

SELECT COUNT (COMM) FROM EMP;

8
Count(Distinct
Column_Name)
 The Count(Distinct Column_Name) is used to count all

the rows excluding rows containing duplicates and null

values.

EXAMPLE

Write a query that counts distinct MGR from EMP table.

SELECT COUNT (Distinct MGR) FROM EMP;

9
AVG Function
 The AVG function returns the average of all values of the

expression.
 AVG Function can be used with numeric column only and

will automatically ignore the null values.

Syntax:
AVG(ALL|DISTINCT] Expression)
 ALL: It is the default and is applied to all values

 DISTINCT: it indicates that AVG is performed only

one unique instance of a value.

 Expression: It is any valid expression like column

name.
10
Example
Write a query that calculated the AVG salary of all
employees.

SELECT AVG(SAL) FROM EMP;

11
Example
Write a query that calculated the DISTINCT AVG salary of
all employees.

SELECT AVG(DISTINCT SAL) FROM EMP;

12
SUM Function
 The SUM function returns the sum of all values in an

expression.
 It supports the use of DISTINCT to summarize only

unique values in the expression. Null values are ignored.

 It can be used only with the numeric columns.

SYNTAX:

Sum(All | DISTINCT] Expression)

13
Example
Write a query that displays the sum of salaries of all clerks
in EMP table.
 SELECT SUM(SAL) FROM EMP WHERE JOB=‘CLERK’;

14
MAX Function
 The MAX function returns the maximum value in an

expression. It ignores all null values.

 It can be used with all data types.

SYNTAX:

MAX(All | DISTINCT] Expression)

15
Example
Write a query that finds the maximum salary earned by
clerk.
 SELECT MAX(SAL) FROM EMP WHERE JOB=‘CLERK’;

16
MIN Function
 The MIN function returns the minimum value in an

expression. It ignores all null values.

 It can be used with all data types.

SYNTAX:

MIN(All | DISTINCT] Expression)

17
Example 1
Write a query that finds the minimum salary
earned by clerk.
SELECT MIN(SAL) FROM EMP WHERE
JOB=‘CLERK’;

18
Example 2
Write a query that finds the minimum, maximum and
average salaries of all employees.
 SELECT MIN(SAL), MAX(SAL), AVG(SAL) FROM EMP;

19
GROUP BY Clause
 The GROUP BY clause can be used to divide the rows in

a table into smaller group.

 If aggregate function is used in SELECT statement,

GROUP BY clause produces a single value per aggregate.

20
Example 1
Write a query that calculates the average salary for each
different job.
 SELECT AVG(SAL) FROM EMP GROUPBY JOB;

21
Example 2
Write a query that finds the minimum, maximum salary for
each job type.
 SELECT JOB, MAX(SAL), MIN(SAL) FROM EMP
GROUPBY JOB;

22
Excluding Rows in
GROUPBY Clause
Some rows may be pre-excluded in WHERE
clause before dividing them into groups.

23
Example 1
Write a query that displays the average salary for each job
excluding managers.
 SELECT JOB, AVG(SAL) FROM EMP WHERE JOB!
=‘MANAGER’ GROUPBY JOB;

24
Groups within Groups
 It is also possible to use GROUP BY clause to provide results

for groups within groups.

 Write a query that displays average monthly salary for each

job type in department.

 SELECT DEPTNO, JOB, AVG(SAL) FROM EMP GROUPBY
DEPTNO, JOB;

25
Having Clause
 The HAVING clause is used to filter rows after grouping

them.
 It is also used to restrict rows by applying aggregate

function in SELECT statement.

 It means the HAVING clause is used to restrict the

groups.
 It can be used with all data types.

26
Example 1
Write a query that displays average salary for all
departments employing more than three people.
 SELECT DEPTNO, AVG(SAL) FROM EMP GROUPBY
DEPTNO HAVING COUNT(*)>3;

27
Example 2
Write a query that displays those jobs which have maximum
salary greater than 3000.
 SELECT MAX(SAL), JOB FROM EMP GROUPBY JOB
HAVING MAX(SAL)>3000;

28
Example 3
Write a query that finds all departments which have more
than 3 employees.
 SELECT DEPTNO, COUNT(*) FROM EMP GROUPBY
DEPTNO HAVING COUNT(*)>3;

29
Order of Clauses in
SELECT Statement
 The WHERE clause may still be used to exclude rows.

The order of clause is:

SELECT column(s)

FROM table(s)

WHERE row condition(s)

GROUP BY column(s)

HAVING group of rows condition(s)

ORDER BY column(s);

30
Order of Clauses in
SELECT Statement
 SQL evaluates:

 WHERE clause to establish single rows. It cannot

contain group functions

 GROUP BY clause to setup groups.

 HAVING clause to select groups for displaying.

31
Exercise
Student

Qurat-ul-Ain 32
Exercise (Cont..)
 Write a query to display Maximum cgpa of Students.

 Write a query to display Minimum cgpa of Students.

 Write a query to display “average Age” of all students.

 Write a query to display ‘total cgpa” of all Students.

 Write a query to display the highest, lowest and average age

from student table.

 Write a query to display total Number of students

 Write a query to display total number of students whose city is

distinct.
 Write a query to display total number of Students whose

Last_Name is different. Qurat-ul-Ain 33

Exercise (Cont..)
 Write a Sql Statement to retrieve no of Students

 Write a Sql Statement to retrieve fname, minimum age from

student group by section.

 Write a Sql Statement to retrieve lname, maximum cgpa from

student group by city.

 Write a Sql Statement to retrieve sid, total age from student

group by degree having total age less than 50

 Write a Sql Statement to retrieve Name, average age from

student group by section having average age greater than 50

Qurat-ul-Ain 34
Exercise (Cont..)
Teacher

Qurat-ul-Ain 35
Exercise (Cont..)
 Retrieve no. of Teachers in each City

 Retrieve Qualification, minimum salary from teacher in each city

 Retrieve no. of teacher in each city where city is Rawalpindi

 Retrieve qualification, minimum salary from teacher in from distinct

qualification where minimum salary greater than 120500

 Retrieve city maximum salary from teacher in each city where

maximum salary less than 120000

 Retrieve city, total salary from teacher in each city where total

salary is less than 1900000

 Retrieve city, number of teachers from teacher in each city where

number of teacher is greater than 4

Qurat-ul-Ain 36