COALESCE operator

Assuming that you are calculating total salary by summing up salary and comm columns in emp table.

SELECT EMPNO,ENAME,SAL,COMM,

SAL+COMM  AS TOTAL_SAL,

SAL+COALESCE(COMM,0) AS TOTAL_SAL

FROM EMP

COALESCE function substitutes the value "0" when the comm is null.

It is similar to NVL fucntion.

LIKE operator

I need to display the employee details whose name contains exactly one 'A' 

SELECT * FROM EMP WHERE ENAME LIKE '%A%' AND ENAME NOT LIKE '%A%A%'

NOT IN vs NOT EXISTS with NULL values

NOT IN and NOT EXISTS are not same

EXISTS vs IN operator

IN and EXISTS are same everywhere

Delete based on DATE conditions

Source:

I have a table which contain duplicates on empno and ename on different dates.I need to preserve oldest value and delete rest of them.





Query:

DELETE FROM DEL_EMP
WHERE (EMPNO,LOAD_DT)
NOT IN
(
SELECT EMPNO,MIN(LOAD_DT) FROM DEL_EMP GROUP BY EMPNO
)



Output:

Eliminating Duplicates from the table

Source:

 
ROWID is the pseudo column,which is unique for every row.Based on this rowid the record can be eliminated.

Query:

DELETE FROM DUP_DEL
WHERE ROWID
NOT IN
(
SELECT MAX(ROWID) FROM DUP_DEL GROUP BY EMPNO
);

This calculates the maximum ROWID and deletes the rows which are not in the maximum ROWID.

After Delete: