1 / 19
Télécharger la présentation

SQL Functions: Powerful Tools for Data Manipulation and Formatting

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. Lecture 5 SQL FUNCTIONS

  2. Functions are a very powerful feature of SQL. They can be used to do the following: • Perform calculations on data • Modify individual data items • Manipulate output for groups of rows • Format dates and numbers for display • Convert column data typesSQL functions sometimes take arguments and always return a value.

  3. Types of SQL functions There are two types of functions: • Single-row functions • Multiple-row functions

  4. Single Row Functions These functions operate on single rows only and return one result per row. There are different types of single-row functions. • Character • Number • Date • Conversion • General

  5. Multiple-Row Functions • Functions can manipulate groups of rows to give one result per group of rows. These functions are also known as group functions. Example: • Avg(),count(),max(),min(),sum()

  6. Single Row Function Description • Manipulate data items • Accept arguments and return one value • Act on each row that is returned • Return one result per row • May modify the data type

  7. Type of Argument in the Function • User-supplied constant • Variable value • Column name • Expression. Single row functions can be used in SELECT,WHERE,ORDERBY clauses.

  8. Syntax • function_name is the name of the function, arg1, arg2 is any argument to be used by the function. • This can berepresented by a column name or expression. • Function_name(arg1,arg2…)

  9. Type of Single Row Functions  Character functions: Αccept character input and can return both character and number values • Number functions: Accept numeric input and return numeric values • Date functions: Operate on values of the DATE data type. • Conversion functions: Convert a value from one data type to another • General functions:- NVL,NVL2,NULLIF,CASE,DECODE

  10. Character Function Two types: Case conversion function. Character manipulation functions.

  11. Case Conversion Function LOWER, UPPER • LOWER Converts alpha character values to lowercase SELECT LOWER(ENAME) FROM employee

  12. UPPER: Converts alpha character values to uppercase. SELECT UPPER(ENAME) FROM employee

  13. Character Manipulation Function CONCAT,SUBSTR.LENGTH,TRIM • CONCAT: Joins values together (You are limited to using two parameters with CONCAT.) SELECT CONCAT(ENAME,EMPLNO) FROM employee • SUBSTR: Extracts a string of determined length SELECT SUBSTR(ENAME,1,5) FROM employee

  14. LENGTH: Shows the length of a string as a numeric value SELECT length(ENAME) FROM employee • INSTR: Finds numeric position of a named character SELECT INSTR(ENAME,'A') FROM employee

  15. Replace • Replace lets you substitute one string for another as well as to remove character strings. • Select replace(‘JACK’, ‘J’, ‘BL’); Output: BLACK Reverse: Reverse characters in a string Select reverse(‘abcd’); Output: dcba

  16. MID() • Used to extract character from the text field. MID(name, 1 ,4)

  17. Number Function •  ROUND: Rounds value to a specified decimal Round(45.436,2) Result: 45.44 • TRUNC: Truncates value to a specified decimal TRUNCATE(45.436,2) Result: 45.43 • MOD: Returns remainder of division MOD(1600,300) Result: 100

  18. Controlling for number precision • Ceil and floor functions are used to round either up or down to the closest integer. • Select ceil(72.445) 73 • floor(72.445) 72

  19. DATE FUNCTIONS • Used to perform date calculations or format date values. • MYSQL displays date in this format'YYYY-MM-DD'  • Curdate() for retrieving current date. • CURTIME() for retrieving current time • NOW() for retrieving time and date.

More Related