Oracle 10g Database Functions and Queries

-->SUBSTRING

--> GREATEST and LEAST FUNCTION:-

SELECT SubStr('Shiva_Rama_Krishna',0) FROM Dual;

SELECT SubStr('Shiva_Rama_Krishna',11) FROM Dual;

SELECT SubStr('Shiva_Rama_Krishna',122) FROM Dual;

SELECT SubStr('Shiva_Rama_Krishna',3,11) FROM Dual;

SELECT SubStr('Shiva_Rama_Krishna',9,-1) FROM Dual;

SELECT SubStr('Shiva_Rama_Krishna',-4,6) FROM Dual;

SELECT ename,job,substr(job,6) FROM emp WHERE substr(job,4,2)=upper('es');

SELECT Concat(InitCap(ename),Concat(' is a ',Concat(SubStr(job,1,3),' Eater'))) FROMemp WHERE SubStr(job,4,3)=Upper('age');

-->LENGTH

SELECT Length('Raju.chinthapatla') "My NAME Length is" FROM Dual;

SELECT InitCap(Ename),job FROM e WHERE SubStr(job,4,length(SubStr(job,4,3)))=Upper('age');

-->INSTRING

SELECT InStr('Shiva-Rama-Krishma','a',1,1) from Dual;

SELECT InStr(job,'A',3,1) FROM emp WHERE job=Upper('manager');

SELECT InStr(job,'A',3) FROM emp WHERE job=Upper('manager');

SELECT InStr(job,'A') FROM emp WHERE job=Upper('manager');

-->RPAD & LPAD

SELECT RPad(LPad('Raju.ch',10,'*'),13,'*') FROM Dual;

SELECT lPad(rPad('Raju.ch',15,'*'),23,'*') FROM Dual;

SELECT LPad('Raju.Ch',20) FROM Dual;

SELECT rPad('Raju.Ch',3) FROM Dual;

-->RTRIM & LTRIM & TRIM

SELECT LTrim('Ch.Raju','Ch.') FROM Dual;

SELECT RTrim('Raju.Ch','.ch') FROM Dual;

SELECT LTrim('raju','') FROM Dual;

SELECT Trim('s' FROM 'smisths') FROM Dual;

-->POWER

SELECT Power(5,4) FROM Dual;

SELECT Power(5,-4) FROM Dual;

-->SQROOT

SELECT sqrt(100) FROM Dual;

-->ABSOLUTE

SELECT abs(-100) FROM Dual;

SELECT sal,comm,sal-comm,Abs(sal-comm) FROM e;

-->SIGN

SELECT Sign(-100), Sign(100), Sign(0) FROM Dual;

-->ROUND

SELECT SYSDATE,SYSDATE+10/2 FROM Dual;

SELECT ename,hiredate,Round((SYSDATE-hiredate)/365) FROM e;

-->ADD_MONTHS

SELECT SYSDATE,Add_Months(SYSDATE,1) FROM Dual;

-->MONTHS_BETWEEN

SELECT ename,SYSDATE,hiredate,round(Months_Between(SYSDATE,hiredate)/12) " Years Of Experience" FROM e;

-->NEXT_DAY

SELECT SYSDATE,Next_Day(SYSDATE,'TUE') FROM Dual;

--> LAST_DAY <--

SELECT SYSDATE,Add_Months(Last_Day(SYSDATE),-1)+1 FROM Dual;

-->TRUNC

SELECT Round(sysdate,'YY'),Trunc(SYSDATE,'MM') FROM Dual;

--> T0_CHAR NUMBER CONVERTION <--

--Digit Maker

SELECT 12.34,12,To_Char(12.34-12,'009900'),To_Char(12-12.34,'009900') FROM Dual;

SELECT 12.55,12,To_Char(12.55-12,'009900'),To_Char(12-12.55,'009900') FROM Dual;

--> T0_CHAR DATE CONVERTION <--

SELECT 123456789,

To_Char(123456789,'L99G99G99G999D99')"Currency,GROUP,Decimal",

To_Char(123456789,'$99,99,99,999.99')"Currency,GROUP,DECIMAL SYMBOLS",

To_Char(123456789,'xxxxxxxxx')"HexaDecimals",

To_Char(000056789,'000099999')"Zero Indicator"

FROM Dual;

SELECT comm,sal,comm-sal,

To_Char(comm-sal,'99,999.99mi')," MINUS "

To_Char(comm-sal,'99,999.99pr')"Nagative"

FROM e;

-->SIGN

SELECT comm,sal,comm-sal,To_Char(comm-sal,'s99,999.99') FROM e;

--Digit Maker

SELECT 12.34,12,To_Char(12.34-12,'009900'),To_Char(12-12.34,'009900') FROM Dual;

SELECT 12.55,12,To_Char(12.55-12,'009900'),To_Char(12-12.55,'009900') FROM Dual;

--> T0_CHAR DATE CONVERTION <--

-->A.D. OR B.C.//AD OR BC

SELECT hiredate,

To_Char(hiredate,'b.c.')"Before Crist",

To_Char(hiredate,'ad')"After Death",

To_Char(hiredate,'a.d.')"After Death",

To_Char(sysdate,'AM')"AM",

To_Char(sysdate,'PM')"PM"

FROM e;

SELECT sysdate,

To_Char(sysdate,'cc-ad')"CENTURY",

To_Char(sysdate,'d')"DAy IN WEEK",

To_Char(sysdate,'dd')"DAY IN MONTH",

To_Char(sysdate,'ddd')"DAY IN YEAR",

To_Char(sysdate,'dddd')"DAY In YEAR and IN WEEK",

To_Char(sysdate,'DY')"DAY SPELL(sun)",

To_Char(sysdate,'DAY')"DAY SPELL",

To_Char(sysdate,'W')"WEEK OF THIS MONTH",

To_Char(sysdate,'IW')"(WI)WEEK OF THIS YEAR",

To_Char(sysdate,'WW')"(WW)WEEK OF THIS YEAR",

To_Char(sysdate,'WWW')"WEEK OF THIS YEAR AND MONTH",

To_Char(sysdate,'MM')"MONTH NO",

To_Char(sysdate,'MON')"MONTH SPELL(jan)",

To_Char(sysdate,'MONTH')"MONTH SPELL",

To_Char(sysdate,'y')"LAST DIGIT OF THE YEAR",

To_Char(sysdate,'yy')"LAST 2 DIGIT OF THE YEAR",

To_Char(sysdate,'yyy')"LAST 3 DIGIT OF THE YEAR",

To_Char(sysdate,'yyyy')"4 DIGIT OF THE YEAR",

To_Char(sysdate,'YYYYSP')"YEAR SPELL",

To_Char(sysdate,'YEAR')"YEAR SPELL",

To_Char(sysdate,'DD-MM-YYYY')"DATE",

To_Char(sysdate,'DD-MON-YYYY')"DATE",

To_Char(sysdate,'DD-RM-YYYY')"ROMAN NUMBER",

To_Char(sysdate,'Q')"QUARTER OF THE YEAR",

To_Char(sysdate,'J')"JULLIAN",

To_Char(sysdate,'HH-MI-SS:AM')"12 HOURS TIME",

To_Char(sysdate,'HH24-MI-SS:AM')"24 HOURS TIME",

To_Char(sysdate,'DDth-DDthsp')"DAY NUMBER SPELL",

To_Char(SYSDATE,'DDthsp-MONTH-YYYYsp')"DATE SPELL",

To_Char(SYSDATE,'fmDDthsp-MONTH-YYYYsp')"REMOVE THE BLANK SPASES"

FROM Dual;

SELECT sysdate,To_Char(sysdate,'cc-ad')"CENTURY" FROM Dual;

SELECT sysdate,To_Char(sysdate,'d')"DAy IN WEEK" FROM Dual;

SELECT sysdate,To_Char(sysdate,'dd')"DAY IN MONTH" FROM Dual;

SELECT sysdate,To_Char(sysdate,'ddd')"DAY IN Y EAR" FROM Dual;

SELECT sysdate,To_Char(sysdate,'dddd')"DAY In YEAR and IN WEEK" FROM Dual;

SELECT sysdate,To_Char(sysdate,'DY')"DAY SPELL(sun)" FROM Dual;

SELECT sysdate,To_Char(sysdate,'DAY')"DAY SPELL" FROM Dual;

SELECT sysdate,To_Char(sysdate,'W')"WEEK OF THIS MONTH" FROM Dual;

SELECT sysdate,To_Char(sysdate,'IW')"(WI)WEEK OF THIS YEAR" FROM Dual;

SELECT sysdate,To_Char(sysdate,'WW')"(WW)WEEK OF THIS YEAR" FROM Dual;

SELECT sysdate,To_Char(sysdate,'WWW')"WEEK OF THIS YEAR AND MONTH" FROM Dual;

SELECT sysdate,To_Char(sysdate,'MM')"MONTH NO" FROM Dual;

SELECT sysdate,To_Char(sysdate,'MON')"MONTH SPELL(jan)" FROM Dual;

SELECT sysdate,To_Char(sysdate,'MONTH')"MONTH SPELL" FROM Dual;

SELECT sysdate,To_Char(sysdate,'y')"LAST DIGIT OF THE YEAR" FROM Dual;

SELECT sysdate,To_Char(sysdate,'yy')"LAST 2 DIGIT OF THE YEAR" FROM Dual;

SELECT sysdate,To_Char(sysdate,'yyy')"LAST 3 DIGIT OF THE YEAR" FROM Dual;

SELECT sysdate,To_Char(sysdate,'yyyy')"4 DIGIT OF THE YEAR" FROM Dual;

SELECT sysdate,To_Char(sysdate,'YYYYSP')"YEAR SPELL" FROM Dual;

SELECT sysdate,To_Char(sysdate,'YEAR')"YEAR SPELL" FROM Dual;

SELECT sysdate,To_Char(sysdate,'DD-MM-YYYY')"DATE" FROM Dual;

SELECT sysdate,To_Char(sysdate,'DD-MON-YYYY')"DATE" FROM Dual;

SELECT sysdate,To_Char(sysdate,'DD-RM-YYYY')"ROMAN NUMBER" FROM Dual;

SELECT sysdate,To_Char(sysdate,'Q')"QUARTER OF THE YEAR" FROM Dual;

SELECT sysdate,To_Char(sysdate,'J')"JULLIAN" FROM Dual;

SELECT sysdate,To_Char(sysdate,'HH-MI-SS:AM')"12 HOURS TIME" FROM Dual;

SELECT sysdate,To_Char(sysdate,'HH24-MI-SS:AM')"24 HOURS TIME" FROM Dual;

SELECT sysdate,To_Char(sysdate,'DDth-DDthsp')"DAY NUMBER SPELL" FROM Dual;

SELECT sysdate,To_Char(SYSDATE,'DDthsp-MONTH-YYYYsp')"DATE SPELL" FROM Dual;

SELECT sysdate,To_Char(SYSDATE,'fmDDthsp-MONTH-YYYYsp')"REMOVE THE BLANK SPASES" FROMDual;

--> TRANSLATE ONE CHARACTER TO ANOTHER CHARACTER

SELECT'raju.chinthapatla',translate('raju.chinthapatla','abcdefghijklmnopqrstuvwxyz.','1234567890!@#$%^&*()-=_+:,<>')" Encrypted NAME " FROM Dual;

SELECT '*10-.389$)81^1)@1'" Encrypted NAME ",Translate('*10-<389$)81^1)@1','1234567890!@#$%^&*()-=_+:,<>','abcdefghijklmnopqrstuvwxyz.')" Encrypted NAME " FROM Dual;

--> HAVING CLAUS

SELECT deptno,Sum(sal),Min(sal),Max(sal) FROM e WHERE job='CLERK' GROUP BY deptno HAVINGmin(sal)&lt;1000;

SELECT deptno,Sum(sal),Min(sal),Max(sal) FROM e GROUP BY deptno HAVINGCount(deptno)&gt;=1;

SELECT deptno,Count(deptno) FROM e GROUP BY deptno HAVING Count(deptno)&gt;3;

--> NESTING OF GROUP FUNCTIONS

SELECT sum(min(sal)) FROM e GROUP BY sal;

--> MISCELLANEOUS FUNCTION:

SELECT greatest('D','f','e','f','d','e','f','V','h','V','r') from Dual;

SELECT least('D','f','e','f','d','e','f','V','h','V','r') from Dual;

SELECT USER,UID FROM Dual;

SELECT username,user_id FROM all_users;

SELECT UserEnv('isdba') FROM Dual;

SELECT UserEnv('language') FROM Dual;

SELECT UserEnv('terminal') FROM Dual;

SELECT UserEnv('sessionid') FROM Dual;

SELECT UserEnv('lang') FROM Dual;

SELECT UserEnv('instance') FROM Dual;

SELECT INSTANCE_name FROM v$instance;

CREATE TABLE test(NAME VARCHAR2(20),dpt NUMBER(2));

insert INTO test VALUES('raj',10);

insert INTO test VALUES('raju',20);

insert INTO test VALUES('rajkumar',30);

select * FROM test;

SELECT NAME,VSize(NAME) FROM test;

UPDATE test SET NAME='ALLEN' WHERE NAME='SMITH';

UPDATE test SET NAME='ALLEN' WHERE dpt=10;

ALTER TABLE test MODIFY NAME CHAR(20);

ALTER TABLE test MODIFY NAME varCHAR(20);

DELETE test WHERE NAME='raju';