These are some interview questions and answers asked during my recent interview. Oracle interview questions are very important during any programming job interview. The interviewer always wants to check how comfortable we are with any database either we go for the Java developer position or C, C++ programmer position. So here I have discussed some basic questions related to the oracle database. Apart from these questions which are very specific to the Oracle database, you may find some general questions related to database fundamentals and SQL like 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 a cursor, trigger, and views. These questions also give an idea about the formats of questions asked during the Oracle Interview.
Some of the most important topics in Oracle Interview questions are SQL, date, inbuilt function, stored procedure, and less used features like a cursor, trigger, and views. These questions also give an idea about the formats of questions asked during the Oracle Interview.
Oracle Interview Questions and Answers
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 the 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 the 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 the previous Oracle Interview question, this is also one of the popular questions in various Oracle Interviews. to_char() function is used to convert date to the 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?
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.
Related SQL Interview Questions and Answers for practice
- Difference between view and materialized view in SQL
- How to find the second highest salary in SQL
- How to use auto incremented identity id in Oracle and SQL Server
- Top 5 Courses to learn SQL in depth
- 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
- 50 Microsoft SQL Server Interview Questions with answers
- 12 Database Index Interview Questions with Answers
Thanks for reading this article so far. If you like these Oracle Interview Questions then please share with your friends and colleagues. If you have any questions or doubt fell free to ask in comments.
P. S. - If you are new to Oracle SQL and want to learn both Oracle and PL/SQL in depth and looking for best online courses then you can also checkout these best Oracle SQL Courses to learn Oracle SQL in depth.
I have often greeted with this question in Oralce interviews, Which version of Oracle database do you use? and difference between two version of Oracle database e.g. 10g and previous version etc.
ReplyDeleteHi,
ReplyDeleteAbove Help Are Great.
You can more get mysql interview questions & answers on :
http://www.webslike.com/Thread-Oracle-Interview-Questions-Answers
I think, most popular question on Oracle interviews are, How to find difference between two dates in Oracle database? Can you please provide multiple answers for this question, and also reasoning behind, choosing best way to do that?
ReplyDeleteI was asked to find number of days between two dates in Java, and questions like find the orders which are more than one months old or 15 days old. As follow-up they asked me how to find number of months between two dates. So, they were pretty detailed and touching different aspect of date and time in Oracle.
ReplyDeleteGreat questions, Here is my 2 cents :
ReplyDelete1) How do you find MONTH and YEAR from a date in Oracle?
Answer : By using EXTRACT function, you can use it to get MONTH and YEAR from a DATE object. By the way, you can also use EXTRACT function to find number of months, years between two dates , as follows :
Extracting YEAR from DATE, and finding number of YEARS between dates :
SELECT EXTRACT(YEAR FROM TO_DATE('30/07/2011', 'DD/MM/YYYY')) TRADE_YEAR,
EXTRACT(YEAR FROM TO_DATE('30/10/2018', 'DD/MM/YYYY')) MATURITY_YEAR,
(EXTRACT(YEAR FROM TO_DATE('30/07/2011', 'DD/MM/YYYY')) - EXTRACT(YEAR FROM TO_DATE('30/10/2018', 'DD/MM/YYYY'))) NUM_OF_YEARS
FROM DUAL;
Result:
TRADE_YEAR MATURITY_YEAR NUM_OF_YEARS
2011 2018 -7
Similarly, you can find MONTH from DATE and calculate number of months between them :
---------------------------------------
SELECT EXTRACT(MONTH FROM TO_DATE('30/07/2011', 'DD/MM/YYYY')) TRADE_MONTH,
EXTRACT(MONTH FROM TO_DATE('30/10/2018', 'DD/MM/YYYY')) MATURITY_MONTH,
(EXTRACT(MONTH FROM TO_DATE('30/07/2011', 'DD/MM/YYYY')) - EXTRACT(MONTH FROM TO_DATE('30/10/2018', 'DD/MM/YYYY'))) NUM_OF_MONTHS
FROM DUAL;
----------------------------------------
Result :
TRADE_MONTH MATURITY_MONTH NUM_OF_MONTHS
7 10 -3
5. Write a SQL Statement to the show the following output exactly in multiple lines?
ReplyDeleteI ' AM
A
DEVELOPER.
can any one help, how 2 display this
You need use chracter function to do this.
ReplyDeleteSelect 'I am ' ||Chr(10) ||'a'||Chr(10)||'Developer' from dual;
Thanks
Jaspreet
Column s1 noprint
ReplyDeleteSelect q’[I’am]’,1 s1 from dual
Union
Select ‘a’ ,2 from dual
Union
Select ‘developer’, 3 from dual;
I was looking for some short Oracle database questions for telephonic round of interview, but didn't find any good list, so end up creating my own.
ReplyDeletewhat is explain plan in oracle?
Explain plan describe the execution plan choosen to execute a SQL query by Query Engine.
What is $ORACLE_BASE and where it is defined? $ORACLE_BASE is similar to $JAVA_HOME, it's an environment variable points to the
location of Oracle installation folder in disk. In Windows, you can defined it under user defined environment variable.
What is index in Oracle Database?
An index is a database object which is applied to a column to make searching easy in database. Index is normally used to improve SQL query performance.
How does a Query executes in Oracle? Can you list steps involved?
Following steps are involved in parsing SQL query :
1) parsing
2) binding
3) execute
4) fetch
What is init.ora file, where does it used, when it is read?
what is TOAD?
Difference beween segment, schema and table in Oracle database?
BCP doesn't exists in Oracle. Its a SQL Server/Sybase tool.
ReplyDeleteAs someone else pointed out, BCP is a command line tool for sql server/sybase. Oracle has never had "bcp".
ReplyDeleteAlso, you are wrong about five savepoints. It isn't limited to five. A simple example demonstrates "five" is not even close to reality:
ops$tkyte%ORA11GR2> connect /
Connected.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> drop table t;
Table dropped.
ops$tkyte%ORA11GR2> create table t ( x int );
Table created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> begin
2 for i in 1 .. 1000
3 loop
4 insert into t (x) values ( i );
5 execute immediate 'savepoint x_' || i;
6 end loop;
7 end;
8 /
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR2> select max(x) from t;
MAX(X)
----------
1000
ops$tkyte%ORA11GR2> rollback to savepoint x_500;
Rollback complete.
ops$tkyte%ORA11GR2> select max(x) from t;
MAX(X)
----------
500
ops$tkyte%ORA11GR2> rollback to savepoint x_100;
Rollback complete.
ops$tkyte%ORA11GR2> select max(x) from t;
MAX(X)
----------
100
ops$tkyte%ORA11GR2> rollback;
Rollback complete.
ops$tkyte%ORA11GR2> select max(x) from t;
MAX(X)
----------
--------------------------------------------------------------------
also - snapshots are the name materialized views were formally known as. they haven't been called snapshots since 1995 (20 years ago as of this writing...). while create snapshot still works - it is deprecated and is rewritten automagically as create materialized view. And they can be read only or read write....