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?
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?
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?
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.
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