Hard Difficulty Questions

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

Why it fails: A fixed-column table forces every category to share the same columns, resulting in many NULLs and requiring schema changes whenever a new attribute is introduced. This conflicts with the need for frequent schema evolution without downtime and does not accommodate highly variable attributes per category efficiently.

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

Why it fails: An entity-attribute-value table stores attributes as rows, which provides flexibility but typically results in complex queries with many self-joins and poor performance at scale. It also loses type safety and constraints, making it a poor fit compared to a document store for this scenario despite superficial flexibility.

A star schema with a dimension table per attribute

Why it fails: A star schema with a dimension per attribute would require creating and maintaining a dimension table for every new attribute, which is operationally heavy and still requires schema changes. It is designed for analytical querying of structured dimensions, not for flexible, frequently changing attribute sets across many categories.

Missing WHERE clause

Why it fails: A WHERE clause filters rows before aggregation and has no bearing on whether SELECT columns are aggregated or grouped. WHERE is the right tool for restricting input rows, while the reported error concerns grouping semantics in the SELECT list.

Incorrect join type

Why it fails: Join type governs row matching between tables, not column aggregation validity, so it cannot trigger this error. Choosing inner, left or full joins is correct when combining tables, whereas the error concerns SELECT columns absent from GROUP BY.

Using an alias in WHERE

Why it fails: Aliases are not permitted in WHERE because that clause is evaluated before SELECT, but this produces an invalid-column error, not the aggregate/GROUP BY message. Aliases belong in ORDER BY or HAVING, where the projection is already resolved.

Table naming conventions

Why it fails: Table naming conventions are cosmetic and easily mapped during migration; they do not cause DDL failures. They tempt because schema comparisons highlight naming differences, but the real blocker is incompatible column data types or unsupported constraint syntax between the two database platforms.

Storage engine choice

Why it fails: Storage engine choice is an internal implementation detail of the source or target platform, not a DDL statement carried in the schema. It tempts because engines affect performance, but migration failure stems from DDL incompatibilities such as unsupported data types or constraint definitions.

Primary key naming

Why it fails: Primary key naming is cosmetic; renaming constraints during migration is trivial and rarely blocks data movement. It tempts because DDL scripts visibly differ between platforms, but the actual failure driver is incompatible data types or constraint syntax, not identifier labels.

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

Why it fails: Manually splitting the data into yearly tables and unioning them can reduce scan scope, but it pushes partition management into application and view logic. Storage tiering is possible only by moving whole tables, and the plan for a UNION ALL view must touch every branch, so queries without a date predicate become slower and maintenance grows with each year. This is a hand-rolled approximation of partitioning.

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

Why it fails: Compression reduces the footprint of the current table but does not separate the oldest year onto different storage, and moving data to a separate database means it is no longer queryable in place. Restoring on demand introduces downtime and operational overhead that the requirement explicitly rules out. This option trades availability for cost rather than achieving both.

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

Why it fails: Columnstore indexes skip column segments that a query does not reference, but they do not skip rows by date unless rowgroup elimination happens to align with the filter, which is not guaranteed. More importantly, an unpartitioned table cannot be moved filegroup by filegroup, so the oldest year cannot be relocated to cheaper storage without rewriting the whole table. This option addresses scan efficiency but not storage tiering.

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.

Implement database mirroring using the primary-secondary synchronous replication setting.

Why it fails: MongoDB has no primary-secondary mirroring mode; replication is achieved through replica sets, whose elections promote a secondary automatically. Synchronous replication describes a write concern, so it would be relevant when tuning durability guarantees rather than failover.

Increase the write concern to 'all' to force nodes to write simultaneously.

Why it fails: Write concern governs how many nodes acknowledge a write before it returns success; it does not elect a new primary when the current one becomes unreachable. Raising it to 'all' would be the right choice when the priority is preventing rollbacks on acknowledged writes.

Configure a sharded cluster with a single mongos instance.

Why it fails: Sharding distributes data across shards for horizontal scale, and a single mongos is a routing tier with no failover role; neither provides automatic primary election. A sharded cluster would be correct when dataset size or throughput exceeds one replica set's capacity.

It prevents the insertion of duplicate records

Why it fails: Unique constraints and primary keys prevent duplicates, but the type of key does not change this capability.

It ensures that the database complies with ACID requirements

Why it fails: ACID compliance is a function of transaction management, not primary key selection.

It automatically creates a clustered index on the table

Why it fails: While often used as a clustered index, a surrogate key does not automatically create one without an explicit definition.

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.

Use data compression

Why it fails: Compression shrinks data pages and backups, not the sequential transaction log records written on commit. It is tempting because it reduces storage and I/O generally, but log writes are append-only and already minimal; the correct fix targets log write latency itself.

Increase the buffer pool size

Why it fails: Buffer pool memory caches data pages, so enlarging it reduces reads from data files, not writes to the transaction log. It is tempting as the classic I/O tuning knob, but the bottleneck is the log drive, which buffer pool sizing does not touch.

Reduce the database recovery interval

Why it fails: The recovery interval governs how long the engine waits before checkpointing, not log write volume; shortening it can actually increase log flush frequency. It is tempting because it sounds like tuning recovery behaviour, but that setting addresses crash-recovery time, not the log drive's write throughput.

Database Clustering

Why it fails: Clustering replicates data across nodes for high availability and failover; every node holds a readable copy, widening rather than closing the exfiltration path. It is selected when uptime and automatic failover, not insider threat, are the requirement.

Database Archiving

Why it fails: Archiving moves aged data to secondary storage for retention and cost control; the rogue administrator retains access to the archive, so exfiltration remains possible. It is the right choice for meeting retention policies or shrinking the active dataset.

Database Sharding

Why it fails: Sharding partitions data across nodes for scalability and query distribution; it grants an administrator with access to any shard the same readable data, so it does not restrict exfiltration. It is chosen to spread load or grow beyond one server's capacity.

plt.axis()

Why it fails: plt.axis() sets or retrieves axis limits and toggles axis visibility; it does not create a figure. It is tempting because it manipulates the plot's axes, but adding a new figure requires plt.figure(), which instantiates the canvas object that subsequent plotting calls draw onto.

plt.plot()

Why it fails: plt.plot() draws data series onto the current axes; it does not create a figure. plt.figure() adds a new figure to the canvas. plot() is tempting because it is the first function most users learn for rendering a line chart, and it will implicitly create a figure only when none exists.

plt.show()

Why it fails: plt.show() renders already-created figures in an interactive window; it does not add a figure to the canvas. plt.figure() performs that creation. show() is tempting because it appears at the end of nearly every Matplotlib script, so it is easily mistaken for the step that produces the figure.

The change management tickets approved during the incident window for the platform team.

Why it fails: Change tickets document intended platform modifications, not data reads. They can show that a change was authorized, but they say nothing about who queried the regulated dataset or whether those queries were within granted privileges, so they are supporting evidence at best.

The data catalog's lineage graph showing which downstream reports consume the regulated dataset.

Why it fails: Lineage describes data flow and dependencies, not individual access events. It can help scope impact after an incident, but it cannot prove who accessed the dataset during a specific window or whether that access was authorized, so it does not satisfy the audit request.

The identity provider's sign-in logs showing which users authenticated and from which IP addresses.

Why it fails: Authentication logs prove who signed in, but they do not show which dataset was queried or what data was returned. A user could authenticate and never touch the regulated table, or could access it through a service account, so these logs alone cannot demonstrate authorized access to the specific dataset.

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.

Easier database installation

Why it fails: BYOK adds key vault configuration, key permissions and rotation steps, so provisioning involves more setup rather than less. It is tempting because the feature is configured during deployment, but its purpose is customer-controlled key custody for encryption at rest, not simplifying installation.

Improved query performance

Why it fails: Encryption key custody has no bearing on query execution plans, indexing or caching, so latency and throughput remain unchanged. It is tempting because BYOK is often bundled with premium database tiers that do include performance features, but the key model itself governs who controls the master key, not how queries run.

Lower storage costs

Why it fails: Key management does not change how data pages, indexes or backups are stored, so billed storage volume is unaffected. It is tempting because BYOK is offered on premium tiers, but the model addresses who owns and rotates the encryption key, not the cost of the bytes persisted.

GROUP BY

Why it fails: GROUP BY collapses rows into aggregated groups and cannot produce a per-row running total alongside detail rows. It is the right clause when the requirement is a single summary value per group, not a cumulative sequence across ordered rows.

PARTITION BY

Why it fails: PARTITION BY splits the result set into independent groups so the window function restarts per group; it does not establish the order in which rows accumulate. It is the correct clause when running totals must reset for each category, such as per account or per department.

ROWS BETWEEN

Why it fails: ROWS BETWEEN defines the frame boundaries of rows included in each calculation, not the sequence in which rows are processed. It is essential for controlling sliding or cumulative frames, and would be the correct clause when the requirement is to limit which rows each running total aggregates.

SHUTDOWN WITH NOWAIT

Why it fails: This stops the server, which is not the same as a controlled failover.

ALTER SERVER ROLE

Why it fails: This is for security roles, not node failover.

RESTORE DATABASE

Why it fails: This is for recovery from a backup file.

Key-Value

Why it fails: Key-value stores retrieve data by a single key with no traversal of relationships between records, so complex, frequently traversed relationships cannot be followed. It tempts for its speed on simple lookups, but graph databases are the correct choice for relationship-heavy queries.

Column-family

Why it fails: Column-family stores group related columns into families but do not model or traverse relationships between entities; graph databases do, using edges and index-free adjacency. It is tempting because column-family suits wide, sparse datasets queried by row key at scale, such as time-series or analytics workloads, not relationship traversal.

Document

Why it fails: Document stores group related fields into self-contained documents and query by document content, not by links between records, so traversing complex relationships is inefficient. It tempts because nested documents appear relational, but graph databases model traversals explicitly.

Oracle Transparent Data Encryption (TDE)

Why it fails: Transparent Data Encryption encrypts data at rest, protecting against unauthorized access to storage media. It does not record who modified data or prevent users from denying their actions. While TDE is important for confidentiality, it is irrelevant to the traceability and non-repudiation requirements described in the scenario.

Oracle Audit Vault and Database Firewall

Why it fails: Audit Vault and Database Firewall centralizes audit data collection and monitors SQL traffic, but it does not inherently ensure that database users cannot deny their actions. It can detect and alert on suspicious activity, yet non-repudiation requires a mechanism that binds users to their transactions at the database level. This solution is more about monitoring and consolidation than about proving individual accountability.

Oracle Database Vault

Why it fails: Database Vault restricts access to application data and enforces separation of duties by preventing privileged users from accessing data they do not own. It does not provide non-repudiation for transactions or tie modifications to individual users in an auditable way. In this scenario, the requirement is to trace changes to specific users and prevent denial, which is an auditing and authentication concern, not access restriction.

Change the primary key to a surrogate auto-increment column and create a secondary index on (customer_id, order_date).

Why it fails: Changing the primary key to a surrogate key would require rebuilding the table and all secondary indexes, a massive operation on a 500 GB table. It also does not directly address the locking issue for range updates on order_date. While it could reduce page splits on inserts, the scenario focuses on update performance and locking, which are better addressed by partitioning or adjusting transaction isolation.

Set the transaction isolation level to READ UNCOMMITTED for the batch process.

Why it fails: READ UNCOMMITTED reduces read locks but does not eliminate write locks for UPDATE statements. InnoDB still acquires exclusive locks on modified rows. Moreover, this isolation level can cause dirty reads and is generally unsafe for production. It does not address the root cause of locking during range updates and may introduce data consistency issues for concurrent readers.

Increase the innodb_buffer_pool_size to cache the entire table in memory.

Why it fails: Increasing the buffer pool can reduce disk I/O, but a 500 GB table likely exceeds available RAM, and the buffer pool only caches pages, not entire tables. Locking during updates is a concurrency issue, not purely an I/O one. Even with all pages cached, InnoDB still holds row locks for the duration of the transaction, so this does not solve the locking problem.

Set the synchronous commit mode to 'Asynchronous' on all nodes.

Why it fails: Asynchronous commit governs data synchronisation and potential data loss, not read access; it does nothing to enable read-only routing on the secondary. It is tempting because asynchronous replicas are often readable, and it would be correct when the requirement is to minimise primary transaction latency across distant replicas.

Set to 'Yes' and force all writes through the secondary replica.

Why it fails: A readable secondary is read-only; it cannot accept writes, so forcing writes through it contradicts Always On's replication model and breaks the workload. It is tempting because offloading traffic from the primary is the stated goal, and it would be correct if the requirement were to offload read-only queries only.

Set to 'No' and route traffic via a secondary listener.

Why it fails: Setting Readable Secondary to No disables read access entirely, so the secondary cannot serve the read-only workloads the scenario requires. It is tempting because a secondary listener still routes connections, and this configuration would be correct when the secondary exists purely for high availability and disaster recovery.