The SQL Interview: Practice Tasks and Revision – SQL Server | Lesson 29

Published / updated4 Oct 2026

Introduction

You have built a real database, filled it with 10,000 students, queried it, secured it, and made it fast. This lesson turns that work into interview answers.

You will see how SQL interviews are usually run, what each experience level is expected to show, a method for live query tasks, and seven classic problems solved on the SMS data. It ends with a revision map of Modules 2 to 5.

By the end of this lesson, you will be able to:

Describe the common rounds of a SQL interview and what each one tests.
Use a five-step method to solve a live query task out loud.
Solve seven classic interview problems on the SMS database.
Avoid the traps interviewers set on purpose: NULL, ties, duplicates, and division.
Explain your SMS database design in two minutes.

Why this matters

Almost every .NET backend interview includes SQL. Freshers get query tasks; senior candidates get design and performance scenarios; leads get trade-off discussions. Many good developers fail not because they do not know SQL, but because they write in silence, miss an edge case, or cannot explain their own project.

You have an advantage: a complete project with real data. Interviewers love "In my school database, I had exactly this problem..." far more than a textbook definition.

The concept

Example: the usual interview rounds

Round	What happens	What it tests	Course lessons
Concepts	Short questions: joins, keys, normalization, indexes	Vocabulary and understanding	5-15
Live query	Write SQL on a laptop or whiteboard	Turning a question into a correct query	16-20
Scenario	"The report is slow / doubled / deadlocks. What do you do?"	Diagnosis and judgement	21-28
Project	"Explain a database you designed"	Real experience and trade-offs	All of Module 3

Example: what each level is expected to show

Level	Expected to
F (0-2 years)	Write correct joins, grouping, and filters; explain keys and normalization
M (3-5 years)	Handle NULLs and ties, use CTEs and window functions, write procedures and transactions
S (6-9 years)	Read plans, choose indexes, fix locking problems, design schemas with reasons
A (10+ years)	Weigh trade-offs: scale, reporting, security, backup strategy, team process

The interview questions at the end of every lesson in this course are tagged with these same levels. Revise all F and M questions; read S and A to understand where the discussion can go.

Example: a method for live query tasks

Interviewers watch how you work as much as the result. Say each step out loud:

Clarify: "Do ties count as one position? Should absent students be included?"
Picture the data: sketch two or three example rows, including an awkward one.
Build in steps: start with FROM and WHERE, run or reason about it, then add joins, grouping, and ordering. Use a CTE for each step if it helps.
Test edge cases: NULLs, ties, duplicates after joins, empty results, integer division.
Explain the cost: "On a large table, I would want an index on this column."

Think of a driving test. The examiner cares that you check your mirrors, not only that you arrive. Clarifying questions and edge-case checks are your mirrors.

Apply it to the SMS

The seven tasks below are the ones interviewers ask most, rewritten for school data. Save the script below as queries\029-interview-practice.sql. Before you read each solution, try it yourself.

Show queries/029-interview-practice.sql

queries/029-interview-practice.sql
-- Lesson 29: classic SQL interview tasks, solved on the SMS database. Read-only.
USE SchoolDb;
GO

-- 1. The second-highest mark in 10-A Unit Test 1 Mathematics (paper 8, enrollments 13-16).
SELECT MAX(MarksObtained) AS SecondHighest
FROM dbo.Marks
WHERE ExamSubjectId = 8
  AND EnrollmentId BETWEEN 13 AND 16
  AND MarksObtained < (
        SELECT MAX(MarksObtained)
        FROM dbo.Marks
        WHERE ExamSubjectId = 8
          AND EnrollmentId BETWEEN 13 AND 16
      );

-- 2. The same, for any N, with DENSE_RANK (N = 2 here).
WITH Ranked AS (
    SELECT
        MarksObtained,
        DENSE_RANK() OVER (ORDER BY MarksObtained DESC) AS Position
    FROM dbo.Marks
    WHERE ExamSubjectId = 8
      AND EnrollmentId BETWEEN 13 AND 16
)
SELECT DISTINCT MarksObtained AS NthHighest
FROM Ranked
WHERE Position = 2;

-- 3. Find duplicates: last names shared by more than one of the 12 students.
SELECT LastName, COUNT(*) AS Students
FROM dbo.Students
WHERE Id <= 12
GROUP BY LastName
HAVING COUNT(*) > 1;

-- 4. Rows in one table with no match in another: current students with no guardian on record.
SELECT COUNT(*) AS StudentsWithoutGuardian
FROM dbo.vw_CurrentEnrollments AS ce
WHERE NOT EXISTS (
    SELECT 1
    FROM dbo.StudentGuardians AS sg
    WHERE sg.StudentId = ce.StudentId
);

-- 5. Rows to columns (a pivot with conditional aggregation): 10-A attendance per student.
SELECT
    ce.FullName,
    SUM(CASE WHEN a.Status = 'Present' THEN 1 ELSE 0 END) AS Present,
    SUM(CASE WHEN a.Status = 'Absent' THEN 1 ELSE 0 END) AS Absent,
    SUM(CASE WHEN a.Status = 'Late' THEN 1 ELSE 0 END) AS Late,
    SUM(CASE WHEN a.Status = 'Leave' THEN 1 ELSE 0 END) AS Leave
FROM dbo.vw_CurrentEnrollments AS ce
INNER JOIN dbo.AttendanceRecords AS a ON a.EnrollmentId = ce.EnrollmentId
WHERE ce.ClassId = 10
  AND ce.SectionName = 'A'
GROUP BY ce.FullName, ce.RollNumber
ORDER BY ce.RollNumber;

-- 6. UNION vs UNION ALL: family names of teachers and guardians.
SELECT COUNT(*) AS LastNamesUnionAll
FROM (
    SELECT LastName FROM dbo.Teachers
    UNION ALL
    SELECT LastName FROM dbo.Guardians
) AS p;

SELECT COUNT(*) AS LastNamesUnion
FROM (
    SELECT LastName FROM dbo.Teachers
    UNION
    SELECT LastName FROM dbo.Guardians
) AS p;

-- 7. Age in whole years on a fixed date, for the 10-A students.
SELECT
    FullName,
    DATEDIFF(YEAR, s.DateOfBirth, '2026-07-01')
        - CASE WHEN DATEADD(YEAR, DATEDIFF(YEAR, s.DateOfBirth, '2026-07-01'), s.DateOfBirth) > '2026-07-01' THEN 1 ELSE 0 END AS AgeOn1July2026
FROM dbo.vw_CurrentEnrollments AS ce
INNER JOIN dbo.Students AS s ON s.Id = ce.StudentId
WHERE ce.ClassId = 10
  AND ce.SectionName = 'A'
ORDER BY ce.RollNumber;
GO

Example: task 1, the second-highest mark

"Find the second-highest Mathematics mark in 10-A's Unit Test 1." The marks are 95, 92, 85, and 67. Two classic answers:

Subquery: the highest mark below the maximum: MAX(...) WHERE MarksObtained < (SELECT MAX(...)).
Window function: DENSE_RANK() OVER (ORDER BY MarksObtained DESC), then position 2.

Both return 92.00. The window version works for any N and handles ties: if two students had 95, DENSE_RANK would still give 92 as the second position. Say this out loud; it shows you thought about ties.

Example: task 2, find duplicates

"Which last names appear more than once among the 12 students?" Group, then keep groups with more than one row:

LastName	Students
Sharma	2

The follow-up is often "now delete the duplicates, keeping one". That is Lesson 20's ROW_NUMBER pattern.

Example: task 3, rows without a match

"How many current students have no guardian on record?" Use NOT EXISTS, never NOT IN on a column that can be NULL (Lesson 19):

StudentsWithoutGuardian
10000

The 10,000 generated students have no guardians; only the 12 fixed students do. In an interview, comment on the result: "That would be a data-quality report for the office."

Example: task 4, rows to columns

"Show each 10-A student's attendance with one column per status." This is a pivot, done with conditional aggregation from Lesson 18:

FullName	Present	Absent
Aarav Sharma	3	0
Diya Patel	3	0
Kabir Singh	2	1
Meera Iyer	3	0

SQL Server also has a PIVOT keyword, but SUM(CASE ...) is easier to read, works everywhere, and is what most interviewers expect.

Example: task 5, UNION vs UNION ALL

"Combine the last names of teachers and guardians." UNION ALL keeps every row; UNION removes duplicates:

LastNamesUnionAll	LastNamesUnion
30	19

8 teachers and 22 guardians make 30 names, but the 22 guardians share 11 family names, so UNION returns 19. UNION must sort or hash to remove duplicates, so prefer UNION ALL when duplicates are impossible or wanted.

Example: task 6, age on a date

"Show each 10-A student's age on 1 July 2026." DATEDIFF(YEAR, ...) counts year boundaries, not birthdays, so subtract 1 if the birthday has not happened yet:

FullName	AgeOn1July2026
Aarav Sharma	15
Diya Patel	14
Kabir Singh	15
Meera Iyer	14

Diya, born 23 August 2011, is still 14 on 1 July. A plain DATEDIFF would wrongly say 15. Interviewers ask this exact question to see whether you test edge cases.

Example: task 7, explain your database

"Tell me about a database you designed." A two-minute answer using this course:

What: "A School Management System database: 22 tables, from academic years to fee payments."
Key design: "Enrollments per student per year, so history is never overwritten; guardians in a junction table for siblings."
Rules in the database: "Constraints for keys and values, a trigger for marks above the maximum, procedures for payments in one transaction."
Performance: "Tested with 10,000 students and 600,000 attendance rows; indexes cut the daily attendance query from 3,351 to 38 page reads."
Safety: "Least-privilege roles, soft delete, an audit log, tested backups."

Example: answering a scenario question

Senior rounds ask scenarios such as "The fee report is suddenly slow" or "Users see deadlock errors". Answer in five parts, each one sentence or two:

Symptom: restate the problem and ask one question about it.
Measure: say what you would check first: plans, logical reads, blocking.
Cause: name the likely causes in order of probability.
Fix: the smallest change that addresses the cause, and how you would prove it worked.
Prevent: a test, a monitor, or a review rule so it does not return.

Use real numbers from your project where you can, such as "a date filter with YEAR() read 1,567 pages; the range version read 38". Numbers show you have done it, not read about it.

Example: the revision map

If asked about	Revise lesson
Keys, constraints, relationships	9, 10, 12
Normalization, data types	7, 8
Joins, grouping, subqueries, window functions	17, 18, 19, 20
Views, functions, procedures, triggers	21, 22, 23
Transactions, locking, deadlocks	24, 25
Indexes and plans	26, 27
Security and backup	28

Common mistakes

Writing in silence. The interviewer cannot see your thinking. Fix: say each step.
Not asking about ties and NULLs. The "correct" query is wrong for their data. Fix: clarify first.
NOT IN with a nullable column. Returns nothing. Fix: NOT EXISTS.
SELECT * and no aliases in a multi-table answer. It looks careless. Fix: name columns and alias tables.
Memorized definitions without examples. Fix: answer every concept with an SMS example.

In real projects

From 16 years of .NET projects

I have interviewed hundreds of developers. The candidates I remember did three things: they asked one clarifying question before typing, they tested their query against an edge case without being asked, and they talked about a real database they had built.

You can do all three with this course. Use the SMS as your project story, and practise the seven tasks out loud until the method is automatic. A calm, structured answer to a medium question beats a rushed answer to a hard one.

Best practices

Clarify ties, NULLs, and duplicates before writing a query.
Build queries step by step, and test with an awkward example row.
Answer concept questions with a short definition plus one SMS example.
Prepare a two-minute story of your database design, with numbers.
Revise all F and M questions from every lesson, out loud.

Recap

SQL interviews test concepts, live queries, scenarios, and your own project.
Expectations grow with level, from correct joins to trade-offs.
Use the five-step method: clarify, picture, build, test, explain.
The seven classic tasks cover ranking, duplicates, missing rows, pivots, unions, and dates.
Your SMS database is a complete, explainable project story.

Homework

Library module task: Answer an interview-style question on your library data: "Which student has borrowed the most books? Show every student tied for first place."

A correct result looks like: Aarav and Advika Sharma, one book each, both shown because they are tied.

Show the solution

library-assignment/029-library-interview.sql
-- Library homework, Lesson 29: an interview-style question on the library data.
-- "Which student has borrowed the most books? Show ties too."
USE SchoolDb;
GO

WITH Borrowing AS (
    SELECT
        StudentId,
        COUNT(*) AS BooksBorrowed,
        RANK() OVER (ORDER BY COUNT(*) DESC) AS Position
    FROM dbo.BookIssues
    GROUP BY StudentId
)
SELECT s.FirstName, s.LastName, b.BooksBorrowed
FROM Borrowing AS b
INNER JOIN dbo.Students AS s ON s.Id = b.StudentId
WHERE b.Position = 1
ORDER BY s.FirstName;
GO

RANK keeps both tied students at position 1. With ROW_NUMBER, one of them would be missing: exactly the trap this lesson warns about.

Quiz

What should you do first in a live SQL task?
1. Start typing immediately
2. Ask clarifying questions about ties, NULLs, and duplicates
3. Write SELECT * FROM every table
4. Ask for the answer
Show answer
Answer: B. Ask clarifying questions about ties, NULLs, and duplicates Clarifying shows thinking and prevents solving the wrong problem.
Marks are 95, 95, 92, 85. What does DENSE_RANK give as position 2?
1. 95
2. 92
3. 85
4. No row
Show answer
Answer: B. 92 Both 95s share position 1; DENSE_RANK continues with 2 for 92.
Why can DATEDIFF(YEAR, DateOfBirth, '2026-07-01') give the wrong age?
1. It counts days
2. It counts year boundaries, not birthdays
3. It ignores leap years
4. It only works for dates after 2000
Show answer
Answer: B. It counts year boundaries, not birthdays Someone born in August 2011 is 14 on 1 July 2026, but the year difference is 15.
Teacher and guardian last names: UNION ALL gives 30, UNION gives 19. Why?
1. UNION ALL is wrong
2. UNION removes duplicate names
3. UNION ignores teachers
4. UNION ALL counts NULLs
Show answer
Answer: B. UNION removes duplicate names Guardians in the same family share last names; UNION keeps each once.
How do you show rows from one table with no matching row in another, safely?
1. NOT IN
2. NOT EXISTS
3. INNER JOIN
4. UNION
Show answer
Answer: B. NOT EXISTS NOT EXISTS is not broken by NULLs, unlike NOT IN.

Interview questions

Questions are grouped by experience level. Click a question to see a model answer. All questions: SQL Server interview questions.

Fresher (0-2 yrs)

F Write a query to find the second-highest mark in a paper. coding

Use DENSE_RANK() OVER (ORDER BY MarksObtained DESC) and keep rank 2, or use a subquery: MAX(MarksObtained) WHERE MarksObtained < (SELECT MAX(...)). For 10-A's Mathematics marks of 95, 92, 85, and 67, both return 92.00, and DENSE_RANK handles ties and any N.

See the example · Permalink

F How do you find duplicate values in a column? coding

Group by the column and keep groups with more than one row: SELECT LastName, COUNT(*) FROM dbo.Students GROUP BY LastName HAVING COUNT(*) > 1;. To delete duplicates while keeping one, use ROW_NUMBER() partitioned by the column.

See the example · Permalink

F What is the difference between UNION and UNION ALL? concept

UNION ALL keeps every row; UNION removes duplicates, which needs a sort or hash. In the SMS, 8 teacher and 22 guardian last names give 30 rows with UNION ALL but 19 with UNION. Prefer UNION ALL when duplicates are impossible or wanted.

See the example · Permalink

Mid (3-5 yrs)

M How do you find rows in one table with no match in another? coding

Use NOT EXISTS with a correlated subquery, or a LEFT JOIN and WHERE right.Id IS NULL. Avoid NOT IN when the subquery's column can be NULL, because one NULL makes it return no rows.

See the example · Permalink

M How do you turn rows into columns, such as one column per attendance status? coding

Use conditional aggregation: SUM(CASE WHEN Status = 'Present' THEN 1 ELSE 0 END) AS Present, one expression per status, grouped by student. SQL Server's PIVOT keyword does the same, but SUM(CASE ...) is easier to read and works in every database.

See the example · Permalink

M Why is DATEDIFF(YEAR, DateOfBirth, @Date) wrong for age, and how do you fix it? coding

DATEDIFF counts year boundaries crossed, not birthdays. Someone born 23 August 2011 gets 15 on 1 July 2026, but is really 14. Subtract 1 when the birthday has not yet happened in that year.

See the example · Permalink

Senior (6-9 yrs)

S Tell me about a database you designed. scenario

In about two minutes, cover what it is, the key design choices, the rules enforced in the database, the performance work with numbers, and safety. For the SMS: 22 tables, enrollments per year, a trigger and transactional procedures, indexes cutting 3,351 reads to 38, and least-privilege roles with tested backups.

How to answer: Use the five-part structure and give at least one measured result. Be ready for follow-ups on any part, and mention a trade-off you made and why.

See the example · Permalink

S The fee report is suddenly slow. Walk me through your answer. scenario

Restate the symptom and ask what changed, then measure: actual plan, logical reads, and blocking. List the likely causes in order, such as a plan change, outdated statistics, or a missing index after data growth. Make the smallest fix, measure again, and add monitoring.

How to answer: Use the five-part scenario structure: symptom, measure, cause, fix, prevent. Keep each part short and concrete, naming the exact tools you would use.

See the example · Permalink

Architect / Lead (10+ yrs)

A As a lead, how would you assess a candidate's SQL skills? design

Mix a short live task on a realistic schema, a design discussion, and a scenario question. Judge how they clarify requirements, handle NULLs and ties, test edge cases, and explain trade-offs, not just whether the query runs.

How to answer: Describe a structured, fair process: the same tasks and scoring rubric for every candidate, with expectations matched to the level. Mention watching for thinking aloud and self-correction.

See the example · Permalink

Resources

Code for this lesson: git tag lesson-29-sql-interview (code repository coming soon)
Files: queries/029-interview-practice.sql, library-assignment/029-library-interview.sql
Video: coming soon

What's next

The SQL Server part is complete. For more SQL first, the optional SQL Extras start with Extra 1: Temp tables and cursors.

Otherwise, Module 6 starts C#, the language you will use to build on top of this database: Lesson 30: The .NET platform.

Want live sessions, code reviews, and interview preparation? Join the next batch at Sahasra Technologies.

Introduction​

Why this matters​

The concept​

Example: the usual interview rounds​

Example: what each level is expected to show​

Example: a method for live query tasks​

Apply it to the SMS​

Example: task 1, the second-highest mark​

Example: task 2, find duplicates​

Example: task 3, rows without a match​

Example: task 4, rows to columns​

Example: task 5, UNION vs UNION ALL​

Example: task 6, age on a date​

Example: task 7, explain your database​

Example: answering a scenario question​

Example: the revision map​

Common mistakes​

In real projects​

Best practices​

Recap​

Homework​

Quiz​

Interview questions​

Fresher (0-2 yrs)​

Mid (3-5 yrs)​

Senior (6-9 yrs)​

Architect / Lead (10+ yrs)​

Resources​

What's next​

Introduction

Why this matters

The concept

Example: the usual interview rounds

Example: what each level is expected to show

Example: a method for live query tasks

Apply it to the SMS

Example: task 1, the second-highest mark

Example: task 2, find duplicates

Example: task 3, rows without a match

Example: task 4, rows to columns

Example: task 5, UNION vs UNION ALL

Example: task 6, age on a date

Example: task 7, explain your database

Example: answering a scenario question

Example: the revision map

Common mistakes

In real projects

Best practices

Recap

Homework

Quiz

Interview questions

Fresher (0-2 yrs)

Mid (3-5 yrs)

Senior (6-9 yrs)

Architect / Lead (10+ yrs)

Resources

What's next