BBampaa

Lessons › SQL › Deep Dive: Database Admin & ETL

SQL lesson

Star schema basics: fact tables and dimension tables

A star schema splits a database into two kinds of tables: a fact table that records every event along with its measurements.

Try this lesson free →
About 5–10 minutes · no sign-up needed

Example

Understanding Retail Star Schemas with Fact and Dimension Tables

SELECT c.region, SUM(o.revenue) AS total_revenue FROM orders o JOIN customers c ON o.customer_id = c.customer_id GROUP BY c.region ORDER BY total_revenue DESC

Result:

regiontotal_revenue
West199.98
Southeast149.99
Northeast149.95
Midwest44.99

Step by step

  1. Run the query without SUM so you can see what the unfiltered values look like.
    SELECT c.region, o FROM orders o JOIN customers c ON o.customer_id = c.customer_id GROUP BY c.region ORDER BY total_revenue DESC;
  2. SUM totals the numbers in a column. Works on numeric columns only.
    SELECT c.region, SUM(o.revenue) FROM orders o JOIN customers c ON o.customer_id = c.customer_id GROUP BY c.region ORDER BY total_revenue DESC;
  3. AS gives the SUM result a friendly column name so the output reads cleanly.
    SELECT c.region, SUM(o.revenue) AS total_revenue FROM orders o JOIN customers c ON o.customer_id = c.customer_id GROUP BY c.region ORDER BY total_revenue DESC;

Practice

Try each one in your head first, then open the answer.

1. Join the orders table to the customers table on customer_id and return customer_id, orders.order_id, and customers.full_name.
SELECT a.customer_id, a.order_id, b.full_name
FROM orders a
JOIN customers b ON a.customer_id = b.customer_id
2. Join the orders table to the shipments table on order_id and return order_id, orders.customer_id, and shipments.shipment_id.
SELECT a.order_id, a.customer_id, b.shipment_id
FROM orders a
JOIN shipments b ON a.order_id = b.order_id
3. Join the orders table to the returns table on order_id and return order_id, orders.customer_id, and returns.return_id.
SELECT a.order_id, a.customer_id, b.return_id
FROM orders a
JOIN returns b ON a.order_id = b.order_id
4. Join the products table to the reviews table on product_id and return product_id, products.brand, and reviews.review_id.
SELECT a.product_id, a.brand, b.review_id
FROM products a
JOIN reviews b ON a.product_id = b.product_id
5. Join the products table to the inventory table on product_id and return product_id, products.brand, and inventory.warehouse.
SELECT a.product_id, a.brand, b.warehouse
FROM products a
JOIN inventory b ON a.product_id = b.product_id

Learn it by doing. This lesson is a short hands-on level on Bampaa: see it work, then build and write it yourself, with instant feedback.

Try this lesson free →
About 5–10 minutes · no sign-up needed