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

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

Actions and Detail Panel

£600

SQL 2-Day Course on intermediate to advanced level, concentrating on query automation.

About this event

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.)

Share with friends