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,
Eg.
JOHN,
1000,1000
PAUL,2000,3000
SMITH,3000,6000
7. select ename, salary, difference in
salary with above employee from the above table.
Eg.
JOHN,
1000,1000
PAUL,2000,1000
SMITH,3000,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.
