Advanced Transact-SQL (T-SQL) : Stored Procedures |Functions

Transact-SQL (T-SQL) is Microsoft's powerful implementation of the widely used Structured Query Language (SQL). This course is designed for those with existing SQL or T-SQL knowledge and aims to elevate your skills with advanced T-SQL techniques. We'll be utilizing the latest free version of Microsoft SQL Server 2017 Express along with a practical AdventureWorks 2014 database for hands-on experience. You'll be installing and working with SQL Server Management Studio, included with SQL Server Express 2017.

This course centers on crafting sophisticated queries using T-SQL, applicable across SQL Server 2012, 2014, 2016, and 2017. You'll delve into the details and capabilities of various function types, including:

• Aggregate Functions: Master summarizing data with functions like SUM, AVG, COUNT, etc.
• Analytic Functions: Unlock advanced data analysis capabilities with window functions.
• Ranking Functions: Determine the relative ranking of data within partitions.

Beyond queries, you'll gain expertise in:

• Stored Procedures: Learn how to create, modify, and delete stored procedures to streamline database operations.
• SET Operators: Effectively manipulate data using UNION, INTERSECT, and EXCEPT.
• String Manipulation: Utilize functions like Substring, Convert, and Cast for data transformation.
• Rounding: Employ the Round function for precise numerical calculations.
• Conditional Logic: Use CASE expressions for complex data manipulation.

What You Will Learn:

• Installing Microsoft SQL Server Express 2017: A step-by-step guide to setting up your development environment.
• Downloading and Attaching a Sample Database: Get started quickly with the AdventureWorks 2014 database.
• Data Conversion: Using the Convert function for data type transformations.
• String Manipulation: Leveraging Substring, Cast, and Round functions.
• Conditional Logic: Implementing CASE expressions for flexible data handling.
• Aggregate Functions: Calculating summaries and statistics.
• Analytic Functions: Performing calculations across sets of rows.
• SET Operators: Combining results from multiple queries.
• Stored Procedure Creation & Management: Building reusable database routines.
• Ranking Functions: Determining data order and position.

This course provides a comprehensive understanding of T-SQL, empowering you to write efficient and powerful queries for data analysis and management in SQL Server.