Use of simple Oracle functions:

• Use the following functions:

1. chr (n):

Select chr(97) “richa” from dual

2. concat(char1,char2):

Select concat(‘richa’,’goyal’) as name from dual

3. instr(string,char):

Select instr(‘richa’,a) as place from dual

4. length(n):

Select length(‘richa’) from dual

5. lpad(char1 ,n [,char2]):

Select lpad(ename,10,’ ’) from emp

6. ltrim(string [,char(s)]):

Select ltrim(‘richa’,r) from dual

7. rpad(char1 ,n [,char2]):

Select rpad(ename,15,’*’) from emp

8. rtrim(string [,char(s)]):

Select rtrim(‘richa’,’a’) from dual

9. replace(char ,search_string , replacement_string):

Select replace(‘richa’,’a’,’ ’) from dual

10. substr(string ,position ,substring length):

Select substr(‘first’,3,2)

11. initcap(char):

Select initcap(ename) from emp

12. lower(string):

Select lower(dname) from dep

13. upper(string):

Select upper(ename) from emp

14. translate(char ,from string ,to string):

Select ename, translate(ename,’a’,’e’) from emp

15. abs(n):

Select abs(-4) from dual

16. ceil(n):

Select ceil(-33.25) from dual

17. cos(n):

Select cos(30) from dual

18. exp(n):

Select exp(5) from dual

19. floor(n):

Select floor(9.5) from dual

20. mod(m ,n):

Select mod(5,3) from dual

21. power(x ,y):

Select power(5,3) from dual

22. round(x [,y]):

Select round(543.65,-1) from dual

23. sign(n):

Select sign(-5) from dual

24. sqrt(n);

Select sqrt(5) from dual

25. trunc(x ,n):

Select trunc(34.66,-1) from dual

26. sysdate:

Select sysdate from dual

27. add_months(d ,n):

Select add_months(‘2-jul-89’,15) from dual

28. last_day():

Select last_day(‘2-jul-2007’) from dual

29. months_between(date1 ,date2):

Select months_between(‘2-jun-88’,’5-jul-07’) from dual

30. next_day(date ,char):

Select next_day(‘2-jul-88’,’sunday’) from dual

31. greatest(expr):

Select greatest(2,3.4,’-2’) from dual

32. least(expr):

Selcet least(2.4,’-2’) from dual

• Display current time in hour : min : sec format

Select to_char(sysdate,’hh:mi:ss’)

• Display salary + commission of emp table

Select sal + NVL(comm.,0) from emp

• Store any date value in hiredate column of table ?

Insert into emp values(hiredate)=to_date(‘02/07/1988’,’dd/mm/yyyy’)

• Display name of employee(s) who join the company in 1985 ?

Select ename from emp where to_char(hiredate,’year’) like 1985

• Display name of the employee(s) who join the company this year ?

Select ename from emp where to_char(hiredate,’year’)=to_char(sysdate,’year’)