Data Manipulation and Querying Interview Questions - SQL

Data Manipulation and Querying (DML + Query Processing) in SQL focuses on how data is retrieved and modified. DML commands (INSERT, UPDATE, DELETE, MERGE) handle data changes, while SELECT with joins, subqueries, grouping, and set operators enables complex querying. Interview questions often test query writing, optimization and handling real-world scenarios like filtering, aggregation, and multi-table joins.

1. What are DML Commands in SQL?

DML (Data Manipulation Language) commands are used to manage data within tables. The main commands are:

• INSERT → add new rows
• UPDATE → modify existing rows
• DELETE → remove rows
• SELECT → retrieve rows

2. What is Query Processing in SQL?

Query processing is the series of steps a database takes to execute an SQL query. It involves:

• Parsing → checking syntax and validity
• Optimization → finding the most efficient execution plan
• Execution → retrieving or modifying the data as requested

3. What is the difference between CHAR and VARCHAR2 data types?

• CHAR: Fixed-length storage. If the defined length is not fully used, it is padded with spaces.
• VARCHAR2: Variable-length storage. Only the actual data is stored, saving space when the full length is not needed.

4. What is normalization in databases?

Normalization is the process of organizing data in a database to reduce redundancy and improve data integrity. This involves dividing large tables into smaller, related tables and defining relationships between them to ensure consistency and avoid anomalies.

5. What is denormalization and when is it used?

Denormalization is the process of combining normalized tables into larger tables for performance reasons. It is used when complex queries and joins slow down data retrieval, and the performance benefits outweigh the drawbacks of redundancy.

6. What is a query in SQL?

A query is a SQL statement used to retrieve, update, or manipulate data in a database. The most common type of query is a SELECT statement, which fetches data from one or more tables based on specified conditions.

7. What are the different operators available in SQL?

• Arithmetic Operators: +, -, *, /, %
• Comparison Operators: =, !=, <>, >, <, >=, <=
• Logical Operators: AND, OR, NOT
• Set Operators: UNION, INTERSECT, EXCEPT
• Special Operators: BETWEEN, IN, LIKE, IS NULL

8. What is a view in SQL?

View is a virtual table created by a SELECT query. It does not store data itself, but presents data from one or more tables in a structured way. Views simplify complex queries, improve readability, and enhance security by restricting access to specific rows or columns.

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

• INNER JOIN: Returns rows that have matching values in both tables.
• LEFT JOIN (LEFT OUTER JOIN): Returns all rows from the left table, and matching rows from the right table.
• RIGHT JOIN (RIGHT OUTER JOIN): Returns all rows from the right table, and matching rows from the left table.
• FULL JOIN (FULL OUTER JOIN): Returns all rows when there is a match in either table.
• CROSS JOIN: Produces the Cartesian product of two tables.

10. What is the difference between INNER JOIN and OUTER JOIN?

• INNER JOIN: Returns only rows where there is a match in both tables.
• OUTER JOIN: Returns all rows from one table (LEFT, RIGHT, or FULL), and the matching rows from the other table. If there is no match, NULL values are returned for the non-matching side.

11. What is the purpose of the GROUP BY clause?

GROUP BY clause is used to arrange identical data into groups. It is typically used with aggregate functions (such as COUNT, SUM, AVG) to perform calculations on each group rather than on the entire dataset.

12. What are aggregate functions in SQL?

Aggregate functions perform calculations on a set of values and return a single value. Common aggregate functions include:

• COUNT(): Returns the number of rows.
• SUM(): Returns the total sum of values.
• AVG(): Returns the average of values.
• MIN(): Returns the smallest value.
• MAX(): Returns the largest value.

13. What is a subquery?

Subquery is a query nested within another query. It is often used in the WHERE clause to filter data based on the results of another query, making it easier to handle complex conditions.

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

• WHERE: Filters rows before any grouping takes place.
• HAVING: Filters grouped data after the GROUP BY clause has been applied.

In short, WHERE applies to individual rows, while HAVING applies to groups

15. What are indexes, and why are they used?

Indexes are database objects that improve query performance by allowing faster retrieval of rows. They function like a book’s index, making it quicker to find specific data without scanning the entire table. However, indexes require additional storage and can slightly slow down data modification operations.

16. What is the difference between DELETE and TRUNCATE commands?

• DELETE: Removes rows one at a time and records each deletion in the transaction log, allowing rollback. It can have a WHERE clause.
• TRUNCATE: Removes all rows at once without logging individual row deletions. It cannot have a WHERE clause and is faster than DELETE for large data sets.

17. What is the purpose of the SQL ORDER BY clause?

ORDER BY clause sorts the result set of a query in either ascending (default) or descending order, based on one or more columns. This helps present the data in a more meaningful or readable sequence.

18. What is a table in SQL?

A table is a structured collection of related data organized into rows and columns. Columns define the type of data stored, while rows contain individual records.

19. What is a cursor in SQL?

Cursor is a database object used to retrieve, manipulate, and traverse through rows in a result set one row at a time. Cursors are helpful when performing operations that must be processed sequentially rather than in a set-based manner.

20. What is the purpose of the SQL SELECT statement?

SELECT statement retrieves data from one or more tables. It is the most commonly used command in SQL, allowing users to filter, sort, and display data based on specific criteria.

21. What are NULL values in SQL?

NULL represents a missing or unknown value. It is different from zero or an empty string. NULL values indicate that the data is not available or applicable.

22. What is a UNION operation, and how is it used?

UNION operator combines the result sets of two or more SELECT queries into a single result set, removing duplicate rows. The result sets must have the same number of columns and compatible data types for corresponding columns.

Example:

SELECT Name FROM Customers
UNION
SELECT Name FROM Employees;

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

• UNION: Combines result sets from two queries and removes duplicate rows, ensuring only unique records are returned.
• UNION ALL: Combines the result sets without removing duplicates, meaning all records from both queries are included.
• Performance-wise, UNION ALL is faster than UNION because it doesn’t perform the additional operation of eliminating duplicates.

Example:

SELECT Name FROM Customers
UNION ALL
SELECT Name FROM Employees;

24. How does the CASE statement work in SQL?

CASE statement is SQL’s way of implementing conditional logic in queries. It evaluates conditions and returns a value based on the first condition that evaluates to true. If no condition is met, it can return a default value using the ELSE clause.

Example:

SELECT ID,
CASE
WHEN Salary > 100000 THEN 'High'
WHEN Salary BETWEEN 50000 AND 100000 THEN 'Medium'
ELSE 'Low'
END AS SalaryLevel
FROM Employees;

25. What are scalar functions in SQL?

Scalar functions operate on individual values and return a single value as a result. They are often used for formatting or converting data. Common examples include:

• LEN(): Returns the length of a string.
• ROUND(): Rounds a numeric value.
• CONVERT(): Converts a value from one data type to another.

Example:

SELECT LEN('Example') AS StringLength;

26. What is the purpose of the COALESCE function?

COALESCE function returns the first non-NULL value from a list of expressions. It’s commonly used to provide default values or handle missing data gracefully.

Example:

SELECT COALESCE(NULL, NULL, 'Default Value') AS Result;

27. What are the differences between SQL’s COUNT() and SUM() functions?

1. COUNT(): Counts the number of rows or non-NULL values in a column.

Example:

SELECT COUNT(*) FROM Orders;

2. SUM(): Adds up all numeric values in a column.

Example:

SELECT SUM(TotalAmount) FROM Orders;

28. What is the difference between the NVL and NVL2 functions?

• NVL(): Replaces a NULL value with a specified replacement value. Example: NVL(Salary, 0) will replace NULL with 0.
  
• NVL2(): Evaluates two arguments:

• If the first argument is NOT NULL, returns the second argument.
• If the first argument is NULL, returns the third argument.

Example:

SELECT NVL(Salary, 0) AS AdjustedSalary FROM Employees; -- Replaces NULL with 0

SELECT NVL2(Salary, Salary, 0) AS AdjustedSalary FROM Employees; -- If Salary is NULL, returns 0; otherwise, returns Salary.

29. How does the RANK() function differ from DENSE_RANK()?

• RANK(): Assigns a rank to each row, with gaps if there are ties.
• DENSE_RANK(): Assigns consecutive ranks without any gaps.

Example:

SELECT Name, Salary, RANK() OVER (ORDER BY Salary DESC) AS Rank
FROM Employees;

If two employees have the same salary, they get the same rank, but RANK() will skip a number for the next rank, while DENSE_RANK() will not.

30. What is the difference between ROW_NUMBER() and RANK()?

• ROW_NUMBER(): Assigns a unique number to each row regardless of ties.
• RANK(): Assigns the same number to tied rows and leaves gaps for subsequent ranks.

Example:

SELECT Name, ROW_NUMBER() OVER (ORDER BY Salary DESC) AS RowNum
FROM Employees;

31. What are common table expressions (CTEs) in SQL?

CTE is a temporary result set defined within a query. It improves query readability and can be referenced multiple times.

Example:

WITH TopSalaries AS (
SELECT Name, Salary
FROM Employees
WHERE Salary > 50000
)
SELECT * FROM TopSalaries WHERE Name LIKE 'A%';

32. What are window functions, and how are they used?

Window functions allow you to perform calculations across a set of table rows that are related to the current row within a result set, without collapsing the result set into a single row. These functions can be used to compute running totals, moving averages, rank rows, etc.

Example: Calculating a running total

SELECT Name, Salary,
SUM(Salary) OVER (ORDER BY Salary) AS RunningTotal
FROM Employees;

33. How does indexing improve query performance?

Indexing allows the database to locate and access the rows corresponding to a query condition much faster than scanning the entire table. Instead of reading each row sequentially, the database uses the index to jump directly to the relevant data pages. This reduces the number of disk I/O operations and speeds up query execution, especially for large tables.

Example:

CREATE INDEX idx_lastname ON Employees(LastName);
SELECT * FROM Employees WHERE LastName = 'Smith';

The index on LastName lets the database quickly find all rows matching ‘Smith’ without scanning every record.

34. What are the trade-offs of using indexes in SQL databases?

Advantages

• Faster query performance, especially for SELECT queries with WHERE clauses, JOIN conditions, or ORDER BY clauses.
• Improved sorting and filtering efficiency.

Disadvantages:

• Increased storage space for the index structures.
• Additional overhead for write operations (INSERT, UPDATE, DELETE), as indexes must be updated whenever the underlying data changes.
• Potentially slower bulk data loads or batch inserts due to the need to maintain index integrity. In short, indexes make read operations faster but can slow down write operations and increase storage requirements.

35. How can you handle duplicates in a query without using DISTINCT?

1. GROUP BY: Aggregate rows to eliminate duplicates

SELECT Column1, MAX(Column2)
FROM TableName
GROUP BY Column1;

2. ROW_NUMBER(): Assign a unique number to each row and filter by that

WITH CTE AS (
SELECT Column1, Column2, ROW_NUMBER() OVER (PARTITION BY Column1 ORDER BY Column2) AS RowNum
FROM TableName
)
SELECT * FROM CTE WHERE RowNum = 1;

36. What is a correlated subquery?

A correlated subquery is a subquery that references columns from the outer query. It is re-executed for each row processed by the outer query. This makes it more dynamic, but potentially less efficient.

Example:

SELECT Name,
(SELECT COUNT(*)
FROM Orders
WHERE Orders.CustomerID = Customers.CustomerID) AS OrderCount
FROM Customers;

37. What is the purpose of the SQL EXCEPT operator?

EXCEPT operator is used to return rows from one query’s result set that are not present in another query’s result set. It effectively performs a set difference, showing only the data that is unique to the first query.

Example:

SELECT ProductID FROM ProductsSold
EXCEPT
SELECT ProductID FROM ProductsReturned;

Use Case:

• To find discrepancies between datasets.
• To verify that certain data exists in one dataset but not in another.

Performance Considerations:

• EXCEPT works best when the datasets involved have appropriate indexing and when the result sets are relatively small.
• Large datasets without indexes may cause slower performance because the database has to compare each row.

38. What is a bitmap index, and how does it differ from a B-tree index?

1. Bitmap Index:

• Represents data with bitmaps (arrays of bits) to indicate the presence or absence of a value in each row.
• Efficient for low-cardinality columns, such as “gender” or “yes/no” fields.
• Can perform fast logical operations (AND, OR, NOT) on multiple columns simultaneously.

2. B-tree Index:

• Uses a balanced tree structure to store indexed data in a sorted order.
• Suitable for high-cardinality columns (e.g., unique identifiers, large ranges of values).
• Supports range-based queries efficiently.

3. Key Difference:

• Bitmap indexes excel with low-cardinality data and complex boolean conditions.
• B-tree indexes are better for unique or high-cardinality data and range queries.

39. Write a query to find the second-highest salary of an employee in a table.

SELECT MAX(Salary) AS SecondHighestSalary
FROM Employee
WHERE Salary < (SELECT MAX(Salary) FROM Employee);

Explanation:

This query identifies the second-highest salary by selecting the maximum salary that is less than the overall highest salary. The subquery determines the top salary, while the outer query finds the next highest value.

40. Write a query to retrieve employees who earn more than the average salary.

SELECT *
FROM Employee
WHERE Salary > (SELECT AVG(Salary) FROM Employee);

Explanation:

This query fetches details of employees whose salary exceeds the average salary. The subquery calculates the average salary, and the main query filters rows based on that result.

41. Write a query to fetch the duplicate values from a column in a table.

SELECT ColumnName, COUNT(*)
FROM TableName
GROUP BY ColumnName
HAVING COUNT(*) > 1;

Explanation:

The query uses GROUP BY to group identical values and HAVING COUNT(*) > 1 to identify values that appear more than once in the specified column.

42. Write a query to find the employees who joined in the last 30 days.

SELECT *
FROM Employee
WHERE JoiningDate > DATE_SUB(CURDATE(), INTERVAL 30 DAY);

Explanation:

By comparing the JoiningDate to the current date minus 30 days, this query retrieves all employees who joined within the last month.

43. Write a query to fetch top 3 earning employees.

SELECT *
FROM Employee
ORDER BY Salary DESC
LIMIT 3;

Explanation:

The query sorts employees by salary in descending order and uses LIMIT 3 to return only the top three earners.

44. Write a query to delete duplicate rows in a table without using the ROWID keyword.

DELETE FROM Employee
WHERE EmployeeID NOT IN (
SELECT MIN(EmployeeID)
FROM Employee
GROUP BY Column1, Column2
);

Explanation:

This query retains only one row for each set of duplicates by keeping the row with the smallest EmployeeID. It identifies duplicates using GROUP BY and removes rows not matching the minimum ID.

45. Write a query to fetch common records from two tables.

SELECT *
FROM TableA
INNER JOIN TableB ON TableA.ID = TableB.ID;

Explanation:

An INNER JOIN is used to find rows present in both tables by matching a common column (in this case, ID).

46. Write a query to fetch employees whose names start and end with ‘A’.

SELECT *
FROM Employee
WHERE Name LIKE 'A%' AND Name LIKE '%A';

Explanation:

The query uses LIKE with wildcard characters to filter rows where the Name column starts and ends with the letter 'A'.

47. Write a query to display all departments along with the number of employees in each.

SELECT DepartmentID, COUNT(*) AS EmployeeCount
FROM Employee
GROUP BY DepartmentID;

Explanation:

By grouping employees by their DepartmentID and counting rows in each group, the query produces a list of departments along with the employee count.

48. Write a query to find employees who do not have managers.

SELECT *
FROM Employee
WHERE ManagerID IS NULL;

Explanation:

This query selects employees whose ManagerID column is NULL, indicating they don’t report to a manager.

49. Write a query to fetch the 3rd and 4th highest salaries.

WITH SalaryRank AS (
SELECT Salary, RANK() OVER (ORDER BY Salary DESC) AS Rank
FROM Employee
)
SELECT Salary
FROM SalaryRank
WHERE Rank IN (3, 4);

Explanation:

This query uses the RANK() window function to rank the salaries in descending order. The outer query then selects the 3rd and 4th highest salaries by filtering for those ranks.

50. Write a query to transpose rows into columns.

SELECT
MAX(CASE WHEN ColumnName = 'Condition1' THEN Value END) AS Column1,
MAX(CASE WHEN ColumnName = 'Condition2' THEN Value END) AS Column2
FROM TableName;

Explanation:

This query converts specific row values into columns using conditional aggregation with CASE. Each column’s value is determined based on a condition applied to rows.

51. Write a query to fetch records updated within the last hour.

SELECT *
FROM TableName
WHERE UpdatedAt >= NOW() - INTERVAL 1 HOUR;

Explanation:

By comparing the UpdatedAt timestamp to the current time minus one hour, the query retrieves rows updated in the last 60 minutes.

52. Write a query to list employees in departments that have fewer than 5 employees.

SELECT *
FROM Employee
WHERE DepartmentID IN (
SELECT DepartmentID
FROM Employee
GROUP BY DepartmentID
HAVING COUNT(*) < 5
);

Explanation:

The subquery counts employees in each department, and the main query uses those results to find employees working in departments with fewer than 5 members.

53. Write a query to check if a table contains any records.

SELECT CASE
WHEN EXISTS (SELECT * FROM TableName) THEN 'Has Records'
ELSE 'No Records'
END AS Status;

Explanation:

The query uses EXISTS to determine if any rows exist in the table, returning a status of 'Has Records' or 'No Records' based on the result.

54. Write a query to find employees whose salaries are higher than their managers.

SELECT e.EmployeeID, e.Salary
FROM Employee e
JOIN Employee m ON e.ManagerID = m.EmployeeID
WHERE e.Salary > m.Salary;

Explanation:

This query joins the Employee table with itself to compare employee salaries to their respective managers’ salaries, selecting those who earn more.

55. Write a query to fetch alternating rows from a table.

WITH RowNumbered AS (
SELECT *, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowNum
FROM Employee
)
SELECT *
FROM RowNumbered
WHERE RowNum % 2 = 0;

Explanation:

This query assigns a sequential number to each row using ROW_NUMBER(), then selects rows where the row number is even, effectively fetching alternating rows. The ORDER BY (SELECT NULL) is used to avoid any specific ordering and just apply a sequential numbering.

56. Write a query to find departments with the highest average salary.

SELECT DepartmentID
FROM Employee
GROUP BY DepartmentID
ORDER BY AVG(Salary) DESC
LIMIT 1;

Explanation:

Grouping by DepartmentID and ordering by the average salary in descending order, the query returns the department with the highest average.

57. Write a query to fetch the nth record from a table.

WITH OrderedEmployees AS (
SELECT *, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowNum
FROM Employee
)
SELECT *
FROM OrderedEmployees
WHERE RowNum = n;

Explanation:

This query uses ROW_NUMBER() to generate a sequential number for each row. The outer query then retrieves the row where the number matches the desired nth position. The approach is portable across most databases.

58. Write a query to find employees hired in the same month of any year.

SELECT *
FROM Employee
WHERE MONTH(JoiningDate) = MONTH(CURDATE());

Explanation: By comparing the month of JoiningDate to the current month, the query selects all employees who were hired in that month regardless of the year.