DP-800 exam prep: Microsoft SQL Server AI Developer course

Start a free Microsoft Azure trial with 12 months of services and a $200 credit, plus always free SQL databases and storage. Learn to pause resources to control costs.

Create an Azure SQL database using the free offer in portal.azure.com, configure a new resource group and server, and connect with Microsoft Entra or SQL authentication.

Learn to install and use SQL Server Management Studio to connect to Azure SQL Database, configure authentication, browse databases and objects, and run queries in the DP 800 database.

Learn the select and from clauses in SQL, practice selecting all columns or specific ones from the saleslt.product table, and explore basic T-SQL syntax and comments.

Rename columns with as to clarify names like product name, use brackets for spaces in aliases, and remember distinct removes duplicates while from identifies the source table and select columns.

Explore the where clause and the like operator to search strings by prefixes, suffixes, or patterns using wildcards %, _, and not like, while understanding case sensitivity and collate options.

Use the where clause to combine multiple conditions with and, or, in, and between, avoid duplicates, and apply brackets and not between for precise data filtering.

Explore aggregations and the group by clause to summarize data, compare count, sum, min, and max, and learn how non-aggregated columns must appear in group by.

Learn how the having clause filters aggregations after group by in SQL, using count(*) and avoiding alias usage in where, and understanding the execution order in T-SQL.

Learn how the order by clause sorts query results in SQL Server, using ascending or descending order and tie-breakers, including top 10. Include multi-column sorts and aliases for deterministic results.

Learn to build a six-clause select statement using select, from, where, group by, having, and order by; count rows per colour, filter nulls, and sort results by count.

Explore number data types for variables and tables in TSQL, including bit, tinyint, smallint, int, and bigint, their ranges, signedness, and basic operations like division and modulo.

Explore string data types in SQL Server, including char, varchar, nchar, and nvarchar, with exact versus variable lengths, and how encoding affects storage and bytes per character.

Explore key string functions in SQL Server, including concatenation, case changes, substring, trim, and length. Learn casting, converting, and formatting between strings and numbers across currencies and cultures.

Explore SQL server date and time data types, including date, time, small date time, date time two, date time offset, and date add with sys date time and sys UTC.

Explore SQL Server's other data types, such as binary and varbinary, JSON and XML, geography and geometry, plus row version and vector for AI applications.

Create a school schema and a scores table, define data types and constraints, and practice basic insert, update, delete operations.

Master inserting, updating, and deleting data in a SQL table using insert, update, and delete statements. Explore column definitions, where clauses, and the distinction between DML and DDL in practice.

Learn how the unique index constraint prevents duplicates and how to create, drop, and apply it as a standalone or table-level constraint. Understand clustered versus non-clustered indexes and null handling.

Learn to implement and manage check constraints in SQL Server, including adding and dropping constraints, enforcing score ranges (0–100), inline definitions, and multi-column checks.

Learn how the primary key uniquely defines each row, differs from the unique constraint, can be defined inline or as a table constraint, and uses clustered indexes with surrogate keys.

Design and implement database-wide sequences for auto numbering across tables. Use default constraints and next value for the sequence, with configurable data type, start, increment, min, max, cycle, and cache.

Learn to query multiple tables with a join, alias the tables, and define on relationships between school.scores and school.pupils to retrieve related pupil names.

Master inner joins as the default, then left, right, and full outer joins, and understand cross joins that multiply rows for unrelated data, used only with a specific purpose.

Learn how to consolidate two pupil id columns using coalesce and is null, and identify missing data by tweaking left and right joins and where clauses.

Create and enforce a foreign key constraint to restrict scores to existing pupils, using alter table add constraint and references pupils(pupil_id), handling existing data with no check or with check.

Explore how to expand a foreign key constraint by applying on update cascade, on delete cascade, set null, set default, and no action, with updates across scores and pupils tables.

Create, alter, and drop views to encapsulate a select statement, then query them in from clauses. Explore view restrictions, such as order by, and inspect definitions with sys views.

Configure view outputs with optional column names and aliases, include computed columns, and apply options like encryption, schema binding, view metadata, and check option.

Explore the challenges of inserting data into complex views in SQL Server, including how group by, having, distinct, top, and with check option affect updatability and inserts.

Explore derived tables and subqueries in SQL Server, using from clause and aliases, comparing left and inner joins, and calculating averages with nested queries.

Learn to use window functions (row_number, rank, dense_rank, and ntile) via the over clause with partition by and order by to order, restart, and bucket results.

Explore lag and lead analytics to access previous and next rows, customize with order by and partition by, and handle nulls with ignore or respect options.

Explore four window functions cumdist, percent rank, percentile_cont, and percentile_disc using a scores table to compute cumulative distributions, medians, and interpolated versus discrete percentiles with partitions.