Database Structures And SQL practice questions

NULLIF

Why it fails: NULLIF returns NULL if two expressions are equal.

IFNULL

Why it fails: IFNULL is vendor-specific (MySQL).

ISNULL

Why it fails: ISNULL is vendor-specific (T-SQL) and less portable than COALESCE.

GRANT

Why it fails: GRANT is used for permissions.

SAVEPOINT

Why it fails: SAVEPOINT allows partial rollbacks.

BEGIN

Why it fails: BEGIN starts a transaction but doesn't finalize it.

DENY

Why it fails: DENY is vendor-specific (like T-SQL) rather than a general DCL command.

DROP

Why it fails: DROP deletes objects, not access rights.

REMOVE

Why it fails: REMOVE is not a standard DCL command.

Scalability

Why it fails: Scalability describes a system's capacity to grow under load, not a transactional guarantee, so it cannot be an ACID property. It is tempting because distributed databases are often marketed on scalability, and horizontal scaling is a genuine design goal — but ACID concerns atomicity, consistency, isolation and durability only.

Availability

Why it fails: Availability concerns continuous service uptime, whereas ACID's durability guarantees committed data survives failure — related but distinct. It is tempting because highly available clusters are commonly discussed alongside transactions, and availability would be the correct focus when designing for fault tolerance rather than transactional correctness.

SELECT p.category, SUM(o.order_total) AS total_sales FROM Orders o JOIN Products p ON o.product_id = p.product_id WHERE o.order_date >= DATEADD(quarter, -1, GETDATE()) ORDER BY p.category;

Why it fails: This query filters and joins correctly but uses ORDER BY instead of GROUP BY. Without GROUP BY, the SUM aggregate would either error or return a single total for all categories, not per-category totals. It fails to produce the required grouped results.

SELECT p.category, SUM(o.order_total) AS total_sales FROM Orders o JOIN Products p ON o.product_id = p.product_id WHERE o.order_date >= DATEADD(quarter, -1, GETDATE()) GROUP BY p.category HAVING SUM(o.order_total) > 0;

Why it fails: The HAVING clause filters out categories with zero or negative total sales. While it may seem harmless, it changes the result set by excluding categories that had no sales in the last quarter. The requirement is to return total sales for each category, including those with zero, so this is incorrect.

SELECT p.category, SUM(o.order_total) AS total_sales FROM Orders o JOIN Products p ON o.product_id = p.product_id WHERE o.order_date >= DATEADD(quarter, -1, GETDATE()) GROUP BY p.category, o.order_total;

Why it fails: This query includes o.order_total in the GROUP BY clause, which groups by each individual order total in addition to category. This produces multiple rows per category and does not yield a single sum per category, defeating the purpose of aggregation.

EXTRACT(DAY FROM CURRENT_DATE - last_purchase_date)

Why it fails: Subtracting two DATE values in PostgreSQL yields an interval, and EXTRACT(DAY FROM interval) returns only the days component of that interval, not the total number of days. For example, an interval of '190 days' would return 190, but an interval of '1 year 20 days' would return 20. This would incorrectly filter customers whose last purchase was over a year ago, making it unsuitable for the 180-day condition.

DATEDIFF(CURRENT_DATE, last_purchase_date)

Why it fails: DATEDIFF is a function supported by SQL Server and some other platforms, but PostgreSQL does not implement DATEDIFF natively. Using it in PostgreSQL 15 produces a syntax error, so the query would fail rather than return zero rows. The analyst's symptom of zero rows indicates the query executed, so switching to DATEDIFF would not solve the underlying date arithmetic problem in this scenario.

DATE_ADD(CURRENT_DATE, INTERVAL -180 DAY)

Why it fails: DATE_ADD is a MySQL function and is not available in PostgreSQL. Even if it were, this expression adds a negative interval to the current date, yielding a single date 180 days ago, not a per-row difference. It would need to be compared to last_purchase_date, but the function itself is invalid in PostgreSQL, so the query would error out rather than return zero rows.

Rewrite the query to use a correlated subquery instead of a join

Why it fails: A correlated subquery is typically executed once per outer row, which for a large Transactions table can be even slower than the current nested loop join. It also risks changing the result set if multiple matching rows exist in CurrencyRates, because a scalar subquery expects a single value. This does not address the underlying I/O problem.

Convert the join to a CROSS JOIN and filter in the WHERE clause

Why it fails: A CROSS JOIN produces the Cartesian product of both tables before filtering, which for a large Transactions table would generate an enormous intermediate result and likely perform far worse. It also changes semantics, because a Cartesian product combined with a WHERE filter is not equivalent to an inner join on the intended key unless the predicate is carefully written.

Add a HASH JOIN hint to force a hash join instead of nested loops

Why it fails: Forcing a hash join can be counterproductive when the outer input is large and the inner input is small with a useful index, because the optimizer may already have chosen nested loops for good reason. A hash join builds a hash table on one input, which may consume significant memory, and the hint does not address the missing index that causes the slow seeks.

UNION

Why it fails: UNION combines the result sets of two or more SELECT statements and removes duplicates across the combined set. It is not used to deduplicate a single result set. Using UNION would require writing two separate queries and would not be the appropriate tool for returning a distinct list of customers from one table.

GROUP BY CustomerID

Why it fails: GROUP BY groups rows with the same CustomerID into a single row, which would achieve the deduplication effect. However, it is typically used with aggregate functions and would require all selected columns to be either aggregated or included in the GROUP BY. For a simple distinct list, GROUP BY is unnecessarily complex and may not be the most efficient or readable solution.

HAVING COUNT(*) > 0

Why it fails: HAVING is used to filter groups after a GROUP BY operation. Without a GROUP BY, it is not applicable. Even with GROUP BY, HAVING COUNT(*) > 0 would filter out groups with zero rows, but every group formed by GROUP BY already has at least one row, so this condition is redundant. It does not address deduplication directly.

READ COMMITTED

Why it fails: READ COMMITTED prevents dirty reads but allows non-repeatable reads, meaning the same query can return different results if another transaction commits between reads. It does not provide a single consistent snapshot for the duration of the transaction, so it fails the requirement for stable reporting data.

READ UNCOMMITTED

Why it fails: READ UNCOMMITTED allows a transaction to see uncommitted changes from other transactions, producing dirty reads. It provides the least consistency and cannot guarantee a stable snapshot, since rows may change or disappear as concurrent transactions commit or roll back. This level is unsuitable for the reporting consistency the administrator requires.

REPEATABLE READ

Why it fails: REPEATABLE READ guarantees that rows read once will not change during the transaction, but it may still allow phantom reads where new rows appear in subsequent queries. Because phantoms can alter aggregate results, it does not fully guarantee the same snapshot across all reads in the reporting transaction.

Use the DISTINCT keyword in the SELECT clause.

Why it fails: DISTINCT eliminates duplicate rows from the result set, but it would not prevent duplicate customer rows when a customer has multiple orders because the order details differ, making each row unique. Applying DISTINCT could also inadvertently remove legitimate duplicate order lines. It does not address the core need to include all customers and avoid duplication from the one-to-many relationship.

Use a FULL OUTER JOIN between Customers and Orders.

Why it fails: A FULL OUTER JOIN returns all rows from both tables, including unmatched rows from both sides. While it would include all customers, it would also include orders that have no matching customer, which is not required and could introduce unwanted rows. Additionally, it does not inherently prevent duplicate customer rows when a customer has multiple orders; the join still produces one row per order, so aggregation or DISTINCT would be needed.

Use a RIGHT JOIN from Customers to Orders.

Why it fails: A RIGHT JOIN from Customers to Orders returns all rows from the right table (Orders) and matching rows from the left table (Customers). Customers without orders would be excluded because they have no matching order. This does not meet the requirement to include all customers. To include all customers using a RIGHT JOIN, the tables would need to be swapped, but as written, it fails the scenario.

Add a hint to force the use of the index on category.

Why it fails: While index hints can force the use of an index, they are not supported in all databases (e.g., PostgreSQL does not have hints). Even in databases that support hints, forcing an index when the optimizer chose a sequential scan might not be optimal if the statistics are wrong. The better approach is to correct the statistics so the optimizer can make the right decision. Hints can also lead to degraded performance if data distribution changes.

Partition the Products table by category.

Why it fails: Partitioning by category could improve performance for queries filtering on category, but it is a significant schema change and may not be necessary if the issue is simply stale statistics. Partitioning adds complexity and might not be supported in all database systems. For a 100,000-row table, partitioning is overkill; updating statistics is a simpler and more direct solution to the optimizer's misestimation.

Create a composite index on Products(category, product_id).

Why it fails: A composite index on (category, product_id) could help if the query needed to retrieve product_id by category, but the join is on product_id and the filter is on category. The existing index on category should suffice for filtering. The performance issue may stem from the optimizer choosing a sequential scan due to low selectivity or outdated statistics. Adding a composite index might not be used and could add overhead. It does not address the root cause.

The SELECT is blocked because the transaction has already performed a write and PostgreSQL requires a COMMIT before any read can proceed.

Why it fails: PostgreSQL fully supports reading within a transaction after a write; uncommitted changes are visible to the same transaction. There is no requirement to COMMIT before a read. This option contradicts standard MVCC behavior and is not a valid cause of the observed blocking.

The UPDATE statement acquired an exclusive lock on the table, and the subsequent SELECT in the same transaction must wait for that lock to be released.

Why it fails: An UPDATE takes a ROW EXCLUSIVE lock on the table, which does not conflict with the ACCESS SHARE lock needed by a SELECT. A SELECT in the same transaction would not be blocked by its own transaction's locks. This option misidentifies the lock type and ignores same-transaction lock compatibility.

The transaction is running at the SERIALIZABLE isolation level, and the SELECT must wait for a serializable snapshot to be established.

Why it fails: At SERIALIZABLE, PostgreSQL uses predicate locking and may abort transactions with serialization failures, but it does not block a SELECT within the same transaction indefinitely. A SELECT in the same transaction sees its own uncommitted changes and does not wait for a snapshot. This cause is incorrect for the scenario.

SELECT employee_id FROM Employees GROUP BY department_id HAVING salary > AVG(salary);

Why it fails: The HAVING clause filters groups, not individual rows, and cannot reference a non-aggregated column like salary. This query attempts to compare an ungrouped salary to an average, which is invalid in standard SQL. It would not return the desired list of employees earning above their department average.

SELECT employee_id FROM Employees e JOIN (SELECT department_id, AVG(salary) AS avg_sal FROM Employees GROUP BY department_id) d ON e.department_id = d.department_id WHERE e.salary > d.avg_sal;

Why it fails: This join with a derived table does return correct results, but it is unnecessarily complex for the scenario. The correlated subquery approach is simpler and directly expresses the intent. However, since the question asks for the statement that will return the correct result, and this one does, it is not the best answer because the subquery version is more straightforward.

SELECT employee_id FROM Employees WHERE salary > (SELECT AVG(salary) FROM Employees);

Why it fails: This subquery computes the average salary across the entire company, not per department. It returns employees earning more than the company-wide average, which does not satisfy the requirement of comparing each employee to their department's average. A correlated subquery referencing the outer department_id is needed to get the correct per-department comparison.

Relational (RDBMS)

Why it fails: Relational schemas enforce fixed columns and joins, so horizontal scaling requires sharding that breaks cross-node consistency and availability. It is tempting because RDBMS suits structured transactional data with ACID guarantees, where a single node or vertical scaling suffices.

Network

Why it fails: A network database model stores records in graph-like sets with owner-member pointers, so it does not provide the schema-less document storage or horizontal partitioning across commodity servers that unstructured data at scale requires. It is tempting because network databases handle complex relationships, but those suit structured, navigational access patterns.

Hierarchical

Why it fails: Hierarchical models store data as a rigid parent-child tree, so they cannot distribute unstructured records across servers or tolerate node loss. It is tempting for fixed one-to-many structures such as file systems or bill-of-materials, where the tree shape is known in advance.

MODIFY TABLE

Why it fails: MODIFY TABLE is not standard SQL for adding a column; MODIFY typically alters an existing column's definition in dialects such as Oracle or MySQL. It is tempting because 'modify' suggests structural change, but the required statement is ALTER TABLE ... ADD COLUMN.

UPDATE TABLE

Why it fails: UPDATE TABLE is not valid SQL syntax; UPDATE modifies existing row values, not table structure. It is tempting because the word 'update' suggests changing a table, but adding a column is DDL, requiring ALTER TABLE ... ADD COLUMN.

CREATE COLUMN

Why it fails: CREATE COLUMN is not a recognised SQL statement; CREATE defines new objects such as tables, views or indexes, not columns within an existing table. It is tempting because 'create' implies adding something new, but column addition is DDL performed via ALTER TABLE.

View

Why it fails: A view stores a saved query and executes its underlying SELECT each time, so it adds no index structure and cannot accelerate lookups on the queried column. It is tempting because views do simplify repeated complex joins and restrict exposed columns, which is their actual purpose.

Stored Procedure

Why it fails: A stored procedure is a named, precompiled batch of SQL executed on demand; it does not create a lookup structure, so the query still scans the table. It is tempting because procedures reduce network round trips and can encapsulate logic, which suits repetitive business operations rather than column-filtered reads.

Trigger

Why it fails: A trigger fires automatically in response to INSERT, UPDATE or DELETE events; it cannot accelerate SELECT statements. It is tempting because triggers are created on tables and can enforce or audit changes, which is the correct choice when the requirement is automatic action on data modification rather than read speed.

Read Committed

Why it fails: Read Committed prevents dirty reads by holding shared locks only for the duration of each statement, releasing them immediately afterwards. That permits non-repeatable reads and phantoms under concurrent writes, so it does not deliver the highest consistency. It suits high-throughput OLTP where blocking must stay minimal, which is why it tempts here.

Repeatable Read

Why it fails: Repeatable Read holds shared locks on read rows until commit, preventing non-repeatable reads but still allowing phantoms, so it does not deliver the highest consistency. It fits workloads needing stable repeated reads without full serialisation.

Read Uncommitted

Why it fails: Read Uncommitted permits dirty reads, giving the lowest consistency and the highest concurrency — the exact inverse of the requirement. It suits reporting where approximate, non-blocking reads are acceptable and stale or uncommitted values cause no harm.

PRIMARY KEY

Why it fails: A primary key enforces uniqueness and non-nullness on the Orders table's own column, but it cannot reference another table's values. It is tempting because primary keys are the standard identifier constraint, yet referential integrity across tables requires a foreign key referencing Customers.

UNIQUE

Why it fails: A unique constraint only prevents duplicate values within the Orders column; it does not verify that each Customer_ID exists in the Customers table. It is tempting because uniqueness feels related to validity, but enforcing cross-table existence is the job of a foreign key constraint.

CHECK

Why it fails: A check constraint validates values against a condition or list within the same table, not against rows in another table. It is tempting because checks enforce data validity, but they cannot query Customers, so a foreign key is required for referential integrity.

DELETE

Why it fails: DELETE removes rows but is DML and can be filtered or rolled back within a transaction, so it does not permanently clear all rows in one irreversible statement. It is tempting because it removes rows while preserving the table. TRUNCATE is the DDL command that deallocates all rows permanently.

DROP

Why it fails: DROP removes the entire table, including its definition, indexes and constraints, so the table no longer exists. It is tempting because it permanently removes data quickly. TRUNCATE instead deallocates all rows while leaving the table structure intact for continued use.

REMOVE

Why it fails: REMOVE is not a standard SQL command; no major database engine accepts it. It is tempting because the word suggests deletion, but SQL defines DELETE, TRUNCATE and DROP for that purpose. TRUNCATE is the command that permanently clears all rows while preserving the table definition.

INNER JOIN

Why it fails: INNER JOIN returns only rows where keys match in both tables, discarding unmatched left-table rows. It would be correct when the requirement is strictly matched pairs, but the stem demands every left row be preserved.

RIGHT JOIN

Why it fails: A RIGHT JOIN preserves every row of the right table, so unmatched left-table records are dropped — the reverse of the requirement. It is tempting because it is the mirror of a LEFT JOIN and would be correct if the stem asked to retain all right-table records with only matching left rows.

FULL JOIN

Why it fails: FULL JOIN returns all rows from both tables, including unmatched rows on each side. It would be correct when unmatched records from both tables must appear, but the stem requires only left-table rows preserved with right-side matches.