Complete Guide to Advanced SQL Server

8h 29mAdvanced2024-08-06

Authors

Adam Wilbert

Data Visualization Expert

Course details

Looking to upskill as a programmer with Microsoft SQL Server? Join instructor Adam Wilbert in this hands-on, interactive, skills-development course, as he shows you how to master advanced SQL Server skills such as programming databases with T-SQL, SQL Server database triggers, SQL Server ledger, SQL Server machine learning services with Python, tabular data processing with DataFrame, and more. Along the way, be sure to test out your new skills in the exercise challenges at the end of each section. This course concludes with a final capstone project where you get to apply your newly acquired knowledge to a real-world SQL Server database.

Skills covered

SQL ServerData Resource ManagementDatabase AdministrationDatabase ManagementProgramming LanguagesMicrosoftSoftware DevelopmentOne-Off

Concepts

0. Introduction

01 - Dive deep into SQL Server
02 - What you should know
03 - Getting the most from the exercise files

1.1 Create Views of the Data

04 - Program databases with SQL Server
05 - Save a query as a view object
06 - Explore existing views
07 - SCHEMABINDING a view
08 - Leverage view objects with indexes

1.2 Create User-Defined Functions

09 - Deterministic vs. nondeterministic functions
10 - Scalar-valued user-defined functions
11 - IF ELSE and CASE statements
12 - Table-valued user-defined functions
13 - Challenge - Create a function
14 - Solution - Create a function

1.3 Work with Stored Procedures

15 - Write and execute a stored procedure
16 - Stored procedure input parameters
17 - Stored procedure output parameters
18 - Create a database trigger
19 - Challenge - Use function in a stored procedure
20 - Solution - Use function in a stored procedure

1.4 Create Transactions

21 - Ensure data consistency with transactions
22 - Create a transaction
23 - Transaction savepoints
24 - Automatically roll back transactions
25 - Challenge - Create a transaction
26 - Solution - Create a transaction

1.5 Implement Error Handling

27 - What is error handling
28 - Capture errors with TRY and CATCH
29 - Generate errors with THROW
30 - Manage transaction control
31 - Challenge - Add error handling to a stored procedure
32 - Solution - Add error handling to a stored procedure

1.6 Data Type Conversions and NULLs

33 - Implicit data type conversions
34 - Understand NULL values
35 - The ISNULL function
36 - Merge rows with COALESCE

2.1 Trigger Fundamentals

37 - Automate SQL Server with triggers
38 - What are SQL Server triggers
39 - Two triggers types - INSTEAD OF and AFTER
40 - Create a trigger to print a message
41 - Timestamp a table modification
42 - Firing INSTEAD OF triggers
43 - Challenge - Create a trigger
44 - Solution - Create a trigger

2.2 Work with Modified Data

45 - Read values modified by a trigger
46 - Using the inserted logical table
47 - Using the deleted logical table

2.3 Database- and Server-Level Triggers

48 - Triggers on CREATE, ALTER, or DROP
49 - Rollback database modifications
50 - Reading the EVENTDATA() XML
51 - Writing data from the EVENTDATA() function
52 - Challenge - Create a DDL trigger
53 - Solution - Create a DDL trigger

2.4 Trigger Management

54 - View all triggers in a database
55 - Enable and disable triggers
56 - Disable nested triggers
57 - Recursive triggers
58 - Reorder trigger execution

2.5 Project - Managing Inventory

59 - Creating triggers to maintain inventory
60 - Counting shipments received
61 - Subtract items sold
62 - Reorder items when low

3.1 SQL Server Ledger Core Concepts

63 - Protect your data with SQL Server Ledger
64 - What is SQL Server Ledger
65 - Ledger tables and ledger databases
66 - Capture the database state in the digest

3.2 Add Ledger Features to Existing Databases

67 - Create updatable ledger tables
68 - Create append-only ledger tables
69 - Move existing data into a ledger table

3.3 Create a New Ledger Database

70 - Use T-SQL to create a ledger database
71 - Enable ledger in Azure SQL Database

3.4 Auditing a Database Protected by Ledger

72 - Manage the ledger digest
73 - Verify ledger integrity
74 - Detect unauthorized data modifications
75 - Recover a compromised database

4.1 Get Started with Machine Learning Services

76 - Analyze SQL Server data with Python
77 - What is machine learning services
78 - Install ML services for Python
79 - Enable script execution in SQL Server
80 - Use variables in Python
81 - Create a Python while loop

4.2 Write Python Scripts for SQL Server

82 - Import a dataset from SQL Server
83 - Manipulate a DataFrame
84 - Output a result set to SQL Server
85 - Python syntax pitfalls
86 - Challenge - Import a DataFrame
87 - Solution - Import a DataFrame

4.3 Python Package Modules and Libraries

88 - The Anaconda open-source packages
89 - Functions in the revoscalepy package
90 - Model, train, and score with microsoftml
91 - Produce graphics with Matplotlib
92 - Get descriptive statistics with pandas
93 - Challenge - Sample a DataFrame
94 - Solution - Sample a DataFrame

4.4 Processing Tabular Data

95 - Return values with indexes and series
96 - Convert a series to a DataFrame
97 - Add multiple series to a DataFrame
98 - Include the index in a DataFrame
99 - Slice a DataFrame to series
100 - Challenge - Import and process data
101 - Solution - Import and process data

4.5 Create a SQL Stored Procedure

102 - Create a Python stored procedure
103 - Parameterize the procedure
104 - Challenge - Write a stored procedure
105 - Solution - Write a stored procedure

4.6 Create an External Data Science Client

106 - Install MLS on a standalone server
107 - Add development tools to the client
108 - Work with Jupyter Notebooks

5.1 Final Project

109 - GameScore database final project setup
110 - Create a trigger to add points to player records
111 - Calculate global score with custom function
112 - Write a stored procedure - Part 1
113 - Write a stored procedure - Part 2
114 - Add error handling and test the system - Part 1
115 - Add error handling and test the system - Part 2

Conclusion

116 - Next steps