-->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)<1000;
SELECT deptno,Sum(sal),Min(sal),Max(sal) FROM e GROUP BY deptno HAVINGCount(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:
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';
No comments:
Post a Comment