Thursday, July 28, 2016

VirtusaPolaris - PLSQL interview Questions 1st Round

Experience Level - 3- 5 Years

SQL Questions:

Brief  about your experience, skills and projects you worked on.

1.Differenfe between truncate and delete
2. which is faster- truncate or delete
3.difference between union and union all
4. which is faster between union and union all and why
5.Table EMP (ename, salary , empno). select all the employees who name in uppercase e.g. JOHN, PAUL, SMITH
6.Select  ename, salary , cumulative_salary from the above table,
                JOHN, 1000,1000
7. select ename, salary, difference in salary with above employee from the above table.
                JOHN, 1000,1000
8. What all analytical functions you know
9. What is listagg, give an example
10. Output 1 to 10 numbers using a select statement in a single column.
11.I have a two sessions ,in one session i am inserting rows, in another session i have created a table and closed the session. what happens to the rows in the first session. Will those be committed or not. Give reasoning.

PLSQL Questions:

1. How much rating you can give for PLSQL out of 1 to 5.
2. what are packages
3. What are the advantages of Packages.
4.  How the packages hide the business logic.
5.what are private functions in packages, why are the required, when should be those private factions or procedures.
6.Disadvantage of packages.
7. How can I call private function of package body outside from the package
8. I have a badly  performing package impaction performance.  The package is written like this. I have 1 million rows in a table, i have a cursor from which i have fetched the rows and put into collation variable. I am processing each row . How will you optimize the performance.
9. I have very less memory available in the session. And I am using collections to fetch entire collection of records fetched by a cursor. Since memory is very low, session got crashed. how will you optimize the performance of the session.
10. I have one million of rows in a table to be processed ( update each employee's salary in employees table  with 10000) using a procedure in a package. what is your approach.
11. While processing millions of records, i have encountered a failure of update of employees salary due to some constraint violation. what will happen and how will you handle this scenario.
12 . Explain about external tables. In which scenarios external tables are used.
13. Advantages of external tables.
14. What all operations can be done on external tables.
15. What is autonomous transaction
16. Explain a scenario where it is used effectively.
17. What are global temporary tables.  
18. I have inserted some rows into the global temporary table and i have closed my session. what will happen to rows and what the possible cases.
19.can i have triggers on global temporary tables
20. I have a global temporary table where in  a session,  4 records got inserted, in the same session, an autonomous have been run which inserted a new row. GTT is of ON COMMIT preserve rows. Autonomous transaction got ended. How many rows will be present now. The main session is not closed.
21. What is dynamic sql.
22. In what plsql objects, dynamic sql can be used.
23. I have a table A which has 1 million records . I have another table  B which has same structure and similar records. I need to sycn table A with table B in one shot. How can i achieve this.
24.What is Merge, explain the syntax.
25. Using merge can I only insert the records or update the records.