MS SQL Server Advanced 2-Day Course, Manchester. Classroom or Virtual.

MS SQL Server Advanced Course

Pre-requisite: General Queries, Built-in Functions and Expressions

Duration: 2 consecutive days, the first of which shows as the scheduled date.

Course summary

Revision:

• Query Summary and revisions, Aggregates, Group by, Having. Cubes, Pivot, unPivot.
• Update, Insert and Delete Records, Merge, Commit, Rollback, Save Point

Creating Tables in SQL

• Create, alter and drop Tables
• Constraints:  Not Null , Unique , Default , Check , Primary Key , Foreign Key , Identity / Auto-increments
• Inserting data into tables:  values, single rows and multiple rows using queries
• Sequencing
• Indexes, Create, alter and drop  Indexes

SQL Sub-queries

• ·Sub-queries in the WHERE, SELECT, From or Having clauses
• ·Using SOME, ALL, ANY and IN clauses in the Where Clause ·Self-contained sub-queries
• ·Correlated sub-queries
• ·Sub-queries with the EXISTS predicate

Derived Tables and CTEs

• ·Using SQL derived tables ·SQL Common Table Expressions (CTEs) ·Recursive CTEs 

Create, alter and drop  Views

SQL Temporary Tables and Table Variables

• ·Create and use SQL temporary tables
• ·Create and use SQL table variables
• ·SQL Tables vs. Table Variables 

SQL Stored Procedure Basics

• ·Stored Procedure Principles ·Creating stored procedures ·Executing stored procedures ·System stored procedures , Variables: ·Declaring variables ·SET versus SELECT , Parameters and Return Values: 
• ·Passing parameters ·Default values and WHERE clauses ·Output parameters ·Using RETURN
• Testing Conditions: ·The IF/ELSE statement ·CASE ·CHOOSE ,
• Looping: ·Syntax of SQL WHILE ·Breaking out of a loop ·Basic transactions ·Beginning a transaction ·Committing/rolling back ·Deleting and updating ·Using DELETE and UPDATE ·Sys. Objects ·Dropping objects 

User defined Functions

• Table Valued Functions
• ·In-line table-valued functions ·Multi- Statement table-valued functions ·Limitations of user-defined functions
• Scalar Functions
• ·What are scalar functions? ·Some examples ·Disadvantages of scalar functions ·Three advantages

Triggers

• Create or replace triggers 

Error-Handling: SQL TRY/CATCH, SQL System error functions

Debugging: The SQL Server debugger, Debugging (breakpoints, etc.)