E -PLSQL Interview Questions

E 2nd round

Explain about your current roles and responsibilities.
What is the challenge u have faced recently.
Explain about oracle architecture.
Dbwr will write to db files, from bd files which process will read.
What is refcursor.
cursor attributes.
what is implicit cursor.
differene between decode and case.
how u find the implicit cursor rows fetched/processed.
What is explicit cursor.
what are exceptions.
what saved exceptions, how will it be used, write a program.
what are joins,explain each of it.
what are triggers, write a program to update a table, coulmn a, column b, add a+b and update column c. with out using update statement in cursor.
begin select * from employees; end; -- what will be the result
how to make the above program work with out using declaration section
Queries using joins, exists, not in, rank , dense rank.
which is fater in or exists. why
can we use commit in trigger.how.
difference between function and procedure.
write a package to delete the temporary tables created daily in the database.
prodecure can return using return keyword, How.
ontime functions/procedures?
global variables, what is its scope.
SQL loader, different types of files . give example of sqlloder implementation.
i have file with multiple columns, with out delimiter, how will i load the file.
what are bind variables.
what is soft parse, what is hard parse.
what are hints.
explain plan - explain
i have an sql working fine yesterday. today i is not working.  How will u identify the issue.
how do u difference between plans of same query, if there is any difference.
what are indexes, why are they used.
when to use bitmap index. what is the advantage.
If i have many dmls of the index, what will be the impact, how.
How to find the processes running in linux.
How to go to the last line of the file in vim editor.
how to rename a file or folder.
table has four columnbs:a,b,c,d select all the rows from the table without retriving duplicates . unique record identifier (a,b,c,d).

E - PLSQL Interview Questions

E 1st round

Queries on joins, group by , not in, rank , dense rank, decode, case.
(for half an hour to write queries ,given different tables and data)
get the name till @ symbol in email id.
can i use commit in a trigger.
i have two tables.  table a has few rows, table b havs more rows. how will u synch table a and b. Or how will u load table b into table a ,if exist update , if not insert.
i have a procudure. i what to know where all this procedure is used in different objects.
list all the files in unix.
kill a job
find all the file names
find all the processes
high resource consumption procsses.
list 5th line  in a file.
number of lines in a file

Cap Gemini -PLSQL interview questions

Capgemini 2nd round

Table A:has col1 with values(a,b,c). Table B -col1 with values (b,c,c,d).
Table A:has col1 with values(a,b,c,null). Table B -col1 with values (b,c,c,d,null).
Queries on union , unionall, interset, minus, data sets include null values.
queries on equijoin, self join, outer joins.
queries in corelated subqueires, sub queries.
queries in not in and not exists. which is faster. is there any differene in the output with the above 2nd dataset
top 10 salaried employees from employee table.
how to eliminate duplicates.
What are constraints. why are they used.
A database in 10g is completely migrated to database in 11g. How will u verify the new database has everything properly.
queries on decode, case.
What is select null+1 from dual
Explain Plan , what do u look for in explain plan
What are hits. Tell me the hints you know.
I have an sql query, It has lot of joins in it. While querying , i got an error, temp space full. How do u overcome this error.
what is looping chain of synonyms.
How bulk collect and forall works.
How do u optimize an sql query.

CapGemini - PLSQL Interview Questions

Capgemini 1st round

Tell me about yourself
what are packages
what is explain plan
i have an sql statement which is well written, bit still very slow in fetching the data.
how do u read an awr report
by default what degree of parallalism will be used
explain bulk collect and forall
what it limit
how do u determine the limit of a bulk collect fetch.
how can i use commit in a trigger.
what are different types of triggers.

TCS - PLSQL Interview Questions

TCS 1st round:

what is coreleated sub query.
I have a table emp with 10 rows and department 5 rows, inner query has 5 rows from dept and outer query will have 10 rows from 10 rows, how many times corelated subquery will execute.4
which is faster in or exists
why exists is faster.
what are different types of triggers
difference between row level and statement level
what is bulk collect and for all, limit. 
what are save exceptions
what is index. different types of indexs
what are pratitions, explain different types.

Alshaya - 1st Round

Experience Leve : 3-5 Years

·         Roles and Responsibilities of current project

·         Rate yourself out of 10 in sql and plsql

·         What are materialized views

·         Different methods of refreshing Materialized views and normal view , difference between both

·         What are indexes? How it improves the performance

·         A table which gets updated frequently , should we create index  on the frequently updated columns

·         Can we write commit inside trigger.

·         Can we alter the execution sequence of triggers?

·         What is mutating trigger

·         How to avoid mutating trigger

·         Select all the employees whose have worked for  more than one year in the current organization

·         Schema A and schema B, table employees exist in both the schemas. Procedure PRC1 is in schema A. on executing prc1, from schema B, employee table from which schema gets effected.

·         Grants required executing DML on a table in different schema.

·         Whether oracle generates insert scripts when uploading data through sqlldr.

·         Select employees from a table earning maximum salary department wise.

·         Difference between truncate and delete. Which is faster in performance?

·         Table has records.  Can we alter a table to add column with not null constraint.

·         Difference between function and procedure

·         What is NOCOPY hint? Explain

·         Select all the names which contain kumar in their name irrespective of case;

·         upper(ename) like’ %KUMAR%’;

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,

                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.