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
??