INFORMATION SYSTEMS

1
INFORMATION SYSTEMS

• IS1
• IPT 2003

L8
2
IS1 - SQL

• SQL provides five predefined functions-
• sum
• count
• avg
• max
• min

3
IS1 - SQL
Each of these functions operates on a bag and
returns a single value, which must be a number
for sum, count, avg and either a number or
string for max or min
4
IS1 - SQL
A function is called by simply naming it and
placing its argument in parenthesis after its
name.
5
IS1 - SQL
ESP
Select sum (score) from ESP
6
IS1 - SQL
ESP
Select sum (score) from ESP
27
7
IS1 - SQL
Select sum (score) from ESP
ESP
27 The function call sum (distinct b) returns the
sum of all the elements in the set included in
the bag, i.e. duplicates are ignored. 22
8
IS1 - SQL
Select sum (score) from ESP
ESP
27 The function call sum (distinct b) returns the
sum of all the elements in the set included in
the bag, i.e. duplicates are ignored. SELECT Sum
(Score) From (SELECT DISTINCT score FROM ESP) 22
9
IS1 - SQL
The function call count() returns the number of
rows in a specified table. The function call
count (distinct col) returns the number of
distinct values in a specified column, excluding
null values.
10
IS1 - SQL
count Select count () from ESP- Select
count() from ESP where score 3 Select
count(distinct score) from ESP
11
IS1 - SQL
count Select count () from ESP- 6 Select
count() from ESP where score 3 5 Select
count(distinct score) from ESP 5
12
IS1 - SQL
The function count function is often used in
requests that ask How many ? For
example How many obtained an ESP score above 3?
13
IS1 - SQL
The function avg function returns the average of
the values in a bag Select avg (score) from
ESP
14
IS1 - SQL
The function avg function returns the average of
the values in a bag Select avg (score) from
ESP 4.83 Duplicates are counted.
15
IS1 - SQL
The function avg function returns the average of
the values in a bag Select avg (distinct score)
from ESP 5 Duplicates are counted.
16
IS1 - SQL
The function avg function returns the average of
the values in a bag In Access we need to obtain
the distinct ages before we count, avg. Select
distinct age from student Select count age from
ages
17
IS1 - SQL
The function max function returns the maximum
value in a bag, min returns the minimum. If the
values are numbers max give the largest number
and min gives the smallest. If the values are
character stings max gives the lexicographically
latest.
18
IS1 - SQL
In SQL the five functions may be called
immediately after select. Or in a having
clause. Illegal Select Name from ESP Where
Score avg (Score).
19
IS1 - SQL
In SQL the five functions may be called
immediately after select. Or in a having
clause. legal Who is above average? Select
Name from ESP Where Score (Select avg (Score)
from ESP)
20
INFORMATION SYSTEMS

• IS1
• IPT 2003

L8
21
IS1 - SQL
Scores for ART
ART
WHO achieved an Art score in the subrange 76 to
100?