Oracle SQL Queries

ORACLE STANDS FOR ----- Oakridge Relational Analytical Computing & Logical

1) LAST 3 DAYS TRANSACTIONS :

select vdate,visit,vdate-3,(select visit from hotel where vdate=to_date('15-jan-12')-3) visit from hotel where vdate='15-jan-12';

(OR)

select ROWNUM,EMPNO,HIREDATE from (select rownum,EMPNO,HIREDATE from EMP order by HIREdate desc)

where rownum<=3

2) WAY TO DROP MULTIPLE TABLES AT A TIME:

BEGIN

FOR I IN (SELECT TABLE_NAME FROM USER)TABLES)

LOOP

EXECUTE IMMEDIATE ('DROP TABLE' ||USER||'.'

I.TABLE_NAME||'CASCADE CONSTRAINTS')

END LOOP;

END;

3)  DISPLAY LAST 6MONTHS TRANSACTIONS:

SELECT EMPNO,ENAME.HIREDATE,MONTHS_BETWEEN(SYSDATE,ADD_MONTHS(SYSDATE,-6)) FROM EMP;

4)  WAY TO FIND CHILD TABLE CONSTRAINTS:

SELECT TABLE_NAME FROM USER_CONSTRAINTS WHERE R_CONSTRAINT_NAME IN (SELECT CONSTRAINT_NAME FROM USER_CONSTRAINTS WHERE TABLE_NAME='DEPT');

5)  WAY TO DIVIDE THE NAME INTO 3 PARTS:

SELECT NAME,SUBSTR(NAME,1,INSTR(NAME,' ',1,1)-1) F_NAME,

SUBSTR(NAME,INSTR(NAME,' ',1,1)+1,INSTR(NAME,' ',1,2)-INSTR(NAME,' ',1,1)-1) M_NAME,

SUBSTR(NAME,INSTR(NAME,' ',1,2)+1) L_NAME FROM TABLE_NAME;

6)  DECODE FORMAT:

SELECT SNO,ENAME, DECODE(DEPTNO,10,'ORACLE',20,'JAVA',30,'DOTNET',DNO) “DNAME” FROM EMP;

7)  RETRIVING PARTICULAR RECORE THROUGH ROW NUMBER:

SELECT ROWNUM,EMPNO FROM EMP GROUP BY ROWNUM,EMPNO HAVING ROWNUM=&N;

OR

SELECT * FROM EMP WHERE ROWNUM < HIGH LEVEL

MINUS

SELECT *FROM EMP WHERE ROWNUM < LOW LEVEL;

8)  RETIRIVNG EVEN OR ODD RECORDS:

A)  EVEN RECORDS:

SELECT ROWNUM,EMPNO FROM EMP GROUP BY ROWNUM,EMPNO HAVING MOD(ROWNUM,2)=0;

B)  ODD RECORDS:

SELECT ROWNUM,EMPNO FROM EMP GROUP BY ROWNUM,EMPNO HAVING MOD(ROWNUM,2)=1;

9)  WAY TO RETRIVE NON-DUPLICATE RECORDS:

SELECT DISTINCT GRADE,HISAL,LOSAL FROM SALGRADE WHERE EXISTS (SELECT COUNT(*) FROM SALGRADE WHERE GRADE = SALGRADE.GRADE HAVING COUNT(*)>1;

10) RETRIVING DUPLICATE RECORDS:

SELECT DISTINCT GRADE,HISAL,LOSAL FROM SALGRADE WHERE EXISTS (SELECT COUNT(*) FROM SALGRADE WHERE GRADE = SALGRADE.GRADE HAVING COUNT(*)=1;

11) RETRIVING 2ND MAX(SAL) WHEN 2PEOPLE GETTING SAME SAL:

SELECT *FROM EMP WHERE &N-1 = (SELECT COUNT(DISTINCT(SAL)) FROM EMP B WHERE EMP.SAL<B.SAL;

12)  RETRIVING MAX(SAL) -- DEPTNO WISE:

SELECT DEPTNO,MAX(SAL) FROM EMP GROUP BY DEPTNO;

13)  RETRIVING MAX(SAL) – JOB WISE;

SELECT ENAME,SAL FROM EMP WHERE SAL IN (SELECT MAX(SAL) FROM EMP GROUP BY JOB);

14)  RETRIVING LAST 5 RECORDS:

SELECT * FROM (SELECT ROWNUM R,EMPNO,ENAME FROM EMP) WHERE R> (SELECT MAX(ROWNUM)-5 FROM EMP);

15) WAY TO CREATE A NEW TABLE BY COPYING RANDOM RECORDS FROM ANOTHER TABLE:

SELECT ROWNUM,EMPNO,ENAME FROM XXAA GROUP BY ROWNUM,EMPNO,ENAME HAVING ROWNUM IN (1,3,7,9,14) ORDER BY ROWNUM;

16)  RETRIVING THE VALUE THAT MOST OCCURS IN THE COLUMN:

SELECT JOB,COUNT(*) FROM EMP GROUP BY JOB HAVING COUNT(*) >= ALL(SELECT COUNT(*) FROM EMP GROUP BY JOB);

17)  WAY TO CREATE LOG IN & LOG OFF SESSIONS AUTOMATICALLY:

CREATE TABLE SESSION_INFO (USERNAME VARCHAR2(20),

LOGONDATE DATE,

SESSION_ID VARCHAR2(20),

IP_ADDR VARCHAR2(20),

HOST_NAME VARCHAR2(20),

AUTH_TYPE VARCHAR2(20));

-- RELATED TRIGGER:

CREATE OR REPLACE TRIGGER TRG_SESSION_INFO

BEFORE LOGOFF ON DATABASE

DECLARE

SESSION_ID VARCHAR2(20);

IP_ADDR VARCHAR2(20);

HOST_NAME VARCHAR2(20);

AUTH_TYPE VARCHAR2(20);

BEGIN

SELECT SYS_CONTEXT('USERENV','SESSIONID') INTO SESSION_ID FROM

DUAL;

SELECT SYS_CONTEXT('USERENV','IP_ADDR') INTO IP_ADDR FROM DUAL; SELECT SYS_CONTEXT('USERENV','HOST') INTO HOST_NAME FROM DUAL;

SELECT SYS_CONTEXT('USERENV','AUTHENTICATION_TYPE') INTO

AUTH_TYPE FROM DUAL;

INSERT INTO SESSION_INFO

VALUES('USER',SYSDATE,SESSION_ID,IP_ADDR,HOST_NAME,AUTH_TYPE);

END;

-- TO CHECK:

SELECT *FROM SESSION_INFO;

18) FINDING DAYS & MONTHS FROM DOB:

SELECT (SYSDATE-TO_DATE('09-APL-1990')) DAT TO SECOND FRON DUAL;

19) FINDIND THE LETTERS STARTING WITH “H” & “P”:

SELECT *FROM XXAA WHERE SUBSTR(ENAME,1,INSTR(ENAME,' ',1,1)-1) LIKE 'H%' AND SUBSTR(ENAME,INSTR(ENAME,' ',1,1)+1) LIKE 'P%';

20) GENERATING MATHEMATICS TABLE:

DECLARE

V_NUM NUMBER:=&ENTERNUMBER;

V_RESULT NUMBER;

BEGIN

FOR I IN 1 .. 10 LOOP

V_RESULT:=I*V_NUM;

DBMS_OUTPUT.PUT_LINE(V_NUM||'*'||I||'='||V_RESULT);

END LOOP;

END;

21) DISPLAYING HIGHEST SAL & ALL OTHER SAL'S IN DECENDING ORDER:

SELECT *FROM (SELECT *FROM EMP ORDER BY SAL DESC) WHERE ROWNUM=1

UNION ALL

SELECT * FROM (SELECT * FROM EMP ORDER BY SAL) WHERE ROWNUM <> (SELECT MAX(ROWNUM) FROM EMP);

22) DISPLAY YESTERDAY FOR ALL HIREDATE COLUMN IN THE TABLE:

SELECT HIREDATE,NEW_TIME(HIREDATE,'EST','PDT') FROM EMP;

(OR)

Select hiredate,hiredat-1 from emp;

23) DISPLAY DATE IN WORDS:

SELECT SYSDATE,TO_CHAR(SYSDATE,'DDSP MONTH YEAR') FROM DUAL;

24) DISPLAY NUMBER IN WORDS:

SELECT SAL,(TO_CHAR(TO_DATE(SAL,'J'),'JSP')) FROM EMP;

25) DISPLAY NTH MAX/MIN/AVG SAL – DEPTNO WISE:

SELECT DEPTNO,SAL,RN FROM(SELECT DEPTNO,SAL,ROW_NUMBER() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) RN FROM EMP ORDER BU SAL DESC) WHERE RN = &RN;

26) 1ST MAX(SAL) IN DNO=10 & 2ND IN 20 & 3RD IN 30:

SELECT DEPTNO,SA;,RN FROM (SELECT DEPTNO,SAL,DENSE_RANK() OVER (PARTITON BY DEPTNO ORDER BY SAL DESC) RN FROM EMP) WHERE RN = 1 AND DEPTNO = 10

UNION ALL

SELECT DEPTNO,SA;,RN FROM (SELECT DEPTNO,SAL,DENSE_RANK() OVER (PARTITON BY DEPTNO ORDER BY SAL DESC) RN FROM EMP) WHERE RN = 2 AND DEPTNO = 20

UNION ALL

SELECT DEPTNO,SA;,RN FROM (SELECT DEPTNO,SAL,DENSE_RANK() OVER (PARTITON BY DEPTNO ORDER BY SAL DESC) RN FROM EMP) WHERE RN = 3 AND DEPTNO = 30

-- RELATED QUERY:

SELECT DEPTNO,ENAME,EMPNO,ROW_NUM() OVER (PARTITION BY DEPTNO ORDER BY EMPNO) AS OUTPUT FROM EMP;

28)  LAST 5 MIN(SAL) RECORDS:

SELECT DISTINCT SAL FROM EMP E WHERE 5> = (SELECT COUNT(DISTINCT SAL) FROM EMP 1 WHERE A.SAL<=E.SAL) ORDER BY SAL DESC;

29)  WAY TO FIND NTH,2ND ,3RD …….. NTH MAX(SAL):

SELECT SAL FROM (SELECT ROWNUM AS SALINDEX,SAL FROM (SELECT DISTINCT SAL FROM EMP ORDER BY SAL DESC) WHERE SALINDEX = &N;

(OR)

SELECT DISTINCT SAL FROM EMP E1 WHERE &N-1 = (SELECT COUNT(DISTINCT(E2.SAL)) FROM EMP E2 WHERE E2.SAL>E1.SAL)

30)  TOP 2 SAL:

SELECT DISTINCT TOP 2 SAL FROM EMP WHERE SAL NOT IN (SELECT DISTINCT TOP 1 SAL FROM EMP ORDER BY SAL DESC) ORDER BU EMP.SAL DESC;

31)  NTH MAX(SAL) THROUGH NESTED QUERY:

SELECT SAL,ENAME,EMPNO,DEPTNO FROM (SELECT SAL,ENAME,EMPNO,DEPTNO FROM EMP WHERE SAL IN (SELECT MAX(SAL) FROM EMP));

(OR)

SELECT * FROM EMP A WHERE &N = (SELECT COUNT(DISTINCT (B.SAL)) FROM EMP B WHERE A.SAL<=B.SAL);

32)  CREATING NEW TABLE WITH EVEN & ODD RECORDS:

-- WITH EVEN RECORDS:

CREATE TABLE EMP1 AS SELECT * FROM EMP WHERE (ROWID,1) IN (SELECT ROWID,MOD(ROWNUM,2) FROM EMP);

-- WITH ODD RECORDS:

CREATE TABLE EMP2 AS SELECT * FROM EMP WHERE (ROWID,0) IN (SELECT ROWID,MOD(ROWNUM,2) FROM EMP);

33)  DISPLAY MOBILE NUMBER IN DIFFERENT FORMAT:

SELECT MBLNUMBER “OLD FORMAT”,

REGEXE_LIKE(MBLNUMBER,'([[:DIGIT:]]{3})\.([[:DIGIT:]]{3}\.([[:DIGIT:]] {4})','(\1)\2-\3') “NEW FORMAT” FROM EMP;

34)  WAY TO REMOVE DUPLICATE RECORDS FROM TABLE:

DELETE EMP WHERE ROWID NOT IN (SELECT MIN(ROWID) FROM EMP GROUP BY EMPNO);

35)  RETRIVING ONLY DUPLICATE RECORDS:

SELECT DISTINCT EMPNO,ENAME,JOB,SAL,DEPTNO FROM EMP E WHERE EXISTS (SELECT COUNT(*) FROM EMP WHERE EMPNO=E.EMPNO HAVING COUNT(*) > 1);

36)  RETRIVING THE NON-DUPLICATE RECORDS:

SELECT DISTINCT EMPNO,ENAME,JOB,SAL,DEPTNO FROM EMP E WHERE EXISTS (SELECT COUNT(*) FROM EMP WHERE EMPNO=E.EMPNO HAVING COUNT(*)=1;

37)  EXCEPT 1ST RECORD , I WANT TO RETRIVE ALL OTHER RECORDS:

SELECT * FROM EMP WHERE ROWID NOT IN (SELECT MAX(ROWID) FROM EMP;

38)  EXCEPT LAST RECORD , I WANT TO RETRIVE ALL OTHER RECORDS:

SELECT * FROM EMP WHERE ROWID NOT NOT IN (SELECT MIN(ROWID) FROM EMP;

39)  RETRIVING ONLY TOP 5 HIGHLY PAID EMPLOYEE'S:

SELECT ROWNUM,EMPNO FROM (SELECT ROWNUM,EMPNO FROM EMP ORDER BY SAL DESC) WHERE ROWNUM<=5;

40)  RETRIVING NTH MAX(SAL) BY USING LEVEL:

SELECT LEVEL,MAX(SAL) FROM EMP WHERE LEVEL = &N CONNECT BY PRIOR SAL>SAL GROUP BY LEVEL;

41) RETRIVING EMPLOYEE POSITION DEPENDING UPON HIS SUPERIORITY:

SELECT LEVEL,EMPNO,JOB,SAL FROM EMP CONNECT BY PRIOR EMPNO=MGR START WITH MGR IS NULL ORDER BY LEVEL;

-- RELATED QUERY:

SELECT A.ENAME,B.SAL,A.COMM.B.DEPTNO FROM XXAA A,XXAA B WHERE A.DEPTNO=B.DEPTNO AND INSTR(A.SAL,B.DEPTNO)>0;

43) i need the sql/plsql that will return all months from last 12 months of the sysdate. i.e  if current monthh is FEB then result should be march 08, april 08, jun 08, …, feb 09

DECLARE

today DATE := SYSDATE; BEGIN

FOR i IN 1 .. 12 LOOP IF i = 1 THEN

DBMS_OUTPUT.put_line(SYSDATE);

ELSE

DBMS_OUTPUT.put_line(add_months(SYSDATE,-i));

END IF;

END LOOP; END;

(44)  DECLARE

today DATE:= SYSDATE; BEGIN

FOR i IN 1 .. 11 LOOP IF i = 0 THEN

DBMS_OUTPUT.put_line('SYSDATE'); ELSE

DBMS_OUTPUT.put_line(add_months(SYSDATE,i)); END IF;

END LOOP;

dbms_output.put_line(add_months(sysdate+1,12));

END;

45) The following query returns the last name for those employees with a double vowel in their last name (where last_name contains two adjacent occurrences of either a, e, i, o, or u, regardless of case):

SELECT last_name

FROM employees

WHERE REGEXP_LIKE (last_name, '([aeiou])\1', 'i');

46) WAY TO RETRIVE THE RECORD DEPENDING ON CHAR VALUES:

Select chr(99),EMP.* FROM EMP WHERE SUBSTR(ENAME,1,1)=CHR(99);

49) Way to find the common columns in between two tables:

Select column_name from user_tab_column where table_name='EMP'

Intersect

Select column_name from user_tab_column where table_name='DEPT';

50) WAY TO DISPLAY NAME IN VERTICAL AXIS WAY:

declare

name varchar2(20); begin name:='&NAME';

for i in 1..length(name) loop dbms_output.put_line(substr(name,1,1)); name:=substr(name,2);

end loop; end;