These Oracle questions have been designed for various interviews, competitive exams and entrance tests. We have covered questions on both basic and advanced concepts which will help you improve your skills to face interview questions on Oracle.
Who are these Oracle interview questions designed for?
All the developers, programmers and software engineers will find these questions extremely useful. All freshers, BCA, BE, BTech, MCA and college students wanting to make a career in Software Development will be highly benefited by these questions.
Oracle interview questions topics
This section covers Oracle topics like - View, functions, datatype, subquery, Join, VArray, Cursor, constraints, database objects, Oracle backup, trigger etc.
1. What is a View and how is it different from a table?
Answer:
A view is a user-defined database object that stores the results of a SQL query. You can refer a view as a logical table as it does not store data physically.
It is a virtual table that derives its data from one or more table
A table holds data but not SQL query.
You can update and delete data from a table but you can't do so with a view.
The table can be updated or deleted while Views cannot be done so.
Video : Oracle interview questions and answers for freshers
2. What is the use of WITH CHECK OPTION in views?
Answer:
If view is created with WITH CHECK OPTION, row insertion is allowed based on some condition. To ensure the consistency of the view, you use the WITH CHECK OPTION clause when you create or modify the view. It prevents visible rows from being updated to non visible rows.
3. What is a Data Dictionary and how can it be created?
Answer:
Whenever a new database is created, a database specific data dictionary gets created by the system.
The data dictionary is where Oracle stores metadata about objects in the database. You can think of it as the set of tables that keep track of your tables, indexes, and other objects. For example, when you create a table, the table name, column names, types, tablespace, and more are stored in the data dictionary.
It has a set of read-only tables and views and it is physically stored in the SYSTEM tablespace.
4. Explain the difference between TRANSLATE and REPLACE.
Answer:
Translate is used for character by character substitution. It replaces a single character at a time.
It will replace the 1st character in the str_replace with the 1st character in the replacement_str. Then it will replace the 2nd character in the str_replace with the 2nd character in the replacement_str and so on
The NVL function is used to replace NULL value with another value. It lets you substitute a value when a null value is encountered
NVL(Value, replace value)
6. What is COALESCE function?
Answer:
The COALESCE() function returns the first non-null expression in a list. If all values in the list are null, then the coalesce function will return NULL.
Coalesce(value1, value2,value3,…)
7. What is LOB datatype?
Answer:
LOB is Large Object Data type. Tables can have multiple columns for LOB data types. But LOBs can't be primary keys nor use with distinct, group by, order by or Join
Types of LOB are BLOB, CLOB and NCLOB
BLOB is an abbreviation for Binary Large object. BLOB accepts large binary object such as Image and Video files
CLOB is an abbreviation for character large object, can store large amounts of character data
NCLOB accepts CLOB data in unicode that represents most universal standard which supports all major languages.
