SQL Interview Queries & Practical Database Masterclass for Freshers

-- Approach 1: Using DENSE_RANK() Window Function (Best Industry Standard)
WITH RankedEmployees AS (
    SELECT 
        emp_id, 
        emp_name, 
        salary,
        DENSE_RANK() OVER (ORDER BY salary DESC) as rank_num
    FROM employees
)
SELECT emp_id, emp_name, salary
FROM RankedEmployees
WHERE rank_num = 2; -- 2nd Highest Salary

-- Approach 2: Using LIMIT & OFFSET (PostgreSQL / MySQL)
SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1; -- 2nd highest (OFFSET = N - 1)

-- Query 1: Find duplicate emails and their frequency
SELECT email, COUNT(*) as occurrence_count
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

-- Query 2: Delete duplicate records while keeping the smallest ID
DELETE FROM users
WHERE id NOT IN (
    SELECT MIN(id)
    FROM users
    GROUP BY email
);

-- Self Join to find Employee Name along with their Manager Name
SELECT 
    e.emp_name AS Employee,
    COALESCE(m.emp_name, 'Top Executive / CEO') AS Manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.emp_id;

-- Find the Highest Paid Employee in EACH Department
WITH DepartmentRanks AS (
    SELECT 
        emp_id,
        emp_name,
        department_id,
        salary,
        DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) as dept_rank
    FROM employees
)
SELECT emp_id, emp_name, department_id, salary
FROM DepartmentRanks
WHERE dept_rank = 1;

-- Creating B-Tree Index for faster query lookups
CREATE INDEX idx_users_email ON users(email);