Free Microsoft DP-900 practice questions

10 free Microsoft DP-900 practice questions with the correct answer and a full explanation for each, taken from the CertStash pack of 323 questions. Work through them, then open each answer to check your reasoning.

Get all 323 questions (US$39) · Download these 10 as a PDF

Question 1

Which statement is an example of Data Manipulation Language (DML)?

  1. REVOKE
  2. DISABLE
  3. INSERT
  4. GRANT
Show answer and explanation

Correct answer: C. INSERT

INSERT is a Data Manipulation Language (DML) statement used to add new rows of data to a table. DML statements modify data within the database. REVOKE and GRANT are Data Control Language (DCL) statements for managing permissions, and DISABLE is not a standard SQL statement.

Why the other options are wrong

  • A. REVOKE is a Data Control Language (DCL) statement for revoking permissions, not DML.
  • B. DISABLE is not a standard SQL DML statement.
  • D. GRANT is a Data Control Language (DCL) statement for granting permissions, not DML.

Question 2

You have a SQL query that combines customer data and order data. The query includes calculated columns.

You need to create a database object that would allow other users to rerun the same SQL query.

What should you create?

  1. an index
  2. a view
  3. a scalar function
  4. a table
Show answer and explanation

Correct answer: B. a view

A view is a database object that stores a predefined SQL query, allowing other users to rerun the same query without needing to write it themselves. Views can include calculated columns and combine data from multiple tables. An index improves query performance but does not store queries, a scalar function returns a single value but is not designed for sharing complex queries, and a table stores data but not query logic.

Why the other options are wrong

  • A. An index optimizes query performance but does not store the query logic itself.
  • C. A scalar function returns a single value and is not the appropriate object for storing multi-row query results.
  • D. A table stores data but does not encapsulate the query logic needed to combine and calculate data.

Question 3

Your company recently reported sales from the third quarter.

You have the chart shown in the following exhibit.

Which type of analysis is shown in the fourth quarter?

Exhibit for question 3

  1. predictive
  2. prescriptive
  3. descriptive
  4. diagnostic
Show answer and explanation

Correct answer: A. predictive

The fourth quarter in the chart shows a shaded gray area extending beyond the solid blue line of actual Q3 sales, representing a range of possible future outcomes. This is characteristic of predictive analysis, which uses historical data and trends to forecast future values. The chart displays Q1-Q3 as actual reported sales (descriptive data) and Q4 as a projected range with uncertainty bounds, which is the hallmark of predictive modeling used to estimate future performance.

Why the other options are wrong

  • B. Prescriptive analysis would recommend specific actions or decisions to take, not simply project future sales ranges.
  • C. Descriptive analysis describes what has already happened; Q4 represents forecasted future data, not historical description.
  • D. Diagnostic analysis explains why something happened in the past; Q4 shows future projections rather than analysis of past causes.

Question 4

Which statement is an example of Data Manipulation Language (DML)?

  1. REVOKE
  2. DISABLE
  3. CREATE
  4. UPDATE
Show answer and explanation

Correct answer: D. UPDATE

UPDATE is a Data Manipulation Language (DML) statement used to modify existing data in a table. DML statements are used to insert, update, delete, and retrieve data. REVOKE is a Data Control Language (DCL) statement, CREATE is a Data Definition Language (DDL) statement, and DISABLE is not a standard SQL statement.

Why the other options are wrong

  • A. REVOKE is a Data Control Language (DCL) statement for managing permissions.
  • B. DISABLE is not a standard SQL DML statement.
  • C. CREATE is a Data Definition Language (DDL) statement for creating database objects.

Question 5

You have the following data.

Which type of data is this?

Exhibit for question 5

  1. unstructured
  2. semi-structured
  3. structured
Show answer and explanation

Correct answer: B. semi-structured

This data is JSON (JavaScript Object Notation), which is a semi-structured format. Sem-tructured data has some organizational properties and schema elements (keys defining the structure) but lacks the rigid, fully-defined schema of structured data like relational databases. The data exhibits characteristics of semi-structured formats: it uses consistent key-value pairs and nested objects that provide partial organization, but the structure is self-describing through the format itself rather than enforced by a strict database schema. This makes it more organized than unstructured data (like plain text or images) but less rigid than fully structured data.

Why the other options are wrong

  • A. Unstructured data lacks organizational properties and schema entirely, such as plain text documents or images; JSON has clear structural organization through key-value pairs.
  • C. Structured data requires a predefined, rigid schema enforced by a database management system; JSON's flexibility and self-describing nature make it sem-tructured rather than fully structured.

Question 6

You need to create an Azure resource to store data in Azure Table storage.

Which command should you run?

  1. az storage share create
  2. az storage account create
  3. az cosmosdb create
  4. az storage container create ✅Correct Answer: B, az storage account create To create an Azure resource for storing data in Azure Table storage, you must first create a storage account using 'az storage account create'. Table storage is a service within a storage account. 'az storage container create' creates a blob container, 'az cosmosdb create' creates an Azure Cosmos DB account, and 'az storage share create' creates a file share, so none of these commands provision Table storage. The storage account must exist first.
Show answer and explanation

Answer and explanation for question 6

Question 7

You need to modify a view in a relational database by adding a new column.

Which statement should you use?

  1. MERGE
  2. ALTER
  3. INSERT
  4. UPDATE
Show answer and explanation

Correct answer: B. ALTER

The ALTER statement is used to modify existing database objects, including views. To add a new column to a view definition, you use ALTER VIEW. MERGE is used to perform insert, update, or delete operations based on source and target conditions, INSERT adds new rows, and UPDATE modifies existing row data, none of these modify the structure of a view itself.

Why the other options are wrong

  • A. MERGE is used for conditional insert, update, or delete operations, not for modifying view structure.
  • C. INSERT adds new data rows but does not modify the view definition.
  • D. UPDATE modifies data values but does not alter the view structure or definition.

Question 8

You need to implement an Azure platform as a service (PaaS) service that will host a relational database. The solution must support built-in autoscaling.

Which service should you implement?

  1. Azure SQL Database
  2. SQL Server on Azure Virtual Machines
  3. Azure SQL Managed Instance
  4. Azure SQL Edge ✅Correct Answer: A, Azure SQL Database Azure SQL Database is a Platform as a Service (PaaS) offering that includes built-in autoscaling, notably the serverless compute tier, which automatically adjusts compute based on demand, plus independent storage scaling. SQL Server on Azure Virtual Machines is an Infrastructure as a Service (IaaS) solution requiring manual scaling, Azure SQL Managed Instance is PaaS but has no serverless autoscaling tier, and Azure SQL Edge targets edge and IoT scenarios without such autoscaling.
Show answer and explanation

Answer and explanation for question 8

Question 9

Which Azure storage solution provides native support for POSIX-compliant access control lists (ACLs)?

  1. Azure Table storage
  2. Azure Data Lake Storage
  3. Azure Queue storage
  4. Azure Files
Show answer and explanation

Correct answer: B. Azure Data Lake Storage

Azure Data Lake Storage provides native support for POSIX-compliant access control lists (ACLs) at both the file and directory level, enabling fine-grained permission management. This is a key feature that distinguishes Data Lake Storage from other Azure storage solutions and makes it suitable for big data analytics workloads requiring advanced security controls.

Why the other options are wrong

  • A. Azure Table storage is a NoSQL key-value store and does not support POSI-ompliant ACLs.
  • C. Azure Queue storage is designed for message queuing and does not support fil-evel ACLs.
  • D. Azure Files supports SMB and NFS protocols but does not provide POSIX-compliant ACLs at the same level as Data Lake Storage.

Question 10

Which type of database is Azure Database for PostgreSQL?

  1. Platform as a service (PaaS)
  2. Infrastructure as a service (IaaS)
  3. Microsoft SQL Server
  4. on-premises
Show answer and explanation

Correct answer: A. Platform as a service (PaaS)

Azure Database for PostgreSQL is a Platform as a Service (PaaS) offering, meaning Microsoft manages the infrastructure, operating system, and database software, while customers focus only on data and application configuration. This eliminates the need for manual patching, backups, and maintenance tasks.

Why the other options are wrong

  • B. Infrastructure as a Service involves managing VMs and infrastructure yourself, which is not the case with Azure Database for PostgreSQL.
  • C. Azure Database for PostgreSQL is a PostgreSQL service, not Microsoft SQL Server.
  • D. Azure Database for PostgreSQL is a cloud-hosted service, not an on-premises solution.

That was 10 of 323.

The full Microsoft DP-900 pack has all 323 questions, each with the answer, the explanation and why the other options are wrong, plus a questions-only copy for timed runs. US$39, paid once, with free monthly updates and a pass-or-your-money-back guarantee.

Get the full pack