DP-900 : Microsoft Azure Data Fundamentals : Part 03

DP-900 : Microsoft Azure Data Fundamentals : Part 03

1. You have an e-commerce application that reads and writes data to an Azure SQL database.

   Which type of processing does the application use?

   • stream processing
   • batch processing
   • Online Analytical Processing (OLAP)
   • Online Transaction Processing (OLTP)

   Explanation:
   OLTP is designed to serve as a persistent data store for business or front-end applications. OLTP administers day to day transaction of an organization.

2. When can you use an Azure Resource Manager template?

   • to automate the creation of an interdependent group of Azure resources in a repeatable way
   • to apply Azure policies for multi-tenant deployments
   • to provision Azure subscriptions
   • to control which services and feature administrators and developers can deploy from the Azure portal
   Explanation:
   You can automate deployments and use the practice of infrastructure as code. In code, you define the infrastructure that needs to be deployed
   To implement infrastructure as code for your Azure solutions, use Azure Resource Manager templates (ARM templates). The template is a JavaScript Object Notation (JSON) file that defines the infrastructure and configuration for your project. The template uses declarative syntax, which lets you state what you intend to deploy without having to write the sequence of programming commands to create it. In the template, you specify the resources to deploy and the properties for those resources.

3. You have an Azure SQL database that you access directly from the Internet.

   You recently changed your external IP address.

   After changing the IP address, you can no longer access the database. You can connect to other resources in Azure.

   What is a possible cause of the issue?

   • role-based access control (RBAC)
   • Dynamic Host Configuration Protocol (DHCP)
   • Domain Name Service (DNS)
   • a database-level firewall
   Explanation:

   The Azure SQL Database firewall lets you decide which IP addresses may or may not have access to either your Azure SQL Server or your Azure SQL database.

   When creating an Azure SQL Database, the firewall needs to be configured before anyone will be able to access the database. By default, no external access to your SQL Database will be allowed until you explicitly assign permission by creating a firewall rule.

4. DRAG DROP

   Match the tools to the appropriate descriptions.

   To answer, drag the appropriate tool from the column on the left to its description on the right. Each tool may be used once, more than once, or not at all.

   

   

   Explanation:

   Box 1: Microsoft SQL Server Management Studio (SSMS)
   SQL Server Management Studio (SSMS) is an integrated environment for managing any SQL infrastructure, from SQL Server to Azure SQL Database.

   Box 2: Microsoft Visual Studio Code
   Visual Studio Code is a streamlined code editor with support for development operations like debugging, task running, and version control. It aims to provide just the tools a developer needs for a quick code-build-debug cycle and leaves more complex workflows to fuller featured IDEs, such as Visual Studio IDE.

   Box 3: Azure Data Studio
   Azure Data Studio offers a modern, keyboard-focused SQL coding experience that makes your everyday tasks easier with built-in features, such as multiple tab windows, a rich SQL editor, IntelliSense, keyword completion, code snippets, code navigation, and source control integration (Git). Run on-demand SQL queries, view and save results as text, JSON, or Excel. Edit data, organize your favorite database connections, and browse database objects in a familiar object browsing experience.

   Box 4: Microsoft SQL Server Data Tools (SSDT)
   SQL Server Data Tools (SSDT) is a modern development tool for building SQL Server relational databases, databases in Azure SQL, Analysis Services (AS) data models, Integration Services (IS) packages, and Reporting Services (RS) reports. With SSDT, you can design and deploy any SQL Server content type with the same ease as you would develop an application in Visual Studio.

5. HOTSPOT

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

   NOTE: Each correct selection is worth one point.

   

   

   Explanation:

   Box 1: Yes
   Tables are database objects that contain all the data in a database. In tables, data is logically organized in a row-and-column format similar to a spreadsheet. Each row represents a unique record, and each column represents a field in the record.

   Box 2: No
   An index is an on-disk structure associated with a table or view that speeds retrieval of rows from the table or view.

   Box 3: Yes
   A view is a virtual table whose contents are defined by a query. Like a table, a view consists of a set of named columns and rows of data.

6. Which command-line tool can you use to query Azure SQL databases?

   • sqlcmd
   • bcp
   • azdata
   • Azure CLI
   Explanation:

   The sqlcmd utility lets you enter Transact-SQL statements, system procedures, and script files at the command prompt.

   Incorrect Answers:
   B: The bulk copy program utility (bcp) bulk copies data between an instance of Microsoft SQL Server and a data file in a user-specified format.

   D: The Azure CLI is the defacto tool for cross-platform and command-line tools for building and managing Azure resources.

7. HOTSPOT

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

   NOTE: Each correct selection is worth one point.

   

   

8. HOTSPOT

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

   NOTE: Each correct selection is worth one point.

   

   

9. HOTSPOT

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

   NOTE: Each correct selection is worth one point.

   

   

10. HOTSPOT

    You have the following SQL query.

    

    What are dbo.Products and ProductName? To answer, select the appropriate options in the answer area.

    NOTE: Each correct selection is worth one point.

    

    

11. HOTSPOT

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

    NOTE: Each correct selection is worth one point.

    

    

12. Which statement is an example of Data Definition Language (DDL)?

    • SELECT

    • JOIN

    • UPDATE

    • CREATE

13. HOTSPOT

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

    NOTE: Each correct selection is worth one point.

    

    

    Explanation:

    Box 1: Yes
    Azure Data Studio is a cross-platform database tool for data professionals using on-premises and cloud data platforms on Windows, macOS, and Linux.

    You can use Azure Data Studio to connect to an Azure SQL Database server. You’ll then run Transact-SQL (T-SQL) statements to create and query Azure SQL databases.

    Box 2: No
    SQL Server Management Studio is for configuring, managing, and administering all components within Microsoft SQL Server, not to create SQL notebooks.
    Instead use Azure Data Studio to create SQL notebook.

    Box 3: Yes
    You can use the Azure Data Studio to restore databases.

14. You are deploying a software as a service (SaaS) application that requires a relational database for Online Transaction Processing (OLTP).

    Which Azure service should you use to support the application?

    • Azure Cosmos DB
    • Azure HDInsight
    • Azure SQL Database
    • Azure Synapse Analytics
    Explanation:

    Azure SQL Database is relational database and a managed service.

    Incorrect Answers:
    A, B: Cosmos DB, HDInsight are non-relational databases.
    D: Azure Synapse Analytics is for data warehousing, not for Online Transaction Processing

15. What are two benefits of platform as a service (PaaS) relational database offerings in Azure, such as Azure SQL Database? Each correct answer presents a complete solution.

    NOTE: Each correct selection is worth one point.

    • access to the latest features
    • complete control over backup and restore processes
    • in-database machine learning services
    • reduced administrative effort for managing the server infrastructure
    Explanation:

    A: Azure SQL Database is a fully managed platform as a service (PaaS) database engine that handles most of the database management functions such as upgrading, patching, backups, and monitoring without user involvement.

    D: SQL Database delivers predictable performance with multiple resource types, service tiers, and compute sizes. It provides dynamic scalability with no downtime, built-in intelligent optimization, global scalability and availability, and advanced security options. These capabilities allow you to focus on rapid app development and accelerating your time-to-market, rather than on managing virtual machines and infrastructure.

16. HOTSPOT

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

    NOTE: Each correct selection is worth one point.

    

    

    Explanation:

    Box 1: No
    Microsoft handles all patching and updating of the SQL and operating system code. You don’t have to manage the underlying infrastructure.

    Box 2: Yes
    SQL Database is a fully managed service that has built-in high availability, backups, and other common maintenance operations.

    Box 3: No

17. DRAG DROP

    You have a table named Sales that contains the following data.

    

    You need to query the table to return the average sales amount per day. The output must produce the following results.

    

    How should you complete the query? To answer, drag the appropriate values to the correct targets. Each value may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.

    NOTE: Each correct selection is worth one point.

    

    

    Explanation:

    Box 1: SELECT

    Box 2: GROUP BY

    Example:
    When used with a GROUP BY clause, each aggregate function produces a single value covering each group, instead of a single value covering the whole table. The following example produces summary values for each sales territory in the AdventureWorks2012 database. The summary lists the average bonus received by the sales people in each territory, and the sum of year-to-date sales for each territory.

    SELECT TerritoryID, AVG(Bonus)as ‘Average bonus’, SUM(SalesYTD) as ‘YTD sales’
    FROM Sales.SalesPerson
    GROUP BY TerritoryID;

18. When you create an Azure SQL database, which account can always connect to the database?

    • the Azure Active Directory (Azure AD) account that created the database
    • the server admin login account of the logical server
    • the Azure Active Directory (Azure AD) administrator account
    • the sa account
    Explanation:
    When you first deploy Azure SQL, you specify an admin login and an associated password for that login. This administrative account is called Server admin.

19. You manage an application that stores data in a shared folder on a Windows server.

    You need to move the shared folder to Azure Storage.

    Which type of Azure Storage should you use?

    • queue
    • blob
    • file
    • table
    Explanation:
    Azure file shares can be mounted concurrently by cloud or on-premises deployments of Windows, Linux, and macOS. Azure file shares can also be cached on Windows Servers with Azure File Sync for fast access near where the data is being used.

20. Your company is designing a database that will contain session data for a website. The data will include notifications, personalization attributes, and products that are added to a shopping cart.

    Which type of data store will provide the lowest latency to retrieve the data?

    • key/value
    • graph
    • columnar
    • document