Chapter 31 Exercises: Database Programming

Part A: Conceptual Understanding

A.1. Explain the difference between Query.Open and Query.ExecSQL. When do you use each?

Guidance Open is used for SELECT statements that return a result set — you can then navigate rows with Next, read field values, etc. ExecSQL is used for statements that modify data (INSERT, UPDATE, DELETE) or change structure (CREATE TABLE, ALTER TABLE) and do not return a result set. Using Open on an INSERT produces an error; using ExecSQL on a SELECT loses the results.

A.2. What is SQL injection? Write a dangerous query and the equivalent safe parameterized query.

Guidance Dangerous: 'SELECT * FROM users WHERE name = ''' + edtName.Text + ''''. If edtName.Text is '; DROP TABLE users; --, the database executes the destructive statement. Safe: Query.SQL.Text := 'SELECT * FROM users WHERE name = :name'; Query.Params.ParamByName('name').AsString := edtName.Text;. The parameter is treated as data, never as SQL code.

A.3. Describe the SQLdb component chain (Connection > Transaction > Query > DataSource > DBGrid). What role does each component play?

Guidance Connection: establishes the link to the database file or server. Transaction: groups operations for atomicity (all succeed or all fail). Query: executes SQL and holds results. DataSource: acts as a bridge between the dataset (query) and visual controls, notifying controls when the current record changes. DBGrid: displays rows and columns from the DataSource, allowing navigation and editing.

A.4. What are the advantages of SQLite over typed files for PennyWise? List at least four.

Guidance 1. Complex queries without loading all data into memory (WHERE, GROUP BY, ORDER BY). 2. Data integrity through constraints (NOT NULL, UNIQUE, FOREIGN KEY). 3. Atomic transactions prevent corrupted files from crashes. 4. Indexing for fast searches without custom code. 5. Schema changes (ALTER TABLE) without rewriting migration code. 6. Standard SQL — knowledge transfers to any database.

Part B: Exploration and Analysis

B.1. Create a SQLite database with a products table (id, name, price, quantity). Insert 10 products programmatically. Display them in a TDBGrid. Add a search TEdit that filters the grid by product name using a parameterized LIKE query.

B.2. Use SELECT SUM(price * quantity) as total FROM products to display the total inventory value in a TLabel. Add SELECT AVG(price) as avg_price to display the average price. Experiment with other SQL aggregate functions: COUNT, MIN, MAX.

B.3. Create two tables: authors (id, name) and books (id, title, author_id, year). Insert sample data. Write a JOIN query that displays book titles with their author names. Display the results in a TDBGrid.

Part C: Coding Challenges

C.1. Student Grade Database (Intermediate) Build a student grade tracker with two tables: students (id, name, email) and grades (id, student_id, subject, score, date). Create forms for adding students and adding grades. Display a report showing each student's average score using GROUP BY and AVG. Use a TDBGrid with a JOIN query.

C.2. Library Catalog (Intermediate) Build a library catalog with tables for books, authors, and borrowings. Implement: add book, search by title/author, check out (record a borrowing), check in (update return date), and an overdue books report (WHERE return_date IS NULL AND borrow_date < date threshold).

C.3. PennyWise Budget Tracker (Advanced) Extend PennyWise's database with a budgets table (id, category_id, month, limit_amount). Add a form for setting monthly budgets per category. Create a report that shows each category's total spending vs. budget for the current month, highlighting categories that are over budget. Use a single SQL query with JOIN and GROUP BY.

C.4. Database Migration (Advanced) Write a migration tool that reads PennyWise's old flat-file format (from Chapter 13/29) and imports all expenses into the new SQLite database. Handle duplicate detection (do not import an expense that already exists, based on date+description+amount).

Part M: Metacognitive Exercises

M.1. Compare the code for "find all expenses in the Food category" using flat files (loop through every record) vs. database (SELECT * FROM expenses WHERE category = 'Food'). Which is simpler? Which would be faster with 100,000 records?

M.2. The chapter emphasized parameterized queries. Will you always remember to use them? What strategy will you use to make sure you never accidentally concatenate user input into SQL?

M.3. If you have used databases before (MySQL, PostgreSQL, MongoDB), how does the Lazarus SQLdb experience compare? If this is your first database experience, what was the most surprising aspect?