Oracle Interview Questions and Answers

Oracle Interview Questions and Answers : SQL

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

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);
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