deloitte pl sql interview
Question 1 :create table prod (sno int,name varchar(10),price int)
insert into prod values (1,'A',1000)
insert into prod values (2,'B',2000)
insert into prod values (3,'C',3000)
select sno,name,price from prod order by (select max(price) from prod) desc
Answer :
Output
sno name price
1 A 1000
2 B 2000
3 C 3000
Question 2 : Write a SQL Query to get the output like
Teams 1 | India Vs Aus
Team 2 | SA vs SL
Answer :
SELECT
(SELECT match_name FROM matches WHERE match_name = 'India Vs Aus') AS "Teams 1",
(SELECT match_name FROM matches WHERE match_name = 'SA vs SL') AS "Team 2";
Question 3 :
Write a SQL Query to get the output like
10 --> 20
20 --> 30
30 --> NillAnswer:
SELECT
a.sno || ' --> ' || COALESCE(CAST(b.sno AS TEXT), 'Nill') AS result
FROM
number a
LEFT JOIN
number b ON b.sno = (
SELECT MIN(sno) FROM number WHERE sno > a.sno
)
ORDER BY
a.sno;
Question 4:
Explain lead and lag in sql with Example
LEAD:
Returns the value of an expression from the next row, as determined by the ORDER BY clause. Think of it as peeking into the future.
LAG:
Returns the value of an expression from the previous row, as determined by the ORDER BY clause. Think of it as glancing into the past.
SELECT
date,
sales_value,
product,
LAG(sales_value, 1, 0) OVER (PARTITION BY product ORDER BY date) AS previous_sales,
LEAD(sales_value, 1, 0) OVER (PARTITION BY product ORDER BY date) AS next_sales
FROM
sales;
Question 5: What is Interval partitioning in OracleInterval partitioning in Oracle is an extension of range partitioning. It automatically creates partitions based on a specified interval as new data
arrives. This eliminates the manual effort of creating new partitions.
Question 6:What is increment metrics in Oracle
To increment metrics in Oracle views, you'll likely need to use a combination of materialized views, triggers, and potentially sequences,
depending on your specific needs
Question 7:Suppose there was a table of employee salary data. Write a SQL query to find the employees who earn more than their direct
manager.
Deloitte employees Example Input:
employee_id name salary department_id manager_id
1 Emma Thompson 3800 1
2 Daniel Rodriguez 2230 1 10
3 Olivia Smith 8000 1 8
4 Noah Johnson 6800 2 8
5 Sophia Martinez 1750 1 10
8 William Davis 7000 2 NULL
10 James Anderson 4000 1 NULL
Example Output:
employee_id employee_name
3 Olivia Smith
Question 8:Discuss the concept and use of packages in PL/SQL.
Packages group related procedures, functions, variables, and cursors. They support modular design, hide implementation details, and improveperformance by loading all objects at once into memory.
Question 9:Explain the concept of pipelined table functions and their use cases.
Pipelined table functions return rows as they’re processed, instead of waiting for the full result set. They use PIPE ROW() inside the function
and are useful for transforming large datasets or streaming results to SQL queries.
Question 10:Write a PL/SQL procedure to reverse a string without using built-in functions.
Question 11:How do you find and remove duplicate rows using PL/SQL logic?
Use ROWID to identify duplicates:
DELETE FROM employees
WHERE ROWID NOT IN (
SELECT MIN(ROWID) FROM employees GROUP BY emp_id
);
Question 12:What steps do you take when a PL/SQL job fails silently in a production environment?
“I check the scheduler logs using DBA_SCHEDULER_JOB_RUN_DETAILS, review the exception log table (if used), and test the job manually. I
also verify privileges and parameter values passed at runtime.”
