Question 1

What are DDL, DML, DCL and TCL commands?

Accepted Answer

DDL defines structure (CREATE, ALTER, DROP, TRUNCATE), DML manipulates data (SELECT, INSERT, UPDATE, DELETE), DCL controls access (GRANT, REVOKE) and TCL manages transactions (COMMIT, ROLLBACK, SAVEPOINT).

Question 2

What is the difference between WHERE and HAVING?

Accepted Answer

WHERE filters individual rows before grouping and cannot use aggregate functions, while HAVING filters groups after GROUP BY and can use aggregates like COUNT or SUM.

Question 3

Explain the different types of joins.

Accepted Answer

INNER JOIN returns only matching rows, LEFT JOIN returns all rows from the left table with NULLs where there is no match, RIGHT JOIN does the opposite, FULL OUTER JOIN returns all rows from both, and CROSS JOIN returns every combination.

Question 4

What is the difference between DELETE, TRUNCATE and DROP?

Accepted Answer

DELETE is DML that removes selected rows using WHERE and fires triggers, TRUNCATE is DDL that quickly removes all rows while keeping the table structure, and DROP removes the table itself including structure, data and indexes.

Question 5

How does SQL handle NULL values?

Accepted Answer

NULL means unknown, so comparisons with = or <> return unknown rather than true. Use IS NULL or IS NOT NULL to check it, and COALESCE to substitute a default value.

Question 6

What is the difference between UNION and UNION ALL?

Accepted Answer

UNION combines results and removes duplicates, while UNION ALL keeps all rows including duplicates and is faster because it skips the de-duplication step.

Question 7

Write a query to count orders per customer and sort by highest.

Accepted Answer

Group by the customer, count rows per group and order by the count descending; join to customers to include names.

Question 8

Write a query to find the second highest salary.

Accepted Answer

A portable approach is to find the max salary that is less than the overall max. Alternatively use DISTINCT with ORDER BY and LIMIT/OFFSET, or DENSE_RANK.

Question 9

How do you find the Nth highest salary, and per department?

Accepted Answer

Use DENSE_RANK ordered by salary descending, optionally partitioned by department, and filter on the rank. DENSE_RANK handles ties without skipping ranks.

Question 10

How do you find duplicate records in a table?

Accepted Answer

Group by the columns that define a duplicate and keep groups where COUNT is greater than one. To see the full rows, use a window COUNT or join back.

Question 11

Scenario: delete duplicate rows but keep one copy.

Accepted Answer

Number rows within each duplicate group using ROW_NUMBER and delete every row whose number is greater than one, keeping the lowest id.

Question 12

What are subqueries and correlated subqueries? When would you use a join instead?

Accepted Answer

A subquery is a query nested inside another. A correlated subquery references the outer query and is logically evaluated once per outer row. Joins are often more readable and efficient for combining columns, while EXISTS subqueries are ideal for existence checks.

Question 13

What is a CTE and why use it?

Accepted Answer

A Common Table Expression, defined with WITH, is a named temporary result set used within one query. It makes complex queries readable, lets you reuse a result multiple times, and supports recursion for hierarchies.

Question 14

What is the difference between ROW_NUMBER, RANK and DENSE_RANK?

Accepted Answer

ROW_NUMBER gives a unique sequential number to every row, RANK gives tied rows the same rank and then skips numbers, and DENSE_RANK gives ties the same rank without gaps.

Question 15

Write a query to list employees who earn more than their manager.

Accepted Answer

Self join the employees table, aliasing one copy as the employee and another as the manager, joining on manager_id, then compare salaries.

Question 16

How do you calculate a running total and compare with the previous row?

Accepted Answer

Use SUM as a window function ordered by date for a running total, and LAG to access the previous row's value for day-over-day comparisons, without collapsing rows like GROUP BY does.

Question 17

What are indexes, and what is the difference between clustered and non-clustered?

Accepted Answer

An index is a data structure, usually a B-tree, that speeds up lookups at the cost of extra storage and slower writes. A clustered index defines the physical order of table rows so there is only one per table; a non-clustered index is a separate structure pointing to the rows, and there can be many.

Question 18

Scenario: compare two tables to find mismatched records after a data migration.

Accepted Answer

Use a FULL OUTER JOIN on the key to find rows missing on either side and rows whose values differ, or EXCEPT in both directions. Using IS DISTINCT FROM handles NULL comparisons correctly.

Question 19

Scenario: the dashboard says 1,250 orders last month. How do you verify it with SQL?

Accepted Answer

I confirm the business definition first, such as which statuses count and which timezone defines the month, then write an independent query with a half-open date range and compare. Mismatches usually come from timezone, cancelled orders, or duplicates from joins.

Question 20

Scenario: find each customer's latest order.

Accepted Answer

Use ROW_NUMBER partitioned by customer and ordered by order date descending, then keep row number 1. It returns exactly one row per customer even when dates tie.

Question 21

What is the difference between a primary key, unique key and foreign key?

Accepted Answer

A primary key uniquely identifies each row and cannot be NULL, and a table has only one. A unique key also enforces uniqueness but a table can have several and they can usually hold NULLs. A foreign key references another table's key to enforce referential integrity.

Question 22

What is the logical order of execution of a SELECT query?

Accepted Answer

Logically a query runs FROM and JOIN, then WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, and finally LIMIT or OFFSET. That is why a column alias defined in SELECT cannot be used in WHERE but can be used in ORDER BY.

Question 23

What is the difference between DISTINCT and GROUP BY?

Accepted Answer

DISTINCT removes duplicate rows from the result, while GROUP BY groups rows so you can apply aggregate functions per group. Without aggregates they often return the same result.

Question 24

What is the difference between COUNT(*), COUNT(1) and COUNT(column)?

Accepted Answer

COUNT(*) and COUNT(1) both count all rows including those with NULLs and perform the same in modern databases. COUNT(column) counts only rows where that column is not NULL.

Question 25

How do LIKE, IN, BETWEEN and wildcards work?

Accepted Answer

LIKE matches patterns where % means any number of characters and _ means exactly one character. IN checks against a list of values, and BETWEEN is inclusive of both ends.

Question 26

What is the difference between CHAR and VARCHAR?

Accepted Answer

CHAR is fixed length and pads values with spaces to the declared size, while VARCHAR is variable length and stores only the characters used plus a small length overhead. CHAR suits fixed codes like country codes; VARCHAR suits names and emails.

Question 27

Write a query to get total, average, min and max salary per department.

Accepted Answer

Use GROUP BY on the department with SUM, AVG, MIN and MAX, and ORDER BY to sort the output.

Question 28

How do you use CASE WHEN in SQL?

Accepted Answer

CASE WHEN adds conditional logic inside a query, returning different values based on conditions, like an if-else. It works in SELECT, ORDER BY and inside aggregates for conditional counts.

Question 29

What is a view and why use one?

Accepted Answer

A view is a saved SELECT query that behaves like a virtual table. It simplifies complex joins, restricts which columns users can see, and gives a stable interface over changing tables.

Question 30

What constraints can you define in SQL?

Accepted Answer

The main constraints are NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK and DEFAULT. They enforce data rules at the database level regardless of which application writes the data.

Question 31

Find the highest-paid employee in each department.

Accepted Answer

Rank employees within each department by salary descending using DENSE_RANK and keep rank 1, which also returns ties. A correlated subquery comparing to the department MAX works too.

Question 32

Find employees who earn more than their manager.

Accepted Answer

Self-join the employees table, aliasing one copy as the employee and the other as the manager on e.manager_id = m.id, then filter where the employee's salary is greater.

Question 33

Find customers who have never placed an order.

Accepted Answer

Use LEFT JOIN with WHERE the right side IS NULL, or NOT EXISTS. I avoid NOT IN when the subquery column can contain NULLs, because then it returns no rows.

Question 34

List departments with more than 5 employees and their average salary.

Accepted Answer

Join employees to departments, group by department, and filter the groups with HAVING COUNT(*) > 5.

Question 35

Table A has values (1,1,2,NULL) and B has (1,1,3,NULL). How many rows does each join return?

Accepted Answer

INNER JOIN returns 4 rows because each of the two 1s in A matches each of the two 1s in B, and NULLs never match. LEFT JOIN returns 6 (the 4 matches plus 2 and NULL from A), RIGHT JOIN 6, FULL OUTER JOIN 8, and CROSS JOIN 16.

Question 36

What is a correlated subquery? Find employees earning above their department average.

Accepted Answer

A correlated subquery references a column from the outer query, so it is logically evaluated once per outer row. Here the inner query computes the average for the current employee's department.

Question 37

What is the difference between EXISTS and IN?

Accepted Answer

IN compares a value against a list or subquery result, while EXISTS just checks whether the subquery returns at least one row and stops at the first match. The big practical difference is NOT IN versus NOT EXISTS when NULLs are involved.

Question 38

How do you fetch odd or even rows and the first/last N records?

Accepted Answer

Tables have no inherent order, so I generate a ROW_NUMBER over a defined ORDER BY and filter on it with MOD. For first or last N I order ascending or descending and apply LIMIT (or TOP / FETCH FIRST).

Question 39

How do you update a table using values from another table?

Accepted Answer

Use an UPDATE with a join or a correlated subquery. The syntax differs by database, so I always run the equivalent SELECT first and wrap it in a transaction.

Question 40

Write queries to get orders from the last 7 days and monthly totals.

Accepted Answer

Filter with a half-open range relative to the current date, and group by a truncated month for monthly totals. Date functions vary by database, so I mention which dialect I am using.

Question 41

What do COALESCE and NULLIF do?

Accepted Answer

COALESCE returns the first non-NULL argument, useful for defaults. NULLIF returns NULL when two values are equal, commonly used to avoid divide-by-zero errors.

Question 42

Which string functions do you commonly use in SQL testing?

Accepted Answer

I use UPPER/LOWER for case-insensitive comparisons, TRIM to catch stray spaces, LENGTH to validate field limits, SUBSTRING and CONCAT to build or split values, and REPLACE to clean data.

Question 43

How do you query an employee hierarchy with a recursive CTE?

Accepted Answer

A recursive CTE has an anchor query that selects the top level, such as the CEO, UNION ALL with a recursive part that joins back to the CTE to find each next level. It is used for org charts, category trees and bill-of-materials.

Question 44

Find users who logged in on 3 or more consecutive days.

Accepted Answer

This is the gaps-and-islands pattern: subtract a ROW_NUMBER from each distinct login date, so consecutive dates share the same group key. Then group by user and that key and keep groups with at least 3 days.

Question 45

How do you pivot rows into columns?

Accepted Answer

The portable way is conditional aggregation: GROUP BY the row key and use SUM or MAX with CASE WHEN for each target column. SQL Server and Oracle also have a PIVOT operator.

Question 46

Calculate each department's share of total salary as a percentage.

Accepted Answer

Aggregate per department, then divide by a window SUM over the whole result with an empty OVER(). Multiply by 100.0 to avoid integer division.

Question 47

How do you compute a 7-day moving average and split customers into quartiles?

Accepted Answer

Use AVG with a window frame of ROWS BETWEEN 6 PRECEDING AND CURRENT ROW over daily totals for the moving average, and NTILE(4) ordered by spend to assign quartiles.

Question 48

A query is slow. How do you troubleshoot and optimize it?

Accepted Answer

I read the execution plan with EXPLAIN (or EXPLAIN ANALYZE) to find full scans, bad join orders and row estimate errors, then add or fix indexes on filter and join columns, avoid functions on indexed columns, select only needed columns, and keep statistics up to date.

Question 49

How do COMMIT, ROLLBACK and SAVEPOINT work?

Accepted Answer

A transaction groups statements so they all succeed or none do. COMMIT makes changes permanent, ROLLBACK undoes everything since the transaction began, and SAVEPOINT marks a point you can roll back to without abandoning the whole transaction.

Question 50

Scenario: what SQL checks would you run after a data load into a customers table?

Accepted Answer

I run row count reconciliation with the source, NULL checks on mandatory columns, duplicate checks on business keys, orphan checks for foreign keys, format and range checks, and aggregate checksums like SUM of amounts compared with the source.

Interview Questions

01What are DDL, DML, DCL and TCL commands?BeginnerBasics

02What is the difference between WHERE and HAVING?BeginnerAggregation

03Explain the different types of joins.BeginnerJoins

04What is the difference between DELETE, TRUNCATE and DROP?BeginnerBasics

05How does SQL handle NULL values?BeginnerBasics

06What is the difference between UNION and UNION ALL?BeginnerBasics

07Write a query to count orders per customer and sort by highest.BeginnerAggregation

08Write a query to find the second highest salary.IntermediateClassic Interview Queries

09How do you find the Nth highest salary, and per department?IntermediateClassic Interview Queries

10How do you find duplicate records in a table?IntermediateClassic Interview Queries

11Scenario: delete duplicate rows but keep one copy.IntermediateClassic Interview Queries

12What are subqueries and correlated subqueries? When would you use a join instead?IntermediateSubqueries & CTEs

13What is a CTE and why use it?IntermediateSubqueries & CTEs

14What is the difference between ROW_NUMBER, RANK and DENSE_RANK?IntermediateWindow Functions

15Write a query to list employees who earn more than their manager.IntermediateJoins