Chapter 31 Self-Assessment Quiz: Database Programming

Section 1: Multiple Choice

Q1. SQLite is best described as:

(a) A client-server database that requires installation and administration (b) An embedded, serverless database stored in a single file (c) A cloud-based database service (d) A NoSQL document store

Answer (b) SQLite is self-contained and serverless. The entire database lives in a single file. No installation, no server process, no configuration required.

Q2. In the SQLdb component chain, what is the role of TDataSource?

(a) It executes SQL queries (b) It manages database transactions (c) It bridges the dataset (query) and visual controls (DBGrid, DBEdit) (d) It creates the database file

Answer (c) TDataSource acts as an intermediary between a dataset (like TSQLQuery) and data-aware visual controls. It notifies controls when the current record changes and routes edits from controls back to the dataset.

Q3. Which SQL statement returns aggregate data grouped by category?

(a) SELECT * FROM expenses ORDER BY category (b) SELECT category, SUM(amount) FROM expenses GROUP BY category (c) SELECT DISTINCT category FROM expenses (d) SELECT category, amount FROM expenses WHERE category IS NOT NULL

Answer (b) GROUP BY category groups rows by category, and SUM(amount) calculates the total amount for each group. This is the SQL equivalent of writing a loop that accumulates totals per category.

Q4. What does FOREIGN KEY (category_id) REFERENCES categories(id) enforce?

(a) The category_id column must be unique (b) Every value in category_id must match an existing id in the categories table (c) The categories table cannot be modified (d) The category_id column auto-increments

Answer (b) A foreign key constraint ensures referential integrity: you cannot insert an expense with a category_id that does not exist in the categories table, and you cannot delete a category that has associated expenses (depending on ON DELETE behavior).

Q5. To prevent SQL injection, you should:

(a) Validate that user input contains no special characters (b) Escape single quotes in user input (c) Use parameterized queries with :param placeholders (d) Limit the length of user input

Answer (c) Parameterized queries are the only reliable protection against SQL injection. The database engine treats parameter values as data, never as SQL code. Options (a), (b), and (d) are partial measures that can be bypassed.

Section 2: True or False

Q6. Switching from SQLite to PostgreSQL in a Lazarus application requires rewriting all TSQLQuery and TDataSource code.

Answer False. Only the connection component changes (TSQLite3Connection to TPQConnection). The TSQLTransaction, TSQLQuery, TDataSource, and TDBGrid code remains identical. This is the benefit of the SQLdb abstraction layer.

Q7. After calling TSQLQuery.ExecSQL for an INSERT statement, you must call TSQLTransaction.Commit to make the change permanent.

Answer True. Until Commit is called, the changes exist only within the transaction and can be rolled back. Without Commit, changes may be lost when the connection closes.

Q8. CREATE TABLE IF NOT EXISTS will delete and recreate a table if it already exists.

Answer False. IF NOT EXISTS means the statement does nothing if the table already exists. It does not drop or modify the existing table. To recreate, you would need DROP TABLE IF EXISTS followed by CREATE TABLE.

Section 3: Short Answer

Q9. Write a parameterized SQL query that selects all expenses from the "Food" category with an amount greater than $50, ordered by date descending.

Answer 
Query.SQL.Text :=
  'SELECT * FROM expenses ' +
  'WHERE category = :cat AND amount > :minamt ' +
  'ORDER BY date DESC';
Query.Params.ParamByName('cat').AsString := 'Food';
Query.Params.ParamByName('minamt').AsFloat := 50.0;
Query.Open;

Q10. Explain the difference between First Normal Form (1NF) and Third Normal Form (3NF) with a PennyWise example.

Answer 1NF: Every column holds a single atomic value. Violation: storing multiple categories in one field like "Food,Transport". Fix: one category per expense. 3NF: Every non-key column depends only on the primary key. Violation: storing the category name and the category's budget limit in the expenses table (the budget depends on the category, not on the expense). Fix: move budget_limit to a separate categories table and reference it via category_id.

Q11. You want to display expenses with their category names (from a separate categories table). Write the SQL JOIN query.

Answer 
SELECT e.date, e.description, c.name as category, e.amount
FROM expenses e
JOIN categories c ON e.category_id = c.id
ORDER BY e.date DESC
The JOIN connects each expense to its category via the category_id foreign key.