Scenario Based Interview questions in oracle sql | database | For Experienced Professional

Free Interview Questions on Oracle SQL | Fresher to Experienced Scenario Based Interview questions in oracle sql | database | For Experienced Professional

Here are scenario-based interview questions designed for experienced professionals in Oracle SQL and database management. These questions cover a variety of advanced topics such as performance optimization, complex queries, and real-world troubleshooting situations.

Chapter 1: Advanced SQL Techniques

Question 1:
Scenario: You are working on a query that requires aggregating data from a large sales table by month and year. How would you structure your query to optimize the performance while ensuring the correct results?
Answer: Use the TO_CHAR function to extract the month and year from the date, and ensure indexing on the date column to improve performance.

SELECT TO_CHAR(order_date, 'YYYY-MM') AS month_year, SUM(sales_amount)
FROM sales
GROUP BY TO_CHAR(order_date, 'YYYY-MM');

Question 2:
Scenario: A query takes too long to execute due to the use of complex joins. How would you identify and optimize this query?
Answer: Use the EXPLAIN PLAN and SQL Trace to analyze the query execution plan. Focus on removing unnecessary joins, indexing frequently accessed columns, and ensuring joins use the most efficient methods (e.g., hash joins, index joins).

Question 3:
Scenario: You need to create a summary report of employees’ salaries by department. However, some departments have no employees. How would you handle this situation to include departments with zero employees?
Answer: Use a LEFT JOIN with the departments table to ensure that even departments with zero employees are included in the results.

SELECT d.department_name, COALESCE(SUM(e.salary), 0) AS total_salary
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_name;

Question 4:
Scenario: You are tasked with generating a report showing the highest salary in each department. How would you write the SQL query?
Answer: Use the MAX function along with GROUP BY to get the highest salary for each department.

SELECT department_id, MAX(salary) AS highest_salary
FROM employees
GROUP BY department_id;

Chapter 2: Subqueries and Joins

Question 5:
Scenario: You need to find all employees who have a higher salary than the average salary in their department. How would you write this query?
Answer: Use a correlated subquery that compares each employee’s salary with the average salary in their department.

SELECT employee_id, salary
FROM employees e
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
    WHERE department_id = e.department_id
);

Question 6:
Scenario: Your query is returning duplicate rows because of multiple matching rows in the joined tables. How can you avoid duplicates in the result?
Answer: Use the DISTINCT keyword to remove duplicates from the query result.

SELECT DISTINCT e.employee_id, e.department_id
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

Question 7:
Scenario: You need to join three tables (employees, departments, and locations) to find employees who work in a specific location. How would you structure this query?
Answer: Use JOIN clauses to link the three tables together, ensuring you connect them correctly via their respective keys.

SELECT e.employee_name, d.department_name, l.location_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
JOIN locations l ON d.location_id = l.location_id
WHERE l.location_name = 'New York';

Chapter 3: Data Manipulation and Transactions

Question 8:
Scenario: You need to insert data into a table but must ensure that the data is not duplicated. How would you approach this?
Answer: Use MERGE to insert data and ensure that if a record with the same key exists, it gets updated instead of inserted again.

MERGE INTO employees e
USING (SELECT 101 AS employee_id, 'John Doe' AS employee_name) new_data
ON (e.employee_id = new_data.employee_id)
WHEN MATCHED THEN UPDATE SET e.employee_name = new_data.employee_name
WHEN NOT MATCHED THEN INSERT (employee_id, employee_name) VALUES (new_data.employee_id, new_data.employee_name);

Question 9:
Scenario: After performing multiple UPDATE statements, you realize the changes need to be rolled back. How do you undo the updates?
Answer: If a COMMIT has not been issued, you can use ROLLBACK to undo the changes. If a commit has been issued, it’s impossible to roll back, but you can perform reverse updates manually.

ROLLBACK;

Question 10:
Scenario: You are inserting data into a table from a file. After inserting, some of the data fails due to a constraint violation. How would you handle the failure?
Answer: Use SAVEPOINT and ROLLBACK TO SAVEPOINT to handle partial transaction failures without rolling back the entire transaction.

SAVEPOINT before_insert;
BEGIN
   INSERT INTO employees VALUES (1, 'John');
   INSERT INTO employees VALUES (2, 'Jane');
   -- If an error occurs
   ROLLBACK TO SAVEPOINT before_insert;
END;

Chapter 4: Performance Tuning

Question 11:
Scenario: You have a query that runs very slowly despite proper indexing. What steps would you take to investigate and resolve this issue?
Answer: Analyze the execution plan using EXPLAIN PLAN and check for full table scans, inefficient joins, and missing indexes. Adjust the query or add appropriate indexes. You can also review database statistics.

Question 12:
Scenario: You have a table with millions of rows, and a query is slow due to large data processing. How can you improve performance for this query?
Answer: Consider partitioning the table, adding appropriate indexes, and optimizing the query by breaking it into smaller chunks or using parallel processing.

Question 13:
Scenario: An application performance is degrading due to inefficient queries. How would you identify which queries are consuming the most resources?
Answer: Use Oracle’s AWR reports or SQL Trace to identify resource-consuming queries, then analyze them for optimization opportunities.

Question 14:
Scenario: How would you resolve a situation where a table scan is taking too long, and you have no indexes on the table?
Answer: Create indexes on the columns used in WHERE, JOIN, and ORDER BY clauses. Analyze the query and execution plan to confirm the correct indexes.

CREATE INDEX idx_employee_id ON employees(employee_id);

Chapter 5: Data Integrity and Security

Question 15:
Scenario: You are tasked with restricting access to sensitive data in a table. How would you secure the data from unauthorized access?
Answer: Use GRANT and REVOKE to assign specific privileges to users. Additionally, use VIEWS to restrict direct access to sensitive columns.

CREATE VIEW emp_view AS
SELECT employee_id, employee_name FROM employees;
GRANT SELECT ON emp_view TO user_role;

Question 16:
Scenario: How would you ensure that all transactions are handled in an atomic manner?
Answer: Use COMMIT to save transactions and ROLLBACK to undo any changes. Use SAVEPOINT to break a transaction into smaller units.

BEGIN
   UPDATE employees SET salary = salary + 500 WHERE department_id = 10;
   SAVEPOINT after_update;
   -- Perform another operation
   COMMIT;
END;

Question 17:
Scenario: You need to audit which users are accessing a particular table. How would you set up auditing in Oracle?
Answer: Use Oracle’s AUDIT functionality to track access to sensitive tables and actions performed by users.

AUDIT SELECT ON employees BY ACCESS;

Chapter 6: Complex Data Structures and Query Design

Question 18:
Scenario: You need to calculate the running total of sales per day. How would you write the query?
Answer: Use the SUM window function to calculate the running total.

SELECT sale_date, sales_amount, 
       SUM(sales_amount) OVER (ORDER BY sale_date) AS running_total
FROM sales;

Question 19:
Scenario: You are working with hierarchical data and need to display employees in a manager-subordinate relationship. How would you approach this query?
Answer: Use CONNECT BY and LEVEL to navigate hierarchical data.

SELECT employee_name, manager_id, LEVEL
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;

Question 20:
Scenario: You need to generate a report of employees who have the same salary. How would you write this query?
Answer: Use the HAVING clause with GROUP BY to find employees with the same salary.

SELECT salary, COUNT(employee_id)
FROM employees
GROUP BY salary
HAVING COUNT(employee_id) > 1;

Chapter 7: Backup and Recovery

Question 21:
Scenario: The database crashes, and you need to recover it to a specific point in time. How would you

approach the recovery process?
Answer: Use RMAN (Recovery Manager) or the FLASHBACK feature to restore the database to a specific time.

FLASHBACK DATABASE TO TIMESTAMP (SYSDATE - INTERVAL '1' HOUR);

Certainly! Here are the next 29 scenario-based interview questions for Oracle SQL and database management.

Chapter 8: Working with Large Data Sets

Question 22:
Scenario: You have a table with millions of rows, and the query is performing poorly. How would you optimize the query to improve performance?
Answer: Consider partitioning the table, creating appropriate indexes, and using parallel processing or optimized queries such as limiting the number of rows returned. Using the EXPLAIN PLAN will help you identify where optimizations can be made.

Question 23:
Scenario: You need to load data from multiple files into a table. The files are large and might cause performance issues if loaded in one go. What strategy would you use?
Answer: Use Oracle’s SQL*Loader or Data Pump utility with parallel processing to efficiently load large volumes of data. You can also break the load into smaller batches to avoid locking and performance issues.

Question 24:
Scenario: You need to generate a report that involves data from a table with over 10 million rows. What would you do to ensure this report runs efficiently?
Answer: You should use appropriate indexing, break down the report into smaller chunks, and consider using materialized views to pre-aggregate data for faster retrieval. You could also use partitioning to optimize the query on large datasets.

Question 25:
Scenario: You need to move large data from one schema to another. How would you do this efficiently?
Answer: You can use Data Pump Export/Import (expdp/impdp) for large data migration, which allows you to move data between schemas efficiently with minimal locking. Alternatively, use INSERT INTO ... SELECT for smaller volumes of data.

Question 26:
Scenario: A large table has become fragmented over time. How would you resolve this issue to optimize performance?
Answer: You can reorganize the table using ALTER TABLE ... MOVE to eliminate fragmentation. Alternatively, use DBMS_REDEFINITION to reorganize a table without downtime.

Chapter 9: Advanced Database Features

Question 27:
Scenario: You have a requirement to store both structured and semi-structured data in Oracle. How would you achieve this?
Answer: You can use Oracle’s support for XMLType and JSON data types to store and manage semi-structured data alongside structured data.

Question 28:
Scenario: You need to recover a table to a specific point in time but cannot use a full database recovery. How would you achieve this?
Answer: You can use FLASHBACK TABLE to recover the table to a previous state without affecting the entire database.

FLASHBACK TABLE employees TO TIMESTAMP (SYSDATE - INTERVAL '1' HOUR);

Question 29:
Scenario: You need to ensure that users cannot perform DML operations on certain tables but should still be able to query them. How would you implement this?
Answer: Revoke the INSERT, UPDATE, and DELETE privileges from the user while retaining the SELECT privilege.

REVOKE INSERT, UPDATE, DELETE ON employees FROM user_name;

Question 30:
Scenario: You are tasked with managing a multitenant database with multiple pluggable databases (PDBs). How would you monitor performance across the PDBs?
Answer: Use the CDB and PDB views (e.g., v$session, v$active_session_history) to monitor performance for each pluggable database. Additionally, Oracle provides the Oracle Multitenant architecture and tools like OEM to manage multitenant environments.

Question 31:
Scenario: You need to generate and store a unique identifier for each row in a table. How would you accomplish this?
Answer: Use a SEQUENCE or SYS_GUID() to generate unique identifiers for rows.

CREATE SEQUENCE employee_seq;
INSERT INTO employees (employee_id, employee_name)
VALUES (employee_seq.NEXTVAL, 'John Doe');

Chapter 10: Backup and Recovery

Question 32:
Scenario: You performed a backup of the database last night, and this morning, the database crashed. How would you recover the database?
Answer: Perform a restore using RMAN and apply archivelogs if necessary to recover the database to the point of failure.

RMAN> RESTORE DATABASE;
RMAN> RECOVER DATABASE;

Question 33:
Scenario: You need to recover a dropped table. What method would you use?
Answer: Use the FLASHBACK TABLE command to recover the dropped table, assuming UNDO data is still available.

FLASHBACK TABLE employees TO BEFORE DROP;

Question 34:
Scenario: You have a table that is frequently updated, and you want to ensure the data is recoverable in case of failure. What approach would you take?
Answer: Enable FLASHBACK on the table to allow you to recover the table to a previous point in time.

ALTER TABLE employees ENABLE ROW MOVEMENT;

Question 35:
Scenario: You want to back up only the schema without including the data. How would you perform this backup?
Answer: Use Data Pump to perform a schema export, excluding data.

expdp user_name/password DIRECTORY=dp_dir DUMPFILE=schema_backup.dmp CONTENT=METADATA_ONLY;

Chapter 11: Data Partitioning

Question 36:
Scenario: You are tasked with improving query performance on a large fact table. How would you approach partitioning the table?
Answer: Partition the table by a frequently used query column, such as date or region, using range or list partitioning to improve query performance.

CREATE TABLE sales (
   sale_id NUMBER,
   sale_date DATE,
   sales_amount NUMBER
)
PARTITION BY RANGE (sale_date) (
   PARTITION sales_q1 VALUES LESS THAN (TO_DATE('2023-04-01', 'YYYY-MM-DD')),
   PARTITION sales_q2 VALUES LESS THAN (TO_DATE('2023-07-01', 'YYYY-MM-DD'))
);

Question 37:
Scenario: You want to partition a table by hash, but some rows do not have a value for the partition key. How would you handle this scenario?
Answer: Use NULL for the partition key, and Oracle will create a separate partition for rows with NULL values.

CREATE TABLE employees (
   employee_id NUMBER,
   department_id NUMBER
)
PARTITION BY HASH (department_id) 
PARTITIONS 4;

Question 38:
Scenario: You need to split a large table into smaller, manageable pieces but still want to access the table as a single entity. How would you achieve this?
Answer: Use partitioning to divide the table logically into smaller parts, which allows Oracle to query them as one table.

Chapter 12: Security and Auditing

Question 39:
Scenario: You need to ensure that only authorized users can update certain rows in a table based on their department. How would you enforce this?
Answer: Implement fine-grained access control (FGAC) using Oracle’s Virtual Private Database (VPD) to restrict access based on the user’s department.

BEGIN
   DBMS_RLS.ADD_POLICY (
      object_schema   => 'HR',
      object_name     => 'employees',
      policy_name     => 'department_policy',
      function_schema => 'HR',
      policy_function => 'check_department_access'
   );
END;

Question 40:
Scenario: You are responsible for auditing which users performed a DELETE operation on the employees table. How would you implement this?
Answer: Use Oracle’s AUDIT feature to track DELETE operations on the employees table.

AUDIT DELETE ON employees BY ACCESS;

Question 41:
Scenario: How would you revoke a user’s ability to execute a certain procedure but still allow them to execute other procedures in the same schema?
Answer: Use REVOKE EXECUTE on the specific procedure without revoking other permissions from the user.

REVOKE EXECUTE ON my_procedure FROM user_name;

Chapter 13: Data Integrity and Constraints

Question 42:
Scenario: You need to ensure that no two employees have the same employee ID in your table. How would you implement this?
Answer: Create a UNIQUE constraint on the employee_id column.

ALTER TABLE employees ADD CONSTRAINT emp_id_unique UNIQUE (employee_id);

Question 43:
Scenario: You want to enforce referential integrity between the employees and departments tables. How would you implement this?
Answer: Use a FOREIGN KEY constraint to link the department_id column in the employees table to the departments table.

ALTER TABLE employees ADD CONSTRAINT fk_dept FOREIGN KEY (department_id) REFERENCES departments(department_id);

Question 44:
Scenario: How would you ensure that the salary column in the employees table always has a value greater than zero?
Answer: Create a CHECK constraint to enforce this condition.

sql ALTER TABLE employees ADD CONSTRAINT check_salary CHECK (salary > 0);

Chapter 14: Advanced SQL Techniques

Question 45:
Scenario: You need to write a recursive query to retrieve all employees and their managers from an organizational hierarchy. How would you implement this using a Common Table Expression (CTE)?
Answer: Use a recursive CTE to retrieve all employees and their managers.

WITH RECURSIVE emp_hierarchy AS (
    SELECT employee_id, manager_id, employee_name
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    SELECT e.employee_id, e.manager_id, e.employee_name
    FROM employees e
    JOIN emp_hierarchy eh ON e.manager_id = eh.employee_id
)
SELECT * FROM emp_hierarchy;

Question 46:
Scenario: You need to transpose rows into columns. How would you implement a pivot operation?
Answer: Use the PIVOT operator to transform rows into columns.

SELECT * FROM (
    SELECT department_id, employee_name, salary
    FROM employees
) PIVOT (
    SUM(salary) FOR department_id IN (10, 20, 30)
);

Question 47:
Scenario: You need to write a query to transform multiple columns into rows (unpivot). How would you implement the UNPIVOT operation?
Answer: Use the UNPIVOT operator to transform columns into rows.

SELECT department_id, employee_name, salary FROM (
    SELECT department_id, employee_name, salary_1, salary_2
    FROM employees
) UNPIVOT (
    salary FOR salary_type IN (salary_1, salary_2)
);

Question 48:
Scenario: How would you implement a query to get the nth highest salary in an employees table?
Answer: Use ROW_NUMBER() or RANK() analytic function to get the nth highest salary.

WITH ranked_salaries AS (
    SELECT salary, ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num
    FROM employees
)
SELECT salary FROM ranked_salaries WHERE row_num = 3;

Chapter 15: Oracle Analytics and Window Functions

Question 49:
Scenario: You need to calculate the running total of salaries by department. How would you achieve this using window functions?
Answer: Use the SUM() function with the OVER clause, partitioned by department.

SELECT department_id, employee_name, salary,
       SUM(salary) OVER (PARTITION BY department_id ORDER BY employee_id) AS running_total
FROM employees;

Question 50:
Scenario: You need to rank employees based on their salary within their department. How would you implement this using the RANK() function?
Answer: Use the RANK() function to rank employees within each department.

SELECT employee_name, department_id, salary,
       RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank
FROM employees;

Question 51:
Scenario: You need to find the difference in salary between an employee and the employee in the previous row. How would you use the LAG() function?
Answer: Use the LAG() function to find the difference between an employee’s salary and the previous row.

SELECT employee_name, salary,
       salary - LAG(salary) OVER (ORDER BY employee_id) AS salary_diff
FROM employees;

Question 52:
Scenario: You need to find the last salary in the list for each department. How would you use the LAST_VALUE() function?
Answer: Use the LAST_VALUE() function to get the last salary within each department.

SELECT department_id, employee_name, salary,
       LAST_VALUE(salary) OVER (PARTITION BY department_id ORDER BY employee_id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_salary
FROM employees;

Question 53:
Scenario: How would you use PARTITION BY and ORDER BY in a window function to calculate the cumulative salary by department?
Answer: You can partition by department and order by employee ID to get the cumulative salary.

SELECT department_id, employee_name, salary,
       SUM(salary) OVER (PARTITION BY department_id ORDER BY employee_id) AS cumulative_salary
FROM employees;

Chapter 16: Working with Large Data Sets

Question 54:
Scenario: You need to efficiently perform bulk inserts into a table with millions of rows. What method would you use?
Answer: Use INSERT ALL or the APPEND hint with INSERT to improve bulk insert performance.

INSERT /*+ APPEND */ INTO employees SELECT * FROM temp_employees;

Question 55:
Scenario: You have a large dataset that is updated frequently. How would you optimize queries accessing this data?
Answer: Use partitioning to split the data into smaller, more manageable pieces, and use indexes to speed up query performance. Consider materialized views for frequently queried data.

Question 56:
Scenario: You need to load a large file into an Oracle table. How would you do this efficiently?
Answer: Use the SQL*Loader utility or Data Pump for high-performance data loading. Both tools can handle large datasets efficiently.

Question 57:
Scenario: You need to fetch data from large tables for reporting purposes. What strategies would you use to ensure optimal query performance?
Answer: Use indexing, query optimization techniques (such as avoiding full table scans), partitioning, and limiting the number of rows fetched by using WHERE clauses and pagination.

Question 58:
Scenario: You need to partition a table based on a date column but want to minimize maintenance overhead. What partitioning strategy would you recommend?
Answer: Use range partitioning on the date column, and ensure that you have an automated process to drop and create partitions on a regular basis.

Chapter 17: Oracle SQL Tuning and Optimization

Question 59:
Scenario: You are tasked with tuning a slow-running query. How would you identify the cause of the performance issue?
Answer: Analyze the query execution plan using EXPLAIN PLAN or DBMS_XPLAN.DISPLAY. Look for full table scans, missing indexes, or inefficient join strategies.

Question 60:
Scenario: You notice that certain queries are performing poorly due to excessive I/O. What strategies can you implement to reduce I/O?
Answer: Use proper indexing, avoid full table scans, optimize joins, and consider materialized views for frequently accessed data.

Question 61:
Scenario: How would you improve the performance of a query involving multiple joins between large tables?
Answer: Use indexed joins, avoid Cartesian joins, and consider partitioning the tables. Also, ensure that the join conditions are based on indexed columns.

Question 62:
Scenario: You want to optimize a query that uses GROUP BY but is running slowly. What optimization techniques would you consider?
Answer: Ensure the group by columns are indexed, use efficient aggregation methods, and consider creating materialized views for pre-aggregated results.

Question 63:
Scenario: How would you use Oracle’s SQL Tuning Advisor to optimize a slow-performing query?
Answer: Use the SQL Tuning Advisor to analyze the query and provide recommendations. You can run the advisor using DBMS_SQLTUNE.

Chapter 18: Advanced Database Features

Question 64:
Scenario: You need to store hierarchical data in Oracle. How would you model this using CONNECT BY?
Answer: Use CONNECT BY along with START WITH to query hierarchical data such as organizational charts or bill of materials.

SELECT employee_id, manager_id, employee_name
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;

Question 65:
Scenario: You are tasked with storing and querying JSON data in Oracle. How would you store and retrieve JSON in an Oracle table?
Answer: Use the JSON data type to store JSON data in columns and use JSON functions like JSON_VALUE to query it.

CREATE TABLE json_data (data JSON);
INSERT INTO json_data VALUES ('{"name": "John", "age": 30}');
SELECT JSON_VALUE(data, '$.name') FROM json_data;

Question 66:
Scenario: How would you use MULTI-tenant architecture in Oracle to manage multiple databases?
Answer: In Oracle’s multitenant architecture, you create a Container Database (CDB) and multiple Pluggable Databases (PDBs). Use ALTER PLUGGABLE DATABASE to manage each PDB independently while sharing the same container.

Question 67:
Scenario: You need to enable Flashback technology to recover from accidental data loss. How would you configure it?
Answer: Enable Flashback by setting up UNDO tablespaces and enabling FLASHBACK logging.

ALTER DATABASE FLASHBACK ON;

Question 68:
Scenario: How would you enable and use Oracle Flashback to query a table at a specific point in time?
Answer: Use the FLASHBACK query feature to

view data from a previous timestamp.

SELECT * FROM employees AS OF TIMESTAMP TO_TIMESTAMP('2024-12-01 10:00:00', 'YYYY-MM-DD HH24:MI:SS');

Previous Lesson

Back to Course