Advanced SQL (classroom and online)
Course
In London and Manchester
*Indicative price
Original amount in GBP:
£ 995
Description
-
Type
Workshop
-
Level
Advanced
-
Location
-
Duration
2 Days
-
Start date
Different dates available
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 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. Suitable for: This Advanced SQL course is aimed primarily at people working in business and public sector organisations
Important information
Documents
- Advanced SQL course outline.pdf
Facilities
Location
Start date
Start date
Start date
About this course
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.
Reviews
Subjects
- Stored procedures
- Variables
- Parameters and return variables
- Scalar functions
- Testing conditions
- Looping
- Error Handling
- Deleting using SQL
- Updating data in SQL
- Inserting Data
- Creating Tables
- Transactions
- Temporary tables and table variables
- Table-valued functions
- Derived tables and CTEs
- Debugging
Teachers and trainers (4)
Andrew Gould
Senior Trainer
Andy Brown
Microsoft Trainer & Programmer
David Wakefield
Director / Programmer / Trainer
Sam Lowrie
Trainer
Course programme
- Creating stored procedures
- Executing stored procedures
- System stored procedures
- Declaring variables
- SET versus SELECT
- Tricks with variables
- So-called global variables
- Passing parameters
- Default values / WHERE clauses
- Output parameters
- Using RETURN
- What they are
- Writing user-defined functions
- Worked examples
- Pros and cons of scalar functions
- IF / ELSE statement
- Using CASE where possible
- Syntax of WHILE
- Breaking out of a loop
- Using TRY / CATCH
- System error functions
- Custom error messages
- Ways to drop tables
- DELETE versus TRUNCATE
- The UPDATE statement
- Updating using joins
- Making tables (SELECT INTO)
- Appending data (INSERT INTO)
- Inserting individual rows
- Creating tables in SQL
- Primary keys and indexes
- Adding relationships
- Beginning a transaction
- Committing / rolling back
- Scope (local versus global)
- Using temporary tables
- Creating table variables
- Pros and cons of each approach
- In-line table-valued functions
- Mult-statement table-valued functions
- Using derived tables
- Common Table Expressions (CTEs)
- Multiple CTEs in a single query
If time
Cursors- Syntax of fetching rows
- Why not to use cursors!
- Debugging queries and procedures
- Setting breakpoints
If time
Dynamic SQL- Building up dynamic SQL
- Executing dynamic SQL
- Disadvantages of dynamic SQL
If time
Pivots- Assembling data for pivoting
- Using PIVOT
- Dynamic column headers
If time
Triggers- Insert, update and delete triggers
- Using the generated tables (eg INSERTED)
- Using INSTEAD OF
Additional information
Advanced SQL (classroom and online)
*Indicative price
Original amount in GBP:
£ 995