Appendices: Projects, Interview Questions, Cheat Sheet & Certification

Capstone projects, interview questions, a quick-reference cheat sheet, and the certification path.

Appendix A: Capstone Projects

Project 1: E-Commerce Database (Beginner → Intermediate) Design the ShopDB schema in 3NF with all constraints.

Load 100K+ rows of sample data (use GENERATE_SERIES and NEWID()-based randomization).

Write 30 reporting queries: top customers, monthly sales trends, YoY growth, cohort retention, basket analysis, RFM segmentation.

Build views for reporting.

Project 2: Order Processing API Backend (Intermediate → Advanced)

Stored procedures with TVPs for order creation, stock reservation, payment, and cancellation.

Transactions, error handling, and optimistic concurrency with ROWVERSION.

Audit with temporal tables. Handle deadlocks with retry logic.

tSQLt unit tests for every procedure.

Project 3: Performance Tuning Challenge (Advanced) Use the StackOverflow database (10 GB+ version).

Identify the top 10 slowest queries using Query Store.

Tune them through indexing, rewrites, and statistics. Document before/after STATISTICS IO/TIME and plans.

Reproduce and fix a parameter sniffing problem.

Project 4: Data Warehouse (Advanced) Build a star schema (FactSales, DimDate, DimCustomer with SCD2, DimProduct).

Incremental ETL with CDC or watermarks, using SSIS or T-SQL.

Clustered columnstore, partitioning with a sliding window.

Power BI report on top.

Project 5: HA/DR Lab (DBA) Build a 2-node AG on VMs or containers with a listener and read-only routing.

Implement a backup strategy that meets RPO 15 min / RTO 1 hr. Perform a point-in-time restore.

Configure SQL Agent alerts, Database Mail, and Ola Hallengren maintenance.

Simulate failover and corruption recovery (page restore).

Project 6: Secure Multi-Tenant SaaS DB (Advanced) Row-level security by tenant using SESSION_CONTEXT.

Dynamic data masking and Always Encrypted for PII.

Auditing, least-privilege roles, Ledger for financial records.

Project 7: AI-Powered Semantic Search (SQL Server 2025) Store documents with VECTOR embeddings generated through AI_GENERATE_EMBEDDINGS.

Build hybrid search (full-text + vector) and a RAG retrieval procedure.

Appendix B: Interview Questions

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

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

What are the types of joins? Explain with examples.

What is the difference between WHERE and HAVING?

What is normalization? Explain 1NF, 2NF, and 3NF.

What is the difference between UNION and UNION ALL?

What is a NULL? How do you handle it?

What is the difference between CHAR, VARCHAR, and NVARCHAR?

What is the logical order of query processing?

What is the difference between ISNULL and COALESCE?

What is the difference between ROW_NUMBER, RANK, and DENSE_RANK?

What are CTEs? When would you use a recursive CTE?

What is the difference between a temp table and a table variable?

What is the difference between a stored procedure and a function?

What is the difference between clustered and nonclustered indexes?

What is a covering index? What is a key lookup?

What are INSTEAD OF vs AFTER triggers?

Explain CROSS APPLY vs OUTER APPLY.

How do you find duplicate rows and delete them?

How do you find the Nth highest salary?

What is SQL injection, and how do you prevent it?

What's the difference between @@IDENTITY, SCOPE_IDENTITY(), and IDENT_CURRENT()?

Explain THROW vs RAISERROR.

What are views? Can you update data through a view?

Explain the transaction isolation levels and the phenomena each prevents.

What is RCSI, and how does it differ from SNAPSHOT isolation?

What causes deadlocks, and how do you troubleshoot them?

What is parameter sniffing? How do you fix it?

What is SARGability? Give examples.

How do you read an execution plan? What do you look for first?

What are statistics, and how do they affect query plans?

Explain the Query Store and plan forcing.

When would you use a columnstore index?

Explain partitioning and partition switching.

What are wait statistics? Name common wait types and what they mean.

How does the transaction log work? Why does it grow, and how do you fix that?

Explain the recovery models and backup types. Design a backup strategy for RPO 5 minutes.

What is a tail-log backup?

Compare log shipping, FCI, and Always On AGs.

Synchronous vs asynchronous commit in an AG: what are the trade-offs?

What is TDE vs Always Encrypted vs dynamic data masking?

How does row-level security work?

What is In-Memory OLTP, and when should you use it?

How do you troubleshoot a sudden performance degradation in production?

What is TempDB contention, and how do you resolve it?

What are the new features in SQL Server 2022 and 2025?

What is Accelerated Database Recovery?

Explain memory grants and spills.

How does lock escalation work?

How do you migrate an on-premises database to Azure SQL?

Classic Query Puzzles

-- Nth highest salary
SELECT DISTINCT Salary FROM dbo.Employees ORDER BY Salary DESC OFFSET 2 ROWS FETCH NEXT 1 ROW ONLY; -- 3rd
WITH R AS (SELECT Salary, DENSE_RANK() OVER (ORDER BY Salary DESC) dr FROM dbo.Employees)
SELECT DISTINCT Salary FROM R WHERE dr = 3;

-- Employees earning more than their manager
SELECT e.FullName FROM dbo.Employees e JOIN dbo.Employees m ON m.EmployeeID = e.ManagerID WHERE e.Salary > m.Salary;

-- Highest-paid employee per department
SELECT * FROM (SELECT *, RANK() OVER (PARTITION BY DeptID ORDER BY Salary DESC) r FROM dbo.Employees) x WHERE r = 1;

-- Running total
SELECT OrderDate, Amt, SUM(Amt) OVER (ORDER BY OrderDate ROWS UNBOUNDED PRECEDING) FROM dbo.DailySales;

-- Customers who ordered in every month of 2025
SELECT CustomerID FROM dbo.Orders WHERE OrderDate >= '2025-01-01' AND OrderDate < '2026-01-01'
GROUP BY CustomerID HAVING COUNT(DISTINCT MONTH(OrderDate)) = 12;

-- Find missing IDs (gaps)
SELECT s.value FROM GENERATE_SERIES(1, (SELECT MAX(OrderID) FROM dbo.Orders)) s
WHERE NOT EXISTS (SELECT 1 FROM dbo.Orders o WHERE o.OrderID = s.value);

-- Month-over-month growth percentage
WITH M AS (SELECT DATETRUNC(MONTH, OrderDate) Mn, SUM(Amt) Total
…

Appendix C: T-SQL Cheat Sheet

Common Error Numbers Invalid column / object name Cannot insert NULL FK/CHECK constraint conflict

Duplicate key (PK/UNIQUE constraint / unique index) Divide by zero

String or binary data would be truncated Lock request timeout Transaction log full I/O errors and corruption Cannot open database

-- ===== DDL =====
CREATE DATABASE db;  ALTER DATABASE db SET RECOVERY FULL;  DROP DATABASE IF EXISTS db;
CREATE TABLE t (id INT IDENTITY PRIMARY KEY, name NVARCHAR(50) NOT NULL);
ALTER TABLE t ADD col INT NULL;  ALTER TABLE t ALTER COLUMN col BIGINT;  ALTER TABLE t DROP COLUMN col;
CREATE INDEX IX_t_name ON t(name) INCLUDE (id);   DROP INDEX IX_t_name ON t;
TRUNCATE TABLE t;  DROP TABLE IF EXISTS t;

-- ===== DML =====
INSERT INTO t (name) VALUES (N'a'), (N'b');
UPDATE t SET name = N'x' WHERE id = 1;
DELETE FROM t WHERE id = 1;
MERGE tgt USING src ON ... WHEN MATCHED THEN UPDATE ... WHEN NOT MATCHED THEN INSERT ...;

-- ===== Query =====
SELECT TOP (10) col, COUNT(*) FROM t WHERE ... GROUP BY col HAVING COUNT(*) > 1 ORDER BY 2 DESC;
SELECT ... ORDER BY col OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;
WITH cte AS (SELECT ...) SELECT * FROM cte;
SELECT ROW_NUMBER() OVER (PARTITION BY a ORDER BY b) FROM t;

-- ===== Programming =====
DECLARE @x INT = 1;  SET @x += 1;  IF @x > 1 PRINT 'yes' ELSE PRINT 'no';
WHILE @x < 10 BEGIN SET @x += 1; END
BEGIN TRY BEGIN TRAN; ...; COMMIT; END TRY BEGIN CATCH IF @@TRANCOUNT > 0 ROLLBACK; THROW; END CATCH
EXEC sp_executesql N'SELECT * FROM t WHERE id =
…

Appendix D: Certification Path and Resources

Microsoft Certifications (verify the current list; Microsoft retires and renames exams often)

DP-300: Administering Microsoft Azure SQL Solutions Azure Database Administrator Associate DP-600 / DP-700

Fabric Analytics Engineer / Fabric Data Engineer Power BI Data Analyst Azure Data Fundamentals

Microsoft Learn: SQL Server documentation and T-SQL reference

Microsoft Learn training paths for SQL Server and Azure SQL SQL Server blog and Azure SQL blog *T-SQL Fundamentals*: Itzik Ben-Gan *T-SQL Querying*: Itzik Ben-Gan et al.

*SQL Server Execution Plans*: Grant Fritchey *Pro SQL Server Internals*: Dmitri Korotkevitch

*SQL Server 2022 Query Performance Tuning*: Grant Fritchey *Expert Performance Indexing in SQL Server*: Jason Strate and Grant Fritchey

*Pro SQL Server Relational Database Design and Implementation*: Louis Davidson

*Microsoft SQL Server 2019/2022 Administration Inside Out*: Microsoft Press

*The Data Warehouse Toolkit*: Ralph Kimball Community and Free Tools

Brent Ozar (First Responder Kit, sp_Blitz*) Ola Hallengren (Maintenance Solution) Adam Machanic (sp_WhoIsActive)

Erik Darling, Paul White (optimizer internals), Paul Randal / SQLskills (internals, waits library) dbatools.io (PowerShell)

SQLServerCentral, MSSQLTips, SQLShack, Stack Exchange DBA

PASS Data Community Summit, SQLBits, SQLSaturday events Practice: LeetCode SQL, HackerRank SQL, StrataScratch, SQLZoo, DataLemur Suggested 12-Week Study Plan Fundamentals, setup, data types DDL, DML, SELECT, functions

Aggregation, joins, set operators, subqueries CTEs, window functions, pivot, views, design

Programming, procedures, functions, triggers, errors Transactions, concurrency, cursors, dynamic SQL JSON/XML, temporal, CDC, full-text

Internals, indexing, statistics, plans, Query Store Partitioning, columnstore, In-Memory, TempDB

Security, backup/restore, HA/DR, replication Agent, monitoring, maintenance, configuration, integration, BI 51–56 + Capstone

Linux, Azure, new features, DevOps, best practices Final tip: The fastest way to master SQL Server is to write queries every day, read execution plans for everything, and break things in a lab and then fix them. Happy querying! 🚀