Most Frequently asked Oracle database SQL questions and answers
This article covers a list of all the most important and commonly asked “Oracle Database SQL” interview questions and answers, which will help you to crack any interview.
Most Popular Oracle SQL Interview Questions and Answers
Q1 : What is Tablespace in Oracle?
Ans: Tablespaces are logical structures/ partitions in databases database where data is actually stored. So, we can say Oracle stores data logically in tablespaces and physically in data-files.
Databases, tablespaces, and data-files are closely related, but they have important differences:
An Oracle database consists of one or more logical storage units called tablespaces, which collectively store all of the database’s data.
Each tablespace in an Oracle database consists of one or more files called data-files, which are physical structures that conform to the operating system in which Oracle is running.
- SYSTEM tablespace
- Undo tablespace
- Default temporary tablespace
Q2 : What is Namespace in Oracle?
Ans: A namespace defines a group of object types, within which no two objects have the same name. Objects in different namespaces can share the same name.
Name of an object that share the same namespace must be unique, so that when referenced in any SQL syntax, the exact object can be found.
Q3 : What is data integrity?
Ans: Data integrity is a database state in which all the data values stored in the database are correct. Enforcing data integrity in database ensures the quality of the data in the database.
Categories of data integrity are:
Entity Integrity: It defines a row as a unique entity for a particular table. Entity integrity enforces the integrity of the identifier column(s) or the Primary key of a table.
Domain Integrity: Domain integrity validates the entries for a given table. It can be enforced through;
- CHECK constraint
- NOT NULL constraint
- FOREIGN KEY constraint
- DEFALUT key word
Referential Integrity: It preserves the defined relationship between tables when records are entered or deleted. It ensures that key values are consistent across tables.
User defined Integrity: It allows defining specific business rules that do not fall into any of the other integrity categories. There are business rules specific to an organizational business process. So, these business rules are handled at run time, usually by designing database triggers in PL/SQL.
Q4 : What are constraints in Oracle? Describe the types of constraints?
Ans: Constraints are the rule that helps to maintain data integrity in a database. Constraints in databases are used to restrict values in a database that affects integrity. Constraints in Oracle are of following types:
- NOT NULL Constraint
- Unique Constraint
- Primary Key Constraint
- Foreign Key Constraint
- Check Constraint
- REF Constraint
Q5 : What are the different states of constraint?
Ans: An integrity constraint defined on a table can be in one of the following states:
- ENABLE, VALIDATE
- ENABLE, NOVALIDATE
- DISABLE, VALIDATE
- DISABLE, NOVALIDATE
Q6 : What is deferred constraint?
A deferrable constraint is specified by using DEFERRABLE clause.
You can specify DEFERRABLE to indicate that in subsequent transactions you can use the SET CONSTRAINT[S] clause to defer checking of this constraint until after the transaction is committed. This setting in effect lets you disable the constraint temporarily while making changes to the database that might violate the constraint until all the changes are complete.
You cannot alter the deferrability of a constraint. That is, you cannot specify DEFERRABLE or NOT DEFERRABLE clause in an ALTER TABLE statement. You must drop the constraint and re-create it.
To define a unique constraint on a column as INITIALLY DEFERRED DEFERRABLE, issue the following statement:
CREATE TABLE product
CONSTRAINT unq_product_id UNIQUE (product_id) INITIALLY DEFERRED DEFERRABLE
Q7 : What is a Join? What are the types of Join in Oracle?
Ans: Join is a query that can get data from two or more tables, views or materialized views. A join is performed whenever multiple tables appear in the queries FROM clause.
- Equi Join or Simple join or Inner join
- Non-Equi join
- Self join
- Outer Join
Q8 : What are ANSI Joins in Oracle?
Ans: Oracle became ANSI compliant to joins in the year 1999.
- Cross Join
- Natural Join
- Using Clause
- Inner Join
- Self Join
- Left Outer join
- Right Outer join
- Full Outer join
Q9 : What is the difference between LEFT JOIN and INNER JOIN?
Ans: LEFT JOIN, we say to LEFT OUTER JOIN. Conceptually, Oracle only has outer join which can be achieved by outer join operator (+). If, the (+) operator is placed at the right side of the join condition, then it’s an Left Outer Join. And if, the (+) operator is placed at the left side of the join condition, then it’s an Right Outer Join.
Left/ Right / Full Outer Joins are ANSI concepts and as Oracle is ANSI compliance, it supports these joins. The explanation of these joins lies in their name. Left Outer Join (Left out). Hence Left Outer Join retrieves all the matching rows along with all the rows from Left side table those don’t match the join condition.
Inner Join only retrieves the records from both the tables which satisfy the join condition. It’s also called Equi Join as conceptually the operator used in it is equal to operator (=).
Q10 : How many types of sub-queries we have in Oracle?
Ans: We can have four different types of sub-queries in Oracle.
- Sub-queries in the select list called as Sub-select
- Sub-queries in the WHERE clause called Nested subquery
- Sub-queries in the FROM clause called as Inline view
- Sub-queries in the WHERE clause but the subquery contains a condition which references a column from the outer table and this is called Correlated subquery
Q11 : What is the limit for the following in Oracle database?
- Number of rows in Oracle database table
- Number of columns in Oracle database table
- Number of constraints per column
- Number of columns per Index
- Number of columns per bitmapped index
- Number of columns in partition key
- Number of subqueries in the FROM clause
- Number of subqueries in the WHERE clause
Ans: The limit explained is as per Oracle 10g release 2
Oracle database is designed in such a way that it can support unlimited number of rows per table.
However it imposes a limit in the number of columns per table. The maximum number of columns per table is 1000.
We can define unlimited constraints on a column
Max 32 columns are allowed per index
Max 30 columns are allowed per bitmapped index
Maximum number of columns allowed in partition key is 16
A subquery can contain another subquery. Oracle Database imposes no limit on the number of subquery levels in the FROM clause of the top-level query.
You can nest up to 255 levels of subqueries in the WHERE clause.
Q12 : What is a View?
Ans: View is a schema object and is like a virtual table. A view takes up no storage space other than for the definition of the view in the data dictionary.
View can be within the database either in valid state or in invalid state. The view will operate for any business operation only when it is in valid state. The invalid state view can’t be referred in operational logic, but stay in the database as forced views. As soon as the basic requirements of this view are fulfilled, the view automatically goes into valid state.
Q13 : What is a Complex view?
Ans: A view is called a complex view if the view SELECT query contains any of the following constructs.
- A set operator
- A DISTINCT operator
- An aggregate or analytic function
- * A GROUP BY, ORDER BY, MODEL, CONNECT BY, or START WITH clause
- A collection expression in a SELECT list
- A sub-query in a SELECT list
- A sub-query designated WITH READ ONLY
- Pseudo columns – If a view contains Pseudo columns or expressions, then you can only update the view with an UPDATE statement that does not refer to any of the Pseudo columns or expressions.
Q14 : What is a Sequence?
Ans: Sequence is a schema object that can generate unique sequential values that to integers. The sequence values are often used for Primary Keys and Unique Keys. Sequence numbers are generated independent of tables. So, the same Sequence can be used for one or for multiple tables. When a Sequence number is generated, the Sequence is incremented/decremented independent of the transaction is committed or rolled back.
Sequences can be shared between two users, between two servers of the same database, but can’t be shared between two databases. Multiple users can concurrently increment the same Sequence and in this process one user can never acquire the Sequence number generated by another user.
Q15 : What is a Pseudo-column?
A pseudo-column behaves like a table column but is not actually stored in the table. You can select from pseudo-columns, but you cannot insert, update, or delete their values. Oracle database have following pseudo-columns:
- CURRVAL and NEXTVAL
A pseudo-column is also similar to a function without arguments. However, functions without arguments typically return the same value for every row in the result set, whereas pseudo columns typically return a different value for each row.
Q16 : What is ROWNUM?
ROWNUM is a pseudo column in Oracle Database, that returns a number indicating the order in which Oracle selects the row from a table or set of joined rows. The first row selected has a
ROWNUM of 1, the second has 2, and so on.
ROWNUM value is not assigned permanently to a row (this is a common misconception).
ROWNUM value may change each time a query is executed.
Q17 : What is ROWID?
ROWID is a pseudo column in Oracle Database, that returns the address of a row in a table. You can use the
ROWID pseudo column in the
WHERE clause of a query.
SELECT ROWID, Empno, Ename, Job
WHERE ROWID = "<rowid_value>";
You cannot insert, update, or delete a value of the ROWID pseudo column.
You should not use
ROWID as the primary key of a table. If you delete and reinsert a row, then its
ROWID may change. If you delete a row, then Oracle may reassign its
ROWID to a new row inserted later.
In Oracle database, the
ROWID can be used as a pseudo column and also as a Data Type.
ROWID values have several important uses:
- They are the fastest way to access a single row.
- They are unique identifiers for rows in a table.
Q18 : What is LEVEL?
Ans: LEVEL is a pseudo-column which is never stored in the database and always operates on hierarchical data. LEVEL always produces the generational numbers which are decided from where the hierarchy is started. In short, LEVEL is an auto-generated number finalized at runtime according to the hierarchy from where it starts.
Q19 : What is a Full table scan in Oracle?
A full table scan (also known as a sequential scan) is a scan made on a database where each row of the table is read in a sequential (serial) order and the columns encountered are checked for the validity of a condition.
In a database, a query that is not indexed results in a full table scan, where the database processes each record of the table to find all records meeting the given requirements. Even if the query selects just a few rows from the table, all rows in the entire table will be examined. This usually results in suboptimal performance but may be acceptable with very small tables or when the overhead of keeping indexes up to date is high.
Q20 : What is an Index? How many indexes are there in Oracle?
Ans: Index is a schema object which contains an entry for each value that appears in the indexed column or columns of the table. Indexes are schema objects that are logically and physically independent of the data in the objects with which they are associated. Thus, an index can be dropped or created without physically affecting the table for the index.
Index provides direct, fast access to rows. Oracle database provides a number of indexing schemes; but below are the most used ones.
- B-tree index
- Bitmap and Bitmap join index
- Function based index
- Domain index
Q21 : What are the properties of index?
Ans: Indexes have the following properties:
Usability: Indexes are usable by default or can be made unusable. An unusable index is not maintained by DML operations and is ignored by the optimizer. An unusable index can improve the performance of bulk loads. Instead of dropping an index and later re-creating it, you can make the index unusable and then rebuild it.
Unusable indexes and index partitions do not consume space. When you make a usable index unusable, the database drops its index segment.
Visibility: Indexes are visible (default) or invisible. An invisible index is maintained by DML operations and is not used by default by the optimizer. Making an index invisible is an alternative to making it unusable or dropping it. Invisible indexes are especially useful for testing the removal of an index before dropping it or using indexes temporarily without affecting the overall application.
Q22 : What is B-tree index?
Ans: These indexes are the standard index types. They are excellent for primary key and highly selective indexes.
Q23 : What is Bitmap index?
Ans: In a conventional B-tree index, one index entry points to a single row. In a bitmap index, each index key stores pointers to multiple rows.
Bitmap indexes are primarily designed for data warehousing or environments in which queries reference many columns in an ad-hoc fashion. Situations that may call for a bitmap index include:
The indexed columns have low cardinality, that is, the number of distinct values is small compared to the number of table rows.
The indexed table is either read-only or not subject to significant modification by DML statements.
Q24 : What is Function based index?
Ans: Function based indexes allow creation of indexes on expressions, internal functions, and user-written functions in PL/SQL.
Q25 : Why to use non-unique index?
Ans: Non-unique indexes permit duplicates values in the indexed column or columns. For example, the first_name column of the employees table may contain multiple Mike values. For a non-unique index, the ROWID is included in the key in sorted order, so non-unique indexes are sorted by the index key and rowed (ascending).
Q26 : What is a Synonym?
Ans: A synonym is an alternative name for a table, view, sequence, procedure, stored function, package, materialized view, user-defined object type, or another synonym.
Synonyms provide both data independence and location transparency. That means synonyms are used, when you are granting access to an object from another schema and you don’t want the users to have to worry about knowing which schema owns the object.
Oracle database provides 2 types of synonyms.
- Private Synonym
- Public Synonym
Q27 : What are public synonyms and who owns it?
PUBLIC to create a public synonym. Public synonyms are accessible to all users. However each user must have appropriate privileges on the underlying object in order to use the synonym.
When resolving references to an object, Oracle Database uses a public synonym only if the object is not prefaced by a schema and is not followed by a database link.
A public synonym is owned by the special user group named
PUBLIC and is accessible to every user in a database. A private synonym is contained in the schema of a specific user and available only to the user and to grantees for the underlying object.
Q28 : What is the difference between Truncate and Delete Statement?
TRUNCATE TABLE empties a table completely. Logically Truncate is equivalent to delete statement that deletes all rows. But technically the difference is like:
Data truncated by using truncate statement is lost permanently and cannot be retrieved even by rollback.
Data deleted by using the delete statement can be retrieved back by Rollback. Delete statement does not free up the table object allocated space.
Truncate operations drop and recreate the table, which is much faster than deleting rows one by one.
Performance is low if all rows are attempted for deletion.
Releases the storage space. ROWID associated with each record is released
Space is not released. ROWID’s are not released
Filters can’t be applied on Truncate.
Delete can be conditional.
We cannot truncate the parent table of an enabled foreign key constraint. You must disable the constraint before truncating the table. An exception is that you can truncate the table if the integrity constraint is self-referential.
Delete can be applied to the parent table of an enabled foreign key constraint by providing the below options during foreign key declaration: ON DELETE CASCADE or ON DELETE SET NULL
Q29 : What is Commit, Rollback and Save point?
Ans: Commit, Rollback and Savepoint are DCL commands.
COMMIT – Makes changes to the current transaction permanent. It Erases the savepoints and releases the transaction locks.
SAVEPOINT – Savepoints allow to arbitrarily hold work at any point of time with option of later committing. They are used to divide transactions into smaller portions.
ROLLBACK – This statement is used to undo work.
Q30 : What is the difference between DDL, DML and DCL structures?
Ans: DDL statements are used for defining data. Ex: Create, Alter, Drop, Truncate and Rename.
DML statements are used for manipulating data. Ex: Insert, update, delete, select.
DCL statements are used to control the access of data. Ex: Grant, Revoke.
Q31 : How to get the table definition (DDL) in Oracle?
DBMS_METADATA package in Oracle database, provides a way for you to retrieve metadata from the database dictionary as XML. Use below query to get the ddl of a table “EMP” .
SELECT DBMS_METADATA.GET_DDL('TABLE','EMP') FROM DUAL;
Q32 : How to get the Index DDL definition in Oracle?
SELECT DBMS_METADATA.GET_DDL('INDEX', 'your_index_name')
Q33 : Types of tables in Oracle?
Ans: Oracle Database tables fall into the following basic categories:
Relational tables :- Relational tables have simple columns and are the most common table type.
Object tables :- The columns correspond to the top-level attributes of an object type. A relational table can also be created with the following organizational characteristics and so we can again categorize relational tables as following types:
Heap-organized table : A heap-organized table does not store rows in any particular order. The CREATE TABLE statement creates a heap-organized table by default.
Index-organized table : An index-organized table orders rows according to the primary key values. For some applications, index-organized tables enhance performance and use disk space more efficiently. See “Overview of Index-Organized Tables”.
External table : An external table is a read-only table whose metadata is stored in the database but whose data in stored outside the database.
Temporary table : A temporary table definition persists in the same way as a permanent table definition, but the data exists only for the duration of a transaction or session. Temporary tables are useful in applications where a result set must be held temporarily, perhaps because the result is constructed by running multiple operations.
Q34 : What are temporary tables in Oracle?
Ans: A temporary table is a table that holds data only for the duration of a session or transaction. Temporary tables are useful in applications where a result set is to be buffered (temporarily persisted), many a times they are constructed by running multiple DML operations.
The definition of a temporary table is visible to all sessions, but the data in a temporary table is visible only to the session that inserts the data into the table.
Oracle support two types of temporary tables.
- Global Temporary Tables : Available since Oracle 8i
- Private Temporary Tables : Available since Oracle 18c