This repository contains a collection of SQL queries I wrote for a database systems course. The queries focus on analytical-style reporting over a sales dataset and are written using standard SQL constructs.
The goal of these queries is to demonstrate how to break down complex logic using Common Table Expressions (CTEs), joins, and aggregation, rather than relying on a single large query.
pa1.txt
Contains a set of SQL queries labeled Query1–Query5. These queries focus on grouping, aggregation, and basic analytical reporting over the dataset.
pa2.txt
Contains a more advanced set of SQL queries labeled Query1–Query5. These queries make heavier use of CTEs and self-joins to compare values across different time periods (such as previous and next months).
Across both files, the queries demonstrate:
- Use of Common Table Expressions (WITH clauses) to structure multi-step logic
- Aggregation using AVG, SUM, COUNT, MIN, and MAX
- GROUP BY for per-customer, per-product, and per-time-period analysis
- Self-joins to compare values across different months
- Clear decomposition of complex problems into readable subqueries
Each query is written to be readable and labeled clearly rather than optimized for minimal length.
These queries assume the existence of a table named sales with columns such as:
- cust (customer)
- prod (product)
- month
- year
- quant (quantity)
- state
- date
Exact results depend on having a compatible dataset and schema.
To run a query file in PostgreSQL:
psql -d your_database
\i pa1.txt
Or from the terminal:
psql -d your_database -f pa2.txt
These queries are preserved as examples of coursework-style SQL analytics and focus on correctness, clarity, and logical structure.