Phones or Change Profile to Silent Mode Lecture Plan Functions, Features of Functions Types of Functions Single- Row Functions, Features of Single- Row Functions Types of Single- Row Functions Displaying Data from multiple tables Joins and its types • are programs that take zero or more arguments and return a single value as output. • makes a simple query more powerful and is used to manipulate data values.
Perform calculation on data
Modify individual data items Manipulate output for groups of rows Format dates and numbers for display Convert column data types Types of Functions Single-row functions Aggregate/ Multiple-row functions (also known as Group functions) Analytical functions Object-reference functions Programmer defined functions Types of Functions • Single-Row Function: These functions operate on single row only and return one result per row.
Accept one or more arguments to perform an action to demonstrate the
effect of function on the value provided.
• Aggregate/Multiple-Row Function: These functions can manipulate groups of rows to produce one result per group of rows. Types of Functions • Single-Row Function: • An argument can be one of the following: User supplied content Variable value Column name Expression
SYNTAX: Function_Name [(arg1, arg2 …)]
Explanation : Function_Name is the name of the SQL Function arg1, arg2… is the argument(s) used by the function Features of Single Row Functions • Acting on each row returned in the query • Returning one result per row • Possibly returning a data value of a different type than that referenced • Possibly expecting one or more arguments • Can be used in SELECT, WHERE and ORDER BY clause and can also be nested. Types of Single Row Functions Character Functions Single-Row character function deals with the character data.
The character function is divided into two sub-categories, these are:
Case-manipulation Functions: This sub-category of function converts the case of the character string. There are basically three functions which fall under this sub-category; these are: Character Functions • used to manipulate the characters within the string and as well as the strings itself.
• Various functions which fall under this category are:
concat, substr, length, instr, lpad, rpad, trim etc. Character Functions Function Purpose Example Result
LOWER(colum It is used to SELECT LOWER(‘Sunny sunny holidays
n |expression) convert a string Holidays’) into Lower case. FROM Dual; UPPER(column It is used to SELECT UPPER(‘Sunny SUNNY |expression) convert a string holidays’) HOLIDAYS into Upper case. FROM Dual;
INITCAP(colum It returns the string SELECT INITCAP(‘sunny Sunny Holidays
n |expression) with the first letter holidays’) FROM Dual; of each word in upper case. Function Purpose Example Result CONCAT(column1|e Concatenates two strings. It is SELECT CONCAT(‘Scientific’, ScientificReserch xpression, same as the || operator ‘Research’) column2|expression FROM Dual; 2) SUBSTR(column This function returns column SELECT SUBSTR(‘Tales of Two les of T |expression, m [,n]) |expression which is n long, Cities’, 3,8) FROM Dual; beginning at position m. If m is negative, the position is counted from right to left i.e. backward.
LENGTH(column This function returns the numeric SELECT LENGTH(‘Tales of Two 19
|expression) length in characters of column Cities’) FROM Dual; |expression. INSTR(column This function returns the numeric SELECT INSTR(‘The Three 14 |expression, ’string’, character position in column Musketeers’, ‘K’) FROM Dual; m [,n]) |expression where the nth occurrence of string is found. The search begins at the mth character position in column |expression, if its negative the search starts backward. LPAD(column This function returns the column| SELECT LPAD(‘HELLO’, ***HELLO |expression, m, expression expanded in length to m 8, ’*’) FROM Dual; ‘string’) characters, using string to fill in spaces as needed on the left of column| expression.
RPAD(column This function returns the column| SELECT RPAD (‘HELLO’, 8, HELLO*** |expression, m, expression expanded in length to m ‘*’) FROM Dual; ‘string’) characters, using string to fill in spaces as needed on the right of column| expression.
TRIM(leading This function returns the trim_source SELECT TRIM(‘T’ FROM he Three |trailing |both, with all leading | trailing | both ‘The Three Musketeers’) Musketeers trim_character occurrences of characters in FROM Dual; FROM trim_character removed. A NULL is trim_source) returned if any of the three arguments is NULL. Function Purpose Example Result ASCII(String) This function returns the ASCII SELECT ASCII(‘Hello’) FROM Dual; decimal equivalent of the first 72 character in the String
CHR(m [ USING This function returns the character SELECT CHR(78) FROM Dual; equivalent of the decimal (binary) N NCHAR_CS]) representation of the character. If the optional USING NCHAR_CS is included, the character from National character set is returned.
LTRIM(column| This function returns the column| SELECT LTRIM(‘xyXYyx LTRIM
expression without any leading characters XYyx LTRIM WORD expression, [,string]) WORD’, ‘xy’) AS “Effect of LTRIM” that appears in string. If no string characters are leading character in FROM Dual; column| expression then it is returned unchanged.
RTRIM(character_string This function returns the column| SELECT RTRIM(‘xyXYyx RTRIM
expression without any trailing characters xyXY RTRIM WORD 1, [,character_string2]) WORD xyXYyx’, ‘yx’) AS “Effect that appears in string. If no string characters are trailing character in of RTRIM” FROM Dual; column| expression then it is returned unchanged. Numeric Functions • Single-Row numeric functions deals with the numeric data and perform some kind of mathematical or arithmetic manipulation.
• All the function coming under this category take some argument and returns a numeric values. Function Purpose Example Result ABS(n) This function returns the absolute SELECT ABS(-23) AS “Negative Value” FROM Dual; NEGATIVE VALUE value of n. ------------------------- 23
CEIL(n) This function returns the smallest SELECT CEIL(-32.86) AS “Round up whole number” Round up whole number integer that is greater than or equals FROM Dual; --------------------------- to n. it rounds up to a whole number. ------ -32
FLOOR(n) This function returns the largest integer that SELECT FLOOR(-32.86) AS “Round down whole Round down whole number is less than or equal to n. It rounds down to a number” FROM Dual; --------------------------- whole number. ------ -33
MOD(n1, n2) This function returns n1 modulo n2 or the SELECT MOD(8,3) AS “8 divided by 3” FROM Dual; 8 divided by 3 remainder of n1 divided by n2. If the n1 is ------------------------- negative the result is negative. The sign of n2 2 doesn’t have any effect on the result.
ROUND(n, i) This function returns n rounded to i digits of SELECT ROUND(123.4523,2) AS “Positive round to Positive round to right precision to the right of the decimal. If i is right” FROM Dual; --------------------------- negative, i is rounded to left of the decimal. ------ 123.45
TRUNC(n, i) This function returns n truncated to i digits of SELECT TRUNC(49.926,2) FROM Dual; TRUNC(49.926) precision to the right of the decimal. If i is --------------------- negative n is truncated to left of the decimal. 49.92 Date Functions • Oracle database stores the date in a numeric format, representing the country, year, month, day, hours, minutes and seconds.
• The default display and input format for any date is DD-MON-YY.
• SYSDATE () is a commonly used date function. It returns the current
date and time of the database server
Example SELECT SYSDATE FROM Dual; Date Functions • Oracle database stores Dates in numeric format which makes it possible to perform calculations on them. Operation Result Description
Date + Number Date Adds a number of day(s) to a date.
Date – Number Date Subtract a number of day(s) from a date.
Date – Date Number of Days Subtract one date from another and returns the number of days between the two.
Date + Number/24 Date Adds a number of hours to a date.
Arithmetic operator with dates Function Name Example Result ADD_MONTHS(Date, select sysdate, SYSDATE after add --------- --------- n) add_months(sysdate,5) as "after 19-SEP-22 19-FEB-23 adding" from dual; Current_Date Select Current_date from dual; CURRENT_D --------- 19-SEP-22 EXTRACT(c from select sysdate, extract(month from SYSDATE MONTH YEAR date_time|interval sysdate) as month, extract(year from --------- ---------- --- expression) sysdate) as year from dual; ------- 19-SEP-22 9 2022 . LAST_DAY(SYSDAT select last_day(sysdate) as last_date LAST_DATE Arithmetic operator with dates Function Name Example Result MONTHS_BETWEE SELECT MONTHS_BETWEEN('19-JAN- MONTHS ---------- N(Date1,Date2) 2023','19-SEP-2022') AS MONTHS FROM 4 DUAL; Next_day(Date, SELECT NEXT_DAY('20-SEP,2022','wed') AS 3RD WED --------- Day_of_Week) "3RD WED" FROM DUAL; 21-SEP-22 Round(Date[,forma SELECT ROUND(SYSDATE,'YEAR') AS YEAR, YEAR MTH --------- -------- t]) ROUND(SYSDATE,'MONTH') AS MTH FROM - DUAL; 01-JAN-23 01- OCT-22 . TRUNC(Date[,forma SELECT TRUNC(TO_DATE('19-SEP-22','DD- NEW YEAR --------- Conversion Functions • Conversion functions are used to convert a value from one datatype to another datatype.
• The data type conversion can be done implicitly by Oracle server or
explicitly by the user. Conversion Functions • Implicit data type conversion: These types of conversions are done by Oracle server whenever it supposed to be done. The implicit data type conversion is sub-divided into two categories:
For Assignment For Expressions Conversion Functions Conversion Functions Explicit data type conversion: These types of conversion uses function to convert a value from one data type to another. TO_CHAR explicit data type conversion function • The Oracle TO_CHAR function converts a number or date value to a VARCHAR2 character string.
• the TO_CHAR function is divided into two sub-categories for the two data types:
• TO_CHAR Number Conversion
• TO_CHAR Date Conversion TO_CHAR number conversion • Syntax: TO_CHAR(number, ‘format_model’)
Example: SELECT TO_CHAR(Price,’$99,999.00’) AS PRICE FROM Books; Format Element for number conversion TO_CHAR Date • Syntax:conversion TO_CHAR(date, ‘format_model’)
Example: SELECT TO_CHAR(SYSDATE, ‘fmDDTH MON YYYY’) AS “Formatted Date” FROM Dual; General Functions • General Functions work with any data type and relates to the use of NULL values in the expression list. General Functions • Example 1: select employee_id,nvl(commission_pct,'3') from hr.employees;
• Example 2: select employee_id,coalesce(commission_pct,3) from
hr.employees; A join is a query that combines rows from two or more tables or views Require PRIMARY KEY and FOREIGN KEY to establish a relationship.
Syntax: SELECT Table1.column, Table2.column FROM Table1, Table2
WHERE Table1.column1 = Table2.column2;
Explanation Table1.column table and column from which data is retrieved Table1.column1 = condition that joins (or Table2.column2 relates) the tables together Guidelines to join two or more tables: When writing the SELECT statement that joins tables, precede the column name with the table name for clarity and to enhance the database access. If the same column name appears in more than one table, the column name must be prefixed with the table name(i.e. table alias name). The most important guideline is, to join n tables we need to have n-1 join conditions in the WHERE clause. Use table alias name to improve readability of query and to shorten a long table name. SELECT B.Title, B.P_Code, P.P_Name FROM Books B, Publisher P WHERE P.P_Code = B.P_Code; Oracle offers four most prominent join techniques:
Equi Join Non-Equi Join Outer Join Self Join combines rows from two tables where there are same values for the specified columns. returns only those rows which satisfy the join condition in the WHERE clause. Equality operator (=) is the most common operator used to relate two tables. Are also known as inner join and simple join
Example : SELECT I.B_Code, B.Title, B.Price, I.Quantity FROM Inventory I, Books B A non-equijoin join is a type of join in which we can join two tables with the join condition where the join condition uses other than equal operator (=).
The NON EQUI JOIN uses comparison operator instead of the
equal sign like >, <, >=, <= along with conditions. Used to return all the rows that exist in one table, even though corresponding rows do not exist in the joined table. The (+) is used to denote an Outer join in a query, it is placed at the end of the table name in the WHERE clause. The table with (+) should be the table that does not have matching rows.
Syntax: SELECT table1.column, table2.column FROM table1, table2 WHERE table1.column[(+)] = table2.column [(+)] Using LEFT OUTER JOIN to display data from two tables Example SELECT I.B_Code, B.Title, B.Price, I.Quantity FROM Books B, Inventory I WHERE I.B_Code (+) = B.B_Code; Using RIGHT OUTER JOIN to display data from multiple tables Example: SELECT I.B_Code, B.Title, B.Price, I.Quantity FROM Books B, Inventory I WHERE I.B_Code = B.B_Code (+); A table joining to itself is called Self join. In self-join a table appears twice in the FROM clause of the SELECT statement. Example: SELECT First.Author_ID, Second.Author_ID, First.B_Code FROM Written_By First, Written_By Second WHERE First.B_Code = Second.B_Code; Any Questions ??
