Free Microsoft Azure Data Fundamentals Questions and Answers — Questions and Answers

Question 1: Which kind of items in a relational database should you configure a datatype for?

rows
tables
columns (Correct answer)
fields
relationships

Correct answer: columns

In a relational database, datatypes are configured for columns (also known as attributes). Each column stores a specific type of data, such as integers, text, or dates, for all rows in the table. This ensures data integrity and consistency within that particular column.

Question 2: To aggregate the content of two tables based on a shared column, which SQL operation is used?

JOIN (Correct answer)
UNION
INTERSECT
HAVING

Correct answer: JOIN

The `JOIN` operation in SQL is used to combine rows from two or more tables based on a related column between them. This allows you to retrieve data that is spread across multiple tables, creating a single, unified result set. Other operations like `UNION` combine rows from result sets, not based on shared columns.

Question 3: What SQL clause should be used to copy every row from one table to another?

INSERT – VALUES
SELECT – HAVING
SELECT - INTO (Correct answer)
UPDATE

Correct answer: SELECT - INTO

The `SELECT ... INTO` statement in SQL is specifically designed to copy all or a subset of rows from one table into a *new* table. It creates the new table and inserts the selected data in a single operation. `INSERT - VALUES` adds rows to an existing table, while `UPDATE` modifies existing rows.

Question 4: Data integrity is ensured and duplicate data is decreased in databases using the ______________ procedure.

Normalization (Correct answer)
Projecting
Indexing
Refactoring

Correct answer: Normalization

Normalization is a systematic process of organizing the columns and tables of a relational database to minimize data redundancy and improve data integrity. It involves decomposing tables into smaller, related tables and defining relationships between them. This structured approach helps prevent anomalies and ensures data consistency.

Question 5: Which three claims about a table's characteristics in a relational database are true? Select all applicable answers.

There can only be one foreign key per table.
A table's rows and columns all have the same number of columns. (Correct answer)
A primary key can be constructed from numerous columns in a table. (Correct answer)
There must be a datatype for each column. (Correct answer)
Values must be present in every column.

Correct answer: A table's rows and columns all have the same number of columns.

In a relational table, all rows must have the same number of columns, ensuring a consistent structure. Each column also requires a defined datatype to specify the kind of data it will hold, enforcing data integrity. A primary key, which uniquely identifies each row, can indeed be composed of one or more columns (a composite primary key).

Question 6: You must suggest a remedy that satisfies the following criteria: - Comprises business logic that allows for database product renaming - Increases table entries What should the recommendation contain?

an inline function
a view
a table-valued function
a stored procedure (Correct answer)

Correct answer: a stored procedure

A stored procedure is a pre-compiled collection of SQL statements and business logic stored in the database. It can accept parameters, perform complex operations like renaming products (updating multiple tables) and inserting new entries, offering better performance and security. Inline functions and views are primarily for returning data, not for complex transactional logic.

Question 7: What can you use in a relational database to make a virtual table out of the results of a SELECT statement?

a relationship
a view (Correct answer)
a stored procedure
an index

Correct answer: a view

A view in a relational database is a virtual table based on the result-set of a SQL `SELECT` statement. It does not store data itself but rather provides a dynamic window into the underlying tables, allowing users to query it as if it were a real table. Views simplify complex queries and can enhance security by restricting access to certain data.

Question 8: You have a folder with text files, pictures, and audio files in it. What kind of data do you have?

semi-structured
unstructured (Correct answer)
structured

Correct answer: unstructured

Unstructured data refers to information that does not have a predefined data model or is not organized in a pre-defined manner. Text files, images, and audio files fall into this category because their content is not easily searchable or analyzable by traditional database methods without further processing. Structured data is highly organized, while semi-structured has some organizational properties but lacks a fixed schema.

Question 9: What kind of database would work best for an organizational chart?

columnar
graph (Correct answer)
Azure Table storage
Azure SQL Database

Correct answer: graph

A graph database is ideal for representing and querying relationships between entities, making it perfect for an organizational chart. In this model, employees would be "nodes" and their reporting lines or departmental connections would be "edges," allowing for efficient traversal and analysis of hierarchical and networked structures. Other database types are less suited for complex relationship modeling.

Question 10: Which database type should you employ to store sequential data as quickly as possible?

Azure Table storage
graph
time series (Correct answer)
Azure SQL Database

Correct answer: time series

A time series database is specifically optimized for storing and querying data points that are indexed by time, such as sensor readings or system metrics. They are designed for high-volume writes and efficient retrieval of sequential data, making them the best choice for quickly storing and analyzing time-stamped information. Other database types are not specialized for this kind of sequential data.

Question 11: You create an application that must store data in accordance with the following specifications: - Save historical information from many data sources. - Add data on a recurring basis. Use a denormalized snowflake or star schema. Which database type should you employ?

OLAP (Correct answer)
Azure Table storage
graph
OLTP

Correct answer: OLAP

Online Analytical Processing (OLAP) databases are designed for complex analytical queries and reporting, often using denormalized star or snowflake schemas. They are optimized for reading large volumes of historical data from multiple sources and are typically updated periodically, making them ideal for data warehousing and business intelligence scenarios. OLTP databases are for transactional workloads with frequent updates.

Question 12: Which service enables the storage of information as a graph database?

Azure SQL Managed Instance
Azure SQL Database
Azure Cosmos DB (Correct answer)
Azure Synapse Analytics

Correct answer: Azure Cosmos DB

Azure Cosmos DB is Microsoft's globally distributed, multi-model database service that supports various API models, including Gremlin for graph databases. It allows you to store and query data as a graph, making it suitable for scenarios requiring complex relationship modeling. Azure SQL Database and Managed Instance are relational databases, and Synapse Analytics is for data warehousing and analytics.

Question 13: Which two file types use columnar data storage? Each accurate response gives a complete resolution. Select all applicable answers.

Parquet (Correct answer)
CSV
Avro
ORC (Correct answer)

Correct answer: Parquet

Parquet and ORC (Optimized Row Columnar) are popular columnar data storage formats, especially in big data ecosystems. Columnar storage stores data column by column rather than row by row, which significantly improves query performance for analytical workloads by allowing systems to read only the necessary columns. CSV and Avro are typically row-oriented formats.

Question 14: Which two traits best describe a transactional data workload? Select each appropriate response.

highly denormalized
highly normalized (Correct answer)
optimized for read operations
optimized for create, read, update, and delete (CRUD) operations (Correct answer)

Correct answer: highly normalized

Transactional data workloads, typically handled by OLTP systems, are characterized by frequent, small, and concurrent create, read, update, and delete (CRUD) operations. To ensure data integrity and minimize redundancy, these databases are usually highly normalized. This design prioritizes fast transaction processing and data consistency over analytical query performance.

Question 15: Which sort of data workload stores a lot of aggregated data and is best for read operations?

time series
transactional
graph
analytical (Correct answer)

Correct answer: analytical

Analytical data workloads are specifically designed for processing and querying large volumes of historical and aggregated data. They are optimized for read-heavy operations, enabling users to perform complex analyses, generate reports, and derive insights from the data. This contrasts with transactional workloads, which prioritize rapid, individual write operations.

Question 16: What three characteristics distinguish analytical data workloads? Each accurate response provides a complete solution. Select each appropriate response.

Over time, they compute business metrics. (Correct answer)
They use up-to-date data to operate.
Operations for reading and writing are optimized.
They use historical data to operate. (Correct answer)
Operations for reading are optimized. (Correct answer)

Correct answer: Over time, they compute business metrics.

Analytical data workloads are characterized by their focus on historical data to compute business metrics over time. They are primarily optimized for read operations, allowing for efficient querying and analysis of large datasets to uncover trends and insights. This design prioritizes complex data retrieval and aggregation over rapid, individual write operations or real-time data processing.

Free Microsoft Azure Data Fundamentals Questions and Answers

Which kind of items in a relational database should you configure a datatype for?