DEVELOPER + INTERVIEW TRACK

Microsoft SQL Server

Module-wise syllabus and
printable study tracker
Twenty progressive modules from database fundamentals to query performance,
security, advanced features, and mock interview revision.

BEGINNER INTERMEDIATE ADVANCED

Modules 1-6 Modules 7-13 Modules 14-20

Name: _________________________________________________

Start date: __________________ Target date: __________________

MICROSOFT SQL SERVER - INTERVIEW SYLLABUS

HOW TO USE THIS SYLLABUS

A practical learning system

Study the modules in order. For every topic, write a definition, syntax, one simple example, one practical example, common
mistakes, and two interview questions. Mark a topic complete only after you can execute it and explain it without reading
your notes.

Recommended cycle: Learn -> type the examples -> change the examples -> solve a practice problem -> explain the result
aloud -> revise after 1, 3, 7, and 14 days.

Learning phases
Phase Modules Primary result

Foundation 1-6 Design tables and write correct basic queries

Query fluency 7-13 Solve multi-table and analytical problems

Production reasoning 14-18 Discuss safety, concurrency, security, and performance

Interview readiness 19-20 Solve patterns and explain comparisons concisely

Suggested weekly rhythm

Mon-Tue Learn concepts and type examples

Wed-Thu Complete practice tasks with new sample data

Fri Write interview notes and comparison answers

Sat Solve a timed query set

Sun Revise weak areas and update the tracker

Print study plan | Developer track PAGE 2

MICROSOFT SQL SERVER - INTERVIEW SYLLABUS

CONTENTS

Module roadmap
01. Database fundamentals 4

02. Data types and NULL 5

03. Keys, constraints, and relationships 6

04. Table creation and normalization 7

05. INSERT, UPDATE, and DELETE 8

06. Basic SELECT queries 9

07. Built-in functions and expressions 10

08. Aggregates and grouping 11

09. Joins and set operations 12

10. Subqueries, EXISTS, and APPLY 13

11. CTEs and window functions 14

12. Views, procedures, and functions 15

13. Temporary objects and advanced query tools 16

14. Transactions and error handling 17

15. Concurrency, locking, and isolation 18

16. Indexes and query performance 19

17. Security and safe database programming 20

18. Advanced SQL Server features 21

19. Common SQL interview problems 22

20. Theory comparisons and mock interviews 23

Print study plan | Developer track PAGE 3

MICROSOFT SQL SERVER - INTERVIEW SYLLABUS

MODULE 01 | BEGINNER

01. Database fundamentals

Learning objective: Understand the relational database environment and the vocabulary used in every later module.

Topic checklist
[ ] Data and information [ ] Database, DBMS, and RDBMS

[ ] SQL Server, SSMS, SQL, and T-SQL [ ] Instance, database, schema, and object

[ ] Table, row, column, entity, and attribute [ ] Relationships and cardinality

[ ] Data integrity [ ] Database objects

[ ] DDL, DML, DQL, TCL, and DCL [ ] Transactions and ACID introduction

[ ] Logical versus physical storage [ ] System databases: master, model, msdb, tempdb

Hands-on practice
1. Connect to SQL Server and explore Object Explorer.
2. Create an InterviewPractice database and an hr schema.
3. Identify the database, schema, table, row, and column in a sample system.

Interview checkpoint
[ ] What is the difference between DBMS and RDBMS?
[ ] What is the difference between SQL Server and SSMS?
[ ] What is a schema, and why is it useful?

STATUS [ ] Not started [ ] Learning [ ] Practiced [ ] Interview-ready

REVISION 1 day: ____ 3 days: ____ 7 days: ____ 14 days: ____

Print study plan | Developer track PAGE 4

MICROSOFT SQL SERVER - INTERVIEW SYLLABUS

MODULE 02 | BEGINNER

02. Data types and NULL

Learning objective: Choose accurate, space-conscious data types and model required versus missing values.

Topic checklist
[ ] TINYINT, SMALLINT, INT, BIGINT [ ] DECIMAL and NUMERIC

[ ] FLOAT and REAL [ ] CHAR, VARCHAR, NCHAR, NVARCHAR

[ ] DATE, TIME, DATETIME2, DATETIMEOFFSET [ ] BIT and UNIQUEIDENTIFIER

[ ] VARBINARY, ROWVERSION, and XML [ ] NULL and NOT NULL

[ ] Length, precision, and scale [ ] Implicit and explicit conversion

[ ] CAST and CONVERT [ ] TRY_CAST and TRY_CONVERT

[ ] Choosing types for identifiers, money, phone numbers,

and dates

Hands-on practice
1. Choose types for Employee, Product, Customer, and CustomerOrder tables.
2. Explain why a phone number is text and salary is DECIMAL.
3. Test valid and invalid conversions with TRY_CAST.

Interview checkpoint
[ ] VARCHAR versus NVARCHAR?
[ ] DATETIME versus DATETIME2?
[ ] DECIMAL versus FLOAT?

STATUS [ ] Not started [ ] Learning [ ] Practiced [ ] Interview-ready

REVISION 1 day: ____ 3 days: ____ 7 days: ____ 14 days: ____

Print study plan | Developer track PAGE 5

MICROSOFT SQL SERVER - INTERVIEW SYLLABUS

MODULE 03 | BEGINNER

03. Keys, constraints, and relationships

Learning objective: Identify rows, enforce business rules, and connect tables without creating inconsistent data.

Topic checklist
[ ] Super, candidate, primary, and alternate keys [ ] Natural and surrogate keys

[ ] Composite keys [ ] Foreign keys

[ ] PRIMARY KEY [ ] UNIQUE

[ ] CHECK [ ] DEFAULT

[ ] NOT NULL [ ] IDENTITY

[ ] One-to-one [ ] One-to-many

[ ] Many-to-many and bridge tables [ ] Referential integrity

[ ] NO ACTION, CASCADE, SET NULL, SET DEFAULT

Hands-on practice
1. Create Department and Employee with a one-to-many relationship.
2. Create Student, Course, and StudentCourse for a many-to-many relationship.
3. Attempt invalid inserts to observe constraint errors.

Interview checkpoint
[ ] PRIMARY KEY versus UNIQUE?
[ ] Natural key versus surrogate key?
[ ] When is a composite key useful?

STATUS [ ] Not started [ ] Learning [ ] Practiced [ ] Interview-ready

REVISION 1 day: ____ 3 days: ____ 7 days: ____ 14 days: ____

Print study plan | Developer track PAGE 6

MICROSOFT SQL SERVER - INTERVIEW SYLLABUS

MODULE 04 | BEGINNER

04. Table creation and normalization

Learning objective: Turn business entities into maintainable SQL Server tables.

Topic checklist
[ ] CREATE DATABASE and CREATE SCHEMA [ ] CREATE TABLE

[ ] ALTER TABLE [ ] Add, alter, and remove columns

[ ] Add and drop constraints [ ] DROP TABLE

[ ] TRUNCATE TABLE [ ] Naming conventions

[ ] Schema-qualified names [ ] Dependency awareness

[ ] First normal form [ ] Second normal form

[ ] Third normal form [ ] Repeating groups

[ ] Partial and transitive dependencies

Hands-on practice
1. Normalize a spreadsheet-like order table into CustomerOrder and OrderItem.
2. Create the resulting tables with constraints.
3. Safely add a required column to a table that already contains rows.

Interview checkpoint
[ ] Why normalize data?
[ ] What is a transitive dependency?
[ ] When might denormalization be justified?

STATUS [ ] Not started [ ] Learning [ ] Practiced [ ] Interview-ready

REVISION 1 day: ____ 3 days: ____ 7 days: ____ 14 days: ____

Print study plan | Developer track PAGE 7

MICROSOFT SQL SERVER - INTERVIEW SYLLABUS

MODULE 05 | BEGINNER

05. INSERT, UPDATE, and DELETE

Learning objective: Modify the intended rows safely and understand the effects of each DML statement.

Topic checklist
[ ] Single-row and multi-row INSERT [ ] INSERT...SELECT

[ ] Identity values [ ] SCOPE_IDENTITY

[ ] UPDATE with predicates [ ] UPDATE through joins

[ ] DELETE with predicates [ ] DELETE through joins

[ ] OUTPUT inserted and deleted [ ] @@ROWCOUNT

[ ] DELETE versus TRUNCATE versus DROP [ ] MERGE overview and cautions

[ ] Safe DML workflow

Hands-on practice
1. Insert departments and employees.
2. Give a department a controlled salary increase.
3. Preview rows with SELECT before running UPDATE or DELETE.

Interview checkpoint
[ ] DELETE versus TRUNCATE versus DROP?
[ ] What does SCOPE_IDENTITY return?
[ ] Why can MERGE be risky?

STATUS [ ] Not started [ ] Learning [ ] Practiced [ ] Interview-ready

REVISION 1 day: ____ 3 days: ____ 7 days: ____ 14 days: ____

Print study plan | Developer track PAGE 8

MICROSOFT SQL SERVER - INTERVIEW SYLLABUS

MODULE 06 | BEGINNER

06. Basic SELECT queries

Learning objective: Retrieve, filter, sort, and page data with deterministic results.

Topic checklist
[ ] SELECT and FROM [ ] Column and table aliases

[ ] DISTINCT [ ] TOP

[ ] WHERE [ ] Comparison operators

[ ] AND, OR, NOT [ ] IN and NOT IN

[ ] BETWEEN [ ] LIKE

[ ] SQL Server wildcards [ ] IS NULL and IS NOT NULL

[ ] ORDER BY [ ] OFFSET and FETCH

[ ] Logical query-processing order

Hands-on practice
1. Return the five highest-paid active employees.
2. Search names using prefix, suffix, and character-range patterns.
3. Build a deterministic page of 20 employees.

Interview checkpoint
[ ] Why is TOP without ORDER BY nondeterministic?
[ ] Is BETWEEN inclusive?
[ ] Why is = NULL incorrect?

STATUS [ ] Not started [ ] Learning [ ] Practiced [ ] Interview-ready

REVISION 1 day: ____ 3 days: ____ 7 days: ____ 14 days: ____

Print study plan | Developer track PAGE 9

MICROSOFT SQL SERVER - INTERVIEW SYLLABUS

MODULE 07 | INTERMEDIATE

07. Built-in functions and expressions

Learning objective: Transform strings, dates, numeric values, and NULL safely inside queries.

Topic checklist
[ ] UPPER, LOWER, LEN, TRIM [ ] LEFT, RIGHT, SUBSTRING, REPLACE

[ ] CHARINDEX and CONCAT [ ] STRING_SPLIT and STRING_AGG

[ ] GETDATE and SYSDATETIME [ ] YEAR, MONTH, DAY

[ ] DATEPART and DATENAME [ ] DATEADD and DATEDIFF

[ ] EOMONTH and DATEFROMPARTS [ ] ISNULL, COALESCE, NULLIF

[ ] Simple and searched CASE [ ] IIF

[ ] Conversion styles

Hands-on practice
1. Create full names and clean imported strings.
2. Calculate months employed and month-end dates.
3. Create salary bands and safe percentages.

Interview checkpoint
[ ] ISNULL versus COALESCE?
[ ] What does DATEDIFF count?
[ ] Why use NULLIF in division?

STATUS [ ] Not started [ ] Learning [ ] Practiced [ ] Interview-ready

REVISION 1 day: ____ 3 days: ____ 7 days: ____ 14 days: ____

Print study plan | Developer track PAGE 10

MICROSOFT SQL SERVER - INTERVIEW SYLLABUS

MODULE 08 | INTERMEDIATE

08. Aggregates and grouping

Learning objective: Summarize rows at a clearly defined result grain.

Topic checklist
[ ] COUNT(*) and COUNT(column) [ ] COUNT DISTINCT

[ ] SUM, AVG, MIN, MAX [ ] GROUP BY

[ ] HAVING [ ] WHERE versus HAVING

[ ] Conditional aggregation [ ] Grouping by multiple columns

[ ] NULL behavior in aggregates [ ] ROLLUP

[ ] CUBE [ ] GROUPING SETS

[ ] GROUPING and GROUPING_ID

Hands-on practice
1. Calculate employee count, average salary, and payroll per department.
2. Return departments with at least five employees.
3. Create active and inactive counts with conditional aggregation.

Interview checkpoint
[ ] COUNT(*) versus COUNT(column)?
[ ] WHERE versus HAVING?
[ ] Why must selected detail columns be grouped?

STATUS [ ] Not started [ ] Learning [ ] Practiced [ ] Interview-ready

REVISION 1 day: ____ 3 days: ____ 7 days: ____ 14 days: ____

Print study plan | Developer track PAGE 11

MICROSOFT SQL SERVER - INTERVIEW SYLLABUS

MODULE 09 | INTERMEDIATE

09. Joins and set operations

Learning objective: Combine related data without losing rows or multiplying them unexpectedly.

Topic checklist
[ ] INNER JOIN [ ] LEFT and RIGHT JOIN

[ ] FULL OUTER JOIN [ ] CROSS JOIN

[ ] Self join [ ] Multiple-table joins

[ ] Join predicates [ ] ON versus WHERE

[ ] Unmatched-row patterns [ ] Join cardinality and row multiplication

[ ] UNION [ ] UNION ALL

[ ] INTERSECT [ ] EXCEPT

[ ] Compatible set-operation columns

Hands-on practice
1. Return employees with optional department and project information.
2. Find departments with no employees.
3. Compare two data sets using EXCEPT.

Interview checkpoint
[ ] INNER versus LEFT JOIN?
[ ] UNION versus UNION ALL?
[ ] Why can a join create duplicate-looking rows?

STATUS [ ] Not started [ ] Learning [ ] Practiced [ ] Interview-ready

REVISION 1 day: ____ 3 days: ____ 7 days: ____ 14 days: ____

Print study plan | Developer track PAGE 12

MICROSOFT SQL SERVER - INTERVIEW SYLLABUS

MODULE 10 | INTERMEDIATE

10. Subqueries, EXISTS, and APPLY

Learning objective: Express lookups, comparisons, existence tests, and top-per-parent logic.

Topic checklist
[ ] Scalar and multi-row subqueries [ ] Subqueries in SELECT, FROM, WHERE, HAVING

[ ] Correlated subqueries [ ] EXISTS and NOT EXISTS

[ ] IN versus EXISTS [ ] NOT IN with NULL

[ ] Derived tables [ ] CROSS APPLY

[ ] OUTER APPLY [ ] Top-per-parent patterns

[ ] Relational division

Hands-on practice
1. Find employees earning above the company average.
2. Find employees without projects using NOT EXISTS.
3. Return the latest order for each customer with OUTER APPLY.

Interview checkpoint
[ ] Correlated versus noncorrelated subquery?
[ ] Why prefer NOT EXISTS to NOT IN?
[ ] CROSS APPLY versus OUTER APPLY?

STATUS [ ] Not started [ ] Learning [ ] Practiced [ ] Interview-ready

REVISION 1 day: ____ 3 days: ____ 7 days: ____ 14 days: ____

Print study plan | Developer track PAGE 13

MICROSOFT SQL SERVER - INTERVIEW SYLLABUS

MODULE 11 | INTERMEDIATE

11. CTEs and window functions

Learning objective: Solve ranking, running-total, comparison, hierarchy, and top-N-per-group questions.

Topic checklist
[ ] Common table expression syntax [ ] Multiple CTEs

[ ] Recursive CTEs [ ] MAXRECURSION

[ ] OVER and PARTITION BY [ ] Window ORDER BY

[ ] ROW_NUMBER [ ] RANK

[ ] DENSE_RANK [ ] NTILE

[ ] LAG and LEAD [ ] FIRST_VALUE and LAST_VALUE

[ ] Windowed SUM and AVG [ ] ROWS and RANGE frames

[ ] CTE versus subquery versus temp table

Hands-on practice
1. Return the top three salaries per department.
2. Calculate a running payroll.
3. Build an employee-manager hierarchy and detect excessive recursion.

Interview checkpoint
[ ] ROW_NUMBER versus RANK versus DENSE_RANK?
[ ] What does PARTITION BY do?
[ ] Why specify a window frame?

STATUS [ ] Not started [ ] Learning [ ] Practiced [ ] Interview-ready

REVISION 1 day: ____ 3 days: ____ 7 days: ____ 14 days: ____

Print study plan | Developer track PAGE 14

MICROSOFT SQL SERVER - INTERVIEW SYLLABUS

MODULE 12 | INTERMEDIATE

12. Views, procedures, and functions

Learning objective: Package reusable relational and procedural logic behind stable database interfaces.

Topic checklist
[ ] Views and CREATE OR ALTER [ ] Updatable views

[ ] SCHEMABINDING [ ] Indexed views

[ ] Stored procedures [ ] Input and output parameters

[ ] Return values [ ] SET NOCOUNT ON

[ ] Scalar functions [ ] Inline table-valued functions

[ ] Multi-statement TVFs [ ] Determinism

[ ] Procedure versus function [ ] View versus table

Hands-on practice
1. Create an active-employee view.
2. Create a parameterized employee-search procedure.
3. Create an inline function returning department employees.

Interview checkpoint
[ ] Procedure versus function?
[ ] Does a normal view store data?
[ ] Why are inline TVFs often preferred?

STATUS [ ] Not started [ ] Learning [ ] Practiced [ ] Interview-ready

REVISION 1 day: ____ 3 days: ____ 7 days: ____ 14 days: ____

Print study plan | Developer track PAGE 15

MICROSOFT SQL SERVER - INTERVIEW SYLLABUS

MODULE 13 | INTERMEDIATE

13. Temporary objects and advanced query tools

Learning objective: Select an appropriate intermediate structure and safely generate flexible queries.

Topic checklist
[ ] Local and global temporary tables [ ] Table variables

[ ] CTEs and derived tables [ ] Indexes and statistics on temp tables

[ ] Table-valued parameters [ ] PIVOT and UNPIVOT

[ ] Dynamic SQL [ ] sys.sp_executesql

[ ] Parameterized dynamic SQL [ ] QUOTENAME

[ ] Identifier allow-lists [ ] SQL injection prevention

Hands-on practice
1. Build and index a temporary sales summary.
2. Pivot monthly sales into columns.
3. Create a safe dynamic search with parameterized values.

Interview checkpoint
[ ] #Temp table versus table variable?
[ ] CTE versus temp table?
[ ] How does sp_executesql reduce injection risk?

STATUS [ ] Not started [ ] Learning [ ] Practiced [ ] Interview-ready

REVISION 1 day: ____ 3 days: ____ 7 days: ____ 14 days: ____

Print study plan | Developer track PAGE 16

MICROSOFT SQL SERVER - INTERVIEW SYLLABUS

MODULE 14 | ADVANCED

14. Transactions and error handling

Learning objective: Keep multi-statement business operations atomic and recover safely from errors.

Topic checklist
[ ] Autocommit and explicit transactions [ ] BEGIN, COMMIT, ROLLBACK

[ ] Nested transaction behavior [ ] Savepoints

[ ] @@TRANCOUNT [ ] XACT_STATE

[ ] SET XACT_ABORT ON [ ] TRY...CATCH

[ ] THROW [ ] ERROR_NUMBER and ERROR_MESSAGE

[ ] ACID in practice [ ] Transaction boundaries

[ ] Idempotency introduction

Hands-on practice
1. Implement a money-transfer transaction.
2. Roll back related changes after an error.
3. Test XACT_STATE after a constraint violation.

Interview checkpoint
[ ] COMMIT versus ROLLBACK?
[ ] What does XACT_STATE return?
[ ] Why keep transactions short?

STATUS [ ] Not started [ ] Learning [ ] Practiced [ ] Interview-ready

REVISION 1 day: ____ 3 days: ____ 7 days: ____ 14 days: ____

Print study plan | Developer track PAGE 17

MICROSOFT SQL SERVER - INTERVIEW SYLLABUS

MODULE 15 | ADVANCED

15. Concurrency, locking, and isolation

Learning objective: Reason about correctness when multiple sessions read and change data simultaneously.

Topic checklist
[ ] Shared, exclusive, update, and intent locks [ ] Blocking

[ ] Deadlocks [ ] Deadlock victim and retry

[ ] READ UNCOMMITTED [ ] READ COMMITTED

[ ] REPEATABLE READ [ ] SERIALIZABLE

[ ] SNAPSHOT [ ] Read committed snapshot

[ ] Dirty reads [ ] Nonrepeatable reads

[ ] Phantom reads [ ] Lost updates

[ ] NOLOCK risks [ ] Consistent object-access order

Hands-on practice
1. Reproduce blocking in two query windows.
2. Observe behavior under two isolation levels.
3. Explain a simple deadlock and how consistent access order helps.

Interview checkpoint
[ ] Blocking versus deadlock?
[ ] What is a dirty read?
[ ] Why is NOLOCK unsafe for correctness-sensitive reads?

STATUS [ ] Not started [ ] Learning [ ] Practiced [ ] Interview-ready

REVISION 1 day: ____ 3 days: ____ 7 days: ____ 14 days: ____

Print study plan | Developer track PAGE 18

MICROSOFT SQL SERVER - INTERVIEW SYLLABUS

MODULE 16 | ADVANCED

16. Indexes and query performance

Learning objective: Connect query shapes to access paths and measure improvements with actual evidence.

Topic checklist
[ ] Heap [ ] Clustered and nonclustered indexes

[ ] Unique and composite indexes [ ] Included columns

[ ] Filtered and covering indexes [ ] Index key order

[ ] Seek, scan, and key lookup [ ] Estimated and actual execution plans

[ ] Statistics and cardinality [ ] Sargability

[ ] Implicit conversion [ ] SET STATISTICS IO and TIME

[ ] Parameter sensitivity [ ] Query Store

[ ] Write and storage cost of indexes

Hands-on practice
1. Create an index for a measured query pattern.
2. Rewrite a non-sargable date predicate.
3. Compare logical reads and the actual plan before and after a change.

Interview checkpoint
[ ] Clustered versus nonclustered index?
[ ] Seek versus scan?
[ ] Why can more indexes reduce overall performance?

STATUS [ ] Not started [ ] Learning [ ] Practiced [ ] Interview-ready

REVISION 1 day: ____ 3 days: ____ 7 days: ____ 14 days: ____

Print study plan | Developer track PAGE 19

MICROSOFT SQL SERVER - INTERVIEW SYLLABUS

MODULE 17 | ADVANCED

17. Security and safe database programming

Learning objective: Protect database access with least privilege and injection-resistant query design.

Topic checklist
[ ] Logins and database users [ ] Server and database roles

[ ] GRANT, DENY, REVOKE [ ] Least privilege

[ ] Schema permissions [ ] Ownership chaining

[ ] Parameterized client commands [ ] SQL injection

[ ] Row-level security [ ] Dynamic data masking

[ ] Encryption overview [ ] Auditing overview

[ ] Backup and export sensitivity

Hands-on practice
1. Create a read-only role for a reporting schema.
2. Grant procedure execution without direct table access.
3. Rewrite a concatenated query using parameters.

Interview checkpoint
[ ] Login versus user?
[ ] GRANT versus DENY versus REVOKE?
[ ] How do parameters prevent SQL injection?

STATUS [ ] Not started [ ] Learning [ ] Practiced [ ] Interview-ready

REVISION 1 day: ____ 3 days: ____ 7 days: ____ 14 days: ____

Print study plan | Developer track PAGE 20

MICROSOFT SQL SERVER - INTERVIEW SYLLABUS

MODULE 18 | ADVANCED / OPTIONAL

18. Advanced SQL Server features

Learning objective: Recognize platform features and explain the use case for each without overusing them.

Topic checklist
[ ] Sequences and synonyms [ ] Computed columns

[ ] Temporal tables [ ] JSON and XML

[ ] Full-text search [ ] Partitioning concepts

[ ] Change tracking [ ] Change data capture

[ ] Columnstore indexes [ ] In-memory OLTP overview

[ ] SQL Server Agent basics [ ] Backup and recovery overview

[ ] High availability overview [ ] Feature/version compatibility

Hands-on practice
1. Create and query a simple temporal table.
2. Parse a JSON document with OPENJSON.
3. Explain when a columnstore index fits an analytical workload.

Interview checkpoint
[ ] Temporal table use case?
[ ] Change tracking versus CDC?
[ ] Rowstore versus columnstore?

STATUS [ ] Not started [ ] Learning [ ] Practiced [ ] Interview-ready

REVISION 1 day: ____ 3 days: ____ 7 days: ____ 14 days: ____

Print study plan | Developer track PAGE 21

MICROSOFT SQL SERVER - INTERVIEW SYLLABUS

MODULE 19 | INTERVIEW PRACTICE

19. Common SQL interview problems

Learning objective: Apply core concepts to repeatable problem patterns under interview conditions.

Topic checklist
[ ] Second- and Nth-highest salary [ ] Top N per department

[ ] Find and remove duplicates [ ] Above-group-average rows

[ ] Unmatched rows [ ] Running totals

[ ] Previous and next rows [ ] Missing sequence values

[ ] Consecutive dates [ ] Hierarchy queries

[ ] Highest value per group [ ] First and last event per customer

[ ] Compare two tables [ ] Pivot data

[ ] Overlapping ranges [ ] Relational division

Hands-on practice
1. Solve each pattern with sample data, expected output, and edge cases.
2. Write one solution with joins and another with windows or EXISTS when appropriate.
3. Explain row grain, NULL, duplicates, ties, and performance aloud.

Interview checkpoint
[ ] Can you state assumptions before coding?
[ ] Can you test ties and NULL?
[ ] Can you explain the chosen plan shape?

STATUS [ ] Not started [ ] Learning [ ] Practiced [ ] Interview-ready

REVISION 1 day: ____ 3 days: ____ 7 days: ____ 14 days: ____

Print study plan | Developer track PAGE 22

MICROSOFT SQL SERVER - INTERVIEW SYLLABUS

MODULE 20 | INTERVIEW REVISION

20. Theory comparisons and mock interviews

Learning objective: Deliver concise definitions, differences, examples, and tradeoffs without memorized filler.

Topic checklist
[ ] DBMS versus RDBMS [ ] SQL versus T-SQL

[ ] PRIMARY KEY versus UNIQUE [ ] Clustered versus nonclustered index

[ ] DELETE versus TRUNCATE versus DROP [ ] WHERE versus HAVING

[ ] UNION versus UNION ALL [ ] ROW_NUMBER versus RANK versus DENSE_RANK

[ ] CTE versus temp table [ ] Temp table versus table variable

[ ] Procedure versus function [ ] IN versus EXISTS

[ ] VARCHAR versus NVARCHAR [ ] DATETIME versus DATETIME2

[ ] ISNULL versus COALESCE [ ] Blocking versus deadlock

[ ] Seek versus scan

Hands-on practice
1. Answer each comparison in definition, example, and tradeoff format.
2. Complete three timed mock interviews.
3. Record weak topics and schedule targeted revision.

Interview checkpoint
[ ] Can the answer fit in 60-90 seconds?
[ ] Does it include a concrete example?
[ ] Does it avoid absolute claims?

STATUS [ ] Not started [ ] Learning [ ] Practiced [ ] Interview-ready

REVISION 1 day: ____ 3 days: ____ 7 days: ____ 14 days: ____

Print study plan | Developer track PAGE 23

MICROSOFT SQL SERVER - INTERVIEW SYLLABUS

CAPSTONE PROJECTS

Build evidence, not only notes

Complete at least two projects. Each project should include a diagram, CREATE scripts, sample data, reports, procedures,
transactions, indexes, and a short performance review.

Project A - Employee management

[ ] Department [ ] Employee

[ ] Project [ ] EmployeeProject

[ ] Attendance [ ] SalaryHistory

[ ] Active employee view [ ] Employee search procedure

[ ] Department payroll report [ ] Top earners per department

[ ] Salary-change transaction [ ] Useful index analysis

Project B - E-commerce
[ ] Customer [ ] Address

[ ] Category [ ] Product

[ ] CustomerOrder [ ] OrderItem

[ ] Payment [ ] Shipment

[ ] Order-total report [ ] Customer purchase history

[ ] Stock update transaction [ ] Order search indexes

Project completion definition

[ ] Tables are normalized and all relationships are justified.
[ ] Data types, NULL rules, keys, and constraints are documented.
[ ] At least 20 meaningful queries are tested with edge cases.
[ ] At least two procedures, one view, and one transaction are included.
[ ] Index choices are supported by execution plans and logical-read measurements.
[ ] The complete design can be explained in a 10-minute mock interview.

Print study plan | Developer track PAGE 24

MICROSOFT SQL SERVER - INTERVIEW SYLLABUS

FINAL TRACKER

Module completion record

Module Completed Confidence 1-5 Weak topic / next action

01. Database fundamentals [] ____

02. Data types and NULL [] ____

03. Keys, constraints, and relationships [] ____

04. Table creation and normalization [] ____

05. INSERT, UPDATE, and DELETE [] ____

06. Basic SELECT queries [] ____

07. Built-in functions and expressions [] ____

08. Aggregates and grouping [] ____

09. Joins and set operations [] ____

10. Subqueries, EXISTS, and APPLY [] ____

11. CTEs and window functions [] ____

12. Views, procedures, and functions [] ____

13. Temporary objects and advanced query tools [] ____

14. Transactions and error handling [] ____

15. Concurrency, locking, and isolation [] ____

16. Indexes and query performance [] ____

17. Security and safe database programming [] ____

18. Advanced SQL Server features [] ____

19. Common SQL interview problems [] ____

20. Theory comparisons and mock interviews [] ____

Target interview date: ______________________________

Next mock interview date: __________________________

Final notes: __________________________________________________________________________

______________________________________________________________________________________

Print study plan | Developer track PAGE 25