SQL Interview Questions for Deloitte Data Engineer Roles: Your Ultimate Prep Guide

by

You must be able to answer these SQL Interview Questions if you are applying for Data Engineer position at Deloitte. These SQL Interview Questions were recently asked. Read More

  • Company: Deloitte
  • Position: Data Engineer
  • Experience: 0-3 Years

SQL Interview Questions

Write a query to retrieve the top 3 highest salaries from an employee table.

SELECT DISTINCT Salary 
FROM Employee
ORDER BY Salary DESC
LIMIT 3;

See the code here: https://www.db-fiddle.com/f/o2rrtZXupmq93WtHZKyXaG/0

Explain the difference between a clustered and a non-clustered index.

A clustered index defines the physical order of data in the table, it is faster for range-based queries and each table can have only one clustered index.

A non-clustered index does not change the physical order of table, instead it uses a separate structure (index table) that points to the actual data. A table can have multiple non-clustered indexes.

What are window functions in SQL? Provide examples.

Windows functions perform calculations across a specific range of rows (a window) in a result set without collapsing rows like aggregate functions. They are useful for ranking, running totals, moving averages, and comparisons within partitions.

Example:

ROW_NUMBER() – Assigns a unique row number to each record within a partition (or the entire table if no partition is defined).

SELECT EmployeeID, Name, Department, Salary, 
       ROW_NUMBER() OVER (PARTITION BY Department ORDER BY Salary DESC) AS RowNum
FROM Employee;

RANK() – Similar to ROW_NUMBER(), but ranks are repeated for duplicate values and gaps appear in ranking.

SELECT EmployeeID, Name, Department, Salary, 
       RANK() OVER (ORDER BY Salary DESC) AS RankNum
FROM Employee;

How would you optimize a query that takes too long to execute?

There are many things that can be done to optimize a slow query, some common options are using index, avoid selecting unnecessary columns, filtering early like using where instead of having, using limit clause, use of CTEs and temp tables.

Write a query to find duplicate records in a table.

SELECT Name, Department, Salary, COUNT(*) AS DuplicateCount
FROM Employee
GROUP BY Name, Department, Salary
HAVING COUNT(*) > 1;

See the code; https://www.db-fiddle.com/f/kHGiLGusYiVgiMqdjuY2sy/0

How do you handle NULL values in SQL?

NULL represents the absence of a value in SQL. It is important to handle null values properly to ensure accurate results in queries. It can be handled using ISNULL, IFNULL, CASE, IS NOT NULL, etc.

Explain the difference between DELETE, TRUNCATE, and DROP.

Delete: We can use DELETE when we need to remove specific rows, if needed impact of delete can be rolled back and it does not remove the structure of the table.

Truncate: It removes all records of the table without removing the structure of the table, but the impact of truncate cannot be rolled back.

Drop: It removes all records along with the table structure and schema, Impact of drop cannot be rolled back.

What is a CTE (Common Table Expression), and how is it different from a subquery?

A CTE is a temporary result set defined within a query using the WITH clause. It improves code readability, can be referenced multiple times, and supports recursive statements.

In contrast, a subquery can be harder to read, cannot be reused, and doesn’t support recursion. In terms of performance, a CTE may improve efficiency since it can be referenced multiple times, while a subquery is evaluated each time it appears, which can increase processing time.

Write a query to calculate the running total of sales for each month.

SELECT 
    DATE_FORMAT(SaleDate, '%Y-%m') AS Month,
    SUM(Amount) AS MonthlySales,
    SUM(SUM(Amount)) OVER (ORDER BY DATE_FORMAT(SaleDate, '%Y-%m')) AS RunningTotal
FROM Sales
GROUP BY DATE_FORMAT(SaleDate, '%Y-%m')
ORDER BY Month;

See the code: https://www.db-fiddle.com/f/wk2rL2NQJano1gGumwqknb/0

Explain the difference between INNER JOIN, LEFT JOIN, and FULL OUTER JOIN.

Joins are used to combine data from two tables. An INNER JOIN returns only the rows with matching values in both tables. A LEFT JOIN gives all the rows from the left table, along with matching rows from the right table (or NULL if no match). A FULL OUTER JOIN combines all rows from both tables, showing matching rows wherever possible, and filling with NULLs where there’s no match.

For More SQL Interview Questions Follow Us On:

Jobs In Data (Science | Analysis | Engineering )
LinkedIn Page For Data Job Openings

Thank you for reading! We hope this article has been helpful in your SQL Interview Questions preparation. If you found it valuable, please share it with your network.

Also Read:-

Spread the love