Use AI as a checked SQL assistant

Estimated study time: 75–90 minutes. Read, predict, run, explain, then attempt the independent work.

An AI assistant can explain an error, suggest a query skeleton or propose edge cases. It can also invent columns, choose an unintended definition of revenue or produce a join that multiplies money. The safest working relationship is to let the model propose a draft while you own the metric, access boundary and verification evidence.

Begin with a short data contract. Give the dialect, allowed tables and columns, primary and foreign keys, table grains, date boundaries and business definitions. State the desired output grain and inclusion rules. Ask the assistant to list assumptions and uncertain points before writing SQL. Supply only approved metadata and synthetic examples; a useful query draft rarely needs raw customer names, credentials or full production records.

A prompt that makes review possible

Use the following prompt with an AI tool you are already allowed to use, or complete the review manually. No AI account or paid service is required to finish the course.

“Draft a read-only PostgreSQL query using the synthetic Aabha schema. Return one row per month for completed orders dated from 2025-01-01 inclusive to 2025-04-01 exclusive. Line sales are quantity times historical unit_price minus the total line_discount. Subtract refunds recorded before 2025-04-01 and assign them to the original order month. Aggregate independent child tables before joining. Return order count, buyer count, before-refund sales, refunds, net sales and net average order value. Explain the grain of every intermediate result, identify assumptions, and propose tests. Do not invent columns or execute anything.”

The prompt helps but is not a security control. A model's statement that a query is safe or correct is a claim to test. Compare every identifier with the schema. Read every statement, including CTEs. A statement beginning WITH can contain modifying operations; a SELECT can call functions with side effects or reveal data. Do not use keyword matching as your only defense.

Verify meaning as well as syntax

First, test in the isolated synthetic lab. Check one-row-per-order uniqueness before the monthly grouping, reconcile before-refund sales minus refunds to net sales, and verify the known boundary orders. Use the split-payment case to catch fan-out. Compare a hand-calculated order with the result. Check empty inputs, missing values and a zero denominator. If the model changes its query, rerun the tests instead of assuming the change is cosmetic.

Then review the business interpretation. Does the average divide by orders or lines? Are failed payment attempts excluded? Are buyers counted distinctly within each month rather than summed as if unique across the whole quarter? Does the refund cutoff match the report? A query can parse and return the expected number of columns while answering the wrong question.

Read-only is layered protection

The supplied runner opens a fresh in-memory database and starts a READ ONLY transaction for analysis, with a five-second statement timeout. Read-only transactions block ordinary changes to persistent tables; they are not a complete sandbox or a defense against data disclosure, expensive queries or all side effects. An untrusted script could try to end a transaction. Therefore the runner is for your reviewed practice queries, not for safely executing arbitrary hostile SQL.

In real systems, use a dedicated least-privilege account approved by the database owner, restricted views where appropriate, access auditing, query limits and reviewed parameterized application queries. Keep credentials out of prompts. These operational controls must be arranged with the system owner; this course does not configure production access. Values supplied by application users should be bound as parameters, rather than concatenated into SQL text. Identifiers require a separate controlled selection policy.

EXPLAIN shows an estimated execution plan without running the analyzed SELECT. It can reveal a scan or join shape, but an estimated plan is not proof of speed or correctness. EXPLAIN ANALYZE actually executes the statement, which matters for costly or modifying operations. Our tiny fixture's plan and timing cannot establish production performance.

Keep a short review record: the question, schema version, query, assumptions, test evidence and unresolved limitations. This is more useful than keeping only a screenshot of a plausible result.

Worked example 12 verify

The query checks order-grain uniqueness and three monetary totals. Its 5860 minus 215 equals 5645, but the independent boundary and coverage checks remain necessary.

WITH line_totals AS (
 SELECT order_id,SUM(quantity*unit_price-line_discount) AS line_sales
 FROM aabha_sql.order_items GROUP BY order_id
), refund_totals AS (
 SELECT order_id,SUM(amount) AS refunded
 FROM aabha_sql.refunds WHERE refund_date < DATE '2025-04-01' GROUP BY order_id
), order_net AS (
 SELECT o.order_id,o.customer_id,o.order_date,o.channel,
 l.line_sales,COALESCE(r.refunded,0) AS refunded,
 l.line_sales-COALESCE(r.refunded,0) AS net_sales
 FROM aabha_sql.orders AS o
 JOIN line_totals AS l ON l.order_id=o.order_id
 LEFT JOIN refund_totals AS r ON r.order_id=o.order_id
 WHERE o.status='completed' AND o.order_date >= DATE '2025-01-01'
   AND o.order_date < DATE '2025-04-01'
)
SELECT COUNT(*) AS rows,COUNT(DISTINCT order_id) AS distinct_orders,
 SUM(line_sales) AS sales_before_refunds,SUM(refunded) AS refunds,SUM(net_sales) AS net_sales
FROM order_net;

Expected result from the bundled dataset:

The runnable file is sql/examples/12_verify.sql.

Worked example 12 explain

Inspect the plan shape rather than memorizing the exact cost numbers. The validator checks that a plan is returned; costs may differ across PostgreSQL builds or statistics.

EXPLAIN SELECT order_id FROM aabha_sql.orders
WHERE order_date >= DATE '2025-03-01' AND order_date < DATE '2025-04-01';

Expected result from the bundled dataset:

The runnable file is sql/examples/12_explain.sql.

Independent practice

12a

An AI draft starts from customers and left joins orders, then filters WHERE o.status = 'completed' and counts COUNT(*). Repair the report to keep all customers with correct completed-order counts. Explain both errors.

Hint: The quoted text describes a flawed draft, not executable SQL. Revisit where the completion condition belongs and which key should be counted.

12b

Compute full-quarter net_average_order_value from order_net. Return net_sales, completed_orders and the rounded average. Explain why an average of the three monthly averages is not equivalent.

Hint: Use the full-quarter order denominator. No external AI tool is needed.

Check before moving on

You can reject a plausible SQL draft for a concrete reason, reproduce the failure on synthetic data, and name safety controls beyond a READ ONLY transaction.