DS0-001 Database Structures And SQL — Timed 25 Questions

Question 1

A business user needs to calculate the average age of customers, but some age fields are NULL. Which function should be used to treat NULL as 0?

Accepted Answer

COALESCE. COALESCE returns the first non-NULL value from its arguments, so COALESCE(age, 0) substitutes 0 wherever age is NULL, allowing the average to be computed without NULL propagation. This is the standard SQL function for NULL substitution and is supported across major RDBMS platforms.

Answer

NULLIF

Answer

IFNULL

Answer

ISNULL

Question 2

A transaction involves multiple updates that must either all succeed or all fail. Which command ensures this behavior?

Accepted Answer

COMMIT. COMMIT is the command that permanently writes the transaction's changes to the database and makes them durable, finalizing the all-or-nothing unit of work. When all statements in a transaction succeed, COMMIT atomically persists them; if any fail before COMMIT, a ROLLBACK (explicit or implicit) discards the entire transaction, satisfying atomicity.

Answer

GRANT

Answer

SAVEPOINT

Answer

BEGIN

Question 3

Which command is used to restrict a user's access to a specific table?

Accepted Answer

REVOKE. REVOKE is the SQL command used to remove previously granted privileges from a user, including table-level access. It directly restricts a user's access by withdrawing permissions that were granted with GRANT.

Answer

DENY

Answer

DROP

Answer

REMOVE

Question 4

Which THREE of the following are considered ACID properties in database transactions?

Accepted Answer

Atomicity. Atomicity (A) is a core ACID property because it guarantees that a transaction is treated as a single all-or-nothing unit: either every statement commits or the entire transaction rolls back, so partial updates never persist. Consistency (C) is correct because it requires a transaction to move the database from one valid state to another, preserving all defined constraints, schemas, and integrity rules. Isolation (E) is correct because it ensures concurrently executing transactions do not interfere with each other, with the database behaving as if transactions ran serially, typically enforced through locking or MVCC at a chosen isolation level. Scalability (B) and Availability (D) do not belong here: they are distributed-systems quality attributes often associated with the CAP theorem (Consistency, Availability, Partition tolerance) or with performance goals, not transactional ACID guarantees.

Answer

Scalability

Answer

Availability

Question 5

A financial analyst needs to calculate the total sales for each product category, but only for orders placed in the last quarter. The Orders table contains order_date and order_total, and the Products table contains product_id and category. Which SQL query correctly returns the category and total sales for the last quarter?

Accepted Answer

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;. The correct query joins the tables, filters orders to the last quarter, groups by category, and sums the order totals. It uses GROUP BY on the category only, ensuring one row per category with the total sales. The other options either omit grouping, over-group, or filter out valid categories.

Answer

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;

Answer

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;

Answer

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;

Question 6

A data analyst runs a query on a PostgreSQL 15 database to find customers whose last purchase was more than 180 days ago. The query returns zero rows even though many customers meet the condition. The analyst confirms the 'last_purchase_date' column is defined as DATE and contains valid dates. Which function should the analyst use to calculate the day difference between the current date and 'last_purchase_date'?

Accepted Answer

CURRENT_DATE - last_purchase_date. PostgreSQL supports direct subtraction of DATE values, returning an integer number of days. This is the simplest and most portable approach for calculating day differences. The other options either use functions from other database platforms (DATEDIFF, DATE_ADD) or incorrectly extract only the day component from an interval, which fails for spans over a month.

Answer

EXTRACT(DAY FROM CURRENT_DATE - last_purchase_date)

Answer

DATEDIFF(CURRENT_DATE, last_purchase_date)

Answer

DATE_ADD(CURRENT_DATE, INTERVAL -180 DAY)

Question 7

A financial analyst runs a query that joins a large 'Transactions' table to a small 'CurrencyRates' table and filters on a date range. The execution plan shows a nested loop join with an index seek on the small table for every row of the large table, and the query runs slowly. Which change is most likely to improve performance without altering the result set?

Accepted Answer

Add a covering index on the Transactions table for the join and filter columns. Adding a covering index on the large table gives the optimizer an efficient access path for both the join and the date filter, reducing I/O and often enabling a better join strategy while returning identical results. The other options either change semantics, force a potentially worse plan, or introduce per-row subquery execution.

Answer

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

Answer

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

Answer

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

Question 8

A database developer is writing a query to retrieve a list of all customers who have placed at least one order in the last 30 days. The Orders table contains a CustomerID column and an OrderDate column. The developer wants to include each qualifying customer only once in the result set. Which SQL clause should be used?

Accepted Answer

DISTINCT. The goal is to return each qualifying customer only once. The DISTINCT keyword in the SELECT clause removes duplicate rows from the output, ensuring that even if a customer has multiple orders, they appear only once. This is the standard and most efficient way to achieve deduplication in a simple query, without the overhead of grouping or set operations.

Answer

UNION

Answer

GROUP BY CustomerID

Answer

HAVING COUNT(*) > 0

Question 9

A database administrator must ensure that a reporting query returns a consistent snapshot of data even while concurrent transactions are committing changes. The database uses multi-version concurrency control. Which isolation level should the administrator configure to guarantee that every read in the transaction sees the same snapshot?

Accepted Answer

SNAPSHOT. In an MVCC database, SNAPSHOT isolation assigns each transaction a consistent view of committed data as of its start, so all reads return the same snapshot even as other transactions commit. READ UNCOMMITTED, READ COMMITTED, and REPEATABLE READ each permit some form of changing or phantom data, so they cannot guarantee the stable reporting view the administrator needs.

Answer

READ COMMITTED

Answer

READ UNCOMMITTED

Answer

REPEATABLE READ

Question 10

A database developer is building a reporting query that must combine data from an Orders table and a Customers table. The query needs to include all customers, even those who have not placed any orders, and for customers with orders, it should show the order details. The developer wants to avoid returning duplicate customer rows when a customer has multiple orders. Which two techniques should the developer use? (Choose two.)

Accepted Answer

Use a subquery or aggregate function to return one row per customer.. To include all customers and avoid duplicate customer rows when multiple orders exist, a LEFT JOIN from Customers to Orders preserves all customers, but the one-to-many relationship causes duplicates. Using a subquery or aggregate function to return a single row per customer resolves this. Together, these techniques meet the requirement of showing all customers with summarized order details.

Answer

Use the DISTINCT keyword in the SELECT clause.

Answer

Use a FULL OUTER JOIN between Customers and Orders.

Answer

Use a RIGHT JOIN from Customers to Orders.

Question 11

A database developer is optimizing a query that joins a large fact table 'Sales' with a dimension table 'Products' on product_id. The query filters on Products.category = 'Electronics' and aggregates sales amounts. The developer notices the query performs a sequential scan on 'Products' despite an index on category. The Products table has 100,000 rows, and only 5% belong to 'Electronics'. Which action is most likely to improve query performance?

Accepted Answer

Update statistics on the Products table.. When a query filters on a column with an index but the optimizer chooses a sequential scan, stale statistics are a common cause. The optimizer may misestimate the number of matching rows, leading it to believe a sequential scan is cheaper. Updating statistics provides accurate data distribution information, allowing the optimizer to correctly choose an index scan for the selective filter, thereby improving performance.

Answer

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

Answer

Partition the Products table by category.

Answer

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

Question 12

A data analyst runs a transaction in a PostgreSQL 15 database: they issue an UPDATE on the 'inventory' table for 50 rows, then attempt to run a SELECT on the same table within the same transaction. The SELECT is blocked and waits indefinitely. The analyst confirms that no other session is holding a lock on those rows. Which of the following is the MOST likely cause of the block?

Accepted Answer

The UPDATE statement is still holding row-level locks on the modified rows, and the SELECT is trying to read those same rows with a locking clause such as FOR UPDATE.. The block occurs because the SELECT is not a plain read; it is a locking read (e.g., FOR UPDATE) that conflicts with the row-level locks already held by the same transaction's UPDATE. In PostgreSQL, a transaction cannot acquire a conflicting lock on rows it already modified, leading to an indefinite wait. A plain SELECT without a locking clause would not block.

Answer

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

Answer

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.

Answer

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

Question 13

A database developer is writing a query to find all employees whose salary is above the average salary of their own department. The HR database contains an Employees table with columns employee_id, department_id, and salary. Which SQL statement will return the correct result?

Accepted Answer

SELECT employee_id FROM Employees e WHERE salary > (SELECT AVG(salary) FROM Employees WHERE department_id = e.department_id);. The correct answer uses a correlated subquery that computes the average salary for each employee's department. By referencing the outer department_id, the inner query dynamically calculates the department average, allowing each employee's salary to be compared to that specific average. This meets the requirement precisely and efficiently.

Answer

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

Answer

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;

Answer

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

Question 14

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

Accepted Answer

NoSQL (Document Store). A NoSQL document store is designed for unstructured or semi-structured data and natively supports horizontal scaling through sharding and replication across commodity servers, delivering high availability via automatic failover. Document stores such as MongoDB or Couchbase store data as flexible JSON-like documents, so schema changes do not require migrations. This combination of schema flexibility, horizontal scale, and built-in replication matches the requirement precisely.

Answer

Relational (RDBMS)

Answer

Network

Answer

Hierarchical

Question 15

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

Accepted Answer

ALTER TABLE. The ALTER TABLE statement is the standard SQL DDL command used to modify the structure of an existing table, including adding, dropping, or modifying columns. To add a new column to 'Employees', the correct syntax is ALTER TABLE Employees ADD column_name data_type.

Answer

MODIFY TABLE

Answer

UPDATE TABLE

Answer

CREATE COLUMN

Question 16

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

Accepted Answer

Index. An index on the frequently queried column allows the database engine to locate rows without scanning the entire table, dramatically improving read performance for SELECT queries filtering or joining on that column. Indexes create a sorted structure (B-tree) that enables fast seeks and range scans.

Answer

View

Answer

Stored Procedure

Answer

Trigger

Question 17

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?

Accepted Answer

Serializable. Serializable is the highest isolation level, ensuring complete isolation from other transactions. It prevents dirty reads, non-repeatable reads, and phantom reads, but at the cost of lowest concurrency due to increased locking and potential blocking.

Answer

Read Committed

Answer

Repeatable Read

Answer

Read Uncommitted

Question 18

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?

Accepted Answer

FOREIGN KEY. A FOREIGN KEY constraint is used to enforce referential integrity between two tables. It ensures that the Customer_ID in the Orders table matches an existing ID in the Customers table, preventing orphaned records and maintaining consistent relationships.

Answer

PRIMARY KEY

Answer

UNIQUE

Answer

CHECK

Question 19

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

Accepted Answer

TRUNCATE. TRUNCATE is a DDL operation that removes all rows from a table in a single transaction-efficient operation while preserving the table's structure, columns, constraints, and indexes. It deallocates the data pages rather than logging each row deletion, making it far faster than DELETE for clearing a table. The table definition remains intact and ready for new inserts.

Answer

DELETE

Answer

DROP

Answer

REMOVE

Question 20

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

Accepted Answer

LEFT JOIN. A LEFT JOIN (LEFT OUTER JOIN) returns every row from the left table regardless of whether a match exists in the right table, and for unmatched rows it fills right-table columns with NULLs. This is exactly the behavior described: all left records plus only matching right records.

Answer

INNER JOIN

Answer

RIGHT JOIN

Answer

FULL JOIN