SQL & PLSQL Interview Questions

1.    I want to write a function which inserts a row into a table and I want to get the rowid of the inserted row in the return statement. Is it feasible? Justif.
2.    I have 2 different databases. I don't have db links between them. I want to transfer the data from one database to the other. What will ou do?
3.    What is a materialized view? What is the refresh techniques used?
4.    If I have a MV which contains the data from different tables which are in different databases. Which approach (refresh) will be feasible for this scenario.
5.    Are you saying that MVs will store the local copy of the data?
6.    I have a partition P1 in which two more partitions are there P2 and P3. If I am exchanging data from one partition to the other, what will happen?
7.    In the above scenario, is it feasible to use global indexes or partition indexes?
8.    In a select query, will you write the joins and filter conditions in any specific order or we can write them in any order?
9.    How the sql SELECT will be parsed?
10.    How you will do the debugging in PLSQL
11.    I have a procedure running remotely. I want to see what is happening while its running. What is the method?
12.    What will you do in that log package which will log the errors?(PRAGMA AUTONOMOUS TRANSACTION)
13.    What are the new features of 9i/10g? 
14.    What is the representation of 'g' in 10g?
15.    What is the feature you like most in oracle plsql.
16.    What is explain plan? How you will analze the quer using explain plan?
17.    What is TKPROF? How you will use the TKPROF?
18.    Write down the steps to generate the trace file.
19.    How will You analze the trace file and how You will use the information in the trace file to tune the queries
20.    What are hints? Did you ever used hints? Name some hints you used.
21.    Unix - Learn
22.    Have you ever interacted with clients
23.    Have you been to onsite?
24.    What is an index? Types of indexes
25.    What do you mean b cardinality in bit map index
26.    When you issue the OPEN cursor command, will the data loaded into cursor already. If not when will it be loaded?
27.    What is a cursor? Types of cursors
28.    What is explain plan? 
29.    How you will tune the sql query?
30.    You did any certification? Is it valid now?
31.    What are the advantages and differences between a PACKAGE, PROCEDURE and a FUNCTION?
32.    Explain the oracle architecture.
33.    When will the DBwr writes the data into a DATA FILE?
34.    When will the REDO log buffer will be written b LGwn
35.    Have you done any data modeling? 
36.    What is normalization?
37.    What are the external tables?
38.    What are the aggregate functions you used?
39.    What are the analtical functions you used?
40.     I am writing a trigger in which I am inserting a value into a table and I am issuing SELECT COUNT(*) FROM the same table. What will happen?
41.    If I write the statement level trigger also, will this mutation happens?
42.    What is the possible solution for it?
43.    I have large amount of data. How will you load the data from other database to our database
44.    What is bulk collect and bulk insert? Explain with syntax.
45.    When I am inserting data using bulk insert, I got an exception. How will you continue the loop without aborting?
46.    How you do the exception handling while using bulk insert and bulk collect?
47.    What are plsql collections? 
48.    What are the things you will be having in a trace file? How you will analze and use this  information to tune your query?
49.    What is a trigger? How many no. of triggers we can write on a table?
50.    I have a table called CUSTOMER. Can I create the trigger with the same name
51.    So, If I can create the trigger with the same name means, the two objects are not stored in the same data page? Then where is the trigger stored?
52.    I have a scenario like, whenever I insert into a table with customer id, it should generate a new id and insert when an event occurs. How will you achieve this?
53.    I have a scenario like, I want to restrict the update of salary values like, one can increase the salary but one can't decrease the salary. How will you achieve this?
54.    Can we create triggers on views?
55.    What type of triggers can we create on views?
56.    Can we create a package specification without package body?
57.    Are the variables and other things declared in package spec are global?
58.    If I declare them in package body then are they global?
59.    Can you tell me some new plsql features of Oracle 10g?
60.    What is an inline view?
61.    What is a hierarchical query?
62.    What is dynamic sql?
63.    How you can execute the dynamic sql (Keyword used)
64.    What is a ref cursor?
65.    How many ways you can open a cursor?
66.    What are collections?
67.    How many collections are there in plsql?
68.    What is the difference between variable array and nested table?
69.    The arguments (parameters) in a procedure are of which type? Are they pass by value or pass by reference?
70.    What is bulk collect?
71.    What is returning into clause?
72.    What is NVL2?
73.    What is a foreign key constraint
74.    A: A FOREIGN KEY in one table points to a PRIMARY KEY in another table.
75.    Did you ever worked on ETL
A: ETL stands for extraction, transformation and loading. Etl is a process that involves the following tasks:
extracting data from source operational or archive systems which are the primary source of data for the data warehouse
transforming the data - which may involve cleaning, filtering, validating and applying business rules
loading the data into a data warehouse or any other database or application that houses data
The ETL process is also very often referred to as Data Integration process and ETL tool as a Data Integration platform.
The terms closely related to and managed by ETL processes are: data migration, data management, data cleansing, data synchronization and data consolidation.

The main goal of maintaining an ETL process in an organization is to migrate and transform data from the source OLTP systems to feed a data warehouse and form data marts.
76.    Did you ever involved in database design
77.    Did you ever did import and export in database
78.    What are the tools you use to access the database
79.    What is a materialized view
80.    Difference between simple view and materialized view
81.    Can we update a materialized view?
82.    What is the difference between function and procedure
83.    How do you do performance tuning
84.    What is SQL trace 
85.    What is TKPROF
86.    What is the structure of a procedure
87.    What is a cursor? How many types of cursors are there
88.    What is a constraint? How many constraints are there in oracle
89.    I have two tables crated. I want to define a foreign key constraint on one column of second table from the first table. What is the syntax to add a foreign key constraint to the existing table
90.    What is the maximum size of varchar2 in a table for a column
91.    If I want to store more than 4000 characters data then what should I do
92.    What is the maximum size of LONG data type in oracle
93.    I want to store an Image file which is very big in size. Which data type I need to use
94.    What is LOB? What are the types?
95.    What can I store in a BLOB?