SQL interview questions

 Here’s a list of advanced SQL interview questions with answers to help you prepare effectively:

1. What is the difference between WHERE and HAVING clauses?

• Answer:
  • WHERE filters rows before aggregation (GROUP BY), whereas HAVING filters rows after aggregation.
  • Example:

    sql
    SELECT Department, COUNT(*) AS EmployeeCount
    FROM Employees
    WHERE Salary > 50000
    GROUP BY Department
    HAVING COUNT(*) > 10;
    

2. What is a Common Table Expression (CTE)? How is it different from a subquery?

• Answer:
  • A CTE is a temporary result set defined within a SQL statement using the WITH keyword.
  • It improves readability and can be recursive.
  • Example:

    sql
    WITH EmployeeCTE AS (
        SELECT EmployeeID, ManagerID
        FROM Employees
    )
    SELECT * FROM EmployeeCTE;
    

  • Unlike a subquery, a CTE can be reused multiple times in the same query.

3. Explain Window Functions with an example.

• Answer:
  • Window functions perform calculations across a set of rows related to the current row, without collapsing the result into a single output.
  • Example:

    sql
    SELECT EmployeeID, Salary, 
           RANK() OVER (PARTITION BY Department ORDER BY Salary DESC) AS Rank
    FROM Employees;
    

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

• Answer:
  • ROW_NUMBER() assigns a unique number to each row.
  • RANK() assigns ranks but skips numbers for ties.
  • DENSE_RANK() assigns ranks without gaps for ties.
  • Example:

    sql
    SELECT EmployeeID, Salary, 
           ROW_NUMBER() OVER (ORDER BY Salary DESC) AS RowNumber,
           RANK() OVER (ORDER BY Salary DESC) AS Rank,
           DENSE_RANK() OVER (ORDER BY Salary DESC) AS DenseRank
    FROM Employees;
    

5. What are the different types of joins in SQL?

• Answer:
  • INNER JOIN: Returns matching rows from both tables.
  • LEFT JOIN: Returns all rows from the left table and matching rows from the right table.
  • RIGHT JOIN: Returns all rows from the right table and matching rows from the left table.
  • FULL OUTER JOIN: Returns all rows from both tables.
  • CROSS JOIN: Returns the Cartesian product of two tables.
  • Example:

    sql
    SELECT E.EmployeeName, D.DepartmentName
    FROM Employees E
    LEFT JOIN Departments D ON E.DepartmentID = D.DepartmentID;
    

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

• Answer:
  • UNION removes duplicate rows, while UNION ALL includes all rows, including duplicates.
  • Example:

    sql
    SELECT EmployeeName FROM EmployeesA
    UNION
    SELECT EmployeeName FROM EmployeesB;
    

7. Explain the concept of a clustered and non-clustered index.

• Answer:
  • Clustered Index: Sorts and stores the data rows in the table based on the key values. Each table can have only one clustered index.
  • Non-clustered Index: Contains pointers to the actual data rows, and there can be multiple non-clustered indexes on a table.
  • Example:

    sql
    CREATE CLUSTERED INDEX IX_EmployeeID ON Employees(EmployeeID);
    CREATE NONCLUSTERED INDEX IX_EmployeeName ON Employees(EmployeeName);
    

8. How does the MERGE statement work?

• Answer:
  • The MERGE statement allows inserting, updating, or deleting rows in a target table based on matching rows in a source table.
  • Example:

    sql
    MERGE INTO TargetTable AS T
    USING SourceTable AS S
    ON T.ID = S.ID
    WHEN MATCHED THEN 
        UPDATE SET T.Name = S.Name
    WHEN NOT MATCHED THEN 
        INSERT (ID, Name) VALUES (S.ID, S.Name);
    

9. What is the difference between DELETE, TRUNCATE, and DROP?

• Answer:
  • DELETE: Removes rows based on a condition; logs each deletion.
  • TRUNCATE: Removes all rows without logging individual deletions; faster but cannot be rolled back.
  • DROP: Deletes the table structure and data permanently.
  • Example:

    sql
    DELETE FROM Employees WHERE ID = 1;
    TRUNCATE TABLE Employees;
    DROP TABLE Employees;
    

10. How do you optimize a slow SQL query?

• Answer:
  • Analyze the execution plan.
  • Use proper indexing.
  • Avoid SELECT *; select only required columns.
  • Use joins instead of subqueries.
  • Use EXISTS instead of IN for large datasets.
  • Avoid functions on indexed columns in the WHERE clause.

EXEC sp_who; -- ya EXEC sp_who2;

Yeh queries aapko currently connected sessions dikhaengi, jisme loginame column se user ka naam pata chalega.

---

2. sys.dm_exec_sessions aur sys.dm_exec_connections ka use

sql
SELECT 
    s.session_id,
    s.login_name,
    s.status,
    c.client_net_address,
    s.host_name,
    s.program_name
FROM 
    sys.dm_exec_sessions s
JOIN 
    sys.dm_exec_connections c ON s.session_id = c.session_id
WHERE 
    s.is_user_process = 1;

Explanation:

• login_name: kaun sa user connected hai.

• status: sleeping, running, etc.

• client_net_address: IP address jahan se connect hua hai.

• host_name: client machine ka naam.

---

3. Agar aapko ek specific database ke users dekhne hain:

sql
SELECT 
    DB_NAME(dbid) AS DatabaseName,
    loginame AS LoginName,
    hostname,
    program_name
FROM 
    sys.sysprocesses
WHERE 
    dbid = DB_ID('YourDatabaseName');

---

Aap in queries se real-time mein dekh sakte hain kaun log aapke SQL Server ya kisi specific database se connected hain.

Agar aapko koi specific scenario chahiye (jaise kisne kis table ko access kiya), toh batayein — uske liye alag level ka tracking (Extended Events, Profiler, Auditing) chahiye hota hai.