216 questions total · 3pages · All types, answers revealed
A database developer needs to combine the results of two different SELECT queries into a single result set. Which operator should be used?
UNION stacks results of multiple queries.
Why this answer
The UNION operator is specifically designed to combine the result sets of two or more SELECT statements into one.
Which SQL keyword is used to eliminate duplicate values from a query result set?
DISTINCT removes duplicates from the output.
Why this answer
The DISTINCT keyword is used in a SELECT statement to return only unique values.
A database administrator is planning to normalize a flat-file database containing customer orders. The administrator identifies that customer contact information is repeated for every order record. Which normalization form should the administrator implement to move this contact information to a separate table?
2NF removes partial functional dependencies by separating data into distinct tables.
Why this answer
Moving repeating groups or redundant data into separate tables to eliminate partial dependencies is the goal of Second Normal Form (2NF).
A data engineer is configuring an S3 bucket to comply with GDPR requirements regarding the 'Right to be Forgotten.' Which combination of configuration settings best automates the lifecycle of user-specific data objects that have reached the end of their retention period?
Lifecycle rules provide an automated mechanism to transition or expire objects based on age.
Why this answer
S3 Lifecycle policies allow for the automated deletion of objects based on expiration rules after a specific time period.
When decommissioning a legacy server, what is the most secure way to handle the hard drives containing sensitive data?
These are the only methods considered secure for sensitive data disposal.
Why this answer
Cryptographic erasure or physical destruction are the only ways to guarantee that data cannot be recovered from decommissioned media.
A database is configured with an 'Asynchronous Commit' replication mode. What is the potential risk during a primary server failure?
Because the secondary may lag, failover can result in lost transactions.
Why this answer
Asynchronous replication acknowledges transactions on the primary before they reach the secondary, meaning some committed data might not have been replicated, leading to data loss upon failover.
When creating an ETL pipeline, which stage involves validating that the data meets business rules (e.g., age must be positive)?
Transformation is where data is cleaned and validated.
Why this answer
Data validation or quality checks occur during the transformation phase to ensure data integrity.
Which TWO controls are necessary to maintain the integrity of a data warehouse?
Constraints maintain the logical consistency of relationships.
Why this answer
Integrity is maintained through data validation during the ETL process and by enforcing referential integrity in the database schema.
A DBA is troubleshooting a slow query. The execution plan indicates an 'Implicit Conversion' is occurring, which prevents the use of an index seek. What is the most likely cause?
When comparing a VARCHAR column to an NVARCHAR parameter, SQL Server must convert the column values to match, preventing index usage.
Why this answer
An implicit conversion happens when the data type of a column does not match the data type of the constant or parameter being compared to it, forcing the engine to convert every row.
Which TWO of the following are common types of NoSQL databases?
Stores data as simple pairs.
Why this answer
Document and Key-Value stores are two primary categories of NoSQL databases.
What is the primary function of an index in a database?
Indexes allow the database engine to find data without performing full table scans.
Why this answer
Indexes are designed to speed up data retrieval by providing a faster lookup mechanism than scanning every row in a table.
Which THREE of the following SQL aggregate functions are commonly supported in most relational databases?
Calculates the total.
Why this answer
SUM, COUNT, and AVG are fundamental aggregate functions supported across virtually all SQL standards.
Which dashboarding practice helps prevent users from being overwhelmed by too much data at once?
Interactivity allows users to control the volume of data displayed.
Why this answer
Progressive disclosure or the use of filters allows users to drill down into details only as needed.
Which THREE actions should be performed during a data incident response procedure involving a potential PII leak?
Investigation identifies the scope and root cause.
Why this answer
Incident response requires containment, investigation, and reporting to meet legal obligations like GDPR/CCPA.
When assessing data privacy compliance, which THREE categories of data are typically protected under laws like CCPA or GDPR?
Biometric information is considered highly sensitive.
Why this answer
PII, sensitive biometric data, and financial account information are primary targets for privacy regulation.
Which THREE of the following are valid methods for securing cloud-based data storage?
VPC endpoints keep traffic on the private cloud network.
Why this answer
Securing cloud storage requires a combination of IAM, encryption, and secure network configuration.
In an Oracle Database environment, a DBA identifies that specific queries are performing poorly due to fragmented tables. Which command should the DBA execute to reclaim unused space and defragment the table without dropping the table structure?
This command compacts the table segments and lowers the high water mark to reclaim space.
Why this answer
The ALTER TABLE ... SHRINK SPACE command is the standard way to defragment and reclaim space in an Oracle table while maintaining the table's structure and indexes.
A MySQL DBA needs to diagnose a deadlock issue. Which tool or command provides the most detailed information regarding the specific InnoDB internal locks held by transactions at the time of a deadlock?
This command outputs the InnoDB monitor output, which includes the latest deadlock detection information.
Why this answer
The command 'SHOW ENGINE INNODB STATUS' provides a section specifically detailing the last detected deadlock, including the locks held by the involved transactions.
Which THREE of the following are valid SQL Server authentication modes?
Native database-managed authentication.
Why this answer
SQL Server supports Windows Authentication, SQL Server Authentication, and Mixed Mode (which enables both).
Which SQL command is used to add new records to an existing table?
INSERT adds new records.
Why this answer
INSERT INTO is the standard command for adding new rows to a database table.
What is the primary role of a Data Dictionary in a database management system?
The data dictionary is the centralized source of truth for the database schema.
Why this answer
The data dictionary contains metadata about the database, such as table definitions, column types, and constraints, acting as the central repository for database structure.
Which of the following activities is performed during the 'Testing' phase of a database deployment to ensure the system meets its performance targets?
Load testing validates performance under realistic workload conditions.
Why this answer
Load testing simulates expected user traffic to determine if the database and hardware can handle the production workload without bottlenecking.
An administrator is verifying the storage requirements for a new PostgreSQL database installation. Which configuration parameter in the postgresql.conf file directly impacts the amount of memory allocated for shared buffers?
shared_buffers determines how much memory PostgreSQL uses for caching data.
Why this answer
shared_buffers is the primary parameter that defines how much memory the database server uses for caching data.
A developer needs to calculate the running total of sales. Which SQL feature is most appropriate?
Window functions handle running totals efficiently.
Why this answer
Window functions, specifically SUM() OVER(), allow calculations across a set of rows related to the current row without collapsing the result into a single group.
Which TWO database deployment configurations help mitigate the impact of a hardware failure on the primary storage controller?
MPIO provides redundant paths between the server and storage, protecting against controller failure.
Why this answer
Redundancy at the hardware/storage layer is achieved through RAID configurations and multi-path I/O (MPIO).
You are performing data transformation in Python using Pandas. You have a 'DataFrame' with missing values in the 'Revenue' column. Which method should be used to replace these missing values with the column mean?
fillna() allows replacement of missing values with a specified statistic.
Why this answer
The fillna() method is the standard way to replace NaN values in a Pandas DataFrame.
When cleaning a dataset, you notice that column names contain inconsistent casing (e.g., 'Name', 'NAME', 'name'). Which transformation step should be prioritized to facilitate join operations?
Normalizing casing ensures consistency for joins.
Why this answer
Standardizing string casing (normalization) is a fundamental prerequisite for accurate merging and joining.
To implement a secure 'Multi-Tenant' architecture, which design approach is most effective for data separation?
Separate schemas offer strong logical isolation.
Why this answer
Using a separate schema or database per tenant provides a hard boundary that prevents cross-tenant data access.
Which THREE of the following are standard database maintenance tasks?
Keeps the query optimizer informed for better performance.
Why this answer
Updating statistics, backing up the database, and rebuilding indexes are foundational maintenance tasks for performance and data safety.
During the pre-deployment phase, a team needs to estimate the hardware resources required for a new Oracle Database installation. Which tool should be used to simulate database workload and forecast resource consumption?
Database Replay allows for accurate capacity planning by replaying captured workloads on new hardware.
Why this answer
Oracle Real Application Testing (RAT) and its sub-feature Database Replay are specifically designed to capture production workloads and replay them in a test environment to forecast hardware needs.
Which THREE of the following are necessary prerequisites for implementing database mirroring in a SQL Server environment?
Mirroring requires the log chain.
Why this answer
Mirroring requires the database to be in Full recovery mode, a full backup/restore for seeding, and the correct account permissions for network communication.
Which database backup strategy is recommended to ensure that you can restore a database to a specific point in time?
Transaction logs capture all changes, enabling recovery to any specific point in time between backups.
Why this answer
Transaction log backups, when used in conjunction with full backups and the Full recovery model, allow for point-in-time recovery.
A database administrator is planning a new SQL Server deployment and needs to ensure that the tempdb system database does not become a bottleneck during high-concurrency operations. Which configuration step should be performed during the initial setup?
SQL Server best practices recommend creating multiple tempdb files to reduce contention on system tables.
Why this answer
In SQL Server, tempdb contention is often mitigated by increasing the number of data files based on the number of logical processors.
A database administrator needs to implement Row-Level Security (RLS) to ensure that users can only view records belonging to their specific department. Which database component must be created to enforce this logic?
A predicate function defines the logic used by the security policy to filter rows.
Why this answer
RLS in SQL Server requires a security predicate function (a table-valued function) that returns a filter condition applied by a security policy.
A DBA needs to implement transparent data encryption (TDE) for an entire database. Which component is required to protect the Database Encryption Key (DEK) in a SQL Server environment?
TDE uses a DEK, which must be protected by a certificate or an asymmetric key.
Why this answer
The Certificate or Asymmetric Key is required to protect the DEK, which in turn is protected by the Master Key in the master database.
Which type of join should be used to return all records from the 'Left' table and only matching records from the 'Right' table?
LEFT JOIN preserves all records from the left source.
Why this answer
A LEFT JOIN returns all rows from the left table, and matching rows from the right table; if no match exists, NULL values are returned for right-side columns.
A company is migrating to a cloud data warehouse and must comply with CCPA requirements for data portability. Which action is necessary to ensure compliance regarding user access requests?
Providing a machine-readable export satisfies the portability requirement.
Why this answer
Data portability under CCPA requires that the organization can export user data in a readily usable format.
Which THREE of the following are essential components of a robust disaster recovery plan for a mission-critical database?
Protects data if the primary data center is destroyed.
Why this answer
A complete DR plan requires regular backups, a documented restoration procedure, and off-site storage to protect against site-wide disasters.
Which TWO tasks are critical before performing a major database schema upgrade?
A full backup is the last line of defense if the migration fails.
Why this answer
Backing up the data and testing the migration script in a non-production environment are standard safety practices.
Which command is used to restrict a user's access to a specific table?
REVOKE is the standard DCL command to remove access.
Why this answer
REVOKE is the DCL command used to remove permissions previously granted to a user.
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?
This feature allows for column-level encryption where the key resides in a secure external HSM.
Why this answer
Azure Key Vault is the industry-standard service for managing encryption keys independently of the database engine, ensuring separation of duties.
During a database deployment, the administrator needs to ensure that audit logs are captured for all privileged user actions. Which component should be configured?
This component links specific actions to the audit logs, ensuring visibility into privileged activity.
Why this answer
A SQL Server Audit or similar database-level auditing feature allows the tracking of specific actions (like ALTER or DROP) performed by privileged accounts.
A database is experiencing high I/O wait times on the log drive. What is the most effective configuration change to alleviate this bottleneck?
Transaction logs are write-intensive; moving them to a fast, dedicated drive reduces latency.
Why this answer
Moving the transaction log to a dedicated physical drive with high write performance (low latency) is the most effective way to resolve I/O bottlenecks related to transaction logging.
A company needs to audit all administrative actions taken on their database server. Which feature should the DBA enable?
Enabling audit logs captures the who, what, and when of database interactions.
Why this answer
Database Auditing logs all DDL and DML operations, providing the evidence needed for compliance reporting.
A database administrator notices that the transaction log file is nearly full. Which operation will shrink the file to free up space?
This command is used to shrink a specific file, such as a transaction log.
Why this answer
DBCC SHRINKFILE is the command used to shrink the size of data or log files in SQL Server.
Which TWO actions constitute good data lifecycle management?
Policies dictate how long data should be kept.
Why this answer
Data lifecycle management includes defining retention periods and identifying when to archive vs. destroy data.
When using Python's 'Matplotlib' library, which function is used to add a new figure to the canvas?
This creates a new figure container.
Why this answer
The plt.figure() function initializes a new figure, allowing multiple plots to be managed.
Which THREE of the following items should be monitored to identify performance bottlenecks in a database?
Often the root cause of slow performance.
Why this answer
Disk latency, CPU utilization, and batch requests per second are classic metrics for diagnosing performance issues.
Before finalizing a database deployment, the team must ensure high availability. Which configuration allows for automatic failover to a standby server with zero data loss in a SQL Server environment?
Synchronous commit ensures the secondary is fully up to date, providing zero data loss failover.
Why this answer
Synchronous-commit Availability Groups ensure that transactions are committed on the secondary replica before the primary acknowledges the transaction, preventing data loss.
Which of the following is a 'categorical' variable?
Categories are discrete groups.
Why this answer
A categorical variable represents qualitative information, such as department, color, or gender.
Which TWO of the following scenarios are best suited for using a 'View'?
Encapsulates logic for reuse.
Why this answer
Views are best for simplifying complex queries and implementing row/column level security.
In the context of data quality, what does 'Completeness' refer to?
Completeness confirms all necessary fields are populated.
Why this answer
Completeness measures whether all required data is present in the dataset without missing values.
Which database object is specifically designed to enforce referential integrity between two tables?
Foreign keys maintain the relationship and referential integrity between tables.
Why this answer
A Foreign Key constraint is designed to ensure that data in one table matches valid entries in another table, maintaining referential integrity.
To prevent unauthorized access to specific columns in a 'Salary' table, which object should be created for the reporting team?
Views are frequently used to restrict access to sensitive table data.
Why this answer
A view can be used to expose only specific columns to a user or group, effectively hiding sensitive data like salaries.
Which TWO of the following are common administrative tasks in a Data Governance program?
The catalog must be updated as new data is added.
Why this answer
Governance administrators manage the metadata catalog and oversee the access request/approval process.
A data analyst is writing a SQL query to retrieve unique department names from the 'Employees' table. Which keyword should be utilized to ensure the results do not contain duplicate entries?
DISTINCT effectively filters out duplicate rows in a result set.
Why this answer
The DISTINCT keyword is used to return only unique values from a specific column.
Which THREE of the following are common types of database backups?
Captures the entire database.
Why this answer
Full, differential, and transaction log backups are the standard backup types used in professional database environments.
Which type of data is defined as 'sensitive' and usually requires enhanced protection?
PII is highly regulated and requires specific controls.
Why this answer
Personally Identifiable Information (PII) is subject to strict regulatory requirements and must be protected.
An analyst is writing an SQL query to retrieve total sales per region, but the result must only show regions with more than 100 transactions. Which clause is required?
HAVING is the correct clause to filter aggregated group results.
Why this answer
The HAVING clause is used to filter groups created by the GROUP BY clause, whereas WHERE filters individual rows.
Which NoSQL structure is best suited for scenarios where data relationships are complex and frequently traversed?
Graph databases excel at managing complex relationships.
Why this answer
Graph databases are specifically designed for data that is highly interconnected, such as social networks.
Which TWO of the following are key considerations when configuring a database for high availability?
Affects the sync speed and potential for data loss.
Why this answer
Network latency between nodes and the speed of the storage subsystem are critical to ensure synchronization is efficient and failover is reliable.
Which TWO of the following can be used to prevent unauthorized access to sensitive database tables?
Limits access to specific rows based on user criteria.
Why this answer
Implementing role-based access control (RBAC) and row-level security (RLS) are effective ways to secure access to data.
Which THREE elements are essential when configuring Database Activity Monitoring (DAM)?
Focusing monitoring on PII/sensitive data is more effective.
Why this answer
DAM requires defining the scope (what to monitor), the sensitivity of the data, and the alerting rules for suspicious activity.
Which TWO database features are best used to handle massive volumes of data while maintaining fast query speeds for historical analytics?
Partitioning allows for 'partition elimination', which significantly speeds up queries on large datasets.
Why this answer
Partitioning and columnstore indexing are the two primary technologies for managing and querying large, historical datasets.
A company is planning to migrate a legacy on-premises database to the cloud. Which assessment should be conducted to ensure the application compatibility with the target managed database service?
This identifies potential breaking changes between the legacy version and the target managed service.
Why this answer
A compatibility assessment tool evaluates features, syntax, and dependencies to identify what needs to be changed before moving to a managed service like Azure SQL or AWS RDS.
Which TWO of the following are common database maintenance tasks?
Helps the optimizer plan queries.
Why this answer
Updating statistics and rebuilding indexes are critical maintenance tasks to ensure optimal query performance.
You are deploying a database that requires compliance with FIPS 140-2. What must be configured to ensure the database meets this security standard?
FIPS-mode forces the database to use only validated cryptographic algorithms.
Why this answer
Enabling FIPS-compliant encryption modules ensures that all cryptographic operations performed by the database meet the federal security standard.
Which TWO of the following are examples of logical database design tasks?
Normalization is a logical design process to reduce data redundancy.
Why this answer
Logical design focuses on the structure of the data itself (entities, attributes, relationships) rather than physical storage details.
A data analyst discovers that raw log files contain unmasked social security numbers. Which data governance practice should be implemented immediately to remediate the risk?
DDM obscures sensitive data from unauthorized users at the query layer.
Why this answer
Data masking at the ingestion layer prevents sensitive data from being written to logs in clear text.
You are deploying a database to a cloud environment and need to ensure that data at rest is encrypted according to organizational security policy. Which deployment configuration ensures transparent encryption of the database files?
TDE encrypts the database, log files, and backups at rest without requiring application code changes.
Why this answer
Transparent Data Encryption (TDE) provides encryption for the entire database file at the storage level, which is a standard deployment requirement for cloud databases.
Which technology provides the best protection against data exfiltration from an on-premises database by a rogue administrator?
DAM and firewalls can block queries that indicate bulk exfiltration.
Why this answer
A Database Firewall inspects incoming and outgoing SQL traffic to block unauthorized queries or data exports.
You are reviewing a deployment plan for a multi-tenant database. To ensure security, you want to ensure that each tenant's data is logically isolated. Which strategy is most effective for this?
Schema isolation provides a robust logical boundary between tenant datasets.
Why this answer
Implementing separate schemas for each tenant provides a logical separation that allows for efficient management while keeping data isolated within the same instance.
A database administrator needs to store unstructured data that scales horizontally across multiple servers while maintaining high availability. Which database structure should be selected?
NoSQL databases provide the flexibility for unstructured data and distributed horizontal scaling.
Why this answer
NoSQL databases, such as document stores, are designed for horizontal scaling and handling unstructured data, unlike rigid relational schemas.
Which scenario best justifies the use of a relational database over a NoSQL database?
ACID compliance is a core requirement for financial systems.
Why this answer
Relational databases provide strong ACID compliance, which is essential for financial transactions where data integrity is paramount.
When troubleshooting a performance bottleneck in a visualization, which action is most effective if the data source is a large SQL view?
Materialization pre-calculates the results and allows for indexing.
Why this answer
Materializing the view (creating a physical table with indexes) or optimizing the view's underlying query is the most effective way to resolve performance issues.
Practice DS0-001 by domain
Target a specific domain to shore up weak areas.