Microsoft SQL Server 2025 Database Queries
Microsoft SQL Server 2025 Database Queries is a course in report-level T-SQL that builds on the basics, covering multi-table joins, grouping sets, subqueries, CTEs, window functions, JSON and regular expressions, through to reading execution plans and using indexes to make queries faster. It suits developers, data analysts and reporting teams who pull data from SQL Server.
- Duration18 hours · 3 days
- FormatOnsite / live online
- Next roundOn request
- CertificateIncluded
Course overview
People who can already write some SQL soon meet questions that a plain SELECT and JOIN cannot answer, such as ranking sales within each region, comparing with the previous month, calculating running totals or reading JSON from another system. The result is long, complicated queries, wrong figures caused by duplicated joins or NULLs, and slow performance as data grows, even though current T-SQL already offers shorter and faster tools.
This course takes query writing on Microsoft SQL Server 2025 further: logical query processing, filtering and handling NULL, joining many tables, grouping with GROUPING SETS and ROLLUP, and using date, string and the regular expression functions added in 2025. Learners write subqueries, CTEs, APPLY, PIVOT and window functions, work with the json data type, program in T-SQL with transactions and error handling, and finish by reading execution plans and using indexes and statistics to make queries faster.
What you’ll gain
- Write correct, efficient SELECT statements with an understanding of logical query processing
- Query several tables and group data at several levels correctly
- Solve reporting problems with subqueries, CTEs, APPLY, PIVOT and window functions
- Use the date, string, regular expression and JSON features of SQL Server 2025
- Program in T-SQL with transactions and error handling
- Read execution plans and use indexes and statistics to make queries faster
Who this course is for
- Software developers who write SQL against SQL Server databases
- Data analysts and reporting teams who pull data from SQL Server
- System administrators and IT teams who write scripts to check data
- People who have completed a SQL Server foundation course and want to take their queries further
Prerequisites
- Able to write basic SELECT, WHERE, GROUP BY and JOIN, or completed Microsoft SQL Server 2025 Database Basic
- An understanding of tables, primary keys and foreign keys
- Able to use SQL Server Management Studio or another query tool
- A laptop with permission to install software
Curriculum
Course Details
This course is the 2026 edition of Microsoft SQL Server Database Queries, rewritten for SQL Server 2025 with the query techniques used in real reporting work. It runs for 3 days, 6 hours per day (18 hours in total, 09:00-16:00), as lectures with labs on a sales database holding several years of data. Intermediate level. Day one builds an understanding of logical query processing, NULL handling,
joins and multi-level grouping. Day two covers date, string and regular expression functions, subqueries, CTEs, APPLY, PIVOT and window functions. Day three covers JSON, T-SQL programming with transactions, and faster queries through execution plans and indexes. The course builds on Microsoft SQL Server 2025 Database Basic and does not overlap with server administration or whole-server tuning. Learners take home the query scripts and answers for every lab.
Day 1 Correct Query Foundations, Joins and Grouping
Section 1: Lab: SQL, T-SQL and Logical Query Processing
- The history of SQL and T-SQL, and when to stick to the ANSI standard
- Relational theory and thinking in sets
- Logical query processing: FROM, WHERE, GROUP BY, HAVING, SELECT and ORDER BY
- Compatibility level 170 and the language features of SQL Server 2025
- Lab: explain why a SELECT alias cannot be used in WHERE, then fix the query
Section 2: Lab: Writing Clear and Correct SELECT Statements
- Aliases, calculated expressions and concatenation with CONCAT and ||
- Simple and searched CASE expressions, and IIF
- DISTINCT, TOP WITH TIES and OFFSET FETCH for paging
- GREATEST and LEAST for picking the largest or smallest value in a row
- Lab: grade customers by spend and page through the product list
Section 3: Lab: Filtering and Handling NULL
- Comparison operators in WHERE and filtering text with LIKE
- Three-valued logic with NULL and its effect on WHERE, NOT IN and counts
- IS DISTINCT FROM, COALESCE and NULLIF
- Writing conditions that can use indexes (SARGable) from the start
- Lab: find rows lost because of NULL and correct the report figures
Section 4: Lab: Querying Multiple Tables with Joins
- Normalisation and relationships between tables
- INNER, OUTER, CROSS and SELF JOIN in real problems
- How a condition in ON differs from one in WHERE in an OUTER JOIN
- Preventing double counting from one-to-many joins
- Lab: a multi-table order report whose totals match the accounts
Section 5: Lab: Grouping and Sorting
- ORDER BY on several columns and sorting Thai text with collations
- Aggregate functions, GROUP BY and HAVING
- STRING_AGG for combining text into a list
- GROUPING SETS, ROLLUP, CUBE and the GROUPING function
- Lab: sales with subtotals by region, province and company in a single query
Day 2 Data Types, Subqueries, CTEs and Window Functions
Section 6: Lab: Querying Dates and Text
- How date, datetime2 and datetimeoffset differ
- DATEADD, DATEDIFF, EOMONTH, DATETRUNC and filtering date ranges correctly
- GENERATE_SERIES for producing every date, even days with no sales
- String functions and searching with REGEXP_LIKE, REGEXP_SUBSTR and REGEXP_REPLACE
- Lab: a daily sales report with no missing days, and extracting province codes from addresses
Section 7: Lab: Subqueries and EXISTS
- Scalar and multi-valued subqueries
- How correlated subqueries work
- EXISTS versus IN, and NOT EXISTS versus NOT IN when NULLs are present
- Derived tables and views
- Lab: find customers who bought from every category and customers who have stopped buying
Section 8: Lab: Common Table Expressions and APPLY
- How CTEs make complex queries easier to read
- Recursive CTEs for hierarchies such as organisation charts and product categories
- CROSS APPLY and OUTER APPLY with table-valued functions
- Top N per group with APPLY
- Lab: a multi-level organisation chart and the top 3 products in each category
Section 9: Lab: PIVOT, UNPIVOT and Set Operators
- PIVOT for turning rows into columns
- UNPIVOT and CROSS APPLY VALUES for turning them back
- Dynamic PIVOT with sp_executesql, done safely
- UNION, INTERSECT and EXCEPT for comparing two data sets
- Lab: a monthly sales cross-tab, and checking mismatched data between two systems
Section 10: Lab: Window Functions
- OVER, PARTITION BY and ORDER BY
- ROW_NUMBER, RANK, DENSE_RANK and NTILE
- Running totals and moving averages with ROWS and RANGE
- LAG, LEAD, FIRST_VALUE and the WINDOW clause
- Lab: rank salespeople by region, compare with the previous month and calculate running totals
Day 3 JSON, T-SQL Programming and Query Performance
Section 11: Lab: Working with JSON
- The json data type in SQL Server 2025 and ISJSON
- JSON_VALUE, JSON_QUERY and OPENJSON for reading data
- JSON_OBJECT, JSON_ARRAY and JSON_ARRAYAGG for building JSON
- FOR JSON PATH for returning results to applications
- Lab: load JSON orders from an external system into tables
Section 12: Lab: Programming in T-SQL
- Variables, batches, IF and WHILE
- Stored procedures that take parameters and return results
- Table-valued parameters for passing many rows
- Safe dynamic SQL with sp_executesql
- Lab: a multi-criteria product search procedure that is not open to SQL injection
Section 13: Lab: Transactions and Error Handling
- Transactions, ACID and an introduction to isolation levels
- TRY...CATCH, THROW and XACT_STATE
- SET XACT_ABORT ON and rolling back correctly
- Choosing between MERGE and separate INSERT and UPDATE statements
- Lab: reduce stock and record an order so that it either all succeeds or all rolls back
Section 14: Lab: Query Performance
- Reading estimated and actual execution plans
- Clustered, nonclustered, covering and filtered indexes
- Statistics and their effect on row estimates
- SET STATISTICS IO and TIME for measuring before and after
- Lab: turn a slow report query from a table scan into one that uses an index
Section 15: Workshop: A Sales Report for Management
- Taking a report request from users and planning the query
- Combining data with CTEs, window functions and GROUPING SETS
- Packaging it as a view or stored procedure for the team
- Checking the figures, the execution plan and the indexes
- Presenting the result and reviewing the queries together
Schedule & training options
For individuals — public rounds
No public rounds are open right now. Join the waiting list and we will contact you first when the next round opens, or ask us on LINE. Or call 02-570-8449 or 088-807-9770
For organisations — in-house / private
- Tailor the content to your team’s tools and projects
- Your dates, at your office or live online
- Quotation with tax ID for procurement
Instructors
Frequently asked questions
What is the difference between WHERE and HAVING in SQL?
WHERE filters rows before grouping, so it cannot use aggregate functions such as SUM or COUNT. HAVING filters after GROUP BY, so it can work with each group's totals, for example to find provinces whose total sales exceed one million baht. Filter out unwanted rows with WHERE first wherever possible, because it reduces the data to be grouped and lets indexes be used.
What is a common table expression (CTE), and how does it differ from a subquery?
A CTE is a named subquery defined with WITH before the main statement. It breaks a complex query into readable steps, can be referenced more than once in the same statement, and can be recursive to walk hierarchies such as an organisation chart. A subquery is written inline inside the statement. In performance terms both usually produce similar execution plans, so choose whichever makes the query easier to read and maintain.
How do you use an execution plan to find out why a query is slow?
In SSMS, turn on Include Actual Execution Plan and run the query, then read the plan from right to left. Look for expensive operators such as table or index scans on large tables, many key lookups or sorts that use a lot of memory, and compare estimated with actual row counts. Large differences usually point to out-of-date statistics or conditions that cannot use an index. Measure before and after every change with SET STATISTICS IO and TIME.
Who is the Microsoft SQL Server 2025 Database Queries course for, and what should I know beforehand?
It suits developers, data analysts, reporting teams and IT staff who already write SQL against SQL Server and want to go further. You should be able to write basic SELECT, WHERE, GROUP BY and JOIN, understand primary and foreign keys and use SSMS. If you do not yet have this background, take Microsoft SQL Server 2025 Database Basic first.
What will I be able to do after the Microsoft SQL Server 2025 Database Queries course?
You will be able to write correct queries with a clear grasp of logical processing and NULL, build multi-level reports with GROUPING SETS and PIVOT, solve complex problems with subqueries, CTEs and APPLY, analyse rankings, running totals and changes with window functions, read and build JSON, program in T-SQL with transactions, and read execution plans to make queries faster. Participants receive a Certificate of Completion.
Can the Microsoft SQL Server 2025 Database Queries course be run in-house for our team?
Yes. It can be run in-house for your team, with exercises adapted to the reports the team produces, such as practising on table structures similar to your real systems or reviewing slow queries. You can request a quotation from this page, or contact us on LINE @itgenius or by phone on 02-570-8449.
Related articles
Articles are published in Thai.