Oracle Technical Interview Questions part 3

deepblogs.net

1 month ago

Spread the knowledge

Table of Contents

Oracle Technical Interview Questions part 3

Q1) What are the different types of indexes in Oracle, and when would you use each type?

Oracle offers several types of indexes, each suited for different use cases to optimize query performance. Here’s a rundown of the main ones:

1. B-tree Indexes

Use case: General-purpose indexing. Details: These are the default index type in Oracle. They provide fast access to rows for equality and range queries. Suitable for most indexing needs.

2. Bitmap Indexes

Use case: Low-cardinality columns (columns with few distinct values). Details: Ideal for columns with a limited number of distinct values, such as gender or status flags. Bitmap indexes are efficient for complex queries involving multiple conditions (e.g., OLAP).

3. Clustered Indexes

Use case: Tables clustered together on disk. Details: These indexes are used with clustered tables. They can improve performance for joins between tables that are often queried together.

4. Function-Based Indexes

Use case: Columns involving expressions or functions. Details: Allows indexing on expressions or functions applied to columns, rather than just the column values. Useful when queries involve calculations or transformations.

5. Reverse Key Indexes

Use case: Columns with monotonically increasing values (e.g., sequence numbers). Details: Reverses the bytes of the indexed column. This helps distribute index entries more evenly across the index blocks, reducing contention and improving performance.

6. Composite Indexes

Use case: Multiple columns indexing. Details: Combines multiple columns into a single index. Useful for queries that filter or sort based on multiple columns.

7. Domain Indexes

Use case: Specialized applications (e.g., spatial, text). Details: These are custom indexes created using Oracle’s Data Cartridge framework. Useful for specialized data types like spatial or text.

8. Global and Local Partitioned Indexes

Use case: Large partitioned tables. Details: Indexes that align with table partitions. Global partitioned indexes can span multiple partitions, while local partitioned indexes align with individual table partitions. Useful for improving performance and manageability of partitioned tables. (Oracle Technical Interview Questions part 3)

Q2)How do you implement a recursive query in Oracle SQL? Provide an example.(Oracle Technical Interview Questions part 3)

How do you implement a recursive query in Oracle SQL? Provide an example.

In Oracle SQL, you can implement a recursive query using a Common Table Expression (CTE) with the WITH clause. This type of query is particularly useful for hierarchical or tree-structured data, such as organizational charts, bill of materials, or directory structures.

Here’s a step-by-step example of a recursive query to find all employees in an organization reporting to a specific manager:

Example Table Structure

Assume we have a table called EMPLOYEES with the following structure:

EMPLOYEE_ID	EMPLOYEE_NAME	MANAGER_ID
1	Alice	null
2	Bob	1
3	Carol	1
4	Dave	2
5	Eve	2

Recursive Query to Find All Employees Reporting to a Manager

WITH RECURSIVE EmployeeHierarchy AS (
— Anchor member: Start with the root manager (e.g., employee with ID 1)
SELECT
EMPLOYEE_ID,
EMPLOYEE_NAME,
MANAGER_ID,
LEVEL AS HIERARCHY_LEVEL
FROM
EMPLOYEES
WHERE
EMPLOYEE_ID = 1
UNION ALL
— Recursive member: Join employees with their managers
SELECT
e.EMPLOYEE_ID,
e.EMPLOYEE_NAME,
e.MANAGER_ID,
eh.HIERARCHY_LEVEL + 1
FROM
EMPLOYEES e
JOIN EmployeeHierarchy eh ON e.MANAGER_ID = eh.EMPLOYEE_ID
)
SELECT
EMPLOYEE_ID,
EMPLOYEE_NAME,
MANAGER_ID,
HIERARCHY_LEVEL
FROM
EmployeeHierarchy
ORDER BY
HIERARCHY_LEVEL;

Explanation

Anchor Member: The initial part of the CTE selects the root manager (in this case, Alice with EMPLOYEE_ID = 1).
Recursive Member: The recursive part of the CTE joins the EMPLOYEES table with the result of the previous level, effectively traversing the hierarchy.
Final SELECT Statement: The main query selects from the CTE and orders the results by the hierarchy level.

Oracle interview questions. Pic credit: Pinterest

Q3) What is the purpose of the MERGE statement, and how does it work?(Oracle Technical Interview Questions part 3)

The MERGE statement in Oracle is used to perform an upsert operation—a combination of update and insert—in a single SQL statement. It is particularly useful when you need to either update existing rows in a target table or insert new rows when they do not exist.

Purpose of the MERGE Statement

Streamlines Data Manipulation: Avoids writing separate INSERT and UPDATE statements, reducing complexity and improving performance.
Efficient Data Integration: Commonly used in ETL (Extract, Transform, Load) processes to integrate data from one table into another.
Improved Performance: Optimized for handling large datasets by reducing the number of scans of the target table.

Syntax:

MERGE INTO target_table USING source_table
ON (condition)
WHEN MATCHED THEN
UPDATE SET column1 = value1, column2 = value2, …
WHEN NOT MATCHED THEN
INSERT (column1, column2, …)
VALUES (value1, value2, …);

How It Works

MERGE INTO target_table USING source_table: Specifies the target table where the changes will be made and the source table providing the data.
ON (condition): Defines the matching condition between the target and source tables.
WHEN MATCHED THEN UPDATE: Updates rows in the target table that satisfy the ON condition.
WHEN NOT MATCHED THEN INSERT: Inserts rows into the target table when no match is found.

Example

Assume we have the following tables:

Target Table (employees):

employee_id	name	salary
101	John	50000
102	Alice	60000

Source Table (new_employees):

employee_id	name	salary
101	John	55000
103	Bob	45000

MERGE Statement

Result After Execution

Updated employees Table:

employee_id	name	salary
101	John	55000
102	Alice	60000
103	Bob	45000

Benefits

Simplicity: Single statement for both updates and inserts.
Performance: Reduces the overhead of multiple operations and table scans.
Clarity: Code is more readable and easier to maintain.

Q4) Explain the difference between correlated and non-correlated subqueries. Provide examples.(Oracle Technical Interview Questions part 3)

Correlated Subqueries

A correlated subquery is a subquery that references columns from the outer query. It is executed once for each row processed by the outer query. Essentially, the subquery is dependent on the outer query.

Example:

Let’s consider a table EMPLOYEES with the following columns:

EMPLOYEE_ID	EMPLOYEE_NAME	SALARY	DEPARTMENT_ID
1	Alice	5000	10
2	Bob	6000	20
3	Carol	5500	10
4	Dave	4000	20
5	Eve	4500	10

The following query finds employees whose salary is above the average salary of their respective departments:

SELECT EMPLOYEE_NAME, SALARY, DEPARTMENT_ID
FROM EMPLOYEES e
WHERE SALARY > (
SELECT AVG(SALARY)
FROM EMPLOYEES
WHERE DEPARTMENT_ID = e.DEPARTMENT_ID
);

Non-Correlated Subqueries

A non-correlated subquery is a subquery that is independent of the outer query. It is executed once and the result is used by the outer query.

Example:

Let’s consider the same EMPLOYEES table. The following query finds employees whose salary is above the overall average salary of all employees:

SQL Code:

SELECT EMPLOYEE_NAME, SALARY
FROM EMPLOYEES
WHERE SALARY > (
SELECT AVG(SALARY)
FROM EMPLOYEES
);

Key Differences

Dependency: Correlated subqueries depend on the outer query for their values, while non-correlated subqueries are independent and execute on their own.
Execution Frequency: Correlated subqueries execute once for each row processed by the outer query, whereas non-correlated subqueries execute only once for the entire query.

Q5) What are analytical functions in Oracle? Give an example using RANK() or DENSE_RANK()

What Are Analytical Functions in Oracle?

Analytical functions in Oracle are specialized SQL functions used to perform complex calculations over a set of rows that are related to the current query row. These functions return values based on a group of rows, but unlike aggregate functions (e.g., SUM or AVG), analytical functions do not reduce the number of rows in the result set.

They are commonly used in scenarios requiring ranking, windowing, and reporting operations.

Example Using RANK() and DENSE_RANK()

Let’s consider a table called SALES with the following structure:

SALE_ID	SALESPERSON	SALE_AMOUNT	SALE_DATE
1	Alice	1000	2025-01-01
2	Bob	2000	2025-01-02
3	Alice	1500	2025-01-03
4	Carol	2000	2025-01-04
5	Dave	3000	2025-01-05

Using RANK()

The RANK() function assigns a unique rank to each row within a partition of the result set. It allows for gaps in the ranking where there are ties.

SQL Code:

SELECT
SALESPERSON,
SALE_AMOUNT,
RANK() OVER (ORDER BY SALE_AMOUNT DESC) AS SALES_RANK
FROM
SALES;

Output:

SALESPERSON	SALE_AMOUNT	DENSE_SALES_RANK
Dave	3000	1
Bob	2000	2
Carol	2000	2
Alice	1500	3
Alice	1000	4

Key Differences:

RANK(): Leaves gaps in the ranking sequence when there are ties.
DENSE_RANK(): Does not leave gaps, maintaining a continuous sequence.

Explanation:

RANK():
- Rows with the same salary get the same rank.
- The next rank skips values (i.e., gaps appear).
DENSE_RANK():
- Rows with the same salary get the same rank.
- The next rank is assigned without gaps.

More Visit : https://taazakhobor.in/