DS0-001 Quick Quiz — 10 Questions

Question 1

A database administrator needs to implement column-level encryption for sensitive PII in a SQL Server environment. Which tool should be used to ensure the encryption keys are managed outside the database engine?

Choose an answer to begin — your selection is scored in the full session.

Accepted Answer

SQL Server Always Encrypted with Azure Key Vault. SQL Server Always Encrypted with Azure Key Vault stores encryption keys outside the database engine, ensuring even DBAs cannot see plaintext PII. Column-level encryption is applied at the client driver, so the database only ever handles ciphertext. Azure Key Vault provides centralized, hardware-backed key management with auditing and access policies.

Answer

Database File Stream

Answer

Transparent Data Encryption (TDE)

Answer

SQL Server Data Masking

Question 2

A database administrator needs to grant a new analyst read-only access to a specific table named 'SalesData' in a PostgreSQL database. The analyst should be able to query the table but not modify it. Which SQL statement should the administrator execute?

Accepted Answer

GRANT SELECT ON SalesData TO analyst;. The GRANT SELECT statement is the correct way to provide read-only access to a table in PostgreSQL. It allows the analyst to query the table without granting modification rights. Other options either grant excessive privileges (ALL PRIVILEGES), use invalid privilege types (USAGE, READ), or fail to meet the requirement. This aligns with the principle of least privilege.

Answer

GRANT USAGE ON SalesData TO analyst;

Answer

GRANT ALL PRIVILEGES ON SalesData TO analyst;

Answer

GRANT READ ON SalesData TO analyst;

Question 3

A database administrator needs to grant a user the ability to retrieve data from a specific table but must prevent the user from modifying or deleting any data. Which SQL statement should the administrator execute?

Accepted Answer

GRANT SELECT ON table_name TO user_name;. The correct statement is GRANT SELECT, which provides read-only access. It satisfies the need to retrieve data while ensuring the user cannot modify or delete records. Other options either grant excessive privileges or do not grant the necessary read access.

Answer

GRANT REFERENCES ON table_name TO user_name;

Answer

GRANT ALL PRIVILEGES ON table_name TO user_name;

Answer

GRANT INSERT, UPDATE, DELETE ON table_name TO user_name;

Question 4

A database administrator is designing a backup strategy for a 2 TB OLTP database that must support point-in-time recovery with a maximum data loss window of 15 minutes. Full backups complete in 4 hours, and the transaction log is backed up every 10 minutes. What is the most appropriate recovery model for this database?

Accepted Answer

Full recovery model. The full recovery model is necessary to support transaction log backups and point-in-time recovery. With log backups every 10 minutes, the maximum data loss is 10 minutes, meeting the 15-minute requirement. The simple recovery model does not allow log backups, and bulk-logged recovery may not guarantee point-in-time recovery after bulk operations. The model database is not a recovery model for user databases.

Answer

Bulk-logged recovery model

Answer

Simple recovery model

Answer

Model recovery model

Question 5

A database administrator is deploying a new PostgreSQL 15 instance that will store customer PII. The security policy requires that all data at rest be encrypted, and the platform team wants encryption tied to the host's Trusted Platform Module without managing passphrases. Which deployment approach best satisfies this requirement?

Accepted Answer

Rely on the operating system's full-disk encryption configured with TPM-backed key sealing, and leave PostgreSQL's own storage unencrypted.. Storing PII at rest requires confidentiality for every byte the database writes, including WAL, temporary files, and indexes. TPM-sealed full-disk encryption delivers that transparently and removes the operational burden of passphrase entry, because the key is unsealed only by the specific host. Application-layer column encryption and TLS protect narrower scopes, and checksums address integrity rather than secrecy.

Answer

Set the ssl=on and ssl_cert_file parameters in postgresql.conf so that client connections and stored data are both encrypted.

Answer

Enable the pgcrypto extension and encrypt individual PII columns with pgp_sym_encrypt using a key stored in the application config.

Answer

Configure data_checksums=on during initdb so that every page is checksummed and therefore protected against unauthorized reads.

Question 6

A data architect is designing a schema for a system that must store highly variable product attributes across thousands of categories, where each category has a different set of attributes and the schema must evolve frequently without downtime. Which database structure is best suited for this requirement?

Accepted Answer

A document-oriented NoSQL store with a flexible schema per product. A document-oriented NoSQL store handles variable attributes per product without schema migrations, directly supporting frequent evolution and avoiding downtime. Fixed-column tables, attribute-per-dimension star schemas, and entity-attribute-value designs either require schema changes or introduce query complexity and performance issues.

Answer

A relational table with a fixed set of columns for all possible attributes

Answer

A normalized relational schema with an entity-attribute-value table

Answer

A star schema with a dimension table per attribute

Question 7

A data engineer is profiling a customer table and notices that the Email column contains several NULL values, duplicate addresses, and inconsistent formatting such as mixed uppercase and lowercase characters. The engineer wants to quantify how many rows have a NULL Email before deciding on a cleansing strategy. Which SQL expression should be used to return that count?

Accepted Answer

SELECT COUNT(*) FROM Customer WHERE Email IS NULL;. Missing values are detected with the IS NULL predicate, and the number of such rows is obtained with COUNT(*), which counts rows regardless of column contents. COUNT(Email) would exclude NULLs and return zero when combined with the IS NULL filter. Therefore, counting all rows where Email IS NULL gives the accurate count of missing email addresses needed for data profiling.

Answer

SELECT COUNT(*) FROM Customer WHERE Email = NULL;

Answer

SELECT COUNT(Email) FROM Customer WHERE Email IS NULL;

Answer

SELECT COUNT(Email) FROM Customer;

Question 8

You are debugging a SQL query that uses an aggregate function. You receive an error stating that a column in your SELECT list is not part of an aggregate or a GROUP BY clause. What is the cause?

Accepted Answer

Missing column in GROUP BY. SQL requires that every column in the SELECT list be either aggregated (e.g., SUM, COUNT, AVG) or included in the GROUP BY clause when aggregation is used. The error 'column is not part of an aggregate or GROUP BY' means a non-aggregated column was selected without being listed in GROUP BY, so the engine cannot determine which value to return per group.

Answer

Missing WHERE clause

Answer

Incorrect join type

Answer

Using an alias in WHERE

Question 9

When migrating data between two different relational databases, which DDL-related issue is most likely to cause failure if ignored?

Accepted Answer

Incompatible data types. Incompatible data types are the most likely DDL-related failure point when migrating between different relational database engines (e.g., Oracle to PostgreSQL, SQL Server to MySQL). Each RDBMS implements its own type system with different sizes, precision, and semantics — for example, Oracle's NUMBER vs. SQL Server's DECIMAL, or MySQL's TINYINT(1) vs. PostgreSQL's BOOLEAN — so DDL that creates tables will fail or silently truncate data if types aren't mapped correctly.

Answer

Table naming conventions

Answer

Storage engine choice

Answer

Primary key naming

Question 10

A data warehouse team is deploying a partitioned fact table in SQL Server 2022 that will hold seven years of sales data. Most queries filter on the order date, and the team wants the oldest year moved to cheaper storage while remaining queryable. Which deployment design best meets these goals?

Accepted Answer

Create a partitioned table with a partition function on the order date, map each year to its own filegroup, and place the oldest filegroup on cheaper storage with a read-only filegroup setting.. Native partitioning on the date column provides two things at once: the optimizer eliminates partitions that cannot match a date predicate, and each partition lives in its own filegroup so it can be placed on a different storage tier. Marking the oldest filegroup read-only prevents accidental modification while keeping the data available to the same queries.

Answer

Create seven separate tables, one per year, and build a UNION ALL view across them for reporting queries.

Answer

Enable table compression on the fact table and archive the oldest year to a separate database that is restored on demand.

Answer

Create a single unpartitioned table with a clustered columnstore index and rely on column elimination to skip the oldest year.