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;
