Execute the following single row functions on a Relation • Character Functions o Case-manipulation functions(LOWER, UPPER, INITCAP) o Character-manipulation functions(CONCAT, SUBSTR, LENGTH, INSTR,LPAD | RPAD, TRIM, REPLACE) • Number Functions( ROUND, TRUNC, MOD ) • Date functions o Months_Between o Add_Months o Next_Day o Last_Day o Round o Trunc o Arithmetic with Dates
EXERCISE : 4
AIM : Execute
the following single row functions on a Relation • Character Functions
o
Case-manipulation functions(LOWER, UPPER, INITCAP)
o
Character-manipulation functions(CONCAT, SUBSTR, LENGTH, INSTR,LPAD | RPAD, TRIM, REPLACE)
• Number Functions( ROUND, TRUNC, MOD )
• Date functions
o Months_Between o Add_Months o Next_Day
o Last_Day o Round o Trunc
o Arithmetic with Dates
Description :
a) Character functions :
Case
– manipulation functions ( LOWER , UPPER , INITCAP ) :
1. lower ():
this function converts the uppercase letters to lower case letters what you are
passed to the function.
Syntax:
lower(message)
Example
select lower('SRGEC') as low from dual;
2.upper():
this function is used to convert the lower case letters into uppercase letters.
Syntax:
upper(message)
Example
select
upper('database') as upper1 from dual;
3. initcap():
It
make initial letter to capital letter what you have passed to the function.
Syntax:
initcap(message)
Example
:
select initcap('srgec') from dual;
Character
manipulation functions (CONCAT , SUBSTR , LENGTH , INSTR , LPAD | RPAD , TRIM ,
REPLACE) :
1. lpad(): This
function is used for attaching a new
word to the original one at left side.
Syntax:
lpad(word1,length,word2)
Example:
select
lpad('gec',’6','cse') as lpad1 from dual;
2. rpad(): This function is used
for attaching a new word to the original one at right side.
Syntax:
rpad(word1,length,word2)
Example: select
Rpad('CSE',10,'GEC') from dual;
3.
ltrm():This
function is used for left trimming i.e, it delete(cut) the left most letter.
Syntax:
ltrim('message','character')
Example:
select
ltrim('computerscience','c') as msg from dual;
4.
rtrim()
This
function is used for right trimming.
Syntax:
rtrim('message','character')
Example:
select
rtrim('computerscience','e') as rtrim1 from dual;
5.
concat():This
function is used to add two strings.
Syntax:
Concat(‘string1’
, ‘string 2’)
Example :
select
concat('ABC','DEF') from dual;
6.
Replace
: This
function is used to replace a particular character from a string.
Syntax
:
Replace(‘string’, ‘replaceable char’,char);
Example
:
select replace ('jack and jue','j','bl') from dual;
7.
substring
:This
function is used to extract the substring from a main string .
syntax
:
substr(string , indexing , size);
Example
:
SQL> select substr('srgec is a college',12,7) from
dual;
8.
length
:This
function is used to find the length of a given string.
Syntax
:
Length(string);
Example:
SQL> select length('srgec') from
dual;
9. INSTR():The INSTR() function returns the position
of the first occurrence of a string in another string. This
function performs a case-insensitive search.
Syntax: INSTR(string1, string2)
Example: SQL> SELECT
INSTR('srgec','e') from dual;
Output:
b)
Number
functions (ROUND , TRUNCATE , MOD ) :
1. ROUND :
The ROUND() function rounds a number to a
specified number of decimal places.
SYNTAX :
ROUND(number, decimals, operation)
Example
:
2.
TRUNCATE :
The TRUNCATE() function
truncates a number to the specified number of decimal places.
Syntax :
TRUNCATE(number, decimals)
Example
:
Output
:
3.
MOD
:
The MOD() function returns the remainder of a
number divided by another number.
Syntax
:
MOD(x ,
y)
Example
:
select mod(17,3) from dual;
c)
Date
functions :
Months
_ Between :It gives the number of months between
specified two dates.
|
Result
value |
Months_between(date-exp1,date-exp2) |
|
Negative
result |
If
date-exp1 is earlier than date-exp2 |
|
Integer
result |
If
date-exp1 and date-exp2 have the same day,or both specify the last day of the
month. |
|
Decimal
result |
If
days are different and they are not both specify the last day of the month |
|
Fractional
part |
Always
calcilated as the difference between days divided by 31 despite the number of
days in the month. |
Syntax:
months_between(date1,date2)
Example:
select months_between('28-aug-17','1-jan-17')
as mon from dual;
Add
_Months : This function is used to add the 'n' number of months
to a given date.
Example:
select add_months('28-sep-1997',5) from dual;
Next_Day :
Syntax
:
next_day(date,dayname)
EXAMPLE :
SQL> select sysdate,next_day(sysdate,'monday')
from dual; 
Last _ Day :
It gives the last day of the specified month in a date.
Syntax:
last_date(date)
Example:
select last_day('28-sep-2017') as lastday from dual;
Round
:
The Round() Returns the date rounded by the
specified format unit
Example :
select round(to_date('10-oct-1998'),'MM')
"nearest month" from dual;
Trunc : Truncates
the specified date of its time portion according to the format unit provided.
Example :
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 dates.
·
Add hours to a date by dividing the number
of hours by 24
·
Since the database stores dates as
numbers, you can perform calculations using
arithmetic operators such as addition and subtraction.
You can add and subtract
number constants as well as dates. You can perform the
following operations:
VIVA QUESTIONS
1.
List various Case-manipulation functions
2. Outline the date functions
3. Illustrate Aggregate Functions
Comments
Post a Comment