Interview for experienced PL/SQL programmer @ Mastek

This post is a recorded interview for a job posting that requires experienced PL/SQL Programmers at a Software Firm, Mastek. The interview candidate is a three year experienced PL/SQL, Cognos developer who works for IT division of financial services giant, Merrill Lynch. He was recruited by the company after successfully clearing all three rounds of interview: A Technical round, a Project Manager Interview round followed by HR discussion.

Technical Interview Round

1. Tell me about yourself?
2. What is a partition in Oracle?
3. What is a trigger in SQL?
4. What are the differences between a procedure and function in SQL?
5. What is an Explain Plan?
6. Write a SQL query to find second maximum salary in each department of a Company.
7. Write a SQL query to delete all duplicate records in a table
8. What is mutating table?

Project Manager Interview Round

1. Tell me about yourself?
2. Which technologies have you worked on in your previous companies?
3. Would you like to work on Business Objects(BO)?
4. Tell me the difference between Business Objects(BO) and Cognos?

HR Discussion

1. Why did you want to change your current job?
2. Why do you think should you be selected for this position?
3. How much salary are you expecting?

Technical Interview Round

1. Tell me about yourself?

Introduces himself, tells about about current project, roles and responsibilities, tells about previous work experiences and tells about his technical strengths.

2. What is a partition in Oracle?

Decomposing a table into many parts for better performance and manageability. Types of Partition: 1. List, 2. Range, 3. Hash, 4. Composite. Read more about Oracle partition

3. What is a trigger in SQL?

Trigger is a SQL block executed implicitly when one or more DML event occurs. Read more about triggers.

4. What are the differences between a procedure and function in SQL?

Procedure may or may not return a value through parameters, where as a function always return a value. There are three types of parameters allowed in procedure: IN, OUT and IN OUT, where as there is only one type of parameter allowed in a function: IN. Read more differences between functions and procedures

5. What is an Explain Plan?

Explain Plan on wikipedia, Explain Plan in Oracle

6. Write a SQL query to find second maximum salary in each department of a Company.

select department, max(salary) from employee where salary < (select max(sal) from employee) group by department_no

7. Write a SQL query to delete all duplicate records in a table

delete from employee emp where rowid < (select max(rowid) from employee e1 where emp.column=e1.column)

8. What is mutating table?

A table is called mutating table if it is affected by insert, update or delete statement or when it is affected by a delete cascade statement. Read more about mutating tables in Oracle

Project Manager Interview Round:

1. Tell me about yourself?

Again, tells his name, previous job experience and current project, roles and responsibilities.

2. Which technologies have you worked on in your previous companies?

I have worked on Oracle PL/SQL and Cognos. Goes on telling about the technologies he worked on in previous companies.

3. Would you like to work on Business Objects(BO)?

Yes, I would like to work. Please train me on Cognos, if possible.

4. Tell me the difference between Business Objects(BO) and Cognos?

Cognos is an IBM tool, it is a MOLAP tool. Business Objects is a SAP tool, it is a ROLAP tool.

HR Discussion Round:

1. Why did you want to change your current job?

Firstly current job location is far from my home. It takes me two hours to go to office and two hours to come home. Secondly, I don’t get enough exposure in Cognos as I work only with List reports. I am looking for a better opportunities. (Rants in his mind, “I want a better pay!!”)

2. Why do you think should you be selected for this position?

Gives a bombastic textbook answer and impresses the HR.

3. How much salary are you expecting?

Six and a half hundred thousand rupees per annum (6,50,000 Rs or 13,380$ USD approx)
HR: Well that is too high, you will have to quote lesser.

How about Six hundred thousand Rupees per annum? (6,00,000 Rs or 12,351$ USD approx)
HR: I’m afraid I can not offer you more than five and half hundred thousand Rupees (11,321$ USD approx) for your profile.

Candidate accepts the quotation and HR gives a joining date to the candidate.

About the Author:  Sridhar Jammalamadaka is the Editor of Interview Mantra. He is a student pursuing masters in International Institute of Information Technology. With over 4 years of IT experience, he is obsessed with software programming and computer technologies. He loves to see a software come to life in its development life cycle. He is a person who defies "mob mentality" and a deeply spiritual and a strong believer of moral values and righteousness. Through this website, he wishes to gather a large community of aspiring engineers, entrepreneurs and professionals from all parts of the globe. You can connect with him on Facebook - http://www.facebook.com/sridhar.j