Attra Interview Experience (PL/SQL 7 Years Experience)

The recruitment was for client MasterCard.

Some questions regarding "Tell me about yourself"?
What kind of projects did you worked on?
What is your current role in your team?
Your responsibilities as team member?

Below are some questions which were asked

• What is trigger ? -> answer

• There are 2 triggers trg1 and trg2. Both have been created on table emp on before UPDATE event.Below query was fired

  UPDATE emp SET sal = 100 WHERE empid = 10;

  Which trigger will be fired first and why ?

• How to be confirmed that trg1 should be fired first and then trg2.

• Below is the function

  CREATE OR REPLACE fun1(a IN NUMBER, b OUT NUMBER)
  RETURN number
  IS
  BEGIN
      b := a * 10;
      return 1;
  END;

  What will be the output of below query ?

  SELECT fun1(10,20) FROM dual;

• 2 different types of indexes indx1 and indx2 has been created on table emp(id).Below query has been fired.

  SELECT * FROM emp WHERE id = 1;

  Which index/s will be used ?

• Below is customer table

  custid	custname	sal
  1	A	100
  2	B	150
  NULL	C	NULL
  4	D	200
  5	E	300
  NULL	F	NULL
  7	G	700
  NULL	H	800
  

  What will be output of below queries ?

  select count(1) from customer;
  
  select count(2) from customer;
  
  select count(3) from customer;
  
  select count(4) from customer;
  
  select count(*) from customer;
  
  select count(custid) from customer;
  
  select count(custname) from customer;
  
  select count(sal) from customer;

• Which one is faster count(*) or count(1)?

• Calculate the difference between salaries of current row and next row order by custid? -> Using LEAD Analytical function

• If procedure is declared only in spec, will that be compiled ?

• If procedure is defined only in Body, will that be compiled ? what is this mean ? what is mean if the procedure is in both spec and body?

• What is table functions and where are they used? -> table function

• What are collections? Types of collections ?

• What is bulk collect ? What is LIMIT clause in bulk collect ?

• Insert data from one table into a history table which has same table structure and find out failed records, insert data using bulk collect. -> Using DBMS_ERRLOG

• 12c New features -> Oracle 12c New Features

• What do you look for in explain plan ?

• What is Cost and Cordinality in Explain plan table?