SQL Usefull Functions

-->SUBSTRING

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'))) FROM emp WHERE SubStr(job,4,3)=Upper('age');

-->LENGTH

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

SELECT InitCap(Ename),job FROM emp 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 emp;

-->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" FROM dual;

--> 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 emp WHERE job='CLERK' GROUP BY deptno HAVING min(sal)<1000;

SELECT deptno,Sum(sal),Min(sal),Max(sal) FROM e GROUP BY deptno HAVING Count(deptno)>=1;

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

--> NESTING OF GROUP FUNCTIONS

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

--> MISCELLANEOUS FUNCTION:

--> GREATEST and LEAST 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;

JOINS

--> CORTITION JOIN

SELECT ename ,dname,loc,e.deptno,empno,sal,grade,losal,mgr FROM dept d,emp e,salgrade s;

--> EQUI JOIN

SELECT ename ,d.deptno, dname, job,loc FROM emp e,dept d WHERE e.deptno=d.deptno;

SELECT ename ,empno,job,dname,loc,e.deptno FROM emp e,dept d WHERE e.deptno=d.deptno AND empno=&eno ;

SELECT ename ,empno,job,dname,loc,e.deptno FROM emp e,dept d WHERE e.deptno=d.deptno AND job=Upper('clerk');

SELECT ename ,empno,job,dname,loc,e.deptno FROM emp e,dept d WHERE e.deptno=d.deptno AND job=Upper(&empno);

--> SEIF JOIN

select e.ename,m.ename,e.mgr,m.mgr from emp e,emp m where e.empno=m.mgr;

--> RIGHT//LEFT OUTR JOIN

select e.ename,d.deptno,e.mgr from emp e,dept d where e.deptno=d.deptno(+);

select e.ename,d.deptno,e.mgr from emp e,dept d where e.deptno(+)=d.deptno;

select e.ename,d.deptno,e.mgr from emp e,dept d where e.deptno=d.deptno(+) ORDER BY deptno;

SELECT e.ename,Nvl(m.ename,'supreme authority') FROM emp e,emp m WHERE e.mgr(+) =m.empno;

--> JOIN MORE THAN ONE TABLES

SELECT e.ename,m.ename "manager name",d.dname "department name",e.sal "Employe Salary",se.grade "Employee Grade",m.sal "Manager Sal",sm.grade "Manager sal"

FROM emp e,dept d,emp m,salgrade se,salgrade sm

WHERE (e.deptno=d.deptno) AND (e.mgr=m.empno) AND (e.sal between se.losal AND se.hisal) AND (m.sal BETWEEN sm.losal AND sm.hisal);

-->JOIN MORE THAN ONE TABLE

SELECT e.ename,m.ename "manager name",d.dname "department name",e.sal "Employe Salary",se.grade "Employee Grade",m.sal "Manager Sal",sm.grade "Manager sal" FROM emp e,dept d,emp m,salgrade se,salgrade sm WHERE (e.deptno=d.deptno) AND (e.mgr=m.empno) AND (e.sal between se.losal AND se.hisal) AND (m.sal BETWEEN sm.losal AND sm.hisal);

-->NATURAL JOIN

SELECT ename,deptno,empno,dname,sal,loc FROM emp NATURAL join dept;

SELECT ename,deptno,empno,sal,grade FROM emp NATURAL join salgrade ;

-->USING CLAUSE

SELECT e.ename,d.dname,deptno FROM emp e join dept d USING(deptno);

-->INNER JOIN

SELECT e.ename,e.deptno,d.dname,loc,sal FROM emp e INNER JOIN dept d ON(d.deptno=e.deptno);

SELECT e.empno,e.ename,m.ename,e.mgr FROM emp e inner join emp m ON(e.mgr=m.empno) ORDER BY e.sal desc;

SELECT e.ename,job,d.deptno,d.dname,e.sal,grade,losal,hisal FROM emp e inner join dept d ON(e.deptno=d.deptno) inner join salgrade ON(e.sal BETWEEN losal AND hisal);

SELECT e.ename,m.ename "manager name",d.dname "department name",e.sal "Employe Salary",se.grade "Employee Grade",m.sal "Manager Sal",sm.grade "Manager sal"

FROM emp e inner join dept d on(e.deptno=d.deptno) inner join emp m on(e.mgr=m.empno) inner join salgrade se on(e.sal between se.losal AND se.hisal) inner join salgrade sm on(m.sal BETWEEN sm.losal AND sm.hisal);

-->LEFT//RIGHT//FULL OUTER JOIN

SELECT e.ename,e.mgr,d.dname,d.deptno FROM emp e LEFT OUTER JOIN dept d ON(e.deptno=d.deptno);

SELECT e.ename,e.mgr,d.dname,d.deptno FROM emp e right OUTER JOIN dept d ON(e.deptno=d.deptno);

SELECT e.ename,e.mgr,d.dname,d.deptno FROM emp e full OUTER JOIN dept d ON(e.deptno=d.deptno);