SQL

what is the difference between inner and outer join ? 
what is the difference between union and union all ? 
why do we use merge in oracle application ? 
have you worked with the stored procedures?

indexes -- CREATE INDEX idx_emp_lastname ON employees(last_name);
CREATE INDEX fullName_Index ON TableName (column_Name);

1️⃣ How do you find the third highest salary without using TOP or LIMIT?
2️⃣ How do you remove duplicate rows based on multiple columns?
3️⃣ What’s the use of the PARTITION BY clause in SQL?
4️⃣ How do you calculate percentage contribution of each row to a total?
5️⃣ How do you join three or more tables efficiently?
6️⃣ How can you retrieve only the latest record per user based on a timestamp?
7️⃣ What’s the difference between CHAR, VARCHAR, and TEXT?
8️⃣ How do you handle NULLs in aggregations like AVG, COUNT, or SUM?
9️⃣ How do you generate a calendar or date dimension using SQL?
🔟 What’s the impact of indexing on SELECT vs INSERT performance?
1️⃣1️⃣ How do you retrieve every alternate row from a table?
1️⃣2️⃣ How can you implement IF-ELSE logic inside a SQL query?
1️⃣3️⃣ What is normalization and when should you denormalize data instead?
1️⃣4️⃣ How do you create a running average in SQL?
1️⃣5️⃣ How do you pivot a table dynamically based on values in a column?
1️⃣6️⃣ What are the pros and cons of using stored procedures?
1️⃣7️⃣ How do you filter rows using a value from the previous row in SQL?
1️⃣8️⃣ What is the difference between a primary key and a unique constraint?
1️⃣9️⃣ How do you perform a case-insensitive search in SQL?
2️⃣0️⃣ What techniques do you use to troubleshoot slow-running queries?



TOP 18 SQL INTERVIEW QUESTIONS

1. Find the 2nd Highest Salary
SELECT MAX(salary) FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees)

2. Count Of Employees in Each Department
SELECT department, COUNT(*) AS
employee_count
FROM employees
GROUP BY department

3. Find Duplicate Records
SELECT column_name, COUNT(*)
FROM table_name
GROUP BY column_name
HAVING COUNT(*)>1

4. How can you delete duplicate records
WITH CTE AS (
 SELECT column_name, 
 ROW_NUMBER() OVER (PARTITION BY column_name ORDER BY 
column_name) AS row_num
 FROM table_name
)
DELETE FROM CTE WHERE row_num > 1

5. Find Employees Who joined in 2024
SELECT * FROM employees
WHERE YEAR(joining date) = 2024

6. Find Customers Without Orders
SELECT c.customer_id, c.name
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.customer_id IS NULL

7. Get Total Salary by Department
SELECT department, SUM(salary) AS
total_salary
FROM employees
GROUP BY department

8. Find Nth Highest Salary (Nth = 3)
SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 2

9. Get Employee Details with Department Names
SELECT e.name, d.department_name 
FROM employees e
JOIN departments d ON e.department_id = d.department_id

10. Find all employees whose names contain the letters "a" exactly twice
SELECT * FROM employees
WHERE LENGTH(name) - 
LENGTH(REPLACE(LOWER(name),'a','')) = 2

11. Running total of sales by date
SELECT date, sales,
SUM(sales) OVER (ORDER BY date) AS RunningTotal
FROM sales_data

12. Find employees earning more than average salary in their department
SELECT * FROM employees e
WHERE salary > (
 SELECT AVG(salary) 
 FROM employees
 WHERE department_id = 
e.department_id
)

13. Write a query to count how many employees share the same salary
SELECT salary, COUNT(*) AS
employee_count
FROM employees
GROUP BY salary 
HAVING COUNT(*) > 1

14. Write a query to find the most frequently occurring value in a column
SELECT column_name, COUNT(*) AS freq
FROM table_name
GROUP BY column_name
ORDER BY freq DESC
LIMIT 1

15. Fetch the records where the date is within the last 7 days from today
SELECT * 
FROM table_name
WHERE date_column >= CURRENT_DATE -
INTERVAL 7 DAY

16. How to get the common records from two tables?
SELECT * FROM table1 
INTERSECT 
SELECT * FROM table2

17. How to retrieve the last 10 records from a table
SELECT * 
FROM employees 
ORDER BY employee_id DESC 
LIMIT 10

18. How to find employees whose salary is higher than their manager's 
salary?
SELECT e.employee_id, e.name 
FROM employees e 
JOIN employees m ON e.manager_id = m.employee_id 
WHERE e.salary > m.salary