SQL: Refresh

I will assume that you know the SQL languages.
What we will see is the depth up to which you know it ….. :)
Following are some of the tricky questions about SQL that you may face at the time of interview.

Difference between delete and truncate.
Both these commands are used for the deletion of the rows from table but the basic difference between the two is DELETE is DML command and one can make use of COMMIT/ROLLBACK after this command. The system get it for you from the Rollback segment. Where as TRUNCATE is DDL command and is auto commit. it removes data directly without copying it into the Rollback segment. This is the reason why TRUNCATE is faster.
Another important difference between the two is of water mark index [DBA term].
To explain this here is one example.
Say I have 10 lac rows in one table. I deleted all the records using DELETE command. Now I will try to select * from that table. You will see that it takes some time to give you the result 'No rows Returned'.
Reason being once you issue DELETE it will remove the data in the specified row but the memory allocated by these rows remains as it is. Where as TRUNCATE will remove the memory allocation as well.

To escape special characters when writing SQL
SELECT 'A ''''double quoted'''' word.' AS text FROM DUAL;

Escape wildcard characters while using LIKE
The LIKE keyword allows for string searches. The '_' wild card character is used to match exactly one character, while '%' is used to match zero or more occurrences of any characters. These characters can be escaped in SQL. Examples:


SELECT name FROM emp
WHERE id LIKE '%/_%' ESCAPE '/';

SELECT name FROM emp
WHERE id LIKE '%\%%' ESCAPE '\';

Escape ampersand (&) characters in SQL*Plus
DEFINE setting can be changed to allow &'s (ampersands) to be used in text:

SET DEFINE ~
SELECT 'Anil & Apps' FROM dual;

SET ESCAPE '\'
SELECT '\&anil' FROM dual;

SET SCAN OFF
SELECT '&anil' x FROM dual;


To eliminate duplicate records
Option 1.
SQL> DELETE FROM table_name A WHERE ROWID > (SELECT min(rowid) FROM table_name B
WHERE A.key_values = B.key_values);

Option 2.
SQL> create table table_name2 as select distinct * from table_name1;SQL> drop table table_name1;
SQL> rename table_name2 to table_name1;

Option 3.
delete from
where rowid not in ( select min(rowid)
from exp group by column1..,column2,...column3..);


To add one day/min/sec to date
select sysdate, (sysdate+1) one_day , (sysdate + 1/24/60)one_min, sysdate + 1/24/(60*60) one_second
from dual;

To get diffrence between two date columns
NUMTODSINTERVAL: This function is new to Oracle 9i. It takes two arguments numtodsinterval(x,c) where x is a number and c is a character string denoting the units of x. Valid units are 'DAY', 'HOUR', 'MINUTE' and 'SECOND'.

select numtodsinterval(date_col_1 – date_col_2 ,'day') time_diff from table_name ;



Matrix report in SQL
SELECT *
FROM (SELECT job,
sum(decode(deptno,10,sal)) DEPT_10,
sum(decode(deptno,20,sal)) DEPT_20,
sum(decode(deptno,30,sal)) DEPT_30,
sum(decode(deptno,40,sal)) DEPT_40,
sum(sal) TOTAL_BY_JOB
FROM emp
GROUP BY job)
ORDER BY 1;


To get n1 to n2 rows from table

select * from emp where rownum <=9
minus
select * from emp where rownum <=7

To add column in the middle of the table

SQL> create table test (ename varchar2(20),salary number);

SQL> rename test to test2;

SQL> create table test3 (id varchar2(20));

SQL> create table test as(select test2.ename,test3.id,test2.salary from test2,test3);


To show Hierarchy structure in SQL
select lpad('-',level*5)'> 'ename hierarchy
from emp
connect by prior empno = mgr
start with mgr is null;


Nth row from table
select * from emp
where (rowid, 0) in (select rowid, mod(rownum, 4) from emp)