CS 105 SQL functions

							                CS 105
            Functions in SQL

•What is a function?

•What is an argument?

•What is COUNT?

•How can you use SUM?

                               #1
        Functions built into MySQL
You have already seen a function in SQLyog:
concat(field1, field2)
concat(field1, ‘ , ’ ,field2)




                         CS 105 Fall 2006     #2
         So what is a function?
• A function is a predefined formula that
  accepts one or more arguments as input,
  processes the arguments, and returns an
  output.
    concat (field1, field2)
    count (*)

The function has built-in rules for
 what it does.


                   CS 105 Fall 2006    #3
        A few useful functions
COUNT( field )




                 CS 105 Fall 2006   #4
   Counting different fields




• If you used Count
  on this table, what
  would you get?



               CS 105 Fall 2006   #5
      A strange thing about Count

When the * is used for COUNT, all rows
 (records) are counted even if some
 contain NULL
BUT COUNT(Age) doesn't count a row if its
 field is _____________.




                     CS 105 Fall 2006       #6
    Count (distinct [field] )



COUNT( DISTINCT field ) displays the
 number of unique values in the
 specified field, taken only from the
 records returned




                   CS 105 Fall 2006     #7
   MAX ( ) function


MAX( field ) Returns the maximum value
 in a field




                 CS 105 Fall 2006   #8
          MIN ( ) function

MIN( field ) Returns the minimum value
 in a field (it ignores NULL values)




                  CS 105 Fall 2006   #9
Max and Min can be used on words!!




               CS 105 Fall 2006   # 10
 AVG ( ) function


Avg( field ) Returns the average value
 in a field




                    CS 105 Fall 2006     # 11
              Tidbit about AVG
Columns (fields) containing the NULL value
  are      ____________ in AVG(Age).




                       CS 105 Fall 2006      # 12
               SUM ( ) function
SUM( field ) Will                    all
 entries in a field or a calculation such as
 this one:




                      CS 105 Fall 2006     # 13
  Sum ( ) function, cont.

• SUM( field ) Can total the orders for
  one instance, such as a single order
  for many different items




                    CS 105 Fall 2006   # 14
        Month and Year functions

• Month returns the month
• Year returns the year




                    CS 105 Fall 2006   # 15
              Length Function

• Length( field ) Can return the number
  of characters in a field




   NOT in Course Guide
                    CS 105 Fall 2006   # 16
               To Summarize



• What is a function?

• What is an argument?

• What is COUNT?

• How can you use SUM?


                    CS 105 Fall 2006   # 17