Appendix

  • ID: DBSQL-APP
  • Type: Appendix
  • Audience: Public
  • Theme: Troubleshooting and reference notes

This appendix provides additional reference material and troubleshooting guidance for the Databases and SQL free track.

Common SQL Errors

1. No such table

Error example:

no such table: customers

Possible causes:

  • The SQLite database was not initialized.
  • The file path to the database is incorrect.
  • The working directory is not the project root.

Fix:

python scripts/init-sqlite-db.py

Ensure you are running commands from the repository root directory.

2. No such column

Error example:

no such column: customer_name

Possible causes:

  • Misspelled column name
  • Column does not exist in the selected table
  • Alias not referenced correctly

Fix:

Inspect the schema:

PRAGMA table_info(customers);

Always verify column names before writing complex queries.

3. Incorrect Aggregation

If you see an error related to GROUP BY, verify:

  • All non-aggregated columns are included in GROUP BY
  • Aggregation functions are used correctly

Example of correct usage:

SELECT status, COUNT(*)
FROM orders
GROUP BY status;

Inspecting the Schema

To list tables:

SELECT name
FROM sqlite_master
WHERE type='table';

To inspect columns:

PRAGMA table_info(orders);

Understanding structure prevents most SQL mistakes.

Verifying Join Integrity

Before trusting joined results:

  1. Count rows before join.
  2. Count rows after join.
  3. Confirm expected cardinality.

Example:

SELECT COUNT(*) FROM orders;

If the count increases unexpectedly after a join, review cardinality and join conditions.

SQLite Notes

This free track uses SQLite for simplicity.

Key characteristics:

  • File-based database
  • No server required
  • Lightweight and portable

For production systems, similar SQL concepts apply in:

  • MySQL
  • PostgreSQL
  • Cloud data warehouses

Only connection configuration changes. The relational logic remains the same.

Suggested Practice Questions

  1. What is total revenue per country?
  2. Which product category generates the most revenue?
  3. Which customer placed the highest-value single order?
  4. How many orders were cancelled?
  5. What is the average order value?

Try answering these questions using only SQL before moving results into Python.

Final Reminder

SQL proficiency improves through structured repetition.

  • Inspect schema before writing joins.
  • Verify assumptions with row counts.
  • Keep queries readable and explicit.
  • Let structure guide interpretation.

Relational discipline reduces analytical fragility.

That is the purpose of this pillar.