1 / 15

Using Relational Databases and SQL

Steven Emory Department of Computer Science California State University, Los Angeles. Using Relational Databases and SQL. Lecture 5: Functions. Miscellany. Lab and Homework #3 Questions? Go over some problems? Left and Right Join Comments Which attribute do you check for NULL?

ricky
Download Presentation

Using Relational Databases and SQL

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Steven Emory Department of Computer Science California State University, Los Angeles Using Relational Databases and SQL Lecture 5: Functions

  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: • SELECTLENGTH('STEVEN'); -- output is 6 • SELECTSIN(3.14159); -- output is 2.6e-6 • SELECTCURDATE(); -- 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)‏ • CASEWHEN expr1 ISNULLTHEN expr2ELSE expr1 END • NULLIF(expr1, expr2)‏ • CASEWHEN expr1 == expr2 THENNULLELSE 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.

More Related