Interview Questions

Top 101+ Oracle Interview Questions and Answers

Interview Questions October 19, 2015
Oracle Interview Questions

Oracle Interview Questions and Answers

Oracle Interview Questions and Answers provided here are appropriate for both freshers and experienced candidates. By going through this questionnaire, anyone must be able to recognize the questions and explanations provided. All these questions will surely enable you to prepare for technical interviews and online tests which is conducted at the time of campus placement. The content of this questions includes the topics on Schema, Data File, Overloading, Oracle Instance, Tablespace, DML, Oracle programming, Operators, Query Types, Oracle SQL functions, Oracle Data Types, and many more. Referring to the below question and answers, you will be able to get more knowledge on basic and advanced level concepts. Roles which can excel using this material are Oracle Developers, Oracle Technical Consultant, Application Developer, Principal Consultant, Oracle DBA Lead and so on.

Q1. What are Schema Objects?

Schema objects are tables, views, sequences, synonyms, indexes,databases triggers, procedures, functions, packages.

Q2. What is a Table?

The table is collection inforamation.A table is the basic of rows and columns.data’s are stored rows and columns.

Q3. What is a View?

A View is a virtual table.Not a physical table.
representation of one or more tables
Every view has a query attached to it.

Q4. What is an Index?

An index is uset to performace tunnig in queries. which can be created to increase the performance of data retrieval. Index can be created on one or more columns of a table.

Q5. What is a Data file?

A database’s data files contain all the database data. The data of logical database structures is tables and indexes is physically stored in the data files allocated for a database.

Q6. What is the difference between UNIQUE or PRIMARY KEY Constraint?

A column defined as PRIMARY KEY can contain unique values
same as UNIQUE but UNIQUE can contain NULLs while a column defined as PRIMARY KEY cannot contain NULLs.

Q7. What is PL/SQL ?

PL/SQL is a programming language. SQL and procedural programming language constructs such as named blocks,iterations, conditional branching..

Q8. What is the basic structure of PL/SQL ?

PL/SQL uses block structure as its basic two structure. Anonymous blocks or nested blocks can be used in PL/SQL.

Q9. What is the difference between %ROWTYPE and TYPE RECORD ?

%ROWTYPE is its accept multiple data type.to be used whenever query returns an entire row of a table or view.
TYPE RECORD is accept single data type.to be used whenever query returns columns of different tables or views and variables.

Q10. What is a cursor ? Why cursor is required ?

Cursor is a named private sql area from where information can be passed and excuted or accessed. Cursors are required to process rows individually for queries returning multiple rows.

Q11. Explain the two types of cursors ?

Implicit cursors
Explicit cursors.
For loop cursors.
PL/SQL uses Implicit cursors for queries.
User defined cursors are called explicit cursors.
User using Cursors in for loop the Cursor called for loop Cursor.

Q12. What is Raise_application_error ?

Raise_application_error is a procedure of package DBMS_STANDARD. that allows to user_defined error or error code and error messages from stored sub program or database trigger.

Q13. What is the difference between a PROCEDURE and FUNCTION ?

A PROCEDURE may return one or more values or may not return at all.
A FUNCTION must returns a value using the return statement.

Q14. What is Overloading of procedures ?

The same procedure name is repeated with parameters of different datatypes and parameters in different positions, varying number of parameters is called overloading of procedures.

Q15. What is a package ? What are the advantages of package ?

database object that groups logically related procedures.
The advantages of packages are modularity, easier application design, information hiding, reusability and better performance

Q16. What is difference between TRUNCATE and DELETE?

Truncate is Dml Statements. Cannot be rolled back. Delete allows the filtered deletion.
Deleted records can be rolled back or committed.

Q17. What is a join? Explain the different types of joins?

Join is a query,retrieves data from related columns or rows from multiple tables.
Self join – Joining the table with itself
Equi Join – Joining two tables by equating two common columns
Non Equi Join – Joining two tables based on conditions other than equating two common columns
Outer Join – Joining two tables in such a way that query can also retrieve rows that do not have the corresponding join value in the other table.

Q18. What is a Subquery?

Subquery is a query embedded with another Query
Outer Query based on the Inner Query

Q19. What is correlated sub-query?

Correlated sub-query is a sub-query, which has reference to the main query.

Q20. Difference between SUBSTR and INSTR?

INSTR(string1, string2 [,n,[m]])
its find the position of the string
SUBSTR(string1, n, m)
SUBSTR returns a character string of size m in string 1, starting from nth position of string1.

Q21. Explain UNION, MINUS, UNION ALL, INTERSECT? INTERSECT returns all commmon rows selected by both queries.
MINUS returns all distinct rows selected by the first query but not by the second.
UNION returns all unique rows selected by either query.
UNION ALL returns all rows selected by either query, included all duplicates.
Q22. What are the Usages of SAVEPOINTS?

SAVEPOINTS are Transaction control Language.
its used to subdivide a transaction into smaller parts. It enables rolling back part of a transaction.
Maximums of five save points are allowed.

Q23. What is ROWID?

ROWID is a pseudo column attached to each row of a table.
It is 18 characters long, blockno. Rownumber.filenumber are the components of ROWID.

Q24. What is PRIMARY KEY, UNIQUE KEY, FOREIGN KEY?

PRIMARY KEY is a column or a combination of columns of a table which cab be used to uniquely identify a row of the table. PRIMARY KEY cannot be null.
UNIQUE KEY is a column or a combination of columns of a table, which can be used to identify a row of the table uniquely. UNIQUE KEY can be null.
FOREIGN KEY is a column or a combination of column which is the primary key or unique key of the referenced table. FOREIGN KEY can be null.

Q25. What is ON DELETE CASCADE?

ORACLE maintains referential integrity by automatically removing dependent foreign key values if a referenced primary or unique key value is removed.

Q26. What is difference between CHAR and VARCHAR2? What is maximum SIZE allowed for each type?

CHAR pads spaces to the maximum length. VARCHAR2 does not pad blank spaces.
For CHAR it is 255 and 2000 for VARCHAR2.

Q27. How to store the results of a query into a file?

The results of a query can be stored into a file by spooling into a file.
Eg. SPOOL filename.

Q28. Which two statements about subqueries are true? (Choose two.)

A. A single row subquery can retrieve data from only one table.
B. A SQL query statement cannot display data from table B that is referred to in its subquery,
unless table B is included in the main query’s FROM clause.
C. A SQL query statement can display data from table B that is referred to in its subquery,without including table B in its own FROM clause.
D. A single row subquery can retrieve data from more than one table.
E. A single row subquery cannot be used in a condition where the LIKE operator is used for comparison.
F. A multiple-row subquery cannot be used in a condition where the LIKE operator is used for comparison.

Q29. Which three functions can be used to manipulate character, number, or date column values? (Choose three.)

A. CONCAT
B. ROUND
C. TRUNC
D. RPAD
E. INSTR

Q30. Which statement about a table is true?

A. A table can have up to 10,000 columns.
B. The size of a table does NOT need to be specified.
C. A table CANNOT be created while users are using the
database.
D. The structure of a table CANNOT be modified while the table is online.

Q31. What will be returned from SIGN(ABS(NVL(-32,0)))?

A. 1
B. 32
C. 1
D. 0
E. NULL

Q32. Which functions could you use to strip leading characters from a character string. Select two.

A. LTRIM
B. SUBSTR
C. RTRIM
D. INSTR
E. MOD

Q33. All the operators are used in single row subquery except one

a) Between … and
b) < >
c) =
d) in

Q34. All the commands executes in iSQLplus except one

a) Column
b) Compute
c) define
d) Accept

Q35. Ascript file which will be executed automatically in iSQLPlus is

a) afiedt.buf
b) login.sql
c) both a and b
d) none of the above.

Q36. A command in iSQL plus is used to give the status of old and new value of variable

a) set feedback
b) set verify
c) set confirm
d) none of the above

Q37. All commands are used to save the changes of the transaction except one

a) Commit
b) exitting from sqlplus
c) DDL command
d) savepoint
e) none of the above

Q38. A Clause which is used in joining two tables other than equality operator is

a) join
b) on
c) in
d) using

Q39. A Clause which is the pseudocolumn used to know the current value of the sequence

a) nextval
b) current_val
c) currval
d) none of the above

Q40. A Query which is used in top-N analysis is

a) subquery
b) correlated subquery
c) inline query
d) outer query

Q41. An operator is used to get and display the redundant records

a) Union all
b) Distinct
c) Union
d) Intersect

Q42. All the datatypes with respect to Oracle 9i is true except one

a) DATE
b) TIMESTAMP
c) TIMSTAMP with TIME ZONE
d) TIMESTAMP WITH LOCAL TIME ZONE
e) None of the above

Q43. What is database?

A database is a logically collection of data with some inherent meaning.the data’s stored rows and collumns
database are used to store the informations.

Q44. To select records from the given row?

select * from SIVAEMP where rownum < =(select count(*)-&n from SIVAEMP);

To check the leap year select decode(mod(2004,4),0,’leap year’, ‘not a leap year’) from dual

To delete duplicate records delete from sivaemp where rowid not in(select max(rowid) from sivaemp group by empno)

To keep latest single record delete from sivaemp where rowid not in(select min(rowid) from sivaemp group by empno)//to keep oldest record

To select second max salary

select * from sivaemp where salary = (select max(salary) from sivaemp where salary not in (select max(salary) from sivaemp))

To select nth max salary select * from sivaemp a where &n-1 = (select count(*) from sivaemp b where a.salary < b .salary)
select * from sivaemp a where &n = (select count(*) from sivaemp b where a.salary < =b.salary)

To select nth min salary select * from sivaemp a where &n-1 = (select count(*) from sivaemp b where a.salary > b.salary)
select * from sivaemp a where &n = (select count(*) from sivaemp b where a.salary > =b.salary)

To select top(n) max salaries select * from sivaemp a where &n > (select count(*) from sivaemp b where a.salary < b .salary)

To select top(n) min salaries select * from sivaemp a where &n > (select count(*) from sivaemp b where a.salary > b.salary)

To select records from particular row(the remaining rows) select * from sivaemp minus select * from sivaemp where rownum < = (select count(*)- &n-1 from sivaemp )

to select first n rows select * from sivaemp where rownum < = (select count(*) + &n -count(*) from sivaemp)

Q45. Explain the difference between trigger and stored procedure.

Trigger in act which is performed automatically before or after a event occur when DML operations are occur Trigger is Fire.
Stored procedure is a set of functionality which is executed when it is explicitly invoked.

Q46. Differences between DATE and TIMESTAMP in Oracle

Date is used to store date and time values including month, day, year, century, hours, minutes and seconds. TimeStamp datatype stores everything that Date stores and additionally stores fractional seconds.
Date: 16:05:14
Timestamp: 16:05:14:

Q47. Create a copy of EMP table without any data?

CREATE TABLE EMP1 AS SELECT * FROM EMP WHERE‘CHIRU’=’VENKY’;

Q48. Delete the 10th record of EMP table?

DELETE FROM EMP WHERE ENAME= (SELECT ENAME FROM EMP WHERE ROWNUM < =10 MINUS SELECT ENAME FROM EMP WHERE ROWNUM<10);

Q49. Find all the departments which have more than 3 employees? SELECT D.DNAME, COUNT (E.ENAME) FROM EMP E, DEPT D WHERE D.DEPTNO=E.DEPTNO GROUP BY DNAME HAVING COUNT (EMPNO) > 3;

Display the manager who is having maximum number of employees working under him?

SELECT DISTINCT M.ENAME, COUNT (E.ENAME)
FROM EMP E, EMP M WHERE E.MGR=M.EMPNO
GROUP BY M.ENAME HAVING COUNT (E.ENAME) > =ALL
(SELECT COUNT (E.ENAME) FROM EMP E, EMP M
WHERE E.MGR=M.EMPNO GROUP BY M.ENAME);

Q50. Display the name of employees who joined on the same date?

SELECT A.ENAME FROM EMP A, EMP B
WHERE A.EMPNO < > B.EMPNO AND A.HIREDATE=B.HIREDATE;

Q51. Delete those employees who joined the company 10 years back from today?

SELECT ENAME FROM EMP
WHERE (SYSDATE-HIREDATE)/365 > 10

Display those employees whose salary is ODD value? SELECT ENAME, SAL FROM EMP
WHERE MOD (SAL, 2) < > 0;

Q52. Find out the number of employees whose salary is greater than their manager salary?

SELECT COUNT (E.ENAME) FROM EMP E, EMP M
WHERE E.MGR=M.EMPNO AND E.SAL > M.SAL

Q53. Select the count of employees in each dept where count is greater than 3?

SELECT JOB, COUNT (*) FROM EMP
GROUP BY JOB HAVING COUNT (*) > 3;

Display name of those employees who are getting the highest salary? SELECT * FROM EMP WHERE SAL IN (SELECT MAX (SAL) FROM EMP);

Q54. What is a transaction ?

A transaction is a set of SQL statements between any two COMMIT and ROLLBACK statements.

Q55. What is implicit cursor and how is it used by Oracle ?

An implicit cursor is user pass single line queries that time a cursor which is internally created by Oracle.It is created by Oracle for each individual SQL.

Q56. Which of the following is not a schema object :

Indexes, tables, public synonyms, triggers and packages ?

Public synonyms

Q57. Is there a PL/SQL Engine in SQL*Plus?

SQL*Plus does not have a PL/SQL engine.Thus, all your PL/SQL are send directly to the database engine for execution.This makes it much more efficient as SQL statements are not stripped off and send to the database individually.

Q58. Can one read/write files from PL/SQL?

Included in Oracle 7.3 is a UTL_FILE package that can read and write files.The directory you intend writing to has to be in your INIT.
Before Oracle 7.3 the only means of writing a file was to use DBMS_OUTPUT with the SQL*Plus SPOOL command.
DECLARE
fileHandler UTL_FILE.FILE_TYPE;
BEGIN
fileHandler := UTL_FILE.FOPEN(‘/home/oracle/tmp’, ‘myoutput’,’W’);
UTL_FILE.PUTF(fileHandler, ‘Value of func1 is %sn’, func1(1));
UTL_FILE.FCLOSE(fileHandler);
END;

Q59. How can I protect my PL/SQL source code?

PL/SQL V2.2, available with Oracle7.2, implements a binary wrapper for PL/SQL programs to protect the source code.This is done via a standalone utility that transforms the PL/SQL source code into portable binary object code (somewhat larger than the original).This way you can distribute software without having to worry about exposing your proprietary algorithms and methods.SQL*Plus and SQL*DBA will still understand and know how to execute such scripts.Just be careful, there is no “decode” command available. The syntax is: wrap name=myscript.sql oname=xxxx.yyy

Q60. Can one use dynamic SQL within PL/SQL? OR Can you use a DDL in a procedure ? How ?

From PL/SQL V2.1 one can use the DBMS_SQL package to execute dynamic SQL statements.
Eg: CREATE OR REPLACE PROCEDURE DYNSQL AS
cur integer;
rc integer;
BEGIN
cur := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(cur,’CREATE TABLE X (Y DATE)’,
DBMS_SQL.NATIVE);
rc := DBMS_SQL.EXECUTE(cur);
DBMS_SQL.CLOSE_CURSOR(cur);
END;

Q61. What are the various types of parameter modes in a procedure ?

IN, OUT AND INOUT.

Q62. What are the constructs of a procedure, function or a package ?

The constructs of a procedure, function or a package are :variables and constants
cursors
exceptions

Q63. Why Create or Replace and not Drop and recreate procedures ?

So that Grants are not dropped.

Q64. Can you pass parameters in packages ? How ?

Yes.You can pass parameters to procedures or functions in a package.

Q65. What are the parts of a database trigger ?

A triggering event or statement
A trigger restriction
A trigger action

Q66. What are the various types of database triggers ?

There are 12 types of triggers
Insert, Delete and Update Triggers.
Before and After Triggers.
Row and Statement Triggers.

Q67. What is the advantage of a stored procedure over a database trigger ?

We have control over the firing of a stored procedure but we have no control over the firing of a trigger.

Q68. What are cascading triggers? What is the maximum no of cascading triggers at a time?

When a statement in a trigger body causes another trigger to be fired, the triggers are said to be cascading.Max = 32.

Q69. What are mutating triggers ?

A trigger giving a SELECT on the table on which the trigger is written.

Q70. What are constraining triggers ?

A trigger giving an Insert/Updat e on a table having referential integrity constraint on the triggering table.

Q71. Describe Oracle database’s physical and logical structure ?

Physical : Data files, Redo Log files, Control file.
Logical : Tables, Views, Tablespaces, etc.

Q72. Can you increase the size of a tablespace ? How ?

Yes, by adding datafiles to it.

Q73. What is the use of Control files ?

Contains pointers to locations of various data files, redo log files, etc.

Q74. What is the use of Data Dictionary ?

It Used by Oracle to store information about various physical and logical structures e.g.Tables

Q75. What are the states of a rollback segment ? What is the difference between partly available and needs recovery ?

The various states of a rollback segment are :
ONLINE
OFFLINE
PARTLY AVAILABLE
NEEDS RECOVERY
INVALID.

Q76. What is the maximum no.of columns a table can have ?

254.

Q77. Can you pass a parameter to a cursor ?

Explicit cursors can take parameters,User create cursor called Explicit Cursor
CURSOR c1 (median IN NUMBER) IS
SELECT job, ename FROM emp WHERE sal > median;

Q78. What are the various types of RollBack Segments ?

The types of Rollback sagments are as follows :
Public Available to all instances
Private Available to specific instance

Q79. What are the disadvantages of SQL ?

Cannot drop a field
Cannot rename a field
Cannot manage memory
Procedural Language option not provided
Index on view or index on index not provided
View updation problem

Q80. When to create indexes ?

To be created when table is queried for less than 2% or 4% to 25% of the table rows.

Q81. How can you avoid indexes ?

To make index access path unavailable Use FULL hint to optimizer for full table scan Use INDEX or AND-EQUAL hint to optimizer to use one index or set to indexes instead of another

Q82. What is the difference between a view and a synonym ?

Synonym is Schema Object,just a second name of table,.View can be created with many tables, and with virtual columns and with conditions.

Q83. What is the difference between foreign key and reference key ?

Foreign key is attribute which refers to another table primary key. Reference key is the primary key of table referred by another table.

Q84. What is a package cursor ?

a cursor declare in the package specification without an SQL statement.
The SQL statement for the cursor is attached at runtime from calling procedures.

Q85. What are snap shots and views

Snapshots are mirror or replicas of tables. Views are built using the columns from one or more tables.

We Provide Best Oracle Training in Chennai at Besant Technologies with Certification & Placement Assistance.For More Details on Oracle Latest Interview books please contact 8099 770 770.

Besant Technologies – Chennai & Bangalore Branch Locations

Besant Technologies - Velachery Branch

Plot No. 119, No.8, 11th Main road, Vijaya nagar,

Velachery, Chennai - 600 042

Tamil Nadu, India

Landmark - Reliance Digital Opposite Street

  +91-8099 770 770

  info@besanttechnologies.com

Besant Technologies - Tambaram Branch

No.2A, 1st Floor, Duraisami Reddy Street,

West Tambaram, Chennai - 600 045

Tamil Nadu, India

Landmark - Near By Passport Seva

  +91-8099 770 770

  info@besanttechnologies.com

Besant Technologies - OMR Branch

No. 5/318, 2nd Floor, Sri Sowdeswari Nagar,

OMR, Okkiyam Thoraipakkam, Chennai - 600 097

Tamil Nadu, India

Landmark - Behind Okkiyampet Bus Stop,

  +91-8099 770 770

  info@besanttechnologies.com

Besant Technologies - Porur Branch

No. 180/84, 1st Floor, Karnataka Bank Building,

Trunk Road, Porur, Chennai - 600 116

Tamil Nadu, India

Landmark - Opposite to Gopalakrishna Theatre

  +91-8099 770 770

  info@besanttechnologies.com

Besant Technologies - Anna Nagar Branch

Plot No:1371, 28th street kambar colony,

I Block, Anna Nagar, Chennai - 600 040

Tamil Nadu, India

Landmark - Behind Reliance Fresh

  +91-8099 770 770

  info@besanttechnologies.com

Besant Technologies - T.Nagar Branch

Old No:146/2- New No: 48,

Habibullah Road,T.Nagar, Chennai - 600 017

Tamil Nadu, India

Landmark - Opposite to SGS Sabha

  +91-8099 770 770

  info@besanttechnologies.com

Besant Technologies - Thiruvanmiyur Branch

22/67, 1st Floor, North mada street, Kamaraj Nagar

Thiruvanmiyur, Chennai 600041

Tamil Nadu, India

Landmark - Above Thiruvanmiyur ICICI Bank

  +91-8099 770 770

  info@besanttechnologies.com

Besant Technologies - Siruseri Branch

No. 4/76, Ambedkar Street, OMR Road, Egatoor, Navallur,

Siruseri, Chennai 600130

Tamil Nadu, India

Landmark - Near Navallur Toll Gate, Next to Yamaha Showroom

  +91-8099 770 770

  info@besanttechnologies.com

Besant Technologies - Maraimalai Nagar Branch

No.37, Ground Floor, Thiruvalluvar Salai,

Maraimalai Nagar,Chennai 603209

Tamil Nadu, India

Landmark - Near to Maraimalai Nagar Arch

  +91-8099 770 770

  info@besanttechnologies.com

Besant Technologies - BTM Layout Branch

No 2, Ground floor, 29th Main Road,

Kuvempu Nagar,BTM Layout 2nd Coming from Silkboard,

AXA company signal, Stage, Bangalore - 560 076

Karnataka, India

Landmark - Next to OI Play School

  +91-8767 260 270

  info@besanttechnologies.com

Besant Technologies - Marathahalli Branch

No. 43/2, 2nd Floor, VMR Arcade,

Varthur Main Road, Silver Springs Layout,

Munnekollal, Marathahalli, Bengaluru - 560037

Karnataka, India

Landmark - Near Kundalahalli Gate Signal

  +91-8767 260 270

  info@besanttechnologies.com

Besant Technologies - Rajaji Nagar Branch

No. 309/43, JRS Ecstasy, First Floor,

59th Cross, 3rd Block, Bashyam Circle,

Rajaji Nagar, Bangalore - 560 010

Karnataka, India

Landmark - Near Bashyam Circle

  +91-8767 260 270

  info@besanttechnologies.com

Besant Technologies - Jaya Nagar Branch

No. 2nd Floor,1575,11th Main Road,

4th T-Block, Pattabhirama Nagar,

Jaya Nagar, Bangalore - 560 041

Karnataka, India

Landmark - Opposite to Shanthi Nursing Home

  +91-8767 260 270

  info@besanttechnologies.com

Besant Technologies - Kalyan Nagar Branch

No.513, 4th Cross Rd

2nd Block, HRBR Layout,

Kalyan Nagar, Bangalore - 560 043

Karnataka, India

Landmark - Opposite to kalayan nagar Axis Bank

  +91-8767 260 270

  info@besanttechnologies.com

Besant Technologies - Electronic City Branch

No. 7, 3rd Floor, Ganga Enclave,

Neeladri Road, Karuna Nagar, Doddathoguru Village,

Electronics City Phase 1, Electronic City,

Bangalore - 560100, karnataka, India

Landmark - Adjacent to HDFC Bank and State Bank Of India

  +91-8767 260 270

  info@besanttechnologies.com

Besant Technologies - Indira Nagar Branch

No.54, 1st Floor,

5th Main kodihalli,

Bengaluru, Karnataka 560008, India

Landmark - Behind Leela Palace Hotel,

  +91-8767 260 270

  info@besanttechnologies.com

Besant Technologies - HSR Layout Branch

Plot No. 2799 & 2800, 27th Main,

1st Sector, HSR Layout,

Bengaluru, Karnataka 560102, India

  +91-8767 260 270

  info@besanttechnologies.com

Besant Technologies - Hebbal Branch

No.29, 2nd Floor, SN Complex,

14th Main Road, E-Block Extention, Sahakara Nagar,

Bengaluru, Karnataka -560092, India

  +91-8767 260 270

  info@besanttechnologies.com

Scroll Up