SQL Analysis – Company DB

A self-practice SQL project built around a fictional company database (company_db). The dataset and scenario are entirely made up — created to practice SQL querying, joins, aggregation, window functions, and CTEs in MySQL / MySQL Workbench.

Project story

The database models a company where employees place internal procurement orders (buying equipment and software). It's made up of two clusters of tables that meet at orders:

• People side: employees and departments. Each employee belongs to a department, and each department has a manager — who is also an employee. This creates a self-referencing relationship inside employees (manager_id points back to another row in the same table).
• Product side: categories, products, orders, and order_items. Products belong to categories (laptops, monitors, peripherals, etc.). Orders are the purchasing events, and order_items is the bridge table between orders and products, since one order can contain several products and the same product can appear across many orders. Each line item stores the quantity and the unit price at the time of purchase.

The two clusters connect through orders.employee_id — every order is tied to the employee who placed it.

Summary: employees (organized into departments, with managers) place orders for company products, and those orders can contain multiple line items spanning different product categories.

How to run it

1. Open MySQL Workbench (or any MySQL client).
2. Run database/company_db_SCHEMA.sql first — it drops/recreates company_db, creates all tables, and inserts the seed data.
3. Run script/company_db_ANALYSIS.sql to execute the analysis queries against the database.

Questions & results

Each entry below links to the corresponding query in company_db_ANALYSIS.sql, the concepts it practices, and a screenshot of the result in MySQL Workbench. Questions 5 and 10 produced result sets too long for a single screenshot, so each is split across two.

Q1 — Orders with employee and department names

Every order alongside the full name of the employee who placed it and their department — only when all three exist. Concepts: INNER JOIN, CONCAT

Q2 — Order count per employee

Every employee with their total order count, including those who placed zero orders, sorted descending. Concepts: LEFT JOIN, COUNT, GROUP BY

Q3 — Employees with no orders

Employees who have never placed a single order. Concepts: LEFT JOIN, IS NULL filtering

Q4 — Employees and their managers

Each employee alongside their manager's full name; employees without a manager still appear. Concepts: self-join, LEFT JOIN

Q5 — Full order breakdown

Order ID, employee, product, category, quantity, unit price, and line total. Products without a category still appear. Concepts: multiple INNER JOINs, LEFT JOIN, calculated column

Q6 — Department salary summary

Employee count, average/min/max salary, and total payroll per department — only departments averaging above 70,000. Concepts: GROUP BY, HAVING, aggregate functions

Q7 — Revenue per category

Total revenue per product category excluding cancelled orders, with uncategorised products labelled, sorted by revenue. Concepts: COALESCE, LEFT JOIN, DISTINCT counting, aggregates

Q8 — Salary vs. company average

Each employee's salary next to the company-wide average and the difference between them. Concepts: correlated/scalar subquery

Q9 — Above-average earners per department

Employees earning more than their own department's average salary. Concepts: CTE, window function (AVG() OVER (PARTITION BY ...))

Q10 — Top spender per department

The top-spending employee in each department based on non-cancelled order value. Concepts: CTEs, RANK() OVER (PARTITION BY ... ORDER BY ...), multi-table joins

Q11 — Running revenue total

Each non-cancelled order with its value and a running cumulative revenue total ordered by date. Concepts: CTE, SUM() OVER (ORDER BY ...) running total

Q12 — Salary ranking within department

Employees ranked by salary within their department using two ranking methods, plus a 3-tier salary split. Concepts: RANK(), DENSE_RANK(), NTILE() window functions

Q13 — Order status breakdown per employee

One row per employee with order counts pivoted into delivered / cancelled / shipped / pending columns. Concepts: conditional aggregation (CASE + SUM)

Q14 — Employee × department matrix

Every possible employee-department pairing, flagged for whether it's the employee's actual department. Concepts: CROSS JOIN, CASE

Key learnings / takeaways

This project helped me move past basic SELECT/WHERE queries and practice the SQL patterns used in real analysis. Main things I learned:

• INNER JOIN vs LEFT JOIN matters a lot. INNER JOIN only keeps rows that match in both tables, so employees with no orders disappear. LEFT JOIN keeps all rows from the left table even with no match (Q1 vs. Q2/Q3).
• A table can join to itself. The employees table has a manager_id that points back to another employee, so I joined employees to itself to get each person's manager's name (Q4).
• HAVING filters after grouping, WHERE filters before. To filter on an aggregate like an average salary, I had to use HAVING, because WHERE can't see the result of AVG() (Q6).
• COALESCE replaces NULLs with a default value. I used it to show "Uncategorised" instead of a blank category name (Q7).
• Window functions compare a row to its group without losing detail. Using OVER (PARTITION BY ...), I could compare each employee to their department's average or rank, while still keeping one row per employee instead of collapsing everything into a summary (Q9, Q10, Q12).
• RANK() and DENSE_RANK() handle ties differently. RANK() skips numbers after a tie (1, 2, 2, 4), DENSE_RANK() doesn't (1, 2, 2, 3) (Q12).
• Running totals can be done with a window function. SUM() OVER (ORDER BY ...) adds up values row by row, which gave me a running total without any extra subqueries (Q11).
• CASE + SUM can turn rows into columns. I used this to count orders by status (delivered, cancelled, shipped, pending) as separate columns for each employee (Q13).
• CROSS JOIN creates every possible combination. I used it to pair every employee with every department, on purpose (Q14).
• CTEs make a query easier to read. Writing a WITH block first and then querying from it helped break harder questions into smaller, clearer steps (Q9, Q10, Q11).

Tools

• MySQL 8 — database engine
• MySQL Workbench — used to design the database, run queries, and capture result screenshots