scenario questions

A junior data analyst runs the following query against a retail sales table and receives 42,000 rows instead of the expected 12,000: SELECT c.customer_id, c.customer_name, o.order_id FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id WHERE o.order_status <> 'cancelled'; Which action should the analyst take to return all customers, including those with no orders, while still excluding cancelled orders?

You need to change the collation of a database. What is the impact of performing this operation?

A database administrator is deploying a new Microsoft SQL Server 2022 instance that will host a mission-critical OLTP database. The business requires zero data loss and automatic failover to a secondary server in a different subnet within the same data center. The administrator needs to configure a high-availability solution that supports automatic failover and synchronous commit. Which solution should the administrator implement?

During the testing phase of a database deployment, you discover that the database is not accepting external connections. You have verified that the database service is running. What is the most likely culprit?

A developer needs to ensure that a 'Customer_ID' field in a 'Orders' table always references a valid ID in the 'Customers' table. Which constraint should be applied?

A database architect is tasked with choosing a database model for a system that requires highly variable, semi-structured data storage (e.g., JSON documents). Which model provides the best flexibility for this requirement?

A database administrator is deploying a new SQL Server instance that will host a data warehouse. The instance must store data and log files on separate physical volumes to maximize performance and simplify backup operations. The server has four drive letters available: C, D, E, and F. The administrator wants to ensure that tempdb is also isolated. Which configuration should the administrator use?

A database administrator is designing a schema for a financial application that must enforce that every account balance is never negative. The application inserts and updates balances frequently, and the DBA wants the rule enforced at the database level regardless of which application or script performs the change. Which database object should the DBA create to enforce this rule?

A database administrator is deploying a new database that will store sensitive customer data. The organization requires that all data at rest be encrypted using AES-256. The database supports Transparent Data Encryption (TDE). After enabling TDE, what additional step must be performed to ensure that the encryption keys are protected and can be recovered in case of a disaster?

In PostgreSQL, you need to monitor the performance of your long-running queries. Which view is the most appropriate to inspect the query execution statistics for all active sessions?

A data analyst is working with a dataset that contains a column with values such as 'N/A', 'null', ' ', and 'unknown'. The analyst needs to count the number of truly missing values in this column. Which approach is most appropriate?

Which SQL statement is used to remove a table entirely from the database?

An administrator is deploying a new database and needs to ensure that the backup strategy complies with the '3-2-1' backup rule. What does the '1' in this rule represent?

A retail company stores customer email addresses in a column named email_addr in an Oracle Database. A new privacy policy requires that analysts querying the table see a masked value such as j***@example.com, while the marketing application that sends campaigns must still read the real address. Which Oracle feature should the database administrator configure?

Which access control model should be implemented to ensure that a user can only access files based on their specific job role?

Which THREE of the following are valid methods to improve database performance for read-heavy workloads?

A developer wants to use a subset of production data for testing. Which method ensures the test data retains its structure while protecting privacy?

A database administrator is designing a table to store product inventory. The table must enforce that each product has a unique SKU and that the quantity_on_hand column never drops below zero. The administrator adds a PRIMARY KEY constraint on SKU and a CHECK constraint on quantity_on_hand. A developer then tries to insert a row with a NULL SKU and a negative quantity. Which statement describes the outcome?

A database architect is designing a schema for a high-volume transaction system. The system requires strict data consistency and supports complex queries with multiple joins. The architect must choose between a normalized relational model and a denormalized model. Which approach is most appropriate and why?

An administrator needs to improve the security posture of a newly deployed PostgreSQL database. Which TWO actions should be performed to harden the environment?

Which of the following is a limitation of the 'Star Schema' design compared to a fully normalized schema?

A financial services firm is designing a new data warehouse that will hold account balances and transaction histories. The governance committee wants to formalize who owns each data domain, who is accountable for its quality, and how stewardship duties are delegated across business units. Which artifact should the data governance lead produce first to establish these assignments?

You are performing a backup for a database that is in a 24/7 environment. Which backup type should you use if you want to avoid interfering with the existing backup sequence of full and differential backups?

Which TWO of the following actions are considered best practices for maintaining data integrity in a data pipeline?

Which THREE of the following represent common data transformation tasks in a preparation workflow?

A company subject to GDPR needs to ensure that user data is deleted upon request across all distributed nodes in a NoSQL cluster. Which process is most critical to demonstrate compliance?

A multinational corporation is implementing a data governance program and needs to ensure compliance with the GDPR's data minimization principle. Which TWO practices should the data governance team enforce? (Choose two.)

A business analyst is preparing a quarterly sales report and needs to categorize customers into 'High', 'Medium', and 'Low' value tiers based on their total annual spend. The analyst writes a SQL query against a Microsoft SQL Server database and wants to assign the tier label in the result set without modifying the underlying table. Which SQL construct should the analyst use?

A database administrator is deploying a new database server and needs to ensure that the database software starts automatically when the operating system boots. On a Linux system, which of the following should the administrator use to manage the database service?

A database administrator is deploying a new PostgreSQL database cluster that must support a read-intensive reporting workload. The reporting queries are long-running and scan large tables. The administrator wants to avoid blocking write transactions on the primary server. Which deployment approach should the administrator use?

A financial services company stores trade records in a table where the combination of TradeDate and TradeID must be unique, but individual TradeID values can repeat on different dates. The database administrator needs to enforce this uniqueness at the database level. Which constraint should be implemented?

Which THREE factors should be considered when choosing between a traditional RDBMS and a NoSQL database for a new project?

Which TWO of the following are recognized components of a mature Data Governance framework? (Select TWO)

Which TWO of the following are benefits of using a data warehouse?

A data engineer must load a 400 GB Parquet dataset from an object store into a columnar warehouse. The source files are partitioned by date but each partition contains many small files averaging 5 MB. Queries against the loaded table are slow because the warehouse scans far more data than necessary. Which action most directly improves query performance by reducing scanned data?

A retail company is implementing a new data classification policy. The security team needs to categorize data based on its sensitivity and the impact of unauthorized disclosure. Which data classification level typically requires the highest level of protection and is subject to the strictest access controls?

When preparing a database deployment plan, what THREE factors must be considered to ensure successful capacity planning?

An organization is migrating sensitive financial records to a cloud environment. Which security control ensures that only authorized applications can decrypt the data?

A multinational corporation is subject to the General Data Protection Regulation (GDPR). The data protection officer is reviewing the data lifecycle for customer personal data. Which two actions are required to comply with GDPR's data minimization and storage limitation principles? (Choose two.)

A database administrator is evaluating storage subsystems for a mission-critical database. The database uses random I/O patterns for most operations. Which storage performance metric is most critical for this workload?

A data engineer is designing a table that stores sensor readings. Each row must include the sensor ID, a timestamp, and a numeric reading. The sensor ID and timestamp together uniquely identify each row, and the table will be queried frequently by sensor ID for the most recent reading. Which table design best supports this scenario?

When performing sentiment analysis on customer feedback, which preprocessing step is most critical to ensure words like 'Running' and 'run' are treated as the same base concept?

A data analyst is building a report that joins a fact table of retail transactions to a dimension table of products. The product dimension contains a column 'category' that is frequently used in filters, but the analyst notices that many transactions have a NULL category because the product was not properly matched during the ETL load. The analyst wants to ensure that all transactions appear in the report, even when the category is missing, and that missing categories are labeled as 'Uncategorized'. Which SQL join type and function combination should the analyst use?

A database administrator is defining the data lifecycle policy for a new deployment. What is the final step in the data lifecycle for records that have exceeded their retention period and are no longer required for regulatory compliance?

A company is conducting a data governance audit. Which TWO of the following items should be verified to ensure proper data security?

Which regulatory act specifically governs the protection of healthcare-related data?

Which THREE of the following are indicators of significant index fragmentation in a database?

A data analyst needs to retrieve a list of all unique countries from a Customers table, sorted alphabetically. Which SQL statement should be used?

A retail company is classifying its data assets for a new governance program. The legal team asks which category of data, if disclosed, would most directly trigger notification obligations under state breach-notification laws because it can identify a specific individual. Which classification should be applied?

An application requires an automatic timestamp for every row inserted into an 'Audit' table. Which feature should the developer implement?

A database administrator is deploying a new PostgreSQL 16 cluster for an online transaction processing (OLTP) application. The application must survive the loss of any single server in the primary data center without manual intervention, and the administrator wants the cluster to automatically promote a standby if the primary fails. The administrator has three servers available and wants to minimize the risk of split-brain. Which deployment approach should the administrator use?

Which TWO of the following are DDL (Data Definition Language) commands?

A data analyst needs to calculate the average order value per customer for the previous month. The Orders table contains CustomerID, OrderID, and OrderTotal. The analyst writes a query that includes the AVG(OrderTotal) function and groups by CustomerID. However, the result shows some customers with an average of NULL. Which condition could cause this?

Which THREE database configuration settings help ensure data security and regulatory compliance?

A new data engineer joins a team and needs read access to a production analytics database to troubleshoot a reporting issue. The security policy requires least privilege and time-bound access. Which approach best meets the policy?

You are migrating an on-premises Oracle database to Oracle Cloud Infrastructure (OCI) using a tool that requires the least amount of downtime while keeping the source and destination databases in sync. Which method should you select?

Which THREE items should be included in a standard database deployment checklist to ensure consistency across environments?

A production database is experiencing blocking and deadlocks during peak hours. The DBA suspects that long-running transactions are holding locks. Which dynamic management view (DMV) should the DBA query to identify the blocking chain and the SQL text of the blocking session?

A financial services firm allows analysts to query a data warehouse through a BI tool. Security policy requires that analysts see only customers in their assigned portfolio, even though all customer rows reside in one table. The warehouse supports session variables that carry the analyst's portfolio identifier. Which control should be implemented?

Which THREE of the following are common data types in relational databases?

You are implementing a data governance framework. Which role is primarily responsible for ensuring that the data quality, security, and lifecycle policies are defined and adhered to for a specific business domain?

Which TWO settings should be configured in a database deployment to improve performance for heavy read-intensive reporting workloads?

Which TWO of the following statements about SQL are true?

An administrator needs to restrict a user's ability to see only rows where the 'Region' column is 'North'. What feature should be used?

During a disaster recovery test, the DBA realizes the database restoration takes too long. Which action is the most effective way to improve the restore speed of a large database?

A database administrator is deploying a production environment and needs to restrict network access to the database server. Which tool or setting is the first line of defense at the network layer?

A query is slow due to excessive scanning of a large table. The execution plan shows a 'Table Scan'. What is the most likely missing element?

A healthcare analytics team stores patient records in a PostgreSQL database. A new analyst joins and must be able to query only the columns patient_id, visit_date, and diagnosis_code from the encounters table, but must be blocked from viewing patient_name and ssn. Which mechanism should the database administrator implement to meet this requirement?

A database administrator is deploying a new Oracle 19c database and needs to ensure that if the production server's power fails, no committed transactions are lost. The storage array has a write-back cache with a battery-backed unit. Which configuration should the administrator verify?

A developer needs to retrieve a list of all customers who have placed at least one order, along with the total amount spent by each. The 'Customers' table has one row per customer, and the 'Orders' table has one row per order with a 'CustomerID' foreign key. Which SQL construct should the developer use?

Which THREE of the following are examples of descriptive statistics?

A database administrator is troubleshooting a query that occasionally runs slowly. The query uses a parameterized stored procedure. The execution plan shows that sometimes the query uses an index seek and sometimes a clustered index scan. The DBA suspects parameter sniffing. Which action should the DBA take to mitigate the issue while preserving the ability to use the optimal plan for each execution?

A retail company is designing a data warehouse star schema for sales analysis. The team must choose attributes for the fact table and the dimension tables. Which TWO of the following should be placed in the fact table rather than a dimension table? (Choose two.)

You are planning the disk layout for a SQL Server production database. Which THREE best practices should you follow to optimize storage performance?

A database administrator is deploying a PostgreSQL 15 cluster that will serve a global application with users in North America, Europe, and Asia. The requirement is that reads be served with low latency from each region while writes are accepted in only one region. Which deployment architecture should the administrator choose?

A user wants to add a new column to an existing table named 'Employees'. Which SQL statement should be executed?

A database administrator is preparing to deploy a new Oracle Database 19c instance that will store regulated financial data. The compliance team requires that all data at rest be encrypted and that encryption keys be managed outside the database to satisfy separation-of-duties controls. Which deployment approach should the administrator use?

Which TWO indicators should be monitored when testing a database deployment to ensure it is correctly sized for performance?

When planning a backup strategy, a company requires that the Recovery Point Objective (RPO) be set to 15 minutes. Which backup schedule meets this requirement?

A report needs to identify the 'top 10' customers by total spend. In SQL, which clause would you use to return only the first 10 results after sorting by spend descending?

An analyst is using a CTE (Common Table Expression) to pre-aggregate sales data before performing a join. What is the primary benefit of using a CTE instead of a subquery in this scenario?

Which THREE of the following are benefits of implementing a standardized database schema migration process?

Which THREE of the following are types of data joins in SQL?

When planning database capacity, you calculate the projected growth of a table based on transaction volume and row size. What does 'database bloat' refer to in this context?

An application is experiencing deadlocks. Which strategy would effectively reduce the frequency of deadlocks?

When working with time-series data, what does a 'lag' feature represent in an analysis?

Which SQL command is used to permanently remove all rows from a table while keeping the table definition intact?

An organization must ensure that PII in a legacy SQL Server database is masked before being accessed by the HR analytics team. Which SQL Server feature should the DBA implement to ensure the data is obscured at the query level without changing the underlying storage?

Which THREE factors are critical to consider when configuring a database for disaster recovery (DR)?

Which THREE of the following are standard SQL aggregate functions?

A database administrator is reviewing a schema and needs to ensure data integrity. Which TWO of the following are valid SQL constraints that can be applied to a column to enforce data integrity? (Choose two.)

Which protocol should be enforced for all data-in-transit between application servers and database instances?

Which TWO of the following are common symptoms of an undersized transaction log file?

Which DML statement is used to update existing records in a database table?

Which TWO of the following are legitimate ways to optimize SQL query performance?

A company requires that all database backups be immutable for 7 years to meet regulatory audits. How can this be achieved?

A database administrator is planning the deployment of a new PostgreSQL 15 cluster that will support a high-volume web application. The application's workload is expected to be read-heavy, with a read-to-write ratio of approximately 10:1. The primary server has 64 GB of RAM, and the database size is estimated at 500 GB. To improve read performance and provide high availability, the administrator plans to implement streaming replication. Which configuration should the administrator implement to best meet these requirements?

In Power BI, which visualization component is best suited to show the relationship between two numerical variables and identify potential trends or clusters?

Which operator is used to search for a pattern in a string column?

During database testing, a developer finds that a specific query is performing poorly due to a lack of statistics. What action should be taken to update the database optimizer's knowledge of the table data distribution?

To improve read performance on a table frequently queried by a specific column, which database object should be created?

A company needs to implement centralized identity management for all database administrators. Which service is appropriate?

A database server is consistently hitting high CPU usage. You suspect an unoptimized query is causing a full table scan. Which tool should you use to capture the execution plan of currently running queries?

A database developer needs to combine the results of two different SELECT queries into a single result set. Which operator should be used?

A financial analyst runs a query that joins a 500-million-row transactions table to a 200-row currency table on a non-indexed currency code column. The optimizer chooses a nested loop join and the query runs for hours. Which execution strategy should the analyst encourage to improve performance most effectively?

Which SQL keyword is used to eliminate duplicate values from a query result set?

A database administrator is deploying a new online transaction processing (OLTP) database on a four-socket server with 512 GB of RAM and a storage area network (SAN) that uses solid-state drives. The application performs many small, random reads and writes. The administrator needs to configure the database instance to minimize physical I/O and improve response time. Which configuration should the administrator implement?

A database administrator is designing a backup strategy for a mission-critical database that must support point-in-time recovery. The database is in the FULL recovery model. They plan to take a full backup every Sunday, differential backups nightly, and transaction log backups every 15 minutes. A failure occurs on Wednesday at 10:07 AM. What is the minimum set of backups required to restore the database to the point of failure?

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?

A database administrator is reviewing a query that joins a large Sales table with a small Regions table. The query filters on a column in the Regions table and returns only matching rows from both tables. The administrator notices that the query plan shows a nested loop join. Which factor most likely influenced the optimizer's choice of this join method?

When decommissioning a legacy server, what is the most secure way to handle the hard drives containing sensitive data?

A database is configured with an 'Asynchronous Commit' replication mode. What is the potential risk during a primary server failure?

When creating an ETL pipeline, which stage involves validating that the data meets business rules (e.g., age must be positive)?

Which TWO controls are necessary to maintain the integrity of a data warehouse?

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?

A database administrator is designing a schema for a new order processing system. The system must enforce that every order is associated with exactly one customer, and that a customer can place many orders. Which relationship type and implementation should be used?

Which TWO of the following are common types of NoSQL databases?

What is the primary function of an index in a database?

Which THREE of the following SQL aggregate functions are commonly supported in most relational databases?

A database administrator is reviewing a schema for a reporting database. The team needs to ensure that queries against a large 'Sales' table return consistent results even if a long-running report is executing while new sales are being inserted. Which TWO of the following techniques help provide a consistent snapshot of data for the report? (Choose two.)

Which dashboarding practice helps prevent users from being overwhelmed by too much data at once?

Which THREE actions should be performed during a data incident response procedure involving a potential PII leak?

A municipal government agency must publish an open dataset of building permits. The governance policy requires that individual applicants not be re-identifiable from the published file, while aggregate statistics remain accurate. Which technique should the data steward apply before publication?

A data warehouse contains a fact table with billions of rows. Analysts frequently run aggregation queries that group by product_category and calculate total sales. These queries are slow because they scan the entire fact table. You need to improve performance without changing the queries. What should you implement?

When assessing data privacy compliance, which THREE categories of data are typically protected under laws like CCPA or GDPR?

A data engineer is profiling a customer table and finds that the 'email' column contains 12% NULL values, while the 'customer_id' column is fully populated and unique. The business rule states that every customer record must have a valid email address. Which action should the engineer take to enforce this rule in the pipeline?

Which THREE of the following are valid methods for securing cloud-based data storage?

A database administrator is planning a new SQL Server 2022 deployment that must support a read-only reporting workload without impacting the primary OLTP workload. The reporting queries are long-running and scan large tables. Which two features should the administrator implement to meet this requirement? (Choose two.)

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?

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?

A database team is designing a new table to store employee data. They need to ensure that the table structure supports efficient queries and maintains data integrity. Which two of the following statements about primary keys and indexes are true? (Choose two.)

A database developer is writing a query that must return the product_name and the total quantity sold for each product from a Sales table. The Sales table contains product_id, quantity, and sale_date. The Products table contains product_id and product_name. The query should include only products that have total sales quantity greater than 100. Which SQL statement is correct?

A multinational retailer stores customer loyalty data in a PostgreSQL cluster. Privacy counsel requires that EU customer records be purged automatically 30 days after account closure, while US records must be retained for seven years for tax purposes. Which data governance control best satisfies this requirement?

Which SQL command is used to add new records to an existing table?

What is the primary role of a Data Dictionary in a database management system?

Which of the following activities is performed during the 'Testing' phase of a database deployment to ensure the system meets its performance targets?

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?

A data analyst needs to retrieve the average order value for each region from a sales table. The table has columns region, order_id, and order_total. Which SQL statement correctly returns one row per region with its average order value?

A developer needs to calculate the running total of sales. Which SQL feature is most appropriate?

A DBA is designing a backup strategy for a database that must meet a recovery point objective (RPO) of 15 minutes and a recovery time objective (RTO) of 1 hour. The database is in the FULL recovery model. Which combination of backup types and scheduling best satisfies these objectives?

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?

A database administrator is planning the deployment of a new database server. The organization requires that the database be able to recover from a failure with minimal data loss, and the Recovery Point Objective (RPO) is 5 minutes. Which backup strategy should the administrator implement?

To implement a secure 'Multi-Tenant' architecture, which design approach is most effective for data separation?

Which THREE of the following are standard database maintenance tasks?

A database administrator is planning the deployment of a new Microsoft SQL Server 2022 instance for a financial application. The application requires that committed transactions survive a power loss and that the storage subsystem provide the lowest possible write latency. The administrator must choose the appropriate recovery model and disk configuration. Which combination best meets these requirements?

A multinational bank must demonstrate that its data processing activities are lawful and documented. An auditor requests evidence that each processing purpose has a documented legal basis, defined retention, and identified data subjects. Which artifact should the data governance team produce?

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?

Which THREE of the following are necessary prerequisites for implementing database mirroring in a SQL Server environment?

Which database backup strategy is recommended to ensure that you can restore a database to a specific point in time?

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?

A financial services firm must prove to regulators that its nightly ETL job loaded the same source data that was archived for a given business date, and that no record was altered after extraction. Which combination of practices best provides this non-repudiation evidence?

A financial services company is building a data governance program and must classify data assets so that protection controls map correctly to sensitivity. Which two actions are essential first steps in establishing data classification? (Choose two.)

A database administrator is responsible for a PostgreSQL database that stores financial transactions. The compliance team requires that all changes to the transactions table be recorded with the user, timestamp, and old and new values for auditing. The application performs INSERT, UPDATE, and DELETE operations. The DBA must implement a solution that captures all changes without modifying application code. Which PostgreSQL feature should the DBA use?

Which type of join should be used to return all records from the 'Left' table and only matching records from the 'Right' table?

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?

Which THREE of the following are essential components of a robust disaster recovery plan for a mission-critical database?

Which TWO tasks are critical before performing a major database schema upgrade?

A data analyst is querying a sales table with columns order_id, order_date, customer_id, and order_total. They need to return each order along with the running total of order_total for each customer, ordered by order_date. Which SQL feature should they use?

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?

During a database deployment, the administrator needs to ensure that audit logs are captured for all privileged user actions. Which component should be configured?

A database administrator is preparing the deployment plan for a new transactional database that must support point-in-time recovery and minimize data loss after a storage failure. The organization has approved additional storage for this purpose. Which TWO deployment measures best meet these requirements? (Choose two.)

A database is experiencing high I/O wait times on the log drive. What is the most effective configuration change to alleviate this bottleneck?

A company needs to audit all administrative actions taken on their database server. Which feature should the DBA enable?

A database administrator notices that the transaction log file is nearly full. Which operation will shrink the file to free up space?

A data analyst is writing a query to find the average order value (AOV) per customer from an orders table that includes cancelled and returned orders. They want to exclude those orders from the calculation but still show all customers, including those with no valid orders. Which SQL construct should they use to ensure customers with no valid orders appear with a NULL or zero AOV?

Which TWO actions constitute good data lifecycle management?

A data governance committee is defining the requirements for a new master data management program covering customer records across four business units. Which two practices are essential to maintain trustworthy golden records? (Choose two.)

A data analyst needs to calculate the total sales for each product category, but only for categories where the total sales exceed $10,000. The sales table contains columns sale_id, product_id, category, and amount. Which SQL query correctly produces the desired result?

When using Python's 'Matplotlib' library, which function is used to add a new figure to the canvas?

Which THREE of the following items should be monitored to identify performance bottlenecks in a database?

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?

A data systems team runs an analytics workload on a database that is also used for OLTP. Reports have become slower over time, and the team suspects that an index is being scanned instead of seeked. They want to capture the actual execution plan for a specific report query to confirm the operator used. Which action should they take?

A data analyst is using a subquery in the WHERE clause to filter customers who have placed at least one order. The subquery returns customer_id values from the orders table. Which SQL construct correctly expresses this requirement?

A healthcare analytics team stores patient records in a PostgreSQL database. A new compliance mandate requires that the database administrator be prevented from reading the contents of the 'diagnosis' column in plaintext, while the application must still be able to query and filter on that column. Which mechanism should the team implement?

A cloud-based SaaS provider stores customer data in a multi-tenant database. The provider wants to ensure that a customer's data is logically isolated from other customers' data, even though they share the same database schema. Which security control should be implemented to enforce this isolation?

Which TWO of the following scenarios are best suited for using a 'View'?

In the context of data quality, what does 'Completeness' refer to?

A data analyst is examining a query that uses a LEFT JOIN between a customers table and an orders table. The analyst notices that some customers appear multiple times in the result set. Which of the following is the most likely cause?

A database administrator is configuring a new SQL Server instance for a high-volume online transaction processing (OLTP) system. The administrator needs to optimize tempdb performance. Which two actions should the administrator take? (Choose two.)

Which database object is specifically designed to enforce referential integrity between two tables?

To prevent unauthorized access to specific columns in a 'Salary' table, which object should be created for the reporting team?

A financial services firm is implementing a data governance program and must classify data assets. Which two characteristics are used to determine whether data should be classified as confidential under a typical data classification policy? (Choose two.)

Which TWO of the following are common administrative tasks in a Data Governance program?

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?

Which THREE of the following are common types of database backups?

Which type of data is defined as 'sensitive' and usually requires enhanced protection?

A database administrator is asked to implement a mechanism that ensures data cannot be repudiated by a user who made changes to a financial ledger table. The mechanism must provide cryptographic proof of the origin and integrity of each transaction. Which approach should be used?

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?

A data platform team is implementing encryption for a new MongoDB cluster that stores customer PII. The security policy requires protecting data at rest and ensuring that compromised credentials for one tenant cannot expose another tenant's data. Which two measures should the team implement to meet these requirements? (Choose two.)

Which NoSQL structure is best suited for scenarios where data relationships are complex and frequently traversed?

Which TWO of the following are key considerations when configuring a database for high availability?

Which TWO of the following can be used to prevent unauthorized access to sensitive database tables?

Which THREE elements are essential when configuring Database Activity Monitoring (DAM)?

Which TWO database features are best used to handle massive volumes of data while maintaining fast query speeds for historical analytics?

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?

Which TWO of the following are common database maintenance tasks?

A database administrator needs to add a new column named 'loyalty_points' of type INTEGER to an existing 'customers' table without affecting existing rows' data. Which SQL statement should the administrator execute?

You are deploying a database that requires compliance with FIPS 140-2. What must be configured to ensure the database meets this security standard?

Which TWO of the following are examples of logical database design tasks?

You are managing a PostgreSQL database that supports a global application. Users in different regions experience slow query performance when filtering on a timestamp column that stores UTC values. You need to ensure that queries filtering on local time are efficient. What should you do?

An analyst joins a large fact table to a small lookup table using an inner join and notices that the result contains more rows than the original fact table. After reviewing the data, the analyst discovers that the lookup table contains duplicate values in its join key. Which action should the analyst take to ensure each fact row is matched to at most one lookup row?

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?

A database administrator is configuring a new SQL Server instance that will host a database with sensitive financial data. The compliance team requires that all data at rest be encrypted, and that the encryption keys be managed by a hardware security module (HSM). Which SQL Server feature should the administrator implement to meet these requirements?

Which technology provides the best protection against data exfiltration from an on-premises database by a rogue administrator?

You are a database administrator for a high-traffic e-commerce platform. The order history table has grown to 500 GB and queries filtering on order_date are performing poorly. You need to implement a partitioning strategy that allows you to quickly archive old orders by switching out partitions. Which partitioning approach should you use?

A database administrator is deploying a new database instance that will store sensitive customer information. The organization must comply with PCI DSS requirements for encryption of data at rest and in transit. Which TWO configurations should the administrator implement to meet these requirements? (Choose two.)

A database administrator needs to store unstructured data that scales horizontally across multiple servers while maintaining high availability. Which database structure should be selected?

A financial institution is designing a data governance program. The Chief Data Officer wants to ensure that data quality issues are identified and resolved before data is used in regulatory reporting. The organization has multiple business units that each own their data. Which data governance component should be established to formally assign accountability for data quality and define escalation paths?

Which scenario best justifies the use of a relational database over a NoSQL database?

When troubleshooting a performance bottleneck in a visualization, which action is most effective if the data source is a large SQL view?

A data analyst is using a window function to rank products by sales within each region. They write: RANK() OVER (PARTITION BY region ORDER BY sales DESC). They notice that products with identical sales receive the same rank, but the next rank is skipped (e.g., 1,2,2,4). They want to assign consecutive ranks without gaps. Which function should they use instead?

An administrator needs to restrict access to a specific S3 bucket so that only the Finance team can view files. Which configuration is the most effective approach?

A database administrator wants to check the current connection count. Which system catalog or dynamic management view would typically provide this information?

An analyst notices that a database query is performing full table scans even though a WHERE clause is present on a indexed column. What is a likely reason for this?

Which THREE of the following are standard SQL join types?

Which TWO of the following are potential issues when denormalizing a database?

A data analyst is building a monthly customer churn report. The churn definition is: a customer is churned in month M if they had at least one transaction in month M-3 but zero transactions in months M-2, M-1, and M. Which SQL approach correctly identifies churned customers for each month without missing customers who simply had no activity at all?

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?

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?

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?

When normalizing a database, what is the primary goal of moving data into a third normal form (3NF)?

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?

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?

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?

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?

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

Which THREE of the following are recognized data governance best practices?

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?

Which THREE of the following are components of a standard SQL SELECT statement?

A data engineer is designing a database for a sensor network that generates 20,000 readings per second. The schema is fixed, and each reading must be stored with minimal write latency while preserving the exact order of arrival for later time-series analysis. Which database structure should the engineer choose?

What is the benefit of using a 'Dashboard' over a static report?

A company is designing a distributed NoSQL database deployment using MongoDB. They need to ensure high availability and automatic failover in the event that a primary node becomes unreachable. What must the architect configure to achieve this?

Which tool provides a graphical interface for managing SQL Server instances, including configuring security and creating maintenance plans?

What is the primary purpose of a 'Normalization' process in database design?

Which document is essential to establish the legal basis for processing personal data under GDPR between a controller and a processor?

A data architect is designing a schema for a high-concurrency e-commerce system. They decide to use a surrogate key instead of a natural key for the Orders table to ensure join performance and data integrity. What is the primary advantage of this design choice?

Which THREE of the following are benefits of database indexing?

You are managing a database that uses transparent data encryption. If you need to back up the database, what additional component must be included to ensure the backup can be restored on a different server?

In data visualization, what is the 'Data-Ink Ratio' intended to improve?

A database administrator is investigating performance degradation during heavy concurrent write operations. Which isolation level provides the highest level of data consistency but the lowest concurrency?

In the context of database deployment, what is the primary purpose of a staging environment?

A DBA is designing a backup strategy for a large SQL Server database that must support point-in-time recovery and minimize backup windows. The database is in the full recovery model. Which two backup types should the DBA include in the strategy to achieve these goals? (Choose two.)

When analyzing JSON data stored in a relational database using SQL, how do you typically access nested key-value pairs?

A database administrator is deploying a new database on a cloud platform. The organization requires that the database automatically scale storage capacity as data grows, without manual intervention, and that it maintain high availability across multiple availability zones. Which cloud database deployment option should the administrator choose?

As a Data Privacy Officer, you are reviewing technical controls for data security. Which TWO of the following are best practices to ensure 'Privacy by Design' in a new application? (Select TWO)

A data engineer is deploying a new MySQL 8.0 instance that will store customer records for users in the European Union. The compliance team requires that all data at rest be encrypted and that encryption keys be managed separately from the database server. Which action should the engineer take?

When reviewing a database security configuration, which finding poses the highest risk?

A data systems technician is asked to recommend a partitioning strategy for a very large PostgreSQL table that stores time-series sensor readings. Queries almost always filter on the reading_timestamp column and return a narrow time window, while older data must remain available for compliance for seven years. The team wants the planner to prune partitions automatically and wants to keep maintenance simple. Which approach should the technician recommend?

An organization is deploying a new MySQL 8.0 database for an order-entry application. The team needs the database to enforce referential integrity so that an order row cannot reference a customer that does not exist. Which deployment step accomplishes this?

Which action represents a 'Data Lifecycle' phase where data is safely removed?

When joining two tables in SQL where you need to return all records from the left table even if there is no match in the right table, which JOIN type is appropriate?

To ensure non-repudiation in a database system, what must be captured for every transaction?

What is the first step in a Data Governance program?

An organization is deploying a database that requires a Recovery Time Objective (RTO) of less than one minute. Which backup and recovery strategy is most appropriate?

A DBA needs to automate the maintenance of database statistics in SQL Server. Which tool is the standard approach to manage this across all user databases?

What is the primary purpose of a Data Protection Impact Assessment (DPIA) under GDPR?

You are evaluating the data lifecycle of an organization's unstructured data. Which THREE actions should be included in a robust Data Retention and Disposal policy? (Select THREE)

In a multi-tenant cloud database, which mechanism ensures that tenant A cannot see data belonging to tenant B?

Which TWO of the following are common risks associated with performing a direct 'DROP' operation on a large production table?

A database developer is implementing a partitioning strategy for a large table containing historical financial records. The requirement is to allow for efficient archival of data older than five years. Which partitioning strategy should be chosen?

A cloud data platform team must prove to auditors that only approved personnel accessed a regulated dataset during a specific incident window. The platform uses role-based access control and stores query history in the warehouse. Which evidence source most directly demonstrates authorized access during that window?

Which TWO of the following are common types of data visualizations used to represent trends over time?

Which TWO of the following are common issues encountered when integrating data from disparate sources?

A database administrator is planning a disaster recovery strategy for a critical SQL Server database. The business requires a recovery point objective (RPO) of 15 minutes and a recovery time objective (RTO) of 1 hour. The database is currently using the simple recovery model. Which backup strategy should the administrator implement to meet these objectives?

A data analyst is reviewing a query that joins three tables: orders, customers, and products. The query returns more rows than expected. Which TWO of the following are likely causes of the row multiplication? (Choose two.)

Which of the following is a common symptom of a broken access control mechanism in a database?

A security team is implementing an access management strategy. Which THREE of the following are considered essential components of an effective Identity and Access Management (IAM) framework?

A database administrator is asked to create a new database that will store historical data and will not be updated frequently. The database will be read-only most of the time, and the administrator wants to minimize storage space and improve query performance for large scans. Which SQL Server feature should the administrator implement?

A DBA is configuring an Always On Availability Group in SQL Server. To ensure that the secondary node can be used for read-only reporting tasks, which setting must be correctly configured in the Availability Group replica settings?

A database administrator is deploying a new PostgreSQL 15 cluster on Linux and wants to protect against data loss if a single disk in the data volume fails. The server has four identical 1 TB SSDs available. Which RAID level should the administrator configure for the PostgreSQL data directory?

Which of the following is an example of an unstructured data source that a data analyst might need to ingest?

An organization uses a 'bring your own key' (BYOK) model for database encryption. What is the primary benefit?

Which THREE of the following are common SQL constraints?

A database administrator is deploying a new MongoDB 6.0 replica set to store product catalog data. The business requires that the database remain available for reads and writes even if a single data center becomes unreachable, and that writes be acknowledged by a majority of replica set members before returning success. Which deployment topology and write concern should the administrator configure?

A junior DBA runs the following statement against a production table that contains millions of rows with an active workload: TRUNCATE TABLE Sales.Transactions; What is the most important characteristic of this operation that the junior DBA should have understood before executing it?

A healthcare analytics team stores patient records in a PostgreSQL database. Auditors require that any change to the 'diagnosis' column be traceable to a specific database user and timestamp, without relying on application-level logging. Which mechanism should the data engineer implement to meet this requirement?

Which TWO technologies or methods are most effective at protecting sensitive data against insider threats?

You are deploying a MySQL database on Linux and want to ensure that the database files are isolated from system logs for performance reasons. Which MySQL configuration variable should you modify to specify the data directory location?

A database administrator needs to ensure that a query calculating running totals is performant. Which window function clause is essential to order the calculation sequence?

A database administrator is troubleshooting a query that runs slowly only when executed by a specific application login. The same query runs quickly when executed by the administrator's login. The administrator suspects parameter sniffing. Which action should the administrator take to resolve the issue while minimizing the impact on other queries?

A healthcare analytics team stores patient records in a PostgreSQL database. An auditor requires that any change to a patient's diagnosis code be permanently recorded with the identity of the user who made the change, and that this record cannot be altered or deleted by that same user. Which mechanism should the database administrator implement?

A data analyst is preparing a query to compare average order values across customer segments. Before running the analysis, the analyst reviews the dataset and finds that the OrderAmount column has missing values and extreme outliers. Which TWO actions should the analyst take to improve the reliability of the comparison? (Choose two.)

A database administrator is implementing an auditing solution for a SQL Server database that must track both successful and failed logins, as well as data modification statements on a specific table. The solution must minimize performance overhead and store audit data in a secure, tamper-evident format. (Choose two.)

A data analyst is querying a large partitioned table in Apache Hive that stores clickstream events. The table is partitioned by event_date and bucketed by user_id. The analyst needs to retrieve the latest event for each user within the last 30 days. The query currently uses a subquery with MAX(event_timestamp) and a join back to the table, but it runs slowly. Which optimization should the analyst apply to improve performance while preserving the correct result?

Which document outlines the 'Data Owner' responsibilities in a data governance framework?

A database is configured with a high-availability cluster. During a planned maintenance window, you need to fail over to the secondary node manually. Which command or action is used in a Windows Server Failover Cluster (WSFC) environment?

Which database feature is used to automatically maintain data integrity by ensuring that a value in a child table exists as a primary key in a parent table?

Which SQL clause is used to sort the result of a query by one or more columns?

A retail company's data governance team is defining roles for its new data stewardship program. The team needs to assign responsibility for day-to-day management of data quality, including resolving data issues and documenting business rules. Which role should be assigned this responsibility?

A data analyst runs a query against a PostgreSQL 15 data warehouse to calculate month-over-month revenue growth. The query uses the LAG window function partitioned by region and ordered by month. The analyst notices that the first month for each region returns a NULL growth value, which is causing downstream dashboard errors. Which action should the analyst take to handle this NULL value appropriately?

A data governance team is implementing an audit trail for a data warehouse. The team must ensure that every query against sensitive tables is recorded with the user identity, timestamp, and SQL text, and that the logs cannot be altered by database users. Which approach best meets these requirements?

A data engineer is reviewing a table that stores customer support tickets. The table has a column TicketID defined as an IDENTITY column and a column CreatedDate defined as DATETIME2 with a DEFAULT constraint of SYSDATETIME(). During a bulk insert of 500 rows, the engineer notices that all 500 rows received the exact same CreatedDate value, but each TicketID is unique and sequential. What is the most likely explanation for the identical CreatedDate values?

What does the 'P' in the acronym 'KPI' stand for?

A retail company's order-processing database runs on a single server with RAID 10 storage. The database is 800 GB, with a 20 GB transaction log. The recovery model is FULL, and log backups run every 15 minutes. During a power failure, the server shuts down unexpectedly. After restart, the database is marked as 'In Recovery' and takes 45 minutes to become available. The CIO wants to reduce this recovery time. Which action should the database administrator take first?

A database administrator needs to perform a schema-only export of a database. Which tool parameter is typically used for this purpose in mysqldump?

A data engineer is designing a table in a relational database to store product inventory records. The business requires that the combination of 'ProductCode' and 'WarehouseID' uniquely identifies each row, and that no duplicate pairs are allowed. Which constraint should be applied to enforce this requirement?

A company is using a cloud-based data lake. To satisfy data sovereignty requirements, they must ensure data remains in a specific region. How can this be enforced?

Which document is essential to create during the database planning phase to define the specific requirements, constraints, and business rules of the proposed system?

Which THREE of the following are components of a database connection string?

You are auditing data security controls. You find that raw sensitive data is being written to application logs by the ETL process. Which remediation step best aligns with the principle of 'Data Minimization'?

A production database server is running out of memory. Which setting in SQL Server limits the amount of memory the instance can consume from the OS?

To comply with CCPA, an organization must track the 'Right to Know' requests. Which component of a Data Governance Framework is most relevant?

An administrator needs to enforce password complexity on the database level. Where should this policy be configured?