DP-800: Microsoft SQL AI Developer Associate Exam Prep

Build a strong SQL foundation with AI integration for the DP-800 exam, covering design, security, optimization, and AI capabilities through hands-on labs.

Learn core sql basics in azure, including servers, databases, schemas, DDL, DML, DCL, and writing select statements. Compare OLAP and OLTP and explore AI integration and retrieval augmented generation.

Deploy an Azure SQL server and an Azure SQL database within a resource group on portal.azure.com, configure authentication and networking, and prepare for labs using SQL Server Management Studio.

install sql server management studio 2022, connect to an azure sql server and the udemy bp800 sql database, and configure firewall and authentication to enable access.

Access the public GitHub repository dp800-sql-ai-developer for the labs, featuring markdown instructions and code snippets. Follow along with the videos and copy commands to apply the learnings.

Explore Azure SQL offerings from IaaS to PaaS, including Azure SQL Database and Managed Instance, with serverless and provisioned compute, vCore, and DTU options.

Understand how the star schema model uses dimension and fact tables to enable scalable data analysis. See how primary and foreign keys link dimensions to facts to form relationships.

Apply practical labs as an ESG consultant to model carbon emissions in scope 1–3 using sql, build a structured ESG database, and practice data modeling, indexing, querying, and ai-assisted insights.

Create an esg schema and four tables (companies, emissionrecords, energyconsumption, sustainabilityreports) using ddl, establish primary and foreign keys, and populate with data via dml inserts.

Create and visualize a database diagram in SQL Server Management Studio to map primary and foreign keys across four tables—companies, emission records, energy consumption, and sustainability reports.

Gain hands-on experience with SQL select queries, filtering, grouping and aggregations on the ESG schema in SQL Server Management Studio, including update and delete operations.

Explore SQL joins, including inner, left, right, full, semi, anti, and cross joins, to combine data from tables, including fact and dimension tables in star schemas, to derive business insights.

In this hands-on lab, you build a table with 20,000 rows and compare clustered and non-clustered indexes in Azure SQL, observing index seek, execution plans, and performance.

Learn to implement temporal tables with history tracking and system-versioning in Azure SQL Database, then build graph tables with node and edge tables for the supplies to relationship.

Learn how views in Azure SQL Database solve data duplication and complex transformations by offering non-materialized and materialized views, with runtime versus cached results and clear use cases.

Learn to implement basic views in SQL, including creating non-materialized views, joining tables, computing total emissions, and classifying emission levels with case statements.

Explore stored procedures as a standardized, pre-compiled, and parameterized SQL object that centralizes complex query logic, improves performance via cached execution plans, and supports flexible result sets.

Develop and run stored procedures in this Azure SQL lab, including get companies, get emissions by company and by date, and calculate totals with input and output parameters.

Create and test scalar functions in Azure SQL Database, including getEmissionLevel and calculate carbon tax, then use them in selects and filters to derive emission insights.

Explore table-valued functions in Azure SQL, learning inline and multi-statement TVF, their use in select statements, and how they return reusable, read-only result sets.

Explore how SQL triggers in Azure SQL Database respond to data and schema changes with DDL and DML triggers, enabling real-time auditing, logging, and enforced rules.

Learn to implement SQL triggers in Azure SQL, including DML and DDL triggers, by building an audit log table and recording insert and update history on emission records.

Explore common table expressions (CTEs) to break complex queries into readable, modular blocks within a single query, and compare non-recursive and recursive CTEs with table valued functions.

Practice implementing common table expressions in SQL, including non-recursive and recursive cte patterns, to calculate total emissions, rank companies by emissions, and filter high emitters.

Explore window functions in SQL, focusing on ranking, aggregation, and range. Learn how to apply row_number, rank, dense_rank, and ntile, and create running totals with sum, over partition, order by.

Explore window and aggregation functions in Azure SQL by calculating the running total of CO2 emissions and moving averages within each company, and ranking with rank, row_number, and dense_rank.

Master window functions such as lag, lead, first value, last value, percent rank, and cumulative distribution to compare rows, detect trends, and analyze data across partitions like product category.

Perform a hands-on lab to manipulate json data in Azure SQL using built-in functions, extracting esg rating and score, and parsing nested objects with openjson.

Practice matching patterns with regular expressions in Azure SQL Database using T-SQL to identify and repair corrupted email, phone, and tag data, and extract domains.

Apply fuzzy string matching in a hands-on T-SQL lab to find and standardize similar sounding company names using Levenshtein distance, similarity ratio, and Jaro-Winkler on ESG.companies.

Explore correlated subqueries in advanced T-SQL through hands-on labs, comparing rows by company emissions against averages, with inner joins, exists, top-n results, and 150 percent thresholds.

Learn to implement custom error handling in T-SQL on Azure SQL, using throw and raise error with formatted messages and a stored procedure to guard against negative emissions.

Explore configuring the MS SQL extension in VS Code to connect to an Azure SQL Server, and write queries using GitHub Copilot in notebooks.

Configure GitHub Copilot for SQL by creating instruction files in a VS Code repository, including co-pilot-instruction-files.md and prompts for index creation, to steer T-SQL queries with guardrails in a workflow.

Explore the three encryption layers in Azure SQL—transparent data encryption at rest, column-level encryption, and always encrypted, detailing the column encryption key, column master key, and Azure Key Vault.

Explore dynamic data masking and row-level security to protect PII like phone numbers and salaries, and compare table-level versus view-level masking for tailored access.

Implement dynamic data masking on a sensitive Azure SQL Database table, apply column masking for email, phone, credit rating, and revenue, and test unmasking for support and finance roles.

Learn to implement row-level security in Azure SQL Database using a predicate function and security policy to restrict data by company ID.

Explore how Data API Builder exposes Azure SQL database objects via REST, GraphQL, or MCP routes with a single configuration file, enabling secure, scalable APIs without heavy coding.

Set up the Data API Builder, initialize a project, and expose esg tables via REST and GraphQL APIs, including esg.company and esg.energyconsumption entities.

Learn to expose database entities via REST and GraphQL with the data API builder, and define many-to-one relationships between emission records and companies, then run and query a local server.

Learn the core generative ai jargons, including tokens, system and user prompts, and chat completions api, and compare unimodal and multimodal models for practical ai applications.

Explore the Microsoft Foundry ecosystem for building artificial intelligence agents and retrieval augmented generation chatbots with a model catalog, enterprise knowledge, and secure Azure sql integration.

Deploy a Microsoft Foundry resource in portal.azure.com, create a Foundry project in a single resource group in Sweden Central, then explore the model catalog, deployments, agents, and guardrails.

Explore GenAI deployment types in Microsoft Foundry—global standard, global provisioned, global batch, data zone and standard regional variants—and how data processing location and billing models influence use cases.

Register Foundry deployed models as external models in Azure SQL Database, enabling authenticated T-SQL calls via managed identity or API key, and retrieve vector embeddings.

Create an API key and managed identity credentials for LLM API calls, configure database scope credentials with a master key, grant endpoint access, and assign Cognitive Services OpenAI user role.

Learn to call a large language model API from SQL using a data-based scoped credential, build system and user prompts, and extract the assistant response from the JSON.

Learn to encapsulate LLM API calls in a standardized SQL stored procedure, including prompt payloads, system and user prompts, credentials, error handling, and returning responses for display.

Explore vector embeddings and how they represent words and sentences as 1536-dimensional numerical vectors to capture meaning. Apply them in Azure SQL with foundry for enterprise chatbots.

Deploy the vector embedding engine TextEmbeddingAda002 from Microsoft Foundry to power course labs, configure global standard deployment with 250,000 tokens per minute, and access its API key and API endpoint.

This hands-on lab demonstrates registering an external model in azure sql for vector embeddings and using ai generate embeddings to create 1536-dimensional embeddings for review text and sustainability reports.

Explore embedding maintenance methods for azure sql database: table triggers, change tracking, change data capture, and azure function apps with sql trigger bindings to refresh vector embeddings.