Oracle Interview Question and Answer

1.	What is Oracle table?
	A table is the basic unit of data storage in an Oracle database. The tables of a database hold all of the user accessible data. Table data is stored in rows and columns.

2.	What are Clusters?
	Clusters are groups of one or more tables physically stores together to share common columns and are often used together.

3.	What is an Index?
	An Index is an optional structure associated with a table to have direct access to rows, which can be created to increase the performance of data retrieval. Index can be created on one or more columns of a table.

4.	What are the advantages of views?
	Provide an additional level of table security, by restricting access to a predetermined set of rows and columns of a table. Hide data complexity. Simplify commands for the user. Present the data in a different perspective from that of the base table Store complex queries.

5.	What are the various types of queries?
	The types of queries are : Normal Queries Sub Queries Co-related queries Nested queries Compound queries

6.	What is the difference between clustered and a non-clustered index?
	A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages. A Nonclustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk.

7.	What is a Tablespace?
	A database is divided into Logical Storage Unit called tablespace. A tablespace is used to grouped related logical structures together.

8.	Why use materialized view instead of a table?
	Materialized views are basically used to increase query performance since it contains results of a query. They should be used for reporting instead of a table for a faster execution.

9.	What does ROLLBACK do?
	ROLLBACK retracts any of the changes resulting from the SQL statements in the transaction.

10.	Compare and contrast TRUNCATE and DELETE for a table?
	Both the truncate and delete command have the desired outcome of getting rid of all the rows in a table. The difference between the two is that the truncate command is a DDL operation and just moves the high water mark and produces a now rollback. The delete command, on the other hand, is a DML operation, which will produce a rollback and thus take longer to complete.

11.	what is null value?
	Null Value is neither zero nor it is a blank space. It is some unknown value which occupies 4 bytes of space of memory in SQL.

12.	Define transaction?
	A transaction is a sequence of SQL statements that Oracle Database treats as a single unit.

13.	what is the difference between sql&oracle?
	SQL is Stuctured Query Language.Oracle is a Database.SQL is used to write queries against Oracle DB.

14.	What are different Oracle database objects?
	TABLES VIEWS INDEXES SYNONYMS SEQUENCES TABLESPACES

15.	What is hash cluster?
	A row is stored in a hash cluster based on the result of applying a hash function to the row's cluster key value. All rows with the same hash key value are stores together on disk.

16.	What is a User_exit?
	Calls the user exit named in the user_exit_string. Invokes a 3Gl program by name which has been properly linked into your current oracle forms executable.

17.	What is schema?
	A schema is collection of database objects of a user.

18.	What are Roles?
	Roles are named groups of related privileges that are granted to users or other roles.

19.	What are the dictionary tables used to monitor a database spaces ?
	DBA_FREE_SPACE DBA_SEGMENTS DBA_DATA_FILES

20.	What is a SNAPSHOT?
	Snapshots are read-only copies of a master table located on a remote node which is periodically refreshed to reflect changes made to the master table.

21.	What is a database instance?
	A database instance is a set of memory structure and background processes that access a set of database files. The processes can be shared by all of the users.

22.	What are parameters?
	Parameters provide a simple mechanism for defining and setting the valuesof inputs that are required by a form at startup.Form parameters are variables of type char,number,date that you define at design time.

23.	What are the different file extensions that are created by oracle reports?
	Rep file and Rdf file.

24.	What are clusters?
	Clusters are groups of one or more tables physically stores together to share common columns and are often used together.

25.	What is difference between SUBSTR and INSTR?
	INSTR function search string for sub-string and returns an integer indicating the position of the character in string that is the first character of this occurrence.SUBSTR function return a portion of string, beginning at character position, substring_length characters long.SUBSTR calculates lengths using characters as defined by the input character set.

26.	Define a view?
	A view is a virtual table which is based on the one or more physical tables and views.

27.	What is the difference between a view and a synonym?
	Synonym is just a second name of table used for multiple link of database.View can be created with many tables, and with virtual columns and with conditions.But synonym can be on view.

28.	What is the usage of SAVEPOINTS?
	SAVEPOINTS are used to subdivide a transaction into smaller parts. It enables rolling back part of a transaction. Maximum of five save points are allowed.

29.	What are ORACLE PRECOMPILERS?
	A precompiler is a tool that allows programmers to embed SQL statements in high-level source programs like C, C++, COBOL, etc.The precompiler accepts the source program as input, translates the embedded SQL statements into standard Oracle runtime library calls, and generates a modified source program that one can compile, link, and execute in the usual way.

30.	When do you use WHERE clause and when do you use HAVING clause?
	The WHERE condition lets you restrict the rows selected to those that satisfy one or more conditions.Use the HAVING clause to restrict the groups of returned rows to those groups for which the specified condition is TRUE.

31.	What are the differences between stored procedures and triggers?
	A stored procedures are compiled collection of programs or SQL statements that live in the database. A stored procedure can access and modify data present in many tables. Also a stored procedure is not associated with any particular database object. But triggers are event-driven special procedures which are attached to a specific database object.

32.	What must be installed with ODBC on the client in order for it to work with Oracle?
	SQLNET and PROTOCOL (for example: TCPIP adapter) layers of the transport programs.

33.	What are the different file extensions that are created by oracle reports?
	Rep file and Rdf file.

34.	what is trigger?
	Triggers are special kind of stored procedures that get executed automatically when an INSERT, UPDATE or DELETE operation takes place on a table.

35.	Explain the difference between a data block, an extent and a segment?
	A data block is the smallest unit of logical storage for a database object. As objects grow they take chunks of additional storage that are composed of contiguous data blocks. These groupings of contiguous data blocks are called extents. All the extents that an object takes when grouped together are considered the segment of the database object.

36.	What is bind reference and how can it be created?
	Bind reference are used to replace the single value in sql, pl/sql statements a bind reference can be created using a (:) before a column or a parameter name.

37.	How many LONG columns are allowed in a table? Is it possible to use LONG columns in WHERE clause or ORDER BY?
	Only one LONG column is allowed. It is not possible to use LONG column in WHERE or ORDER BY clause.

38.	What are various types of joins?
	Equi joins Cartesian joins Self join Outer join

39.	What is the maximum number of triggers, can apply to a single table?
	12 triggers

40.	What command would you use to create a backup control file?
	Alter database backup control file to trace.

41.	What is an Oracle index?
	An index is an optional structure associated with a table to have direct access to rows, which can be created to increase the performance of data retrieval. Index can be created on one or more columns of a table.

42.	What are the different index configurations a table can have?
	A table can have one of the following index configurations No indexes A clustered index A clustered index and many nonclustered indexes A nonclustered index Many nonclustered indexes.

43.	What is difference between UNIQUE constraint and PRIMARY KEY constraint?
	A column defined as UNIQUE can contain Nulls while a column defined as PRIMARY KEY can't contain Nulls. A table can have only one primary keys.

44.	What is BCP? When does it used?
	BulkCopy is a tool used to copy huge amount of data from tables and views. BCP does not copy the structures same as source to destination.

45.	How to know which index a table is using?
	SELECT table_name,index_name FROM user_constraints.

46.	What is SYSTEM tablespace and when is it created?
	Every Oracle database contains a tablespace named SYSTEM, which is automatically created when the database is created. The SYSTEM tablespace always contains the data dictionary tables for the entire database.

47.	Which date function returns number value?
	months_between

48.	what is integrity constrains?
	Integrity constraints are used to ensure accuracy and consistency of data in a relational database.

49.	What is the difference between Explicit and Implicit Cursors?
	An Implicit cursor is one created "automatically" for you by Oracle when you execute a query. It is simpler to code An Explicit cursor is one you create yourself. It takes more code, but gives more control - for example, you can just open-fetch-close if you only want the first record and don't care if there are others DBA_DATA_FILES.

50.	what are primary keys?
	Primary key are used to uniquely identify each row of the table. A table can have only one primary key.

51.	What are the components of physical database structure of Oracle database?
	Oracle database is comprised of three types of files. One or more data files, two are more redo log files, and one or more control files.

52.	What is DECODE function used for?
	DECODE is used to decode a CHAR or VARCHAR2 or NUMBER into any of several different character strings or numbers based on value. That is DECODE does a value-by-value substitution.

53.	What is the default return value of a function?
	The default return value from a function is int. In other words, unless explicitly specified the default return value by compiler would be integer value from function.

54.	What is the difference between oracle,sql and sql server?
	Oracle is based on RDBMS. SQL is Structured Query Language. SQL Server is another tool for RDBMS provided by MicroSoft.

55.	How you will avoid your query from using indexes?
	By changing the order of the columns that are used in the index, in the Where condition, or by concatenating the columns with some constant values.

56.	What is a cluster key?
	The related columns of the tables are called the cluster key. The cluster key is indexed using a cluster index and its value is stored only once for multiple tables in the cluster.

57.	What is SGA?
	The System Global Area in an Oracle database is the area in memory to facilitate the transfer of information between users. It holds the most recently requested structural information between users. It holds the most recently requested structural information about the database. The structure is database buffers, dictionary cache, redo log buffer and shared pool area.

58.	What is a data segment?
	Data segment are the physical areas within a database block in which the data associated with tables and clusters are stored.

59.	What is ROWID?
	ROWID is a pseudo column attached to each row of a table. It is 18 characters long, blockno, row number are the components of ROWID.

60.	What is the usage of SAVEPOINTS?
	SAVEPOINTS are used to subdivide a transaction into smaller parts. It enables rolling back part of a transaction. Maximum of five save points are allowed.

OOPS Inetrview Question and Answer

Questions : 1 What is Object Oriented Programming ?  

Answers : 1

It is a problem solving technique to develop software systems. It is a technique to think real world in terms of objects. Object maps the software model to real world concept. These objects have responsibilities and provide services to application or other objects. 

Questions : 2 What is a Class ? 

Answers : 2

A class describes all the attributes of objects, as well as the methods that implement the behavior of member objects. It is a comprehensive data type which represents a blue print of objects. It’s a template of object. 

Questions : 3 What is an Object ? 

Answers : 3

It is a basic unit of a system. An object is an entity that has attributes, behavior, and identity. Objects are members of a class. Attributes and behavior of an object are defined by the class definition.

Questions : 4 What is the relation between Classes and Objects?
Answers : 4

They look very much same but are not same. Class is a definition, while object is instance of the class created. Class is a blue print while objects are actual objects existing in real world. Example we have class CAR which has attributes and methods like Speed, Brakes, Type of Car etc.Class CAR is just a prototype, now we can create real time objects which can be used to provide functionality. Example we can create a Maruti car object with 100 km speed and urgent brakes.

Questions : 5 What are different properties provided by Object-oriented systems ? Answers : 5

Following are characteristics of Object Oriented System’s:-
Abstraction
It allows complex real world to be represented in simplified manner. Example color is abstracted to RGB.By just making the combination of these three colors we can achieve any color in world. It’s a model of real world or concept.

Encapsulation
The process of hiding all the internal details of an object from the outside world.

Communication
Using messages when application wants to achieve certain task it can only be done using combination of objects. A single object can not do the entire task. Example if we want to make order processing form. We will use Customer object, Order object, Product object and Payment object to achieve this functionality. In short these objects should communicate with each other. This is achieved when objects send messages to each other.

Object lifetime
All objects have life time. Objects are created, initialized, necessary functionalities are done and later the object is destroyed. Every object have there own state and identity, which differ from instance to instance.

Questions : 6 What is an Abstract class ? 

Answers : 6

Abstract class defines an abstract concept which can not be instantiated and comparing o interface it can have some implementation while interfaces can not. Below are some
points for abstract class:-
=>We can not create object of abstract class it can only be inherited in a below class.
=> Normally abstract classes have base implementation and then child classes derive from the abstract class to make the class concrete. 

Questions : 7 What are Abstract methods? 

Answers : 7

Abstract class can contain abstract methods. Abstract methods do not have implementation. Abstract methods should be implemented in the subclasses which inherit them. So if an abstract class has an abstract method class inheriting the abstract class should implement the method or else java compiler will through an error. In this way, an abstract class can define a complete programming interface thereby providing its subclasses with the method declarations for all of the methods necessary to implement that programming interface. Abstract methods are defined using "abstract" keyword. Below is a sample code snippet.
abstract class pcdsGraphics
{
abstract void draw();
}
Any class inheriting from "pcdsGraphics" class should implement the "draw" method or else the java compiler will throw an error. so if we do not implement a abstract method the program will not compile. 

Questions : 8 What is the difference between Abstract classes and Interfaces ? Answers : 8

Difference between Abstract class and Interface is as follows:-
Abstract class can only be inherited while interfaces can not be it has to be implemented.
Interface cannot implement any methods, whereas an abstract class can have implementation.
Class can implement many interfaces but can have only one super class.
Interface is not part of the class hierarchy while Abstract class comes in through inheritance.
Unrelated classes can implement the same interface.

Questions : 9 What is difference between Static and Non-Static fields of a class ? Answers : 9

Non-Static values are also called as instance variables. Each object of the class has its own copy of Non-Static instance variables. So when a new object is created of the same class it will have completely its own copy of instance variables. While Static values have only one copy of instance variables and will be shared among all the objects of the class.

Questions : 10 What are inner classes and what is the practical implementation of inner classes? 

Answers : 10

Inner classes are nested inside other class. They have access to outer class fields and methods even if the fields of outer class are defined as private.
public class Pcds
{
class pcdsEmp
{
// inner class defines the required structure
String first;
String last;
}
// array of name objects
clsName personArray[] = {new clsName(), new clsName(), new clsName()};
}
Normally inner classes are used for data structures like one shown above or some kind of helper classes.

Questions : 11 What is a constructor in class? 

Answers : 11

Constructor has the same name as the class in which it resides and looks from syntax point of view it looks similiar to a method. Constructor is automatically called immediately after the object is created, before the new operator completes. Constructors have no return type, not even void. This is because the implicit return type of a class' constructor is the class type itself. It is the constructor's job to initialize the internal state of an object so that the code creating an instance will have a fully initialized, usable object immediately. 

Questions : 12 Can constructors be parameterized? 

Answers : 12

Yes we can have parameterized constructor which can also be termed as constructor overloading. Below is a code snippet which shows two constructors for pcdsMaths class one with parameter and one with out.
class pcdsMaths
{
double PI;
// This is the constructor for the maths constant class.
pcdsMaths()
{PI = 3.14;}
pcdsMaths(int pi)
{
PI = pi;
} } 

  

Questions : 13 What is the use if instanceof keyword? and How do refer to a current instance of object? 

Answers : 13

"instanceof" keyword is used to check what is the type of object.
we can refer the current instance of object using "this" keyword. For instance if we have class which has color property we can refer the current object instance inside any of the method using "this.color". 

Questions : 14 what is Bootstrap, Extension and System Class loader? or Can you explain primordial class loader? 

Answers : 14

There three types of class loaders:-
BootStrap Class loader also called as primordial class loader.
Extension Class loader.
System Class loader. Let’s now try to get the fundamentals of these class loaders.

Bootstrap Class loader
Bootstrap class loader loads those classes those which are essential for JVM to function properly. Bootstrap class loader is responsible for loading all core java classes for instance java.lang.*, java.io.* etc. Bootstrap class loader finds these necessary classes from “jdk/ jre/lib/rt.jar”. Bootstrap class loader can not be instantiated from JAVA code and is implemented natively inside JVM. 

Extension Class loader
The extension class loader also termed as the standard extensions class loader is a child of the bootstrap class loader. Its primary responsibility is to load classes from the extension directories, normally located the “jre/lib/ext” directory. This provides the ability to simply drop in new extensions, such as various security extensions, without requiring modification to the user's class path. 

System Class loader
The system class loader also termed application class loader is the class loader responsible for loading code from the path specified by the CLASSPATH environment variable. It is also used to load an application’s entry point class that is the "static void main ()" method in a class.

Questions : 15 what’s the main difference between ArrayList / HashMap and Vector / Hashtable? 

Answers : 15

Vector / HashTable are synchronized which means they are thread safe. Cost of thread safe is performance degradation. So if you are sure that you are not dealing with huge number of threads then you should use ArrayList / HashMap.But yes you can still
synchronize List and Map’s using Collections provided methods :-
List OurList = Collections.synchronizedList (OurList);
Map OurMap = Collections.synchronizedMap (OurMap);

Questions : 16 What are access modifiers? 

Answers : 16

Access modifiers decide whether a method or a data variable can be accessed by another method in another class or subclass.
four types of access modifiers:

Public: - Can be accessed by any other class anywhere.

Protected: - Can be accessed by classes inside the package or by subclasses ( that means classes who inherit from this class).

Private - Can be accessed only within the class. Even methods in subclasses in the same package do not have access.

Default - (Its private access by default) accessible to classes in the same package but not by classes in other packages, even if these are subclasses. 

Questions : 17 Define exceptions ? 

Answers : 17

An exception is an abnormal condition that arises in a code sequence at run time. Basically there are four important keywords which form the main pillars of exception handling: try, catch, throw and finally. Code which you want to monitor for exception is contained in the try block. If any exception occurs in the try block its sent to the catch block which can handle this error in a more rational manner. To throw an exception manually you need to call use the throw keyword. If you want to put any clean up code use the finally block. The finally block is executed irrespective if there is an error or not.

Questions : 18 What is serialization?How do we implement serialization actually? Answers : 18

Serialization is a process by which an object instance is converted in to stream of bytes. There are many useful stuff you can do when the object instance is converted in to stream of bytes for instance you can save the object in hard disk or send it across the network.

In order to implement serialization we need to use two classes from java.io package ObjectOutputStream and ObjectInputStream. ObjectOutputStream has a method called writeObject, while ObjectInputStream has a method called readObject. Using writeobject we can write and readObject can be used to read the object from the stream. Below are two code snippet which used the FileInputStream and FileOutputstream to read and write from harddisk.