Advanced SQL

• Educational Background: There are no specific educational requirements for this course. 

• Language Proficiency: Learners should have a good command of English, as all course materials, assessments, and discussions are conducted in English.  

• Interest in SQL and Databases: This course is ideal for individuals with a keen interest in learning and building SQL Databases. 

• Master Complex SQL Queries: Learners will be able to write and optimise complex SQL queries, enhancing their data retrieval capabilities. 

• Optimise Database Performance: Learners will gain skills to improve query performance and overall database efficiency through advanced indexing and tuning techniques. 

• Handle Large Datasets Effectively: Learners will learn methods to manage, manipulate, and query large datasets efficiently, ensuring faster and more accurate results. 

• Ensure Database Security: Learners will understand advanced security techniques, including transaction management and access control, to protect sensitive data within databases. 

This course is ideal for professionals seeking to advance their SQL skills, optimise database performance, and handle complex data management tasks efficiently.

• Database Administrators
• Data Analysts
• Data Engineers
• IT Professionals
• Business Intelligence Developers
• Software Developers
• Systems Architects 

Module 1: Creating Tables

• Creating Tables in SQL
• Inserting Data
• Inserting Multiple Rows
• View Table

Module 2: Stored Procedure Basics

• Pros and Cons of Stored Procedures
• Creating Stored Procedures
• Two Ways to Execute
• System Stored Procedures

Module 3: Variables

• Declaring Variables
• SET Versus SELECT
• Global Variables
• Tricks with Variables

Module 4: Parameters and Return Values

• Passing Parameters
• Default Values and WHERE Clauses
• Output Parameters
• Using RETURN

Module 5: Scalar Functions

• Introduction to Scalar Functions
• Various Scalar Functions
• Advantages of Scalar Functions

Module 6: Testing Conditions

• IF/ELSE Conditions
• Using CASE Where Possible

Module 7: Looping

• While Loop
• Breaking Out of a Loop
• Basic Transactions
• Using DELETE and UPDATE
• Sys.Objects

Module 8: Temporary Tables and Table Variables

• Using Temporary Tables
• Creating Table Variables
• Pros and Cons of Each Approach

Module 9: Table Valued Functions

• In-line Table-Valued Functions
• Multi-Statement Table-Valued Functions
• Limitations of User-Defined Functions

Module 10: Derived Tables and CTEs

• Using Derived Tables
• Common Table Expressions (CTEs)
• Recursive CTEs

Module 11: Subqueries

• Subquery
• Using ALL, ANY and IN
• Correlated Subqueries
• Using EXISTS

Module 12: Cursors

• What is Cursor?
• Life Cycle of Cursor
• Types of Cursor
• Syntax of Fetching Rows

Module 13: Error-Handling

• Using TRY/CATCH
• System Error Functions
• Custom Error Messages
• Obsolete @Error Function
• SQL Server Debugger