SQL questions and answers and faq's

Oracle Interview Questions and Answers : SQL
www.allwalkin.blogspot.com

1. To see current user name

Sql> show user;

2. Change SQL prompt name

SQL> set sqlprompt “Manimara > “

Manimara >

Manimara >

3. Switch to DOS prompt

SQL> host
www.allwalkin.blogspot.com
4. How do I eliminate the duplicate rows ?

SQL> delete from table_name where rowid not in (select max(rowid) from table group by

duplicate_values_field_name);

or

SQL> delete duplicate_values_field_name dv from table_name ta where rowid <(select min(rowid) from

table_name tb where ta.dv=tb.dv);

Example.

Table Emp

Empno Ename

101 Scott

102 Jiyo

103 Millor

104 Jiyo

105 Smith

delete ename from emp a where rowid < ( select min(rowid) from emp b where a.ename = b.ename);
www.allwalkin.blogspot.com
The output like,

Empno Ename

101 Scott

102 Millor

103 Jiyo

104 Smith

5. How do I display row number with records?

To achive this use rownum pseudocolumn with query, like SQL> SQL> select rownum, ename from emp;

Output:

1 Scott

2 Millor

3 Jiyo

4 Smith
www.allwalkin.blogspot.com
6. Display the records between two range

select rownum, empno, ename from emp where rowid in

(select rowid from emp where rownum <=&upto

minus

select rowid from emp where rownum<&Start);

Enter value for upto: 10

Enter value for Start: 7

ROWNUM EMPNO ENAME

--------- --------- ----------

1 7782 CLARK

2 7788 SCOTT

3 7839 KING

4 7844 TURNER

7. I know the nvl function only allows the same data type(ie. number or char or date

Nvl(comm, 0)), if commission is null then the text “Not Applicable” want to display, instead of

blank space. How do I write the query?
www.allwalkin.blogspot.com