DP-800: Developing AI-Enabled Database Solutions

You have an Azure SQL database containing these SQL graph tables:

• A NODE table named dbo.Person
• An EDGE table named dbo.Knows

Each row in dbo.Person contains these columns:

• PersonID (int)
• DisplayName (nvarchar(100))

You need to use a MATCH operator with exactly two directed Knows relationships to return the PersonID and DisplayName of people reachable from the person identified by an input parameter named @StartPersonId.

Which Transact-SQL query should you use?

You have an Azure SQL database that includes a column named Notes.

A security review finds that Notes contains sensitive data. You need to protect the data so that neither stored values nor query inputs disclose information about the actual data. The solution must prevent a user from inferring data relationships or repetitions from the encrypted output.

Which should you use?

You have a SQL database in Microsoft Fabric that includes the following functions:

• A multi-statement table-valued function (TVF) named Sales.mstvf_OrderStatus() that returns order status information.
• A scalar user-defined function (UDF) named dbo.ufn_GetTaxMultiplier (@TaxAmt money, @StateCode char(2)) that returns a numeric multiplier used in tax calculations.

Reporting queries frequently join Sales.mstvf_OrderStatus() to Sales.SalesOrderHeader and return large result sets. A performance review shows that the queries generate inconsistent execution plans.

During a code review, a developer finds that the following Transact-SQL statement produced an error:

EXEC @ret = ufn_GetTaxMultiplier @TaxAmt = 100.00, @StateCode = ‘WA’;  

For each of the following statements, select Yes if the statement is true. Otherwise, select No.

You have an Azure SQL database that contains order data.

A reporting query that aggregates monthly revenue for each customer runs frequently. You need to reduce the time required to retrieve the computed values. The solution must not change any underlying table structure.

What should you do?

You have a Microsoft SQL Server 2025 database containing a table named dbo.CustomerMessages. dbo.CustomerMessages has two columns: MessageID (int) and MessageRaw (nvarchar(max)).

MessageRaw can include a phone number in several formats, and some rows do not include a phone number.

Write one SELECT query that meets these requirements:

• Return MessageID, RawNumber, DigitsOnly, and PhoneStatus.
• RawNumber must contain the first substring that matches a phone-number pattern, or NULL when no match is found.
• DigitsOnly must remove every non-digit character from RawNumber, or return NULL.
• PhoneStatus must return Valid when MessageRaw contains a phone number; otherwise, it must return Missing.

Each value may be used once, more than once, or not at all.

SELECT
    MessageID,
    MessageRaw, '\d{3}[\.\-\s]\d{3}[\.\-\s]\d{4}') AS RawNumber,
    MessageRaw, '\d{3}[\.\-\s]\d{3}[\.\-\s]\d{4}'), '\D', '') AS DigitsOnly,
    CASE
        WHEN MessageRaw, '\d{3}[\.\-\s]\d{3}[\.\-\s]\d{4}') = 1
        THEN 'Valid'
        ELSE 'Missing'
    END AS PhoneStatus
FROM dbo.CustomerMessages;