SINGLE ROW FUNCTIOONS

FUNCTIONS:

Function is a Reusable program that returns a value

There are 2 types of functions

1. Pre-defined or built in functions.
2. User defined functions.

• These are used both in SQL and PL/SQL. PL – Procedural Language (it’s a extension to SQL, can contain IF statements, loops, exceptions, OOPs, etc .. )

Functions are very powerful features of SQL and can be used to do the following

     Perform Calculations on data

     Modify individual data items

     Manipulate output for group of rows

     Format dates and number for display

     Converts  column data types

SQL functions are classified into

•       Single row function:

•       Multiple row function:

Single row functions:

•       Manipulate data items

•       Accept arguments  and return one value

•       Act on each row returned

•       Can be nested

•       Accept the arguments which can be column or expression

Single row functions

1. Character Functions:

Character functions are classified into

a)      Case manipulation function

b)      Character manipulation function

a)Case manipulation Functions:

1) Lower

2) Upper

3) INITCAP

Case manipulation Functions:

1. UPPER:

UPPER function converts all letters in the specified string to uppercase. If there are characters in the string that are not letters, they are unaffected by this function.

2. LOWER:

LOWER function converts all letters in the specified string to lowercase.

3. INITCAP:

INITCAP function sets the first character in each word to uppercase and the rest to lowercase.

SQL> select upper('Qspiders'),lower('QSPiders'),initcap('qspiders') from dual;

SQL> select upper(ename),lower(ename),initcap(ename) from emp;

select *

from emp where lower(job) like 'manager'

Dual – is a dummy table which is used for performing some independent operations which will not depend on any of the existing tables.

b)Character Manipulation Functions:

1)CONCAT

2)SUBSTR

3)LENGTH

4)INSTR

5)REPLACE

6) TRIM

Character Manipulation Functions:

CONCAT:  CONCAT function allows you to concatenate two strings together.

SYNTAX:

CONCAT( string1, string2 )

Ex:

SQL> select ename,job, concat(ename,job) from emp;

Concatenate Single Quotes

SELECT CONCAT('Let''s', ' learn Oracle') FROM dual;

1. Write a sql statement to display ‘SMITH is a clerk’

Note: Exactly 2 arguments can be given to concat function.

SUBSTR:

SUBSTR functions allows you to extract a substring from a string.

SYNTAX:

SUBSTR( string, start position [, length ] )

Parameters or Arguments

Ø  string The source string.

Ø  Start position The starting position for extraction.

    The first position in the string is always 1.

Ø  length Optional. It is the number of characters to extract. If this parameter is omitted, the SUBSTR function will return the entire string.

where position and length are both integers.

1. Display 1^st and last character of all the employees.

Length:

Length is used to find the length of the given string.

Syntax: Length(Str)

EX: Select Length(‘Qspiders’) from dual;

Display all the employees whose name has exactly 6 character.

INSTR:

INSTR function returns the location of a substring in a string.

SYNTAX:

INSTR( string, substring [, start_position [, nth_appearance ] ] )

Ex: Instr(‘Qspiders’, ‘s’ , 1 , 1) from dual;

1. Display all the employees whose name start with ‘S’

Replace:

REPLACE function replaces a sequence of characters in a string with another set of characters.

SYNTAX:

REPLACE( string1, string_to_replace [, replacement_string] )

Ex:

SQL> Select Replace('qspiders','s','*') from dual;

1.       Write a sql statement to display the number of spaces present in the given string

.

LTRIM:

LTRIM function removes all specified characters from the left-hand side of a string.

Syntax: LTRIM( string1 [, trim_string] )

RTRIM:

RTRIM function removes all specified characters from the right-hand side of a string.

Syntax: RTRIM( string1 [, trim_string] )

TRIM:  Is used to remove the all specified characters in the given string.

TRIM( [ [ LEADING | TRAILING | BOTH ] trim_character FROM ] string1 )

Ex:  select trim(leading 'e' from 'eewelcome') from dual

2. NUMBER FUNCTIONS:

Round: Round the Value to specified decimal

Ex: Round(23.34)à  23

Trunc:  Truncate the values to specified decimal.

Ex: Trunc(34.6554) à 34

Mod: Returns Reminder of division.

Ex: Mod(10,3)à 1

Ex:

SQL> SELECT * FROM EMP WHERE MOD(SAL,2)=1;

ABS Function:

ABS function returns the absolute value of a number.

Syntax:

ABS( number )

SQRT Function

SQRT function returns the square root of n.

Syntax:

SQRT( n )

POWER Function

POWER function returns m raised to the nth power.

Syntax:

POWER( m, n )

SIGN Function

SIGN function returns a value indicating the sign of a number

Syntax:

SIGN( number )

Note:

If number < 0, then sign returns -1.

If number = 0, then sign returns 0.

If number > 0, then sign returns 1.

Working with Dates:

ü  Oracle database stores dates in an internal numeric format:

     Century, Year, Month, day, hours, minutes, Seconds.

ü  The default date display format is DD-MON-YY

Ex: Select ename, hiredate from emp

•       Sysdate: It is a function that returns date

•       Systimestamp: It is a function that returns date,  time including milliseconds and time zone

Arithmetic With dates:

•       Add or subtract a number to or from a date for a resultant date value

•       Subtract two dates to find the number of days between those date.

Ex : To get future or past date

Select sysdate+100 , sysdate-70 from dual;

Ex: Experience with years;

Select ename,hiredate, round((sysdate-hiredate)/365) from emp;

ADD_MONTHS(date, n)

Adds the specific number of months (n) to a date. The ‘n’ can be both negative and positive:

Select add_months(sysdate, -1) as prev_month , sysdate, add_months (sysdate, 1) as next_month

from dual;

LAST_DAY(date) – Returns the last day in the month of the specified date.

select sysdate, last_day(sysdate) as last_day_curr_month,

last_day(sysdate) + 1 as first_day_next_month

from dual;

The number of days until the end of the month.

select last_day(sysdate) - sysdate as days_left

from dual

MONTHS_BETWEEN(date, date) – Calculates the number of months between two dates.

Example:

select MONTHS_BETWEEN ('31-MAR-2016', '28-FEB-2015')

from dual;

Let’s select the number of months an employee has worked for the company.

 Select months_between (sysdate, HIREDATE)

 from emp

GENERAL FUNCTION:

1> NVL(arg1,arg2)

•       If agr1 is null it returns arg2

•       If arg1 is not null then it returns itself

Converts a null to actual values:

Ex:

1.select ename,sal,comm,nvl(comm,0) from emp

2. SQL> select ename,sal,comm,sal+comm, sal+nvl(comm,0) totalsalary from emp;

NVL2:

SYNTAX:

NVL2(ARG1,AGR2,ARG3)

If Arg1 is null it returns Arg3

If Agr1 is not null then returns Agr2

Ex: SELECT ENAME,SAL, NVL2(COMM,COMM+SAL,SAL) "TOTAL SALARY" FROM EMP;

Conversion Functions:

Conversion function s are used to convert one data type into another data type

There are two type of conversion Functions

1. Implicit conversion Function

Ex: Select 10+’20’ from dual;

2. Explicit conversion function

Ex: Select 10+Ascii(‘A’) from dual;

TO_CHAR: TO_CHAR function is used to typecast a numeric or date input to character type 

TO_NUMBER: The TO_DATE function converts the characters to a date data type

TO_DATE: The TO_NUMBER function converts a character value to a numeric datatype. If the string being converted contains nonnumeric characters, the function returns an error.

Using TO_CHAR function with dates

TO_CHAR(date, ‘Format model’)

The format model:

•       It must enclosed with single quotation mark.

•       Is separated from the date value of a comma

Element of date format:

YYYYà Full year in number

YEARà year spelled out

MMà Two digit value for month

MONTHà Full name of month

MONà Three letter abbreviation of month

DYà Three letter abbreviation of day

DAYà Full name of day

DDà Numeric day of month

Ex: select sysdate,to_char(sysdate,'yyyy year mm month mon dy day dd') from dual

Display the joined day of all the employees?

SELECT CONCAT(ENAME,CONCAT(' JOINED ON ',TO_CHAR(HIREDATE,'DAY'))) FROM EMP

/

Display all the employees who joined on the weekends?

 Select ename,hiredate,to_char(hiredate,'day')

 from emp

 where to_char(hiredate,'DY') IN('SUN','SAT');