🧠 T-SQL Coding Specialist
Course Theme: From Raw Data to Business Decisions
You are hired as a Junior Data Analyst for RetailCo.
Over 10 units, you will design, manage, analyze, and report on business data using Microsoft SQL Server and Transact-SQL.
By the end of this course, you won’t just write queries —
you’ll build a full Business Intelligence System.
🧱 Unit 1 – Foundations of Transact-SQL
Focus: SELECT statements & ORDER BY T-SQL Coding Specialist Exam Ob…
🎯 What You’ll Learn
- How to retrieve data using SELECT
- How to sort data with ORDER BY
- How to read and interpret query results
🏗 Project Role
Data Explorer
You will:
- Explore RetailCo’s Customers, Products, and Orders tables
- Create sorted sales reports
- Understand how raw data becomes readable information
| Lesson Focus | Objective # | Exam Objective |
|---|---|---|
| SELECT basics | 1.1 | ORDER BY |
| Sorting output | 1.1 | ORDER BY |
🧱 Unit 2 – Filtering & Conditional Logic
Focus: WHERE, LIKE, BETWEEN, CASE T-SQL Coding Specialist Exam Ob…
🎯 What You’ll Learn
- Filter rows using WHERE
- Use BETWEEN for ranges
- Use LIKE for pattern matching
- Categorize data using CASE
🏗 Project Role
Business Analyst
You will:
- Segment customers
- Generate time-based sales reports
- Create pricing tiers
| Lesson Focus | Objective # | Exam Objective |
|---|---|---|
| Range filtering | 1.7 | BETWEEN |
| Pattern matching | 1.6 | LIKE |
| Conditional categorization | 1.4 | CASE statement |
🧱 Unit 3 – Set Operations & Aggregation
Focus: UNION, INTERSECT, COUNT, MAX, GROUP BY T-SQL Coding Specialist Exam Ob…
🎯 What You’ll Learn
- Combine datasets
- Calculate totals and counts
- Group data into business summaries
🏗 Project Role
Performance Analyst
You will:
- Merge multi-store data
- Identify shared customers
- Build summary dashboards
| Lesson Focus | Objective # | Exam Objective |
|---|---|---|
| Combine datasets | 1.2 | UNION ALL |
| Find overlaps | 1.3 | INTERSECT |
| Count records | 2.1 | COUNT |
| Find maximums | 2.2 | MAX |
| Grouped summaries | 2.3 | GROUP BY |
🧱 Unit 4 – Subqueries & Table Expressions
Focus: Subqueries, CTEs, Derived Tables T-SQL Coding Specialist Exam Ob…
🎯 What You’ll Learn
- Nested queries
- Correlated subqueries
- Common Table Expressions (CTEs)
🏗 Project Role
Query Architect
You will:
- Find sales above average
- Build readable multi-step queries
- Structure complex logic cleanly
| Lesson Focus | Objective # | Exam Objective |
|---|---|---|
| Nested queries | 2.4 | Subqueries |
| Query organization | 2.5 | CTEs |
| Inline table logic | 2.6 | Derived tables |
🧱 Unit 5 – Ranking & Window Functions
Focus: ROW_NUMBER(), RANK(), DENSE_RANK(), LAG T-SQL Coding Specialist Exam Ob…
🎯 What You’ll Learn
- Rank products and employees
- Compare current vs previous periods
- Analyze trends without losing detail
🏗 Project Role
Analytics Specialist
You will:
- Build top-performer reports
- Compare month-over-month sales
- Generate business leaderboards
| Lesson Focus | Objective # | Exam Objective |
|---|---|---|
| Sequential numbering | 2.7 | ROW_NUMBER |
| Rank with ties | 2.8 | RANK |
| Dense ranking | 2.9 | DENSE_RANK |
| Period comparison | 2.10 | LAG |
🧱 Unit 6 – Data Modification & Transactions
Focus: UPDATE, Transactions, COMMIT T-SQL Coding Specialist Exam Ob…
🎯 What You’ll Learn
- Modify records safely
- Use transactions
- Understand concurrency
🏗 Project Role
Database Operations Engineer
You will:
- Adjust inventory
- Perform batch updates
- Protect data integrity
| Lesson Focus | Objective # | Exam Objective |
|---|---|---|
| Updating data | 2.11 | UPDATE + SET |
| Transaction control | 3.13 | COMMIT |
| Concurrency awareness | 3.14 | Concurrency |
🧱 Unit 7 – Database Structure & Views
Focus: JOINs, CREATE TABLE, CREATE VIEW T-SQL Coding Specialist Exam Ob…
🎯 What You’ll Learn
- Join multiple tables
- Design relational schemas
- Build reusable views
🏗 Project Role
Database Engineer
You will:
- Normalize your schema
- Implement GUIDs
- Create reporting views
| Lesson Focus | Objective # | Exam Objective |
|---|---|---|
| Table creation | 3.2 | CREATE TABLE |
| Unique identifiers | 3.3 | GUID |
| Table relationships | 3.1 | JOIN types |
| Reusable queries | 3.4 | CREATE VIEW |
🧱 Unit 8 – Data Integrity & Conversion
Focus: NULLs, CAST, CONVERT, THROW T-SQL Coding Specialist Exam Ob…
🎯 What You’ll Learn
- Handle NULL values
- Convert data types
- Implement error handling
🏗 Project Role
Data Quality Engineer
You will:
- Clean inconsistent data
- Prevent invalid updates
- Build safe conversion logic
| Lesson Focus | Objective # | Exam Objective |
|---|---|---|
| NULL replacement | 3.5 | COALESCE |
| NULL checking | 3.6 | IS NULL |
| Date precision | 3.7 | DATETIMEOFFSET |
| Data conversion | 3.8–3.11 | CAST / CONVERT / TRY_* |
| Error handling | 3.12 | THROW |
🧱 Unit 9 – Advanced Reporting
Focus: PIVOT, FOR XML T-SQL Coding Specialist Exam Ob…
🎯 What You’ll Learn
- Build cross-tab reports
- Export structured XML
- Prepare data for external systems
🏗 Project Role
Reporting & Integration Specialist
You will:
- Create executive pivot dashboards
- Generate XML exports
- Simulate data integration workflows
| Lesson Focus | Objective # | Exam Objective |
|---|---|---|
| Cross-tab reporting | 2.12 | PIVOT |
| Structured export | 2.13 | FOR XML |
🏆 Unit 10 – Capstone Integration
Focus: Full exam integration T-SQL Coding Specialist Exam Ob…
🎯 What You’ll Build
A complete SQL-powered analytics platform including:
- Relational schema
- Advanced analytics query library
- Ranking & trend analysis
- Transactions & safe updates
- Pivot reports
- XML export
- Technical documentation
🧑💼 Final Role
Junior Data Analyst → BI Architect
| Integrated Skill | Objective # |
|---|---|
| Ranking & analytics | 2.7–2.10 |
| Aggregation | 2.1–2.3 |
| Subqueries & CTEs | 2.4–2.6 |
| Safe updates | 2.11 + 3.13 |
| Reporting | 2.12–2.13 |
| Schema design | 3.1–3.4 |
