Home
Oracle

Top 10 Oracle Interview Question and Answer - Database and SQL

4

These are some interview question and answer asked during my recent interview. Oracle interview questions are very important during any programming job interview. Interviewer always want to check how comfortable we are with any database either we go for Java developer position or C, C++  programmer position .So here I have discussed some basic question related with oracle database. Apart from these questions which is very specific to Oracle database you may find some general questions related to database fundamentals and SQL e.g. Difference between correlated and noncorrelated subquery in database or truncate vs delete in SQL etc.

Some of the most important topics in Oracle Interview questions are SQL, date, inbuilt function, stored procedure and less used features like cursor, trigger and views. These questions also gives an idea about formats of questions asked during Oracle Interview.

Oracle Interview Question and Answer

Question 1: Oracle version 9.2.0.4.0 what does each number refers to?

Answer :oracle version number refers

  • 9-Major database release number
  •  2-Database Maintenance release number
  •  0-Application server release number
  •  4-Component Specific release number
  •  0-Platform specific release number

Question 2: How do you find current date and time in oracle?

Answer: This is one of the frequently asked Oracle Interview questions. I have seen this question every now and then. By the way SYSDATE function is used in oracle to find current date and time of operating system on which the database is running return type of function is DATE

Syntax:  SELECT TO_CHAR (SYSDATE, 'MM-DD-YYYY HH24:MI:SS') "Current_Date"  FROM DUAL.

Question 3: Write the syntax to find current date and time in format “YYYY-MM-DD”.

Answer:  SELECT TO_CHAR (SYSDATE, 'YYYY-MM-DD HH24:MI:SS') "Current_Date"   FROM DUAL

Question 4: How will you convert a date to char in Oracle give one example

Answer : Similar to previous Oracle Interview question, this is also one of the popular question in various Oracle Interviews. to_char() function is used to convert date to character we can specify format also in which we want the output.

SELECT to_char( to_date('11-01-2012', 'DD-MM-YYYY') , 'YYYY-MM-DD') FROM dual;

or

SELECT to_char( to_date('11-01-2012, 'DD-MM-YYYY') , 'DD-MM-YYYY') FROM dual;

Question 5: What is bulk copy or BCP in oracle?

Answer: BCP or bulk copy tool is one type of command line tool for unload data from database came into existence after oracle 8 .it is used to import or export data from tables and views but it will not copy structure of data same. Main advantage is fast mechanism for copying data and we can take backup of important data easily.

Question 6: What are the extensions used by oracle reports

Answer : Oracle reports are used to make business enable to provide information of all level within or outside in secure way. REP file and RDF file extensions are used by oracle report.

Question 7: What is Save Points in Oracle database?

Oracle database Interview Questions and Answers in SQL Answer :  SAVE POINTS are used to divide a transaction into smaller parts. It enables rolling back part of a transaction. Maximum of five save points are allowed. Whenever we encounter error we can rollback from the point where we set our SAVEPOINT.This is useful for multistage transaction and conditional transaction where commit and rollback depend on certain condition. This is another commonly asked Oracle Interview Question and since save points are also available in other database e.g. SQL Server, some time Interviewer follow up with differences with other database as well.

Question 8: How will you convert string to a date in oracle database?

Answer : This Oracle Interview questions is some time asked as follow up of previous Oracle Interview questions related to converting date to char in Oracle. By the way to_ date function is used to convert string to a date function.

Syntax :  to_date(string, format)

Example:   to_date('2012/06/12', 'yyyy/mm/dd')  It will return June 12, 2012

Question 9: What is hash cluster in Oracle?

Answer : This is one of my favorite Oracle Interview question. Hash cluster is one of the techniques to store the table in a hash cluster to improve the performance of data retrieval .we apply hash function on the table row’s cluster key value and store in the hash cluster. All rows with the same hash key value are stores together on disk.key value is same like key of index cluster ,for data retrieval Oracle applies the hash function to the row's cluster key value.

Question 10: What is Snap shot in Oracle database?

Answer : 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.

That’s all on this list of Oracle Interview questions and answers. This can be a good recap before appearing to any programming job interview, where questions from Oracle database is expected. You may want to prepare some SQL Interview question as well along with these Oracle questions, as questions related to SQL query e.g. How to find second highest salary in SQL or How to find duplicate records in table is quite popular on various Oracle Interviews.

Further Learning

Oracle Database 12c Fundamentals By Tim Warner

Oracle PL/SQL Fundamentals vol. I & II

The Complete SQL Bootcamp

Related SQL Interview Questions and Answers for practice

Difference between view and materialized view in SQL

How to use auto incremented identity id in Oracle and SQL Server

How to join three tables in one SQL query

Difference between clustered and non-clustered Index in SQL

How to copy or backup tables using SQL query