Free course · From scratch to advanced
SQL for .NET developers
The SQL every .NET developer needs, from scratch: tables and keys, SELECT, JOIN, changing data, grouping, indexes and transactions, with a diagram for every topic.
Module 1
Tables and queries
What a relational database is, how tables, rows and keys fit together, and how to read data with SELECT and JOIN.
Lesson 1: Databases, tables and keysBeginner
Almost every MVC app stores its data in a relational database such as SQL Server. This lesson explains the building blocks: databases, tables, rows, columns, data types, primary keys and foreign keys, and shows how to create two related tables.
Lesson 2: Data types: choosing the right column typeBeginner
Every column has a data type that decides what it can hold, how much space it takes and how fast it is to search. This lesson shows the SQL Server types you will use every day, how each one maps to C#, and the common mistakes that waste space or lose data.
Lesson 3: Constraints: NOT NULL, UNIQUE, CHECK, DEFAULT and foreign keysBeginner
Constraints are rules the database enforces on every insert and update: a value must exist, be unique, pass a check, get a default or point to a real row in another table. This lesson shows each kind with examples and explains why rules in the database beat rules only in C#.
Lesson 4: Database design and normalizationBeginner
Normalization is how you split data into tables so each fact is stored once. This lesson walks a messy spreadsheet-style table through first, second and third normal form, shows the bugs that duplication causes, and explains when a little denormalization is fine.
Lesson 5: Querying data with SELECTBeginner
SELECT is how you read data. This lesson covers choosing columns, filtering with WHERE, sorting with ORDER BY and limiting rows, explains the order in which SQL actually processes a query, and shows how to run a safe parameterized query from C#.
Lesson 6: Filtering, sorting and pagingBeginner
Most screens show a filtered, sorted page of rows. This lesson covers WHERE with AND, OR, IN, BETWEEN, LIKE and NULL, ORDER BY with ties, and paging with OFFSET and FETCH, plus the keyset alternative that stays fast on page 5,000.
Lesson 7: String, date and conversion functionsBeginner
SQL Server has built-in functions to clean text, work with dates and convert between types. This lesson covers the ones you will reach for every week, from TRIM and CONCAT to DATEADD, EOMONTH and TRY_CONVERT, and warns about using functions on indexed columns in WHERE.
Lesson 8: CASE expressionsBeginner
CASE adds if-then-else logic inside a query. This lesson shows simple and searched CASE, using it to label rows, to sort in a custom order and to count several things in one pass with conditional aggregation, a pattern that replaces many separate queries on big tables.
Lesson 9: Joining tablesBeginner
Data about one thing is often spread across tables: customers in one, orders in another. JOIN combines rows from several tables in one query. This lesson explains INNER, LEFT, RIGHT, FULL and CROSS joins with a picture of what each returns.
Lesson 10: Subqueries and EXISTSIntermediate
A subquery is a query inside another query. This lesson covers subqueries that return one value, a list or a table, correlated subqueries that run per row, and EXISTS and NOT EXISTS, the safest and often fastest way to ask whether related rows exist.
Lesson 11: UNION, INTERSECT and EXCEPTIntermediate
Set operators combine the results of two queries into one. This lesson covers UNION, UNION ALL, INTERSECT and EXCEPT, the rules both queries must follow, and why UNION ALL is usually the one you want.
Module 2
Changing and summarising data
Add, change and remove rows safely, then count, total and group data with aggregate functions.
Lesson 12: Changing data: INSERT, UPDATE and DELETEBeginner
Reading data is half the story. This lesson covers adding rows with INSERT, changing them with UPDATE and removing them with DELETE, how to get a new row's id, and a safe routine that prevents accidentally changing every row in a table.
Lesson 13: Grouping and aggregate functionsIntermediate
Reports need totals, counts and averages: sales per month, orders per customer, the average rating per product. This lesson explains aggregate functions, GROUP BY and HAVING, and the order in which they run.
Lesson 14: Window functions: ROW_NUMBER, RANK, LAG and running totalsIntermediate
Window functions calculate across a set of related rows without collapsing them like GROUP BY does. This lesson covers OVER with PARTITION BY and ORDER BY, ROW_NUMBER, RANK and DENSE_RANK, LAG and LEAD, and running totals, with the classic "latest row per group" pattern.
Lesson 15: MERGE and upsertsIntermediate
An upsert updates a row if it exists and inserts it if it does not. This lesson shows the simple UPDATE-then-INSERT pattern with the locking hints that make it safe under load, the MERGE statement for syncing a whole table, and the traps that make many teams avoid MERGE.
Lesson 16: PIVOT and UNPIVOTAdvanced
PIVOT turns row values into columns, such as one column per month, and UNPIVOT does the reverse. This lesson shows both, the conditional aggregation alternative that many developers find easier, and how to pivot when the column list is not known in advance.
Module 3
Speed and safety
Make queries fast with indexes and keep data consistent with transactions.
Lesson 17: IndexesIntermediate
An index lets the database find rows without reading the whole table, like the index at the back of a book. This lesson explains clustered and nonclustered indexes, how to choose columns to index, and the cost indexes add to writes.
Lesson 18: Reading execution plansAdvanced
An execution plan shows how SQL Server actually runs your query: which indexes it reads, how it joins tables and where the time goes. This lesson shows how to get a plan, the operators to recognise, and the warning signs that point straight to a fix.
Lesson 19: TransactionsIntermediate
A transaction groups several changes so they succeed or fail together. Moving money between two accounts must never debit one without crediting the other. This lesson explains ACID, BEGIN, COMMIT and ROLLBACK, error handling and isolation levels.
Lesson 20: Isolation levels, locking and deadlocksAdvanced
When many users read and write at once, isolation levels decide what one transaction can see of another's unfinished work. This lesson explains locks, the common isolation levels, read committed snapshot, and how to find and prevent deadlocks.
Module 4
CTEs, views, functions and triggers
Name and reuse queries with CTEs and views, package logic in functions, react to changes with triggers, and keep all of them fast on large tables.
Lesson 21: Common table expressions (CTEs)Intermediate
A CTE gives a query a name with WITH, so you can build a complex query in readable steps. This lesson covers normal and recursive CTEs, the classic remove-duplicates trick, and when a temp table is the better choice for very large data.
Lesson 22: ViewsIntermediate
A view is a saved SELECT that you can query like a table. Views hide complex joins, give each app or user only the columns they need, and, as indexed views, can pre-calculate totals over millions of rows.
Lesson 23: User-defined functionsIntermediate
Functions package logic you can call inside a query: scalar functions return one value and table-valued functions return rows. This lesson shows both, explains why inline table-valued functions scale best on large tables, and compares functions with stored procedures.
Lesson 24: TriggersAdvanced
A trigger is code that SQL Server runs automatically when rows are inserted, updated or deleted. This lesson builds an audit trigger that works for many rows at once, shows INSTEAD OF triggers, and explains what triggers cost on large data.
Module 5
Stored procedures and scripts
Write reusable server-side code: stored procedures, temporary tables, error handling with TRY...CATCH and safe dynamic SQL.
Lesson 25: Stored proceduresIntermediate
A stored procedure is named, reusable T-SQL saved in the database. This lesson shows how to create one with parameters, output parameters and return values, when procedures are a good idea for .NET apps, and how to call one safely from C#.
Lesson 26: Temporary tables and table variablesIntermediate
Temporary tables hold intermediate results while a script or procedure runs. This lesson compares local temp tables, table variables and CTEs, shows when each one fits, and covers table-valued parameters for sending a whole list from C# in one call.
Lesson 27: Error handling with TRY...CATCHIntermediate
T-SQL has TRY...CATCH blocks, much like C#. This lesson shows how to catch errors, roll back cleanly, read the error details, raise your own errors with THROW, and how those errors reach your .NET code as SqlException.
Lesson 28: Dynamic SQL and SQL injectionAdvanced
Dynamic SQL builds a query as a string at run time, which is useful for optional search filters and dynamic sorting but dangerous when user input is joined into the string. This lesson shows what SQL injection is, how sp_executesql with parameters prevents it, and how to handle names safely.
Module 6
JSON, security and backups
Store and query JSON, control who can do what with logins, users and permissions, and protect data with backups.
Lesson 29: JSON in SQL ServerIntermediate
SQL Server can store, query and produce JSON. This lesson shows how to return query results as JSON, read values with JSON_VALUE, turn a JSON array into rows with OPENJSON, validate with ISJSON, and index a JSON property for fast searches.
Lesson 30: Logins, users and permissionsIntermediate
Database security decides who can connect and what each account may do. This lesson explains logins and users, roles, granting only what an app needs, schemas as permission boundaries, and safer options for connecting from .NET such as managed identities.
Lesson 31: Backup and restoreIntermediate
Backups are what save you after a bad deploy, a deleted table or a failed disk. This lesson explains full, differential and log backups, recovery models, restoring to a point in time, testing restores, and what Azure SQL does for you automatically.