Oracle Tips

1. To Implement Sleep function in PL/SQL use below code:
DBMS_LOCK.SLEEP(10);
2. Any arithmetic expression containing a null always evaluates to null.
3. ORDER BY clause does not affect the ROWNUM of each row.
4. Why use RowNum
a. To limit the no. of rows returned by a query.
b. To assign unique value to each row of a table.
5. In order to create an empty copy of an existing table use below query:

CREATE TABLE new_table AS
SELECT * FROM old_table

WHERE 1 = 2;

6.How to remove duplicate rows from a table ?

SQL> Select the Duplicates with:

SELECT ename,empid FROM Emp
GROUP BY ename, empid

HAVING COUNT(*) > 1;

Delete the Duplicates with

DELETE from Emp A
WHERE (A.ename,A.empid) IN
    (SELECT B.ename, B.empid FROM Emp B

      WHERE A.ename= B.enameAND A.empid = B.empid
       AND A.rowid > B.rowid);