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

Questions 6–10

1. 6expert · hard

   A company has a stored procedure that processes payroll. The procedure reads employee data, calculates salaries, and updates the `payments` table. Recently, the procedure has been failing intermittently due to deadlocks. The DBA wants to reduce the likelihood of deadlocks without changing the business logic. Which approach is most effective?

   • AEnsure that the stored procedure accesses tables in a consistent order across all transactions.
   • BAdd more indexes to the `payments` table.
   • CUse a cursor to process each employee one at a time.
   • DIncrease the transaction isolation level to SERIALIZABLE.
   Reveal Solution Discussion Select an answer first
2. 7application · medium

   A logistics company has a frequently executed process that inserts a new shipment record, updates the inventory count, and logs the action to an audit table. The application team wants to ensure these three operations always run together and that the SQL logic is maintained in one place rather than in application code. What should they implement?

   • AA view that combines the three operations into a single logical result set.
   • BA DDL command that alters the shipment table to include inventory and audit columns.
   • CA stored procedure that contains the INSERT, UPDATE, and INSERT statements wrapped in a transaction with COMMIT at the end.
   • DA trigger that fires on the shipment INSERT to perform the inventory update and audit log insert.
   Reveal Solution Discussion Select an answer first
4. 8application · easy

   A university database has a `students` table with columns `student_id`, `first_name`, `last_name`, and `enrollment_date`. The registrar's office needs to add a new column `email` to store student email addresses. They also want to ensure that the `email` column cannot contain NULL values. Which DDL command should be used?

   • AUPDATE students SET email = 'unknown@example.com' WHERE email IS NULL;
   • BDROP TABLE students; CREATE TABLE students (student_id INT, first_name VARCHAR(50), last_name VARCHAR(50), enrollment_date DATE, email VARCHAR(255) NOT NULL);
   • CCREATE TABLE students ADD email VARCHAR(255) NOT NULL;
   • DALTER TABLE students ADD email VARCHAR(255) NOT NULL;
   Reveal Solution Discussion Select an answer first
5. 9foundation · easy

   A transaction transfers funds between two bank accounts. If the transaction fails halfway through, the database must ensure that neither account is left with a partial update. Which ACID property guarantees that the transaction is treated as a single, indivisible unit?

   • AIsolation
   • BDurability
   • CAtomicity
   • DConsistency
   Reveal Solution Discussion Select an answer first
6. 10expert · hard

   A banking application transfers funds between two accounts. The code performs an UPDATE to debit account A, then an UPDATE to credit account B. A junior developer suggests wrapping these in a transaction with COMMIT at the end. However, the application must ensure that if the credit to account B fails due to a constraint violation, the debit to account A is also rolled back. Which additional TCL command must be included to guarantee this behavior?

   • AROLLBACK in an error handler after the second UPDATE fails
   • BROLLBACK immediately after the first UPDATE
   • CCOMMIT after each UPDATE
   • DSAVEPOINT before the second UPDATE
   Reveal Solution Discussion Select an answer first