Select Two (Multi-Select) Questions

DROP

Why it fails: DROP is a data definition language statement that removes database objects such as tables or views; it cannot appear within a SELECT statement's clause structure. It is tempting because it is a common SQL keyword, yet it is the correct answer when the question asks which command deletes an existing table definition rather than which components form a query.

GRANT

Why it fails: GRANT is DCL, controlling user privileges on database objects, not a clause within a SELECT statement. It is tempting because it is valid SQL syntax and often appears alongside SELECT in scripts, but the SELECT components are clauses such as FROM, WHERE and GROUP BY.

Filegroup backup

Why it fails: Filegroup backups can be useful for very large databases to reduce backup windows by backing up only specific filegroups, but they are not required for point-in-time recovery. They can be part of a strategy but are not one of the two essential types. The question asks for two types to achieve both goals; filegroup backups alone do not provide point-in-time recovery without transaction log backups.

Copy-only backup

Why it fails: A copy-only backup does not affect the log chain and is typically used for special purposes, such as backing up a database without disrupting regular backup routines. It does not support point-in-time recovery on its own and is not a primary component of a recovery strategy. It is not necessary for the stated goals.

Differential backup

Why it fails: Differential backups can reduce restore time by capturing only changes since the last full backup, but they are not strictly required for point-in-time recovery. The question asks for two types to achieve point-in-time recovery and minimize backup windows; differential backups help with the latter but are not mandatory. The essential types are full and transaction log.

Network cabling standards

Why it fails: Network cabling standards govern physical media, pinouts and transmission characteristics; they establish no identity proofing, authentication or authorisation. They are tempting because physical infrastructure underpins access, yet IAM components operate at the identity and entitlement layer, not the cabling layer.

Database partitioning

Why it fails: Database partitioning splits data across storage segments for performance and scalability; it grants no identity verification, authorisation or entitlement management. It is tempting where access control is conflated with data segregation, but partitioning operates on tables, not on who may authenticate or hold which permissions.

The query filters on a column that contains NULL values in the products table

Why it fails: Filtering on NULL values affects which rows are included or excluded, but it does not duplicate rows. NULL comparisons yield UNKNOWN, so such filters typically reduce the result set rather than expand it. This behavior is unrelated to the row multiplication observed when joining multiple tables.

The query orders the result by a column with duplicate values

Why it fails: ORDER BY only sorts the result set; it never changes the number of rows returned. Duplicate values in the sort column simply produce ties in ordering. Row multiplication originates from join cardinality, not from sorting, so this is not a plausible cause of the unexpected row count.

The query uses an INNER JOIN instead of a LEFT JOIN between orders and customers

Why it fails: Changing INNER JOIN to LEFT JOIN would add rows for unmatched customers, not multiply existing matches. INNER JOIN returns only matching rows, so it generally produces fewer rows than a LEFT JOIN. This is not a cause of row multiplication; it is a filtering behavior that can reduce the result set.

GROUP BY

Why it fails: GROUP BY is a query clause that aggregates rows, not a constraint. Constraints such as PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK and NOT NULL enforce data rules on columns. GROUP BY would be correct in a question about aggregation or SELECT statement syntax.

JOIN

Why it fails: JOIN is a query clause that combines rows from two tables, not a constraint. Constraints such as PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK and NOT NULL restrict column values. JOIN would be correct in a question about SELECT statement clauses or table-combination operations.

Snapshot

Why it fails: Snapshot is a storage mechanism capturing point-in-time state, not a backup type in the standard taxonomy of full, differential and transaction log backups. It is tempting because snapshots restore data quickly, and would be correct for rapid recovery or cloning, but the question asks for backup categories.

Hardware copy

Why it fails: Hardware copy describes physically duplicating media or appliances, not a database backup category; the standard types are full, differential and transaction log. It is tempting because physical replication underpins disaster recovery, and would be correct for site-level resilience, but it is not a database backup type.

Convert OrderAmount to a VARCHAR data type so that non-numeric entries can be stored alongside numbers.

Why it fails: Changing a numeric measure to a string prevents arithmetic operations such as AVG and SUM from working correctly, and it obscures the data quality issue rather than resolving it. Non-numeric entries should be identified and corrected or excluded, not accommodated by weakening the data type. This action would break the intended aggregation entirely.

Remove the customer segment column and analyze the entire population as a single group.

Why it fails: Dropping the segment column eliminates the very comparison the analyst is trying to make. It does not address missing values or outliers and destroys the analytical goal. The reliability problem concerns the measure within each segment, so the segmentation must be preserved while the data quality issues in OrderAmount are handled.

Increase the number of decimal places displayed in the report to make the averages appear more precise.

Why it fails: Adding decimal places changes only presentation and not the underlying data quality. Missing values and outliers continue to distort the computed averages regardless of how many digits are shown. Precision in display can even lend false confidence to biased numbers. The analyst must address the data itself, not the formatting of the output.

Use SQL Server Profiler to trace logins and data modifications, and save the trace to a file.

Why it fails: SQL Server Profiler is a tracing tool, not an auditing solution. It can capture events, but it is deprecated and not designed for secure, tamper-evident auditing. It also imposes performance overhead and requires manual management. It does not provide the built-in security and integrity features of SQL Server Audit, such as the ability to write to the Windows Security log.

Enable C2 audit mode to capture all successful and failed logins and data modifications.

Why it fails: C2 audit mode is a legacy auditing feature that captures a large number of events, including all successful and failed logins and object access. It can cause significant performance overhead and generate large volumes of audit data. It is not recommended for modern SQL Server implementations and does not allow granular scoping to a specific table. It also does not provide tamper-evident storage by default.

Implement a DDL trigger that logs all login events and data modifications to a custom table.

Why it fails: DDL triggers fire in response to data definition language (DDL) events, such as CREATE, ALTER, or DROP statements. They do not fire on login events or DML statements like INSERT, UPDATE, or DELETE. A DML trigger could capture data modifications, but it would not capture logins. This approach would require custom code and would not provide tamper-evident storage or the built-in security of SQL Server Audit.

Enable database auditing to track all SELECT statements on customer tables.

Why it fails: Auditing is important for compliance and monitoring, but it does not encrypt data at rest or in transit. PCI DSS requires encryption to protect data confidentiality, not just auditing access. While auditing can help detect unauthorized access, it does not satisfy the encryption requirements. It is a complementary control, not a substitute for encryption.

Configure the database to use only strong password hashing for authentication.

Why it fails: Strong password hashing protects authentication credentials, but it does not encrypt the customer data stored in the database or transmitted over the network. PCI DSS requires encryption of cardholder data at rest and in transit, which is separate from protecting login credentials. Password hashing is a good practice but does not meet the specific encryption requirements.

Implement column-level encryption for all columns in the database.

Why it fails: Column-level encryption can protect specific sensitive fields, but encrypting all columns is impractical and would severely impact performance and application logic. It is not a requirement for PCI DSS; TDE and SSL/TLS are more appropriate for broad at-rest and in-transit encryption. Column-level encryption is typically used for a few highly sensitive attributes, not as a blanket solution.

SELECT

Why it fails: SELECT is a data retrieval clause that returns rows, not a function that reduces them to a single summary value. It is tempting because SELECT often appears alongside aggregates, but the standard aggregate functions are COUNT, SUM, AVG, MIN, and MAX.

DELETE

Why it fails: DELETE removes rows from a table and returns no aggregate value, so it cannot summarise a column. It is tempting because it is a common SQL statement, but the aggregate functions are COUNT, SUM, AVG, MIN, and MAX, which collapse multiple rows into one result.

Office Security Badge Access

Why it fails: Physical building access control governs who enters premises; it does not define data ownership, stewardship, quality rules or policy enforcement, which are the framework's recognised components. It is tempting because governance does require access controls, but those are logical data-access permissions, not office badge readers.

Hardware Procurement Policy

Why it fails: A hardware procurement policy governs purchasing and asset lifecycle, not data stewardship, ownership, or quality controls, so it sits outside any data governance framework. It is tempting because procurement policies are genuine IT governance artefacts, and would be the right answer if the question asked about infrastructure governance or vendor management rather than data governance.

Network Throughput Monitoring

Why it fails: Network Throughput Monitoring measures packet flow and bandwidth utilisation, which belongs to network operations rather than data governance. It is tempting because monitoring underpins data observability, and throughput metrics would be the right choice when diagnosing latency, capacity or performance issues across infrastructure links.

CONCAT

Why it fails: CONCAT is a scalar string function that joins two or more values into one, returning a value per row rather than collapsing rows. It is tempting because it appears in SELECT lists alongside aggregates, but aggregate functions such as COUNT, SUM, AVG, MIN and MAX operate across row sets to produce a single summary value.

SUBSTR

Why it fails: SUBSTR is a scalar string function that extracts a substring from a column value per row; it does not summarise groups of rows. It is tempting because it is commonly used in SELECT statements with aggregates, but true aggregate functions such as COUNT, SUM, AVG, MIN and MAX reduce many rows to one result.

Publicly listed company stock prices

Why it fails: Publicly listed stock prices are public information, not personal data, so CCPA and GDPR do not protect them. It is tempting because financial data appears sensitive, but these laws protect personal data relating to identifiable individuals, not market disclosures.

Internal server IP addresses

Why it fails: Internal server IP addresses identify infrastructure rather than a natural person, so they fall outside GDPR and CCPA personal-data definitions unless linked to an identifiable individual. It is tempting because internal addressing feels confidential, but confidentiality differs from the privacy-law scope tested here.

Application codebase version

Why it fails: The application codebase version belongs to the application release pipeline, not the database deployment checklist, which covers schema scripts, configuration and rollback artefacts. It is tempting because versioning application code does ensure consistency across environments, but that is a CI/CD concern rather than a database deployment item.

The name of the database lead developer

Why it fails: Naming the database lead developer records an ownership contact, not a deployment artefact, so it cannot enforce cross-environment consistency. It is tempting because identifying an accountable owner is genuinely useful for escalation and change approval, but that belongs in a runbook or support matrix, not a deployment checklist.

The brand of the server hardware

Why it fails: Disaster recovery planning depends on recovery point and recovery time objectives, replication topology and failover capacity, none of which the server hardware brand affects. It is tempting because hardware does influence performance, but DR configuration is driven by RPO, RTO and data replication design.

The database developer's IDE preference

Why it fails: Disaster recovery configuration is governed by RPO, RTO, replication method and failover site capacity; the developer's IDE preference has no bearing on recovery behaviour. It is tempting because tooling affects day-to-day development, but DR planning addresses data durability and service restoration, not authoring environments.

Increasing the number of database replicas

Why it fails: Replicas improve availability and read throughput, not integrity: they faithfully copy corrupted or duplicated rows to every node. Replication is tempting because it is the right control when the requirement is resilience or query scalability rather than accuracy of the stored data.

Running backups every hour

Why it fails: Hourly backups restore availability after loss or damage; they do not prevent invalid, duplicate or orphaned records entering the warehouse. Backup frequency is tempting because it is the correct control when the stated risk is accidental deletion or media failure rather than data correctness.

Using a specific naming convention for servers

Why it fails: A server naming convention aids inventory and operational consistency; it imposes no constraint on the values loaded into warehouse tables. Naming standards are tempting because they are the right control when the requirement is asset tracking, configuration management or audit traceability of infrastructure.

Relational

Why it fails: Relational databases enforce fixed schemas and SQL joins, which is precisely what NoSQL alternatives reject; the common NoSQL types are key-value, document, column-family and graph. It is tempting because relational systems dominate general database work, but the question asks specifically for NoSQL categories.

Hierarchical

Why it fails: Hierarchical is a legacy navigational database model, not a NoSQL category; NoSQL types are key-value, document, column-family and graph. It is tempting because early mainframe databases such as IMS used parent-child hierarchies, so the term appears in database-history material rather than modern NoSQL taxonomy.

Network

Why it fails: Network is the CODASYL navigational model using sets and pointers, not a NoSQL category; NoSQL types are key-value, document, column-family and graph. It is tempting because graph databases also store relationships, but network databases predate NoSQL and lack its schema-flexible, distributed design.

Set the tempdb filegrowth to a small percentage to save disk space.

Why it fails: Setting a small percentage growth for tempdb can cause frequent file growth operations, which are expensive and can lead to fragmentation and performance degradation. Best practice is to pre-size tempdb files appropriately and use a fixed growth increment in megabytes. A small percentage growth does not optimize performance and may cause interruptions during high-volume operations.

Set the tempdb recovery model to FULL.

Why it fails: tempdb uses the SIMPLE recovery model and cannot be changed to FULL. tempdb is recreated at every SQL Server restart, so it does not support transaction log backups or point-in-time recovery. Setting it to FULL is not possible and would not improve performance. This option is technically invalid for tempdb.

Place the tempdb transaction log on the same volume as the data files.

Why it fails: Placing the tempdb log on the same volume as data files creates I/O contention between sequential log writes and random data file I/O. Best practice is to separate tempdb data and log files onto different physical volumes to maximize throughput. This action would degrade performance rather than optimize it, so it is not recommended.

Rebooting the server weekly

Why it fails: Rebooting a server is an infrastructure action, not a database maintenance task; maintenance covers index rebuilds, statistics updates, backups and integrity checks. It is tempting because restarts do clear stuck sessions and memory pressure, which is a valid operational remedy for a hung instance, but it addresses no database-level upkeep.

Renaming every table

Why it fails: Renaming every table breaks application queries, views, stored procedures and foreign keys, so it is a schema migration, not maintenance. It is tempting because renaming a single table is occasionally needed, but bulk renaming is a development change, not a recurring upkeep task.

Deleting all users

Why it fails: Removing all users destroys the database's access model and data ownership, so it is never routine maintenance. It is tempting because user administration is a real DBA duty, but that means granting, revoking and auditing accounts, not wholesale deletion.

The color of the server chassis

Why it fails: Chassis colour is a physical aesthetic attribute with no relationship to replication, failover, or uptime. It is tempting because hardware selection genuinely matters for availability, so any server-related attribute can appear relevant, but colour never influences redundancy or recovery behaviour.

Number of database users

Why it fails: User count affects licensing and capacity planning, not the mechanisms that keep a database reachable, such as replication, clustering, or failover. It is tempting because scaling and load do influence performance, and user numbers are a common sizing input, but they do not deliver high availability.

The database name

Why it fails: The database name is an identifier used for connection strings and object qualification; it has no bearing on failover, replication, or redundancy. It is tempting because naming conventions matter during deployment, and the name is required when creating the database, but it does not affect availability.