MS SQL Server Advanced 2-Day Course, Manchester. Classroom or Virtual.
by PCWorkshops
Location
Online event
Refund policy
No Refunds
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.)