Learn Advanced T-SQL For SQL Server Administrator

Master stored procedures to drive server-side processing in SQL Server databases, covering parameters, variables, control flow, error handling, and reading and applying SQL blocks.

Download VirtualBox, Windows Server, and SQL Server 2014 to set up a self-contained lab for advanced T-SQL training; learn download steps, trial options, and prerequisites before installation.

Install VirtualBox, create a 64-bit Windows server virtual machine with desktop experience, allocate memory and disk space, and boot from an iso to install the operating system.

Set up a shared folder between the host and the virtual machine to centralize SQL 2014 scripts and course materials, then access and run them directly in the VM.

Install SQL Server 2014 on a virtual machine by extracting the ISO with WinISO, mounting it via a shared folder, and completing a guided setup with default features.

Install and configure Adventureworks 2014 for SQL Server by downloading, extracting, moving to the C drive, and restoring the backup in a virtual machine.

Learn what a stored procedure is and how to create it with create procedure or create proc. Identify system procedures with sp_ prefix and adopt a usp_ naming convention.

Discover how to modify and delete stored procedures in SQL Server using alter and drop, with both GUI and script approaches, and understand basic management of sprocs.

Create and use a single parameter in a stored procedure to filter data and return a limited result set, with examples using category names and inner joins.

Learn how to use multiple parameters in a stored procedure to filter by category and subcategory, manage parameter order, and name parameters to ensure the where clause works.

Learn to group multiple sql statements in a single stored procedure using begin and end blocks, with indentation and comments, and understand control of flow keywords while executing and reading.

Learn how variables empower stored procedures by declaring and setting int and varchar data types, and using them with like and where clauses in control flow.

Learn how to use a stored procedure to update multiple related tables in a production sql environment, propagating allergy data across admissions, surgery, medication, and history while ensuring data integrity.

Demonstrates creating and using stored procedures with insert, print, if else, and set nocount on in a production SQL context. Includes testing with a students table.

Explore the if and else logic in T-SQL, driven by boolean conditions that execute code blocks. See examples of variable declarations, comparisons, and update or insert decisions.

Explore nested if-else logic in t-sql: declare variables, begin-end blocks, and print statements, with practical examples reading stored procedures in Adventureworks databases.

Explore how the SQL while loop repeats a block while a condition is true, and learn how break and continue control flow to prevent endless loops and read stored procedures.

Explore how cursors process result sets row by row in T-SQL, compare production performance with set-based queries, and learn how to declare, fetch, and deallocate cursors.

Begin transaction, commit, and rollback to safely control updates and deletes, and see how updating without a where clause can lead to data corruption.

Explore error handling with the SQL Server try catch block, including begin try, begin catch, error functions, and transaction integration to capture and respond to errors.

Explore table variables, containers for temporary data used in stored procedures, created with declare and @ prefix, and compare them to temp tables to decide when to use each.

Explore derived tables that pull data from existing tables for fast on-the-fly results. Name the derived table and place a select inside parentheses to filter data.

Discover how to create a view from a multi-table select to make a reusable, virtual table. Use aggregates and case statements with views to simplify analysis of sales totals.

Discover an Excel formula to create a quoted in-clause list for SQL queries, enabling efficient filtering of sales order numbers in production environments.

Explore sql common table expressions (cte) as memory-resident, temporary data containers within a query, enabling select, insert, delete, and update operations without creating physical tables.

Master creating and joining multiple CTEs in T-SQL by separating them with commas, aliasing each CTE, and querying joined results filtered by department, including example with group department CTE.

Apply aggregates to a CTE to compute total hours by gender using sum and group by. Learn the three CTE steps, separate multiple CTEs with semicolons, and verify results.

Learn to use a common table expression to insert, update, and delete data in SQL Server, with a backup, and a parameterized stored procedure.

Create and alter a stored procedure using a common table expression (cte), add a last name parameter, and apply a where clause to filter results.

Explore how a recursive cte references itself to unravel hierarchical data, determining each employee's level and managers in a company example, using anchor and recursive members with union all.

Learn to perform vlookup in excel to populate product descriptions from a lookup table, using product IDs, defining a named range, and choosing exact match for precise results.

Develop the ability to read and decipher stored procedures, identify begin and end blocks, CTEs and recursive CTEs, and understand error handling and transactions.

Apply permissions to stored procedures to micro-manage access to specific table columns, granting execute rights to users and revoking them to control data visibility.

Learn Power BI fundamentals by downloading Power BI Desktop, designing reports and dashboards, and setting permissions for sharing. Publish dashboards via Power BI services for enterprise-wide access and insights.