02. August 2018 - 11:00
PCWorkshops as Regus, Manchester
Share it on:

MS SQL Server Advanced 2-Day Course, Manchester. Classroom or Virtual. | Thursday, 02. August 2018

MS SQL Server Advanced Course


Because of the Corona-Virus Situation, we offer the courses online only until further notice.


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, Aggergates, Group by, Having. Cubes, Pivot, unPivot.


Update, Insert and Delete Records, Merge, Commit, Rollback, SavePoint



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 functionsDebugging: The SQL Server debugger, Debugging (breakpoints, etc.)