Advanced SQL training course (2 days)
If you are comfortable creating queries in SQL, maybe it is time to move on to learn more advanced programming topics? This two day course assumes that you know how to query data using SQL, and shows you how to really progam in SQL Server. Learn how to write stored procedures; learn the differences between temporary tables, table variables, common table expressions, derived tables and the two types of table-valued function, and when to use each tool; find out what cursors are, and when you should avoid them; and learn about debugging and error trapping. A fun two days getting to know SQL better! The course uses SQL Server 2005 or 2008 R2, although you can elect to use 2012 if you prefer (just let us know in advance).
Advanced SQL training - Course Contents
The contents of our Advanced SQL course are as follows (any items marked with a * will be covered if time allows):
Stored procedure basics
- Pros and cons of stored procedures
- Creating stored procedures
- Three ways to execute
- System stored procedures
Variables
- Declaring variables
- SET versus SELECT
- Tricks with variables and rowsets
- So-called global variables
Parameters and return values
- Passing parameters
- Default values and WHERE clauses
- Output parameters
- Using RETURN
Avoiding scalar functions
- What are scalar functions?
- Some examples
- Disadvantages of scalar functions
- Three alternatives
Testing conditions
- IF / ELSE statement
- Using CASE where possible
Looping
- Syntax of 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
Creating tables
- Creating tables in SQL
- Primary keys and indexes
- Setting constraints
- Creating tables from existing data
|
Inserting data
- Inserting single rows
- Inserting multiple rows
Temporary tables and table variables
- Using temporary tables
- Creating table variables
- Pros and cons of each approach
Table-valued functions
- In-line table-valued functions
- Mult-statement table-valued functions
- Limitations of user-defined functions
Derived tables and CTEs
- Using dervied tables
- Common Table Expressions (CTEs)
- Recursive CTEs
Subqueries
- The concept of a subquery
- Using ALL, ANY and IN
- Correlated subqueries
- Using EXISTS
Cursors
- Syntax of fetching rows
- When to use (and when not to)
Error-handling
- Using TRY / CATCH
- System error functions
- Custom error messages
- The obsolete @@error function
Debugging
- Version differences
- The Visual Studio debugger
- The SQL Server debugger
- Debugging (breakpoints, etc.)
Dynamic SQL (*)
- Building up dynamic SQL
- Executing dynamic SQL
- Pros and cons
|
Click on any link above to show free exercises for the topic in question. You should only attend this course if you have either attended our two-day introduction to SQL course or if you are comfortable creating queries using SQL. There will be a maximum of 6 people on each course, and each person will have their own computer.
