1 "'
2 ||
3 isql*plus http://127.0.00.1/isqlplus
4 desc author
5 initcap('SQL Course')
INSERT('JellwWord','W') 6
LPAD (salary,10,'*') *****24000
RPAD (salary,10,'*') 24000*****
TRIM ('H' from 'HolloWorld') olloWord
substr('helloword',1,5) hello
substr('helloword',-1,5) oword
6 Number Functions
round(45.926,2) 45.93
round(45.926,-2) 0
round(55.926,-2) 100
trunc(45.926,2) 45.92
mod(1600,300) 100
7 data function
systdate
(sysdate-hire_date)/7 as weeks
months_between number of months between two dates
months_between ('01-sep-95','11,jan-94') 19.6774194
add_months add calendar months to date
add_months('11-JAN-94',6) '11-JUL-94'
next_day next day of the date specified
next_day('01-SEP-95','FRIDAY') '08-SEP-95'
last_day last day of the month
last_day('01-feb-95') '28-feb-95'
round round date
assume sysdate='25-jul-95'
round(sysdate,'month') 01-aug-95
round(sysdate,'year') 01-JAN-96
trunc truncate date
trunc(sysdate,'month') 01-Jul-95
trunc(sysdate,'month') 01-JAN-95
8 Conversion Functions
1) implicit data typ conversion
varchar2 or char ---number
varchar2 or char ---date
numbeer ---varchar2
date ---varchar2
2) to_char(date,'format')
format:
YYYY Full year in numbers
YEAR Year spelled out
MM Two-digit value for month
MONTH Full name of the month
MON THree-letter abbreviation of the month
DY Three-letter abbreviation of the day of the week
DAY Full name of the day of hte week
DD Numberic day of the month
HH24:MI:SS AM 15:45:32:PM
DD "of" MONTH 12 of october
3) to_char function with number
TO_CAHR(number,'format_model')
These are some of the format elements you can use with the to_char function to display number as a character.
9 Reqresents a number
0 Forces a zero to be displayed
$ Places a flationg dollar sign
L Uses the floating local currency symbol
. Prints a decimal point
, Print a thousand director
select to_char(qtym,"$999.99")
4) Using t_number and to _date functions
a converting a character string to a number format using to_number function
to_number(char,"format")l
b converting a character string to a date format
to_date(char,"format")
5 Nesting Functions
.Single-row function can be nested to many level
.Nested function can be evaluated from deepest level
6General Function
These function work with any data type and pertain to using nulls
nvl(expr1,expr2);
nvl2(expr1,expr2,expr3)
nullif(expr1,expr2)
coalesce(expr1,expr2,,,,exprn)
1) nvl function
convert a null to an actual function
a Data type can be used are data character and number
b Data types must match
(set wrap off
set line 1000
)
2)Using the COALESCE Function
a The advantage of the coalesce function over nal function is that coalesce function can take multiple alternative value
b If the first value is not null, it return that expression,otherwise,it does a coalesce of remaining expressions
6 Conditional Expressions
a Provide the use of if-then-else logic
b use two methods: case expression decode function
select last_name,job_id,salary,
case job_id when 'it' then 1*salary
when 'manager' then 1.2*salary
else salary end;
from employee.
select last_namek,job_id,salary,
decode(job_id,'it' ,1*salary,
'manager',1.2*salary,
salary)
from employees