DBIS L04a SQL Aggregate Non-Aggregate Functions and Stored Procedures PDF

Preview

Summary

Download DBIS L04a SQL Aggregate Non-Aggregate Functions and Stored Procedures PDF

Description

17/10/2013

     DBIS Lecture – SQL Aggregate & Non-Aggregate Functions

1

 After completing this lesson, you should be able to  use aggregate functions and some other functions to manipulate data  use stored procedures

References: Textbook pp. 108 - 113 http://dev.mysql.com/doc/refman/5.5/en/group-by-functions.html DBIS Lecture – SQL Aggregate & Non-Aggregate Functions

2

1

17/10/2013

 General syntax  Function name followed by arguments enclosed within parenthesis and separated by commas.  No space allowed before the left parenthesis (rounded bracket). Examples

  DBIS Lecture – SQL Aggregate & Non-Aggregate Functions

3

 Function

Purpose



Converts the string to all lowercase characters



Converts the string to all uppercase characters





RESULT: ‘quadratic’

RESULT: ‘HEY’

 !" !#$%&!' ((%'%&# )*(& +!,-&&-

4

2

17/10/2013

 Function

Purpose

.#*!&/ "'&

Rounds the column, expression or value to n decimal places or if n is omitted, no decimal places.

).#*!&/ "

Returns the largest integer not greater than its argument.

0.#*!&/ "

Returns the smallest integer not less than its argument.

123456'5'123456'7' )123456'0123456

DBIS Lecture – SQL Aggregate & Non-Aggregate Functions

5

 123456'5'123456'7' )123456'0123456

0

0

Floor goes to the bottom, Ceiling goes to the top. What happens if the number is a negative number? -45.926

)8123456,816 08123456 ,812 ) DBIS Lecture – SQL Aggregate & Non-Aggregate Functions

6

3

17/10/2013

 ) "9' "5 If expr1 is not NULL, IFNULL() returns expr1; otherwise it returns expr2. IFNULL() returns a numeric or string value, depending on the context in which it is used. *(%&#'*:%.#(')!;'7 ).# +*:%.#( ,-)...