1. What is the difference between an Oracle Database and an Oracle Instance?
Ans:
A database is the physical storage (data, control, redo files), while an instance is the memory (SGA) and background processes that access and manage the data. Database = storage, Instance = engine.
2. How do indexes work in Oracle, and what types are there?
Ans:
Indexes speed up queries by avoiding full table scans. Common types: B-Tree (high-cardinality), Bitmap (low-cardinality), Unique, Function-based, and Reverse key. They improve reads but slow down writes.
3. What are the types of joins in Oracle SQL?
Ans:
INNER JOIN returns matching rows, LEFT JOIN all from left + matches, RIGHT JOIN all from right, FULL OUTER JOIN all rows from both. CROSS JOIN makes Cartesian product, SELF JOIN joins a table to itself.
4. What is a self-join in SQL?
Ans:
A self-join is a specific type of join where a table is joined with itself. It is helpful for locating connections inside a single table, including hierarchical data or comparing rows. In a self join the same table appears twice in the from clause with different aliases to distinguish between the two instances. Self joins are commonly used to query hierarchical data structures like organizational charts.
5. How do the commands DELETE and TRUNCATE vary from one another?
Ans:
The DELETE command is a DML statement eliminates table rows according to a condition and can be rolled back. The TRUNCATE command is a DDL statement that removes all rows from a table and cannot be rolled back. Additionally, DELETE can be used with a WHERE clause to delete specific rows, whereas TRUNCATE removes all rows without any condition. TRUNCATE is generally faster than DELETE because it does not generate individual row delete statements.
6. What is a correlated subquery?
Ans:
A correlated subquery is a subquery references columns from the outer query. Unlike a regular subquery, which is independent, For every row that the outer query processes, a correlated subquery is examined once.This type of subquery is useful for comparing each row to a set of values. Correlated subqueries are often used in WHERE clauses to filter results based on related data.
7. What is normalization in databases?
Ans:
A database design method called normalization removes unwanted features including insertion, update and deletion anomalies and lessens data redundancy. It involves organizing the attributes and tables of a database to ensure that dependencies are properly enforced. The process typically involves dividing large tables into smaller ones and defining relationships among them. Normalization helps in maintaining data integrity and consistency.
8. What distinguishes an OUTER JOIN from an INNER JOIN?
Ans:
An INNER JOIN returns only the rows have matching values in both tables. All rows from a single table are returned via an OUTER JOIN, and the matched rows from the second table if there is no match, the result is NULL on the side of the second table. Outer joins can be further classified into LEFT, RIGHT and FULL OUTER joins, depending on which table's rows are returned when there is no match.
9. What is a trigger in PL/SQL?
Ans:
A trigger is a unique type of stored procedure that starts up automatically when the database server experiences an event. When a user attempts to alter data using a data manipulation language (DML) event, DML triggers start to run. INSERT, UPDATE, and DELETE statements on a table or view are examples of DML events.
10. What is a cursor in PL/SQL?
Ans:
A cursor in PL/SQL is a pointer to a context area that stores the SQL statement and its processing information. Implicit cursors, which are automatically created by Oracle for SQL DML statements and explicit cursors which are specified for sophisticated queries by the programmer requiring multiple fetches. Cursors allow for row-by-row processing of query results.
