Using Relational Databases and SQL

1
Using Relational Databases and SQL
Lecture 5 Functions

• Steven Emory
• Department of Computer Science
• California State University, Los Angeles

2
Miscellany

• Lab and Homework 3
• Questions?
• Go over some problems?
• Left and Right Join Comments
• Which attribute do you check for NULL?
• Should use the primary key attribute
• When to Use DISTINCT
• One-to-many and many-to-many relationships

3
Topics for Today

• Functions
• String Functions (Pages 117 120)?
• Date and Time Functions (Pages 120 123)?
• Control Flow Functions (Page 125)?
• Comparison Functions (Pages 125 126)?
• Numeric Functions (Not in book)?

4
What is a Function?

• Portion of prewritten code in MySQL used to
  perform a very specific task
• Syntax
• function_name(argument_list)?
• argument_list arg1, arg2, arg3, ...
• Examples
• SELECT LENGTH('STEVEN') -- output is 6
• SELECT SIN(3.14159) -- output is 2.6e-6
• SELECT CURDATE() -- output is 2008-10-13

5
Function Tips

• Don't Try to Memorize Everything
• Too many functions
• Every DBMS vendor uses different functions
• Look them up in the book, on the internet, etc.
• Get Parentheses Right
• Type in parentheses first, then fill in the
  arguments
• Nested Functions
• Type each function in separately in notepad
• When done, substitutes nested functions back in

6
String Functions

• CHAR_LENGTH(str)?
• CONCAT(str1, str2, ...)?
• CONCAT_WS(separator, str1, str2, ...)?
• FORMAT(number, decimal_places)?
• LEFT(str, len), RIGHT(str, len)?
• LOCATE(substr, str), LOCATE(substr, str, pos)?
• LOWER(str), UPPER(str)?

7
More String Functions

• SUBSTRING(str, pos, len)?
• SUBSTRING_INDEX(str, delim, count)?
• TRIM(str), LTRIM(str), RTRIM(str)?
• More string functions can be found here.

8
Date and Time Functions

• CURDATE()?
• DATEDIFF(date1, date2)?
• DATE_ADD(date, n_days)?
• DATE_ADD(date, INTERVAL value type)?
• DATE_FORMAT(date, format_str)?
• DATE_SUB(date, n_days)?
• DATE_SUB(date, INTERVAL value type)?

9
More Date and Time Functions

• DAYOFMONTH(date), DAY(date)?
• DAYOFWEEK(date)?
• DAYOFYEAR(date)?
• LAST_DAY(date)?
• MONTH(date)?
• NOW()?
• TO_DAYS(date)?
• More date and time functions can be found here.

10
Control Flow Functions

• IF(condition, true_expr, false_expr)?
• IFNULL(expr1, expr2)?
• CASE WHEN expr1 IS NULL THEN expr2ELSE expr1 END
• NULLIF(expr1, expr2)?
• CASE WHEN expr1 expr2 THEN NULLELSE expr1 END
• More information on control functions can be
  found here.

11
Cast Functions

• CAST(expr AS type)?
• CONVERT(expr, type)?
• More information on cast functions can be found
  here.

12
Numerical Functions

• ABS(value)?
• CEIL(value), CEILING(value)?
• COS(value), SIN(value), TAN(value)?
• DEGREES(value), RADIANS(value), PI()?
• FLOOR(value)?
• POW(X, Y), SQRT(X)?
• ROUND(value), ROUND(value, decimal_places)?
• TRUNCATE(value, decimal_places)?

13
Numerical Functions

• More numerical functions can be found here.

14
Sample Questions

• Report the artist name and member name for each
  member who is the responsible party for each
  artist. Concatenate the first and last name of
  each member with a space between.
• Modify the previous exercise to also report the
  home phone number, formatted as (xxx)xxx-xxxx.
• Report the studio name and the first name of each
  studio contact.
• Report the studio name and the last name of each
  studio contact.

15
More Sample Questions

• Report the artist name and the age in years of
  the responsible member for each artist at the
  time of that artist's entry date.
• Report all genres from the Genre table,
  capitalizing the first letter of each.