1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Oracle Basic interview questions

Discussion in 'Interview Discussions' started by tyro, Nov 23, 2008.

  1. tyro

    tyro Forum Genius

    Messages:
    368
    Likes Received:
    20
    Trophy Points:
    260
    Location:
    India
    Oracle Basic interview questions

    1. What's the command to see the current user name?
    Code (Text):
    Sql> show user;
    2. What is the command to change the SQL prompt name?
    Code (Text):
    SQL> set sqlprompt 'TYRO->'
    TYRO-1 >
    3. How do you switch to DOS prompt from SQL prompt?
    Code (Text):
    SQL> host
    4. How do I eliminate duplicate rows in an Oracle database?
    Code (Text):
    DELETE FROM table_name
          WHERE ROWID NOT IN (SELECT   MAX (ROWID)
                                  FROM table_name
                              GROUP BY duplicate_values_field_name);
    5. How do I display row number with records?
    Use the row-num pseudocolumn with query, like
    Code (Text):
    SQL> select rownum, ename from emp;
    6. How do you display the records within a given range?
    Code (Text):

    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?
    Code (Text):
    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?
    Code (Text):
    SQL> select banner from $version;
     
  2. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Some Alternative answers for some of the above questions .

    1. What's the command to see the current user name?

    Code (Text):
    SQL> SELECT USER FROM DUAL;
    Show user will work basically only on SQL Plus . Will not work same for all tools.

    4. How do I eliminate duplicate rows in an Oracle database?

    a. using Correlated subquery

    Code (Text):

    DELETE FROM TABLENAME t1
    WHERE T1.ROWID >  ( SELECT MIN(T2.ROWID) FROMT t2
                        WHERE t1.dup_col_1= t2.dup_col_1
                        AND     t1.dup_col_2= t2.dup_col_2);
     
    b. Using Analytical Function ( from 8i and above)

    Code (Text):

    DELETE FROM TABLENAME
    WHERE ROWID IN (SELECT ROWID FROM (SELECT ROW_NUMBER() OVER (PARTITION BY dup_col ORDER BY dup_col_1) rnk
                                       FROM   TABLENAME)
                    WHERE rnk>1);
     
    6. How do you display the records within a given range?

    Actually there is no scope for these kind of question in RDBMS. Row number or position of field is not important in any RDBMS. Anyway there is one more way of doing it using analytical function(from 8i and above)

    Code (Text):

    SQL> select empno,ename,row_number() over(order by rownum)rnk
      2  from emp;

         EMPNO ENAME             RNK
    ---------- ---------- ----------
          7369 SMITH               1
          7499 ALLEN               2
          7521 WARD                3
          7566 JONES               4
          7654 MARTIN              5
          7698 BLAKE               6
          7782 CLARK               7
          7788 SCOTT               8
          7839 KING                9
          7844 TURNER             10
          7876 ADAMS              11
          7900 JAMES              12
          7902 FORD               13
          7934 MILLER             14

    14 rows selected.

    SQL> select * from ( select empno,ename,row_number() over(order by rownum)rnk
      2                  from emp )
      3  where rnk between &rangebegin and &rangeend ;
    Enter value for rangebegin: 5
    Enter value for rangeend: 10
    old   3: where rnk between &rangebegin and &rangeend
    new   3: where rnk between 5 and 10

         EMPNO ENAME             RNK
    ---------- ---------- ----------
          7654 MARTIN              5
          7698 BLAKE               6
          7782 CLARK               7
          7788 SCOTT               8
          7839 KING                9
          7844 TURNER             10

    6 rows selected.

    SQL>
     
     
  3. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    9. Explain implicit cursor attributes.

    Reference : Cursor Attributes
     
  4. tyro

    tyro Forum Genius

    Messages:
    368
    Likes Received:
    20
    Trophy Points:
    260
    Location:
    India
    Excellent answers. Thanks for your support rajavu as always
     
  5. jerryvn01

    jerryvn01 Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Hi,

    I do not agreed with you. Any way, your ideal make me thinking about some thing for my project.

    Apart from that, this link below may be useful: Database interview questions
    Please try to keep posting. Tks and best regards
     
  6. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    6. How do you display the records within a given range?

    Using Inline view
    SELECT * FROM (SELECT ROWNUM RN, EMP.* FROM EMP ) WHERE RN BETWEEN &MIN AND &MAX;

    9. Cursor Attributes:
    I hope SQL%ISOPEN will always return false. If I am mistaken , please guide me.
     
  7. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    SQL%ISOPEN will not always return FALSE as an exception may have caused a loop to exit prematurely leaving the driving cursor open. This is used in exception handlers to trigger closing the open cursor before exiting the block.
     
  8. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    Hi Zargon, can you give me one example, i mean code. why because i dont know where to place sql%isopen.
     
  9. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Having misread the post I replied (using the implicit cursor attribute) as though this were an explicit cursor. You are correct as unless something totally absurd occurs SQL%ISOPEN should always return FALSE.

    My apologies for the misinterpretation.
     
  10. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    implicit cursors are always handled by Oracle Server Automatically. and are opened and closed for DQL and DML statements. so we cannot place SQL%ISOPEN in the middle of the statements.
     
  11. deadlock

    deadlock Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    100
    wht abt sum oracle apps ques?
     
  12. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    Sorry I didn't get you? What do mean by this question? Can you explain the question clearly ?? So that I may clear your doubt?
     
  13. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    The question is regarding the availability of Oracle Apps DBA interview questions and possible answers.
     
  14. anna575

    anna575 Active Member

    Messages:
    22
    Likes Received:
    1
    Trophy Points:
    90
    Wow, very nice and incredible post. i like this post because this post is very helpful for me. thank u :hurray :hurray