Important Query

TO GET THE DATA_LENGTH AND DATA_TYPE OF TABLE

>  SELECT  COLUMN_NAME, DATA_TYPE, DATA_LENGTH, DATA_PRECISION, DATA_SCALE FROM USER_TAB_COLUMNS WHERE COLUMN_NAME='INTERCHANGE' AND TABLE_NAME='EMI_TRAN_DETAIL';

 > SELECT * FROM USER_OBJECTS
WHERE OBJECT_NAME = 'APP_MAST_CURRENCYUPDATE';



 SELECT DBMS_METADATA.GET_DDL('VIEW','APP_MAST_CURRENCYUPDATE') FROM DUAL;


CONVERT FROM TIMESTAMP TO DATE FORMAT

> SELECT TO_DATE(TO_CHAR (SYSTIMESTAMP, 'YYYY-MON-DD HH24:MI:SS'),'YYYY-MON-DD HH24:MI:SS') AS MY_DATE FROM DUAL;


CONVERT FROM VARCHAR2 TO DATE FORMAT

>SELECT TO_DATE(LOG_DATETIME, 'DDMMYYYYHH24MISS') FROM app_switchin;



DIFFERENCES OF THE COLUMN

>SELECT COLUMN_NAME FROM USER_TAB_COLS WHERE TABLE_NAME='EMP'
MINUS
SELECT COLUMN_NAME FROM USER_TAB_COLS WHERE TABLE_NAME='EMP1';



CONCAT QUERY FOR SELECT STATEMENT

> SELECT  EMPNO||'

        '||      ENAME   ||'

        '||       SAL    ||'

        '||      DEPTNO  AS EMPLOYEE FROM EMP;

>SELECT 'EMPNO ='|| EMPNO ||',

        ENAME ='|| ENAME ||',

        SAL   ='|| SAL   ||', ,

        DEPTNO='|| DEPTNO  AS EMPLOYEE FROM EMP;  

PASS THE DBMS_OUTPUT.PUT_LINE ARGUMENT

> DBMS_OUTPUT.PUT_LINE('v_id = ' || v_id || ', v_name = ' || v_name ||', v_salary = ' || v_salary);


ALTER THE TYPE IN TABLE 

> ALTER TABLE EMP ADD ADRESS ADDRESS_TYPE;

 REPLACE THE SECOND POSITION OF ENAME BY OTHER CHARACTER 

SELECT ENAME,SUBSTR(ENAME,2,1),REPLACE(ENAME,SUBSTR(ENAME,2,1),'T') FROM EMP1;

INSERT ONE COLUMN'S DATA INTO ANOTHER COLUMN WITHIN THE SAME TABLE

UPDATE EMP1 SET DESTINATION_COLUMN_NAME = SOURCE_COLUMN_NAME;

INSERT INTO TABLE WITH ALIAS NAME

  

>INSERT INTO EMP1 (EMPLOYEE_ID,SALARY)

SELECT EMPNO AS EMPLOYEE_ID, SAL AS SALARY FROM EMP;

 SUB QUERY IN SELECT CLAUSE

>SELECT

(SELECT SUM(SAL) FROM EMP) TOT_SAL,

(SELECT COUNT(*) FROM EMP) CN,

(SELECT MAX(SAL) FROM EMP) MSAL,

(SELECT MIN(SAL) FROM EMP) LEAST_SAL,

(SELECT AVG(SAL) FROM EMP) AVERAGE_SAL

FROM

EMP;

 >SELECT INCR_SAL,MODIFIED_ENAME,LPAD_EMPNO,HIREDATE FROM

(SELECT SAL+1000 AS INCR_SAL, RPAD(ENAME,10,'@') AS MODIFIED_ENAME, LPAD(EMPNO,5,'0') AS LPAD_EMPNO,HIREDATE FROM EMP)

EMPLOYEE WHERE INCR_SAL>3000;

>SELECT E.ENAME, E.SAL,

(SELECT AVG(SAL) FROM EMP WHERE DEPTNO = E.DEPTNO) AVG_SAL_DEPT,

(SELECT MAX(SAL) FROM EMP WHERE DEPTNO =E.DEPTNO) MAX_SAL

FROM EMP E

ORDER BY 1;

>SELECT AVG(SUM_COLUMN1)

  FROM (SELECT SUM(SAL) AS SUM_COLUMN1

        FROM EMP GROUP BY DEPTNO) A ;

>SELECT ENAME,

(SELECT MAX(SAL) FROM EMP) MAXSAL ,

SAL,

((SELECT MAX(SAL) FROM EMP ) - SAL ) DIFFERENCE

FROM EMP

ORDER BY ((SELECT MAX(SAL) FROM EMP ) - SAL);

TOP 2 SAL FROM EMP TABLE 

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

 NTH HIGHEST SAL FROM EMP   

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

 MERGE THREE TABLES

MERGE INTO APP_MAST_MDSFC FC USING (SELECT FC.ROWID FC_RW, EPST.ADJ_AMT_SETT, CARDMASTER.CARD_NUMBER
          from
          APP_MAST_MDSFC FC,APP_MAST_MDSEPST EPST,APP_CARDMASTER CARDMASTER
          where
          FC.PROCESSOR='I'
          AND
          EPST.ADJ_SETT_IND = 'c'
          AND
          FC.TTUM_FLAG is NULL
          AND
          FC.CPD BETWEEN to_date('10-07-2012','dd-mm-rrrr') AND to_date('10-07-2012','dd-mm-rrrr')
          AND
          FC.SWT_SRL_NO = EPST.ORG_SWT_SRL_NO
          AND
          CARDMASTER.CARD_NUMBER(+) = FC.CRD_NUM)
ON (FC.ROWID=FC_RW)
WHEN MATCHED THEN
UPDATE SET EPST_AMOUNT = (ADJ_AMT_SETT / 100);

COUNT THE EMP WHO IS WORKING IN ALL (10,20,30) DEPT

SELECT D.DEPTNO, D.DNAME,

(SELECT COUNT(*) FROM EMP E

WHERE E.DEPTNO = D.DEPTNO) EMPL_CNT

FROM DEPT D;

COUNT THE EMP OF 10TH DEPT

SELECT D.DEPTNO, D.DNAME,

(SELECT COUNT(*) FROM EMP E

WHERE E.DEPTNO = D.DEPTNO) EMPL_CNT

FROM DEPT D WHERE D.DEPTNO=10;