SQL Intermediate to Advanced

Intermediate
Online Classes
Total 12 hours in class

Multi-Day Course
2-8 learners per class

Requirements

You should have a prior understanding of SQL to attend this course. Our Introduction to SQL training course covers all of the prerequisites for this Intermediate to Advanced SQL course.

Class Details

This course is designed to further your understanding of structured query language (SQL) and improve your skills and expand your knowledge. Our Intermediate to Advanced SQL course builds upon the skills learnt in our Introduction to SQL course. It covers windowing, CTEs, dynamic SQL, logical functions, control flow, creating user-defined functions, stored procedures and cursors. You'll also learn how to improve query performance, transaction processing and error handling.

Course Outline

Setting Up a SQL Server Environment
- Setting up Databases
- IF an object EXISTS
Built-In Functions
- Introduction to Built-In Functions
- Using System Functions
- Conversion Functions - CAST and CONVERT
- Using Mathematical functions
- Using String Functions
Date and Time Functions
- GETDATE
- DATEADD
- DATEDIFF
- DATENAME
- DATEPART
- DAY, MONTH and YEAR
- Using CONVERT to format a date/time
Subqueries
- Introduction of Subqueries
- Subqueries vs Joining Tables
- Subqueries with Comparison Operators
- Subqueries with IN and NOT IN Operators
- Subqueries with EXISTS and NOT EXISTS
Multi-Table Queries
- Joining Tables
- INNER JOIN
- INNER JOIN with more than two Tables
- OUTER JOIN
- CROSS JOIN
- SELF JOIN
Aggregate Functions and Group by Extensions
- GROUP BY CUBE and ROLLUP
- Using CASE with aggregate functions
- Using PIVOT operator
Windowing
- Ranking Functions
- ROW_NUMBER
- RANK and DENSE_RANK
Common Table Expressions (CTE)
- Introduction to CTE
- Non-Recursive CTE
- Recursive CTE
- When to use CTEs, and When not to
Dynamic SQL
- Introduction to Dynamic SQL
- Using sp_executesql
- Using Output parameters with sp_executesql
- Temp tables in Dynamic SQL
Temporary Tables and Table Variables
- Using temporary tables
- Creating table variables
Logical Functions
- Using IIF Function
- Using CHOOSE
- Using GREATEST and LEAST
- Using CASE
Local & Global variables
- Introduction to variables
- Variable data types
- Local variables
- Using Global variables
Control of Flow
- IF...ELSE
- BEGIN...END
- Using WHILE loops
User-Defined Functions - UDFs
- Introduction to UDFs
- Creating a Scalar-Valued Function
- Creating an Inline Table-Valued Function
Stored Procedures
- Introduction to Stored Procedures
- Creating User Stored Procedures
- Stored Procedure with Parameters
Transaction Processing
- Introduction to Transaction Processing
- Working with BEGIN TRANSACTION
- Working with COMMIT & ROLLBACK
- Using the TRANCOUNT Global Variable
Query Performance
- Improving query performance
- Clustered Indexes
- Non-Clustered Index

Tutors

Younus Kazi

A professional Online and Classroom Teacher

Younus has over 18 years experiences in working and teaching IT in both public and private organisations. His specialities include Web design, deve...
View More...

Jayan Karmacharya

An experienced programmer and online Educator

Jayan is an Experienced Web Developer and IT tutor with over 10 years of experience in Server side programming, front-end development, database des...
View More...