SQL Code Development DS0-001 Practice Questions (Page 1)

Questions 1–5

1. 1foundation · easy

   A database administrator executes a series of DML statements to update multiple tables. Before the changes are finalized, the administrator decides to undo all changes made since the transaction began. Which TCL command should be used?

   • ASAVEPOINT;
   • BREVOKE;
   • CROLLBACK;
   • DCOMMIT;
   Reveal Solution Discussion Select an answer first
2. 2application · medium

   A reporting team frequently joins the `orders` and `customers` tables to retrieve customer names and their order totals. They find the join syntax cumbersome. You want to provide them with a reusable object that simplifies their queries without duplicating data. What should you create?

   • AAn index on the `orders` table's `customer_id` column.
   • BA view named `customer_orders` that selects customer names and order totals from the joined tables.
   • CA new table `customer_orders` that is populated by a nightly job.
   • DA stored procedure that performs the join and returns the result set.
   Reveal Solution Discussion Select an answer first
4. 3application · easy

   A customer service representative accidentally updated the `phone` column for the wrong customer in the `customers` table. The change has not been committed yet, and the transaction is still open. Which command should be used to undo the UPDATE and return the data to its original state?

   • ADELETE FROM customers WHERE phone = 'wrong_number';
   • BCOMMIT;
   • CUPDATE customers SET phone = 'original_number' WHERE customer_id = 'correct_id';
   • DROLLBACK;
   Reveal Solution Discussion Select an answer first
5. 4expert · hard

   An e-commerce platform processes orders. Two concurrent transactions attempt to update the same inventory item's stock quantity. Transaction T1 reads the stock as 10, and Transaction T2 reads the stock as 10. T1 then updates the stock to 9 and commits. T2 then updates the stock to 9 and commits, overwriting T1's change. Which ACID property is being violated, and what is the most direct way to prevent this scenario?

   • AAtomicity; wrap each UPDATE in a separate transaction with COMMIT.
   • BIsolation; use a higher isolation level such as SERIALIZABLE or use pessimistic locking (SELECT ... FOR UPDATE) on the inventory row.
   • CDurability; use a transaction log to recover the last committed value.
   • DConsistency; add a CHECK constraint that stock must be greater than or equal to zero.
   Reveal Solution Discussion Select an answer first
6. 5foundation · easy

   A developer needs to add a new column named 'email' to an existing 'customers' table. Which DDL command accomplishes this?

   • AALTER TABLE customers ADD email VARCHAR(255);
   • BCREATE COLUMN email ON customers VARCHAR(255);
   • CMODIFY TABLE customers ADD email VARCHAR(255);
   • DUPDATE customers SET email = VARCHAR(255);
   Reveal Solution Discussion Select an answer first