Oracle interview questions

1. What’s the command to see the current user name? Sql> show user;
2. What’s the command to change the SQL prompt name?
   

   SQL> set sqlprompt “database-1 > ”
   database-1 >
   database-1 >

3. How do you switch to DOS prompt from SQL prompt? SQL> host
4. How do I eliminate duplicate rows in an Oracle database?
   

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

5. How do I display row number with records? Use the row-num pseudocolumn with query, like
   

   SQL> select rownum, ename from emp;

6. How do you display the records within a given range?
   

   select rownum, empno, ename from emp where rowid in
   (select rowid from emp where rownum < =&rangeend
   minus
   select rowid from emp where rownum<&rangebegin);

7. The NVL function only allows the same data type. But here’s the task: if the commission field is null, then the text “Not Applicable” should be displayed, instead of blank space. How do you write the query?
   

   SQL> select nvl(to_char(comm.),’Not Applicable’) from emp;

8. Explain explicit cursor attributes. There are four cursor attributes used in Oracle: cursor_name%Found, cursor_name%NOTFOUND, cursor_name%ROWCOUNT, cursor_name%ISOPEN
9. Explain implicit cursor attributes. Same as explicit cursor but prefixed by the word SQL: SQL%Found, SQL%NOTFOUND, SQL%ROWCOUNT, SQL%ISOPEN
10. How do you view version information in Oracle?
    

    SQL> select banner from $version;