IT SQL Server Querying and Programming

Introduction to Databases

• Database Basics
• Data Types
• Using SQL Server Management Studio
• SQL Batches and the GO Command

Querying and Updating the Database

• Selecting Data
• Selecting Columns from a Table
• Inner, Outer and Cross Joins
• Overview of Graph Database
• Union, Intersect and Except
• Updating, Inserting and Deleting Data
• Merging Data
• Recording Changes

Data Warehouses

• Overview of Data Warehouses in SQL Server
• Star Schemas
• SQL Querying for Data Warehouses
• Overview of Inmon and Kimball Methodologies

Tables

• Creating & Altering Tables
• Creating and Updating a Table with Transact SQL
• Dropping Tables
• Temporal Tables

Using Variables and Functions

• Variables
• Functions
• Scalar Functions
• Aggregate Functions
• Splitting, Aggregating and Trimming Strings

Programming with Transact SQL

• Stored Procedures
• Creating Stored Procedures
• Executing Stored Procedures
• Controlling Flow of Code
• User Defined Functions

Advanced Querying

• Subqueries
• Grouping and Summaries
• Views
• Common Table Expressions
• Pivot Tables
• Ranking Functions
• Window Functions

Optimisation

• Designing Indexes
• Creating an Index
• Creating an Index on a View
• Viewing the Execution Plan
• Putting the Performance Tools to Use

Advanced Programming with Transact SQL

• Temporary Tables
• Declaring a Variable with the Table Datatype
• Dynamic SQL
• Cursors

Introduction to SQL Server Support for XML

• Introduction to XML
• XML Casting
• FOR XML
• Overview of SQL Server Support for JSON

Data Integrity

• Error Handling
• Try... Catch Block
• Transactions and Locks
• Triggers
• Triggers in Management Studio

Real World Development

• Writing SQL for Reporting Tools
• Troubleshooting
• Other SQL Server Technologies
• Best Practices