NICE Interactive Solutions Interview Questions Experienced PL/SQL

Round 1: Online test for 70 minutes:

SQL/PLSQL - 15 Questions in 15 Minutes.
Analytical - 15 Questions in 15 Minutes.
DB Concepts - 10 Questions in 10 Minutes.
Write the program for given Question - 1 Question in 30 minutes.

The question was like below :

SELECT details of all the employees whose ename starts with 'J' and display the salary (.5) times of original salary. The result should be descending order of emp_id.

Round 2: Technical F2F :

• What will be output(count of records in tables) of below in current session and in different session.

  create table a (a number);
  
  insert into a values(1);
  insert into a values(2);
  COMMIT;
  
  create table b (b number);
  
  insert into b values(2);
  insert into b values(3);
  
  create table c (c number);
  insert into c values(1);

• Write PL/SQL block to print Fibonacci series.It should not be hard-coded and syntax should be correct.
  Answer :

  declare
  a number:=0;
  b number:=1;
  c number:=0;
  i number := 8;
  fib varchar2(500):= a |||| b;
  begin
      for j in 1..i
          loop
              c := a + b;
              fib := fib ||  || c;
              a := b ;
              b := c ;
          end loop;
      dbms_output.put_line('Fibonacci is : '|| fib);
  end;

• What are the differences between functions and procedures with example ?
• When to use functions and when to use procedures? Benefits of packages?
  Answer :

  Since most modules can be written either as a procedure or a function, the decision between them comes down to efficiency and maintainability. Functions are preferred when a module returns one value. This value may be a variable, record or array, but as long as the module always returns one value else it is a good candidate for a procedure.

• What is explain plan ? How to show explain plan in readable format output in oracle?
• What is two way commit ?
• How commit in DDL statements work in oracle ?
• What is index ? What are the types of indexes?
• When to use Bit-map index ?
• Find the number of 'a' in string 'abaabbaab' in a single sql statement.
  
  Answer : Using REGEXP_COUNT function.
  Other ways to do it :
  

  SELECT LENGTH(REPLACE('abaabbaab','b')) FROM dual;
  
  Result : 5
  
  SELECT LENGTH(TRANSLATE('abaabbaab','ab','a')) FROM dual;
  
  Result : 5
  

• What are aggregate functions ?
• How order by works in aggregate ? Is it before aggregation or after ?
• What is single line function?
• In Unix , What is the command to find all the processes running with Java?
• What is a trigger ? Why do we use trigger?
• If there are millions rows in a table and after each row trigger is applied,what will be impact on performance??should we use statement level trigger in this case ?
• what is the difference between row level and statement level trigger?
• Can procedure be called in a function or can a function be called in a procedure?