Part 1: Querying Data with Transact-SQL

• Introduction to SQL Server 2022: Architecture, Editions, SSMS.

• Basic T-SQL Queries: SELECT statements, logical processing order, joins (Inner, Outer, Cross), sorting (ORDER BY), and filtering (WHERE).

• Data Manipulation: Working with data types (Character, Date/Time), inserting, updating, and deleting data (INSERT, UPDATE, DELETE).

• Advanced Query Techniques: Using built-in functions (Conversion, Logical, NULL handling); grouping and aggregating data (GROUP BY, HAVING).

• Set Operations & Subqueries: Using UNION, EXCEPT, INTERSECT; writing self-contained and correlated subqueries.

• Advanced Constructs: Table expressions (Views, CTEs), window functions (OVER), and pivoting data (PIVOT/UNPIVOT).

• Programming & Error Handling: Implementing stored procedures, dynamic SQL, control flow, transactions, and structured error handling.

Part 2: Developing SQL Databases

• Table Design: Designing tables, using schemas, data types, and advanced designs (Partitioning, Temporal Tables).

• Data Integrity: Enforcing integrity with constraints (Primary Key, Foreign Key, Check, Default).

• Indexing: Implementing and optimizing indexes (Clustered, Nonclustered, Columnstore); using Execution Plans and the Query Store.

• Database Objects: Implementing Views, Stored Procedures, User-Defined Functions, and Triggers.

• Advanced Data Types: Working with XML, Spatial data, and BLOBs (FILESTREAM, Full-Text Search).

• Concurrency & Performance: Understanding transactions, locking, and monitoring performance using Extended Events.

Part 3: Administering a SQL Database Infrastructure

• Security & Access: Configuring data access (users, roles, permissions), implementing Row-Level Security and Dynamic Data Masking; setting up auditing.

• Backup & Recovery: Developing backup strategies (Full, Log, Differential), restoring databases (Point-in-time, Piecemeal), and managing database integrity (DBCC checks).

• Instance Monitoring: Monitoring activity and queries (Query Store, Extended Events), managing indexes and statistics, and configuring SQL Agent alerts.

• High Availability (HA) & Disaster Recovery (DR): Implementing Log Shipping, configuring Always On Availability Groups, and managing Failover Cluster Instances.