Microsoft DP-900 Practice Questions with Explanations

Correct answer: C

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.

Correct answer: B

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.

Correct answer: A

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.

Correct answer: D

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.

Correct answer: B

This data is JSON (JavaScript Object Notation), which is a semi-structured format. Semi- structured 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 semi- structured rather than fully structured.

Correct answer: B

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. While 'az storage container create' creates a blob container (for blob storage) and 'az cosmosdb create' creates a Cosmos database, and 'az storage share create' creates a file share, none of these commands directly provision Table storage. The storage account must exist first.

Why the other options are wrong:

• A. This command creates a file share, not Table storage.
• C. This command creates a Cosmos DB resource, which is a different service from Table storage.
• D. This command creates a blob container, not Table storage.

Correct answer: B

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.

Correct answer: A

Azure SQL Database is a Platform as a Service (PaaS) offering that includes built-in autoscaling capabilities, allowing the database to automatically adjust compute and storage resources based on demand. SQL Server on Azure Virtual Machines is an Infrastructure as a Service (IaaS) solution requiring manual scaling, Azure SQL Managed Instance is a hybrid offering with limited autoscaling, and Azure SQL Edge is designed for edge computing scenarios without comprehensive autoscaling support.

Why the other options are wrong:

• B. SQL Server on Virtual Machines is IaaS, not PaaS, and requires manual management of scaling.
• C. Azure SQL Managed Instance does not provide the same level of built-in autoscaling as Azure SQL Database.
• D. Azure SQL Edge is designed for edge and embedded scenarios, not for PaaS relational database workloads with autoscaling.

Correct answer: B

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 POSIX- compliant ACLs.
• C. Azure Queue storage is designed for message queuing and does not support file- level ACLs.
• D. Azure Files supports SMB and NFS protocols but does not provide POSIX-compliant ACLs at the same level as Data Lake Storage.

Correct answer: A

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.

Correct answer: A

Azure Data Lake Storage supports access control lists (ACLs) at both the file and folder level, providing granular permissions management. This hierarchical ACL support is essential for data governance and security in analytics scenarios.

Why the other options are wrong:

• B. Azure Queue storage is a message queuing service and does not support file or folder-level ACLs.
• C. Azure Blob storage supports role-based access control (RBAC) but not traditional file and folder-level ACLs like Data Lake Storage.
• D. Azure Cosmos DB is a NoSQL database and does not support file and folder-level ACLs.

Correct answer: C

Batch processing inherently involves a time delay between when data is ingested and when results are obtained, as data is collected and processed together in groups rather than immediately upon arrival. This is a defining characteristic that distinguishes batch processing from stream processing or real-time processing.

Why the other options are wrong:

• A. Batch processing specifically does not require immediate processing upon data receipt; data is accumulated before processing begins.
• B. There is no 1 GB size limit requirement for batches; batch sizes are determined by business logic and system requirements.
• D. Batch processing can handle both structured and unstructured data, so this is not a limitation.

Correct answer: A

Atomicity is the ACID property that guarantees each transaction is treated as a single indivisible unit-it either completes entirely and succeeds, or fails completely with no partial results. This all-or-nothing approach prevents data inconsistencies from incomplete transactions.

Why the other options are wrong:

• B. Isolation ensures transactions do not interfere with each other, not that they succeed or fail completely.
• C. Durability ensures committed data persists after a transaction completes, not that transactions are all-or-nothing.
• D. Consistency ensures the database moves from one valid state to another, not that transactions are treated as single units.

Correct answer: C

This diagram represents a graph data store because it displays entities (Employees and Departments) connected by relationships (Works in, Reports to). The structure shows nodes and directed edges that establish connections between different entities, which is the fundamental characteristic of a graph database. The relationships between Sue, Frank, Jon, Bob and their respective departments, along with the reporting hierarchy, form a connected network of data that is best represented as a graph structure.

Why the other options are wrong:

• A. A key/value store simply maps keys to values without representing complex relationships like reporting hierarchies or interconnected entities.
• B. An object data store would treat each entity as an isolated object with properties but would not naturally represent the relationship connections shown in this diagram.
• D. A time series data store is designed for storing timestamped data points over time, not for representing organizational hierarchies and employee relationships.

Correct answer: A

A graph database is specifically designed to store and query data organized as nodes and edges, representing entities and their relationships. Graph databases excel at traversing complex relationship networks and are used for social networks, recommendation systems, and knowledge graphs.

Why the other options are wrong:

• B. Key/value databases store simple key-value pairs without explicit node and edge structures.
• C. Columnar databases organize data by columns for analytical queries, not by nodes and edges.
• D. Time series databases are optimized for time-stamped data points, not graph structures with nodes and edges.

Correct answer: C

The UPDATE statement is used to modify existing records in a table. To change the inventory quantity of Product1 from 100 to 270, you use UPDATE with a SET clause to specify the new value and a WHERE clause to identify which row to modify. The correct syntax would be: UPDATE table_name SET Quantity = 270 WHERE ProductName = 'Product1'.

Why the other options are wrong:

• A. INSERT is used to add new records to a table, not to modify existing values.
• B. MERGE is used to perform conditional insert, update, or delete operations based on matching conditions, which is more complex than needed for a simple value change.
• D. CREATE is used to create new database objects like tables or schemas, not to modify existing data.

Correct answer: B

Azure SQL Database is a fully managed PaaS relational database service that minimizes ongoing maintenance by handling patching, backups, high availability, and scaling automatically. This is the ideal choice for companies seeking a relational database without operational overhead.

Why the other options are wrong:

• A. Azure HDInsight is for big data processing and analytics, not a traditional relational database management system.
• C. Azure Cosmos DB is a NoSQL database, not a relational database solution.
• D. SQL Server on Azure Virtual Machines requires significant manual maintenance including patching, backups, and configuration management.

Correct answer: B

Azure Data Studio is a lightweight, cross-platform tool that supports SQL notebooks, allowing users to embed markdown documentation, SQL queries, and results together in a single interactive notebook. This makes it ideal for creating troubleshooting guides and query documentation.

Why the other options are wrong:

• A. SQL Server Management Studio (SSMS) does not support notebook functionality for embedding documents with queries.
• C. Azure CLI is a command-line interface and does not support interactive notebook creation with embedded documentation.
• D. Azure PowerShell is for infrastructure automation and does not provide SQL notebook capabilities.

Correct answer: D

An e-commerce application that reads and writes data in real-time performs Online Transaction Processing (OLTP). OLTP systems handle individual transactions immediately, which is characteristic of e-commerce platforms that process orders, payments, and inventory updates as they occur. Azure SQL Database is optimized for OLTP workloads with its support for concurrent transactions and ACID properties.

Why the other options are wrong:

• A. Stream processing handles continuous data flows in real-time, not discrete database transactions typical of e-commerce.
• B. Batch processing groups multiple transactions together for processing at scheduled intervals, not the immediate transaction handling needed for e-commerce.
• C. OLAP is used for analytical queries on historical data, not the transactional operations of an e-commerce application.

Correct answer: A

Azure Resource Manager templates enable Infrastructure as Code by automating the creation of interdependent groups of Azure resources in a repeatable and idempotent way. Templates define resource configurations declaratively, allowing consistent deployments across environments and making resource management scalable and maintainable.

Why the other options are wrong:

• B. Azure Policy, not ARM templates, is the service used to apply policies for compliance and governance across subscriptions.
• C. Subscriptions are provisioned through the Azure portal or partner channels, not through Resource Manager templates.
• D. Role-based access control and service/feature restrictions are managed through RBAC and Azure Policy, not through ARM templates.

Correct answer: D

Azure SQL Database uses firewall rules to control which IP addresses can connect to the database. When your public IP address changes, your old IP is no longer in the allowed IP list, and your new IP hasn't been added to the firewall rules. This causes connection failures even though you can access other Azure resources that may have different or no IP-based restrictions.

Why the other options are wrong:

• A. RBAC controls access to Azure resources and management operations, not network connectivity to SQL databases.
• B. DHCP assigns IP addresses but does not block database connections; the firewall rule is what prevents the connection.
• C. DNS translates domain names to IP addresses; if DNS were the issue, you wouldn't be able to resolve the server name at all.

Correct answer: A

sqlcmd is a command-line tool specifically designed to execute SQL queries against SQL Server and Azure SQL databases. It supports interactive and batch query execution, making it the direct tool for database queries from the command line.

Why the other options are wrong:

• B. bcp is the bulk copy utility used for importing and exporting large amounts of data, not for interactive querying.
• C. azdata is a command-line tool for managing Azure Data Studio and SQL Server Big Data Clusters, not for querying standard Azure SQL databases.
• D. Azure CLI is a general-purpose tool for managing Azure resources and services, not specifically for executing SQL queries.

Correct answer: D

CREATE is a DDL (Data Definition Language) statement that defines database schema objects such as tables, indexes, and views. DDL statements modify the structure of the database itself rather than the data within it.

Why the other options are wrong:

• A. SELECT is a DML (Data Manipulation Language) statement used to retrieve data, not define schema.
• B. JOIN is a clause used within DML queries to combine rows from multiple tables, not a DDL statement.
• C. UPDATE is a DML statement used to modify existing data, not define or alter database structure.

Correct answer: C

Azure SQL Database is a PaaS relational database service specifically designed for Online Transaction Processing workloads. It provides full ACID compliance, supports complex transactions, and is optimized for read/write operations characteristic of SaaS applications requiring robust transactional support.

Why the other options are wrong:

• A. Azure Cosmos DB is a NoSQL database designed for distributed, non-relational data and global scale, not traditional OLTP with relational schemas.
• B. Azure HDInsight is a managed analytics service for big data processing, not suitable for transactional OLTP applications.
• D. Azure Synapse Analytics is designed for OLAP and data warehousing with large- scale analytical queries, not transactional OLTP workloads.

Correct answer: B

The server admin login account (also called the server administrator) is the account created during logical server provisioning and always has permission to connect to any database on that server. This is the guaranteed access point regardless of Azure AD configuration or other authentication methods.

Why the other options are wrong:

• A. The Azure AD account that created the database may have limited permissions and may not always have direct connection rights depending on role assignments.
• C. The Azure AD administrator account requires proper Azure AD setup and configuration; it is not guaranteed to always connect without additional setup.
• D. The sa account is a SQL Server system administrator account that does not exist in Azure SQL Database; this is a legacy SQL Server concept not applicable to managed Azure services.

Correct answer: D

DROP is a Data Definition Language (DDL) statement used to remove database objects like tables, databases, or schemas. DDL statements define and modify the structure of database objects. SELECT is DML (retrieval), INSERT is DML (data modification), and DELETE is DML (data modification).

Why the other options are wrong:

• A. SELECT is Data Manipulation Language (DML), not DDL.
• B. INSERT is Data Manipulation Language (DML), not DDL.
• C. DELETE is Data Manipulation Language (DML), not DDL.

Correct answer: C

Azure Data Studio is a cross-platform tool that runs on Windows 10, Ubuntu Desktop, and macOS. It supports connections to Azure SQL databases and provides code assistance features like IntelliSense. SSMS is Windows-only, sqlcmd lacks GUI and IntelliSense features, and Azure Data Explorer is for analytics rather than database querying.

Why the other options are wrong:

• A. sqlcmd is a command-line utility without graphical interface or IntelliSense support.
• B. SQL Server Management Studio runs only on Windows, not on Ubuntu Desktop.
• D. Azure Data Explorer is designed for analytics and big data queries, not for connecting to Azure SQL databases.

Correct answer: B

Azure Active Directory (Azure AD) authentication supports multi-factor authentication (MFA) for connections to Azure SQL databases. Azure AD is the identity platform that integrates with MFA capabilities. Service principal, SQL, and certificate authentication do not natively support MFA requirements.

Why the other options are wrong:

• A. Service principal authentication does not support MFA.
• C. SQL authentication (username/password) does not support MFA.
• D. Certificate authentication does not support MFA.

Correct answer: B

Azure SQL Managed Instance provides native support for cross-database queries and transactions, which is a key advantage over Azure SQL Database. This feature allows developers to run queries that span multiple databases within the same managed instance. Built-in high availability, automatic backups, and encryption at rest are available in both deployment options.

Why the other options are wrong:

• A. Both Azure SQL Database and Managed Instance provide built-in high availability.
• C. Both Azure SQL Database and Managed Instance support system-initiated automatic backups.
• D. Both Azure SQL Database and Managed Instance support encryption at rest.

Correct answer: A

Microsoft SQL Server Data Tools (SSDT) is a graphical design tool that supports project- oriented offline database development, allowing developers to design and model databases in a project structure. SSMS is primarily a query and administration tool, Azure Databricks is for analytics, and Azure Data Studio is for querying rather than design.

Why the other options are wrong:

• B. SQL Server Management Studio is for querying and administration, not database design and modeling.
• C. Azure Databricks is an analytics platform, not a database design tool.
• D. Azure Data Studio is primarily for querying databases, not designing them.

Correct answer: A

A read-only database replica is implemented to offload reporting and analytics workloads from the transactional database, preventing reports from affecting the performance of the application's transactional operations. Auditing is handled through other mechanisms, high availability for regional outages uses geo-replication failover groups, and RPO is improved through backup strategies.

Why the other options are wrong:

• B. Auditing is implemented through Azure SQL Database auditing features, not read- only replicas.
• C. Regional outage protection is achieved using failover groups and geo-replication, not read-only replicas.
• D. Recovery point objective is improved through backup and restore strategies, not read-only replicas.

Correct answer: D

The JOIN clause is used in SELECT statements to combine rows from two or more tables based on related columns. SET is used in UPDATE statements, VALUES is used in INSERT statements, and KEY is used for constraint definitions, not for combining table rows.

Why the other options are wrong:

• A. SET is used in UPDATE statements to modify column values.
• B. VALUES is used in INSERT statements to specify the data being inserted.
• C. KEY is used to define constraints or indexes, not to combine rows from tables.

Correct answer: D

Azure Private Link enables private connectivity between Azure virtual machines and Azure SQL databases by creating private endpoints, eliminating exposure to the public internet. This solution provides secure, dedicated network connections without requiring VPN or gateway configurations.

Why the other options are wrong:

• A. Application Gateway is a web traffic load balancer, not designed for private database connectivity.
• B. Traffic Manager is a DNS-based traffic router for load balancing across regions, not for private connectivity.
• C. DNS is a name resolution service and does not provide private connectivity between services.

Correct answer: B

The WHERE clause is used to filter data based on conditions that can leverage indexes to avoid full table scans. When WHERE clauses reference indexed columns, the database management system uses the index to locate matching rows efficiently rather than scanning every row in the table.

Why the other options are wrong:

• A. SELECT specifies which columns to retrieve but does not instruct the DBMS to use an index.
• C. JOIN combines tables but does not directly instruct the DBMS to use an index over a full table scan.

Correct answer: B

Tabular schema represents structured data in rows and columns where each row represents a single data entity. This is the standard format for relational databases and structured data storage, with consistent columns across rows.

Why the other options are wrong:

• A. XML is a hierarchical, semi-structured format that does not organize data as simple rows and columns.
• C. Blob storage holds unstructured binary data without schema or rows.
• D. JSON is a semi-structured format that can nest data and does not enforce the strict tabular row-column structure.

Correct answer: C

Azure SQL Managed Instance provides the highest compatibility with SQL Server 2019 Enterprise edition because it maintains near-complete SQL Server compatibility, supports the same T-SQL features, agent jobs, cross-database queries, and enterprise features that SQL Server 2019 offers.

Why the other options are wrong:

• A. Azure SQL Database has compatibility constraints and does not support all SQL Server 2019 Enterprise features like cross-database queries and SQL Agent.
• B. Azure Database for MySQL is designed for MySQL workloads, not SQL Server migration.
• D. Elastic pools are a deployment option within Azure SQL Database, which has the same compatibility limitations as single databases.

Correct answer: D

The API selection (SQL, MongoDB, Cassandra, Table, or Gremlin) must be chosen during account creation and cannot be changed afterward. All other settings including geo- redundancy, multi-region writes, and account type can be modified after creation.

Why the other options are wrong:

• A. Geo-redundancy can be configured or modified at any time after account creation.
• B. Multi-region writes can be enabled or disabled after the account is created.
• C. Production or non-production designation can be changed after account creation.

Correct answer: D

The student data exhibits a tabular structure with rows (student records) and columns (StudentNumber and StudentInformation fields). The data contains heterogeneous attributes within each student record, including names, email addresses, phone numbers, and IDs. Columnar data stores are optimized for storing and querying data organized in columns, making them ideal for structured tabular data with mixed attribute types where you may need to access specific fields across many records efficiently.

Why the other options are wrong:

• A. Graph databases are designed for data with complex relationships and connections between entities, not for structured tabular student records.
• B. Key/value stores are simple lookup structures lacking the schema and structure needed to organize multiple typed fields per student record.
• C. Object stores are designed for unstructured binary data like images and documents, not for structured relational student information.

Correct answer: B

Azure Data Lake Storage provides role-based access control (RBAC) at the file and folder level using Azure Active Directory integration and access control lists (ACLs). This granular permission model allows fine-grained security for individual files and directories.

Why the other options are wrong:

• A. Azure Disk Storage is block storage for virtual machines and does not support file/folder level RBAC.
• C. Azure Blob Storage supports RBAC at the container or blob level but not at folder level like Data Lake Storage does.
• D. Azure Queue Storage is a message queue service without file/folder hierarchies or RBAC at that granularity.