SQL Functions : SQL Functions 2-27-2009
Mohammad Waheed
Virtual Das Nobel
We will cover : SQL Functions
SQL avg()SQL count()SQL max()SQL min()SQL sum()SQL ucase()SQL lcase()SQL len()
Has anyone used them before? We will cover
Avg ( ) : Avg ( ) Select avg(salary) from faculty
Calculates the avg salary in the faculty table
Avg() Example 1 : Avg() Example 1
Avg() Example 2 : Avg() Example 2
Count ( ) : Count ( ) Select count (*) from Students
Returns the total number of records in the student table
Count() Example 1 : Count() Example 1
Count() Example 2 : Count() Example 2
Max ( ) : Max ( ) Select max (gpa) from students
Returns the highest gpa
Max() Example 1 : Max() Example 1
Min ( ) : Min ( ) Select min (gpa) from students
Returns the lowest gpa
Min() Example 1 : Min() Example 1
Min() Example 2 : Min() Example 2
Sum ( ) : Sum ( ) Select sum(salary) from faculty
Total salary for all faculty members
Sum() Example 1 : Sum() Example 1
Ucase ( ) : Ucase ( ) SELECT
UCASE(LastName) as LastName,FirstName
FROM students
-------
Returns… Mark Smith as SMITH Mark
Why last name is written first?
Ucase() Example 1 : Ucase() Example 1
Lcase ( ) : Lcase ( ) Select lcase(city) from student
AlBany - > albany
Lcase() Example 1 : Lcase() Example 1
Len ( ) : Len ( ) Select len(firstname) from student
Where studentid = 10
Returns the length of the first name
Mark = 4
Anthony = 7
Len() Example 1 : Len() Example 1
Summary… : Summary… Q?
Lets use these functions during lab