Oracle interview questions

Oracle interview questions

What’s the command to see the current user name? Sql> show user;

What’s the command to change the SQL prompt name?

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

How do you switch to DOS prompt from SQL prompt? SQL> host
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);

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

SQL select rownum, ename from emp;
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);

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;

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

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

How do you view version information in Oracle?

SQL> select banner from $version;

Home > Database, Interview Questions > Oracle interview questions
  1. No comments yet.
  1. No trackbacks yet.