SQL — relational design and PostgreSQL

Designing a relational schema from requirements, then querying a live cloud-hosted PostgreSQL database.

Database design

Produced an entity relationship diagram modelling students, classes and results. A student takes many classes and a class holds many students, so a linking table sits between them carrying both foreign keys plus the attribute that only exists at the intersection — the mark. A mark belongs to the pairing of a student and a class, not to either table alone.

Designed a full schema for a convenience store covering inventory, sales, suppliers, customers and a loyalty scheme — identifying the relationship cardinality for each pair, and resolving the many-to-many between sales and inventory with a sale_items linking table.

Querying

Queries written and run against a Supabase-hosted PostgreSQL database of countries, cities and languages.

SELECT DISTINCT, WHERE with boolean and range predicates, BETWEEN

GROUP BY with COUNT, MAX, SUM and AVG

HAVING to filter groups rather than rows

LIKE for pattern matching on city names

Correlated subqueries — finding countries whose most-spoken language is not official

Scalar subqueries — countries above the overall average population

A data quality check summing language shares to find country totals under 100%

Debugging

A significant part of the work was fault-finding rather than writing from scratch. Each query was supplied with a deliberate defect to locate, fix and explain: a missing table name after FROM, a missing boolean literal after is_official =, a missing sort direction on ORDER BY, an empty pattern inside a LIKE wildcard.

This is closer to real database work than greenfield queries. Most SQL time is spent reading someone else's query and working out why the result looks wrong.

Repository

https://github.com/gmrxowk91-wq/SQL-report