Database checks can confirm a state change that a browser message alone cannot prove. They can also give a false result when joins, grouping, or nulls are misunderstood. Start with correctness, then measure performance on the database and data distribution you actually use.
A small query with two common mistakes
SELECT u.name, COUNT(*) AS order_count, SUM(o.total) AS total_spent
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.name;This groups different users who share a name into one row. It also counts the null-extended join row for a user with no orders, so COUNT(*) is not that user’s order count. A missing sum is NULL rather than zero.
Preserve identity and count the matched rows
SELECT u.id, u.name,
COUNT(o.id) AS order_count,
COALESCE(SUM(o.total), 0) AS total_spent
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE UPPER(u.status) = 'ACTIVE'
OR u.role IN ('admin', 'moderator')
GROUP BY u.id, u.name
ORDER BY total_spent DESC, u.id;This example assumes orders.id is non-null and the requirement includes active users or the two named roles. The unique user ID prevents names from merging; the final ID ordering makes ties reproducible. Check that this matches the intended requirement before using it.
Optimization rules are hypotheses
In PostgreSQL, an index on an expression such as upper(status) can support a matching predicate. It is incorrect to say every function forces a full scan. An index also has write and storage costs; the database planner chooses based on more than query text.
Use the portfolio tool as a learning aid
The SQL Optimizer uses text patterns without connecting to your database. It cannot know statistics, existing indexes, permissions, cardinalities, or production plans. Treat generated suggestions as candidates for review. In particular, adding a LIMIT changes result semantics and is not an automatic safety fix.
Build a useful database assertion
- Use a controlled test database and parameterized values.
- Identify the expected row by a stable key.
- Test zero matches, one match, duplicate names, and null values.
- Wait for asynchronous writes using a bounded condition where needed.
- Inspect the plan and measure representative data before claiming a speed improvement.
Database terminology becomes easier when tied to a result you can check. The objective is a trustworthy assertion, not a query that merely looks optimized.
