Oracle SQL Technical Interview Recent Questions - 4

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

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

   SQL> select rownum, ename from emp;

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

6. 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;

7. Explain explicit cursor attributes. There are four cursor attributes used in Oracle: cursor_name%Found, cursor_name%NOTFOUND, cursor_name%ROWCOUNT, cursor_name%ISOPEN

8. Explain implicit cursor attributes. Same as explicit cursor but prefixed by the word SQL: SQL%Found, SQL%NOTFOUND, SQL%ROWCOUNT, SQL%ISOPEN

9. How do you view version information in Oracle?

   SQL> select banner from $version;

10. How do you switch to DOS prompt from SQL prompt? SQL> host

See more latest Oracle Database, DBA, gaming database - graphics, GUI recent important questions mostly asked in common interview questions pre placement and GD topics. See Technical interview java questions commonly asked placement questions for top IT MNCs in India, USA, UK, Norway, China, UAE, Denmark, Hungary, etc.