exercises and solutions
Database exercises
Practical Example
<?php
declare(strict_types=1);
$pdo = new PDO('sqlite::memory:');
$pdo->exec('CREATE TABLE users (email TEXT NOT NULL)');
$pdo->exec("INSERT INTO users (email) VALUES ('sam@example.com')");
echo $pdo->query('SELECT email FROM users')->fetchColumn() . PHP_EOL;
// Prints:
// sam@example.com
Database exercises should give learners a table, seed data, a query to write, and an expected result they can compare.
The setup query is intentionally fixed here, but exercises that accept values must use prepared statements. Add duplicate, missing-row, and rollback cases as the learner progresses.
Practice
Query A Product Safely
Create an in-memory SQLite products table, insert two rows through a prepared statement, and fetch one product by ID through another prepared statement. Handle a missing ID.
Show solution
Prepared statements keep values separate from SQL syntax. The missing-row branch prevents code from treating false as a product array.
Transfer Stock In A Transaction
Describe a PDO transaction that subtracts stock from one warehouse row and adds it to another. Reject an insufficient source quantity and explain the rollback path.
Show solution
Begin a transaction, lock or update the source row safely, reject insufficient stock, update both rows with prepared statements, and commit only after both writes succeed. Roll back in the exception path.
The transaction prevents a partial transfer where stock is removed from one warehouse but never added to the other.