SQL Advanced

1. What is the difference between RANK(), DENSE_RANK(), and ROW_NUMBER()?

• ROW_NUMBER(): Assigns a unique number to each row.
• RANK(): Assigns a rank, skipping numbers if there are ties.
• DENSE_RANK(): Assigns ranks but without skipping numbers.

SELECT name, salary, 
       ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,
       RANK() OVER (ORDER BY salary DESC) AS rank_num,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank_num
FROM employees;

2. How does CASE work in SQL?

The CASE statement works like an IF-ELSE condition. Example:

SELECT name, salary,
    CASE 
        WHEN salary > 50000 THEN 'High Salary'
        WHEN salary BETWEEN 30000 AND 50000 THEN 'Medium Salary'
        ELSE 'Low Salary'
    END AS salary_category
FROM employees;

3. What is a CTE (Common Table Expression) and how is it different from a Subquery?

A CTE improves readability and reusability.
Example (CTE):

WITH HighSalary AS (
    SELECT name, salary FROM employees WHERE salary > 50000
)
SELECT * FROM HighSalary;

4. What is Indexing in SQL?

Indexing improves search performance by creating a structure that allows faster data retrieval.
Example:

CREATE INDEX idx_employee_name ON employees(name);

5. How do you find duplicate records in SQL?

SELECT name, COUNT(*)
FROM employees
GROUP BY name
HAVING COUNT(*) > 1;

6. How do you fetch the second highest salary in SQL?

SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 1;

7. What is the difference between UNION and UNION ALL?

• UNION removes duplicates.
• UNION ALL includes duplicates.

SELECT name FROM employees1
UNION
SELECT name FROM employees2;

8. How do you optimize a slow SQL query?

• Use Indexes to speed up searches.
• Avoid SELECT *, select only required columns.
• Use Joins efficiently instead of subqueries.
• Use EXPLAIN to analyze query execution.
• Optimize WHERE conditions with indexed columns.

9. What is the difference between OLAP and OLTP?

• OLTP (Online Transaction Processing): Focuses on real-time transaction management (e.g., banking transactions).
• OLAP (Online Analytical Processing): Used for data analysis and reporting (e.g., business intelligence).