Forum

Forums
Oracle
Interview Questions...
Advanced Oracle SQL...
 
Share:
Notifications
Clear all

Advanced Oracle SQL Interview Questions — Asked in TCS, Infosys & Cognizant (2025 Edition)

 
Interview Questions & Answers
Last Post by SQL Admin 4 months ago
1 Posts
1 Users
0 Reactions
164 Views
RSS
Posts: 134
 SQL Admin
Admin
Topic starter
Nov 09, 2025 5:47 am
(@sql-admin)
Reputable Member
Joined: 6 years ago

Looking to crack your next Oracle SQL interview at TCS, Infosys, or Cognizant in 2025?

Then this guide is for you. These are real-world Oracle SQL interview questions — from actual coding rounds and technical interviews — covering analytical functions, joins, subqueries, window functions, and performance optimization.

Whether you’re preparing for a developer, data analyst, or database engineer role, mastering these questions will give you a competitive edge.

🔹 Top Advanced Oracle SQL Interview Questions & Answers (2025 Edition)

1️⃣ How do you find the Nth highest salary in Oracle SQL?

Answer:
Use the DENSE_RANK() analytical function:

 
SELECT salary
FROM (
SELECT salary, DENSE_RANK() OVER(ORDER BY salary DESC) AS rnk
FROM employees
)
WHERE rnk = 3;

✅ Commonly asked in Infosys & Cognizant interviews.

2️⃣ Explain the difference between ROWNUM and ROW_NUMBER().

FeatureROWNUMROW_NUMBER()
PurposeLimits number of rows returnedProvides sequential row numbering
IntroducedLegacy Oracle versionsFrom Oracle 9i onwards
Use CaseWHERE ROWNUM <= 5RANK(), PARTITION BY in analytics

Example:

 
SELECT emp_name, ROW_NUMBER() OVER(ORDER BY salary DESC) AS rnum
FROM employees;

3️⃣ How do you remove duplicates from a table in Oracle?

 
DELETE FROM employees e
WHERE ROWID NOT IN (
SELECT MIN(ROWID)
FROM employees
GROUP BY emp_name, department
);

✅ Uses Oracle’s ROWID pseudocolumn for fast deduplication.

4️⃣ What are analytical functions in Oracle SQL?

Analytical functions perform calculations across sets of rows related to the current row without collapsing the result.
Examples:

  • RANK(), DENSE_RANK(), ROW_NUMBER()

  • LEAD(), LAG()

  • NTILE(), FIRST_VALUE(), LAST_VALUE()

 
SELECT emp_name,
department,
salary,
RANK() OVER(PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees;

✅ Very frequent question in Cognizant analytics interviews.

5️⃣ What is the difference between UNION and UNION ALL?

  • UNION: Removes duplicates (slower).

  • UNION ALL: Keeps duplicates (faster).

 
SELECT emp_id FROM emp_sales
UNION
SELECT emp_id FROM emp_hr;

6️⃣ How do you optimize SQL queries in Oracle?

  • Create indexes on frequently used filter columns.

  • Use EXPLAIN PLAN to analyze query cost.

  • Replace correlated subqueries with joins.

  • Avoid functions on indexed columns in WHERE clauses.
    ✅ Example: Instead of

 
WHERE TO_CHAR(hire_date, 'YYYY') = '2024'

Use

 
WHERE hire_date BETWEEN '01-JAN-2024' AND '31-DEC-2024'

7️⃣ How can you use CASE expressions in Oracle SQL?

 
SELECT emp_name,
CASE
WHEN salary > 100000 THEN 'High'
WHEN salary BETWEEN 50000 AND 100000 THEN 'Medium'
ELSE 'Low'
END AS salary_band
FROM employees;

Helps categorize or create dynamic columns — very common in data analysis reports.

8️⃣ What are correlated subqueries? Give an example.

A correlated subquery refers to columns from the outer query in its condition.

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

✅ Tests conceptual clarity — a favorite in TCS interviews.

9️⃣ How do you retrieve employees with top 2 salaries in each department?

 
SELECT * FROM (
SELECT emp_name, department_id, salary,
DENSE_RANK() OVER(PARTITION BY department_id ORDER BY salary DESC) AS rnk
FROM employees
)
WHERE rnk <= 2;

Shows understanding of windowing + partitioning.

🔟 How do you find employees who have not received any bonus?

Using LEFT JOIN or NOT EXISTS:

 
SELECT e.emp_name
FROM employees e
WHERE NOT EXISTS (
SELECT 1 FROM bonuses b WHERE e.emp_id = b.emp_id
);

✅ Practical question from Infosys Oracle data processing rounds.

💡 Pro Tip:

When answering Oracle SQL interview questions, always mention why you chose that function or query pattern. Example:

“I used DENSE_RANK instead of ROWNUM since it handles duplicate salaries accurately.”

This shows both depth of knowledge and business understanding — what MNC recruiters look for.

🧩 Conclusion

These Advanced Oracle SQL Interview Questions (2025 Edition) from TCS, Infosys, and Cognizant focus on analytical query logic, ranking functions, and optimization techniques.

Consistent practice on Oracle LiveSQL or SQL Developer will help you master query writing and optimization — ensuring you stand out in technical interviews.


Topic Tags
Advanced Oracle SQL Inter Oracle SQL Queries with Infosys Oracle SQL Quest Cognizant SQL Interview Oracle Analytical Functi

Leave a reply

Author Name

Author Email

Title *

 
Preview 0 Revisions Saved
Forum Jump:
  Previous Topic
Next Topic  
Topic Tags:  Advanced Oracle SQL Inter (1) , Oracle SQL Queries with (1) , Infosys Oracle SQL Quest (1) , Cognizant SQL Interview (2) , Oracle Analytical Functi (1) ,
Share: