Introduction
Learn to use the advanced features of SQL, develop your knowledge of writing SQL scripts to manage data stored in a relational database.
Duration
1 Day
Pre-Requisites
We assume you have a basic understanding of SQL SELECT statements.
Course Contents
SQL Scripts
- Good practice when writing SQL queries
- Exploring the templates and stored procedures available in SSMS
- Saving script files v saving stored procedures
- Using Parameterised queries
Advanced SQL SELECT statements
- Recap on writing JOIN statements and column and table aliases
- ISNULL() function v IS NULL criterion
- Useful string, date and convert functions
- Aggregate functions
- Group by clause including rollup
- IF and CASE
- UNION queries
Transaction Control
- Working with INSERT
- Working with UPDATE
- Working with DELETE
- Creating computed columns
- Using Begin transactions with Commit and Rollback
- Simple error trapping
Sub Queries and Window Functions
- Writing sub queries
- Over() and Over(Partition by)
- Row() v Rank()
Managing Views
- Creating a view
- Selecting data using a view
- Altering and dropping views
Stored Procedures
- Introduction to Stored Procedures
- Advantages of Stored Procedures
- Creating and Using Stored Procedures
- Dealing with an INSERT