import { useState } from "react"; import { useSQLQuery } from "@motherduck/react-sql-query"; const N = (v) => (v != null ? Number(v) : 0); export const REQUIRED_DATABASES = [ { type: "share", path: "md:_share/spj/85de884c-0743-4bdb-ba6e-619ae6d3eabf", alias: "spj" } ]; // DuckDB official brand colors — duckdb.org/design/manual const Y = "#fff100"; // Lemon Yellow — primary const OR = "#ff6900"; // Hot Orange — secondary const PU = "#7d66ff"; // Medium Slate Blue — secondary const BL = "#2eafff"; // Maya Blue — secondary const BK = "#0d0d0d"; // Black — body text / dark surfaces const GR = "#626262"; // Grey — muted text const BG = "#fffff8"; // Near-white warm background const LESSONS = [ { id: "intro", label: "1 · Meet the Data" }, { id: "select", label: "2 · SELECT & Filter" }, { id: "agg", label: "3 · Aggregations" }, { id: "join", label: "4 · JOINs" }, { id: "sub", label: "5 · Subqueries" }, { id: "sandbox", label: "🧪 Try It" }, ]; // Inline code chip function Code({ children, block }) { if (block) return ( {children} ); return ( {children} ); } // Striped result table function ResultTable({ data, isLoading, isError, error, caption }) { const rows = Array.isArray(data) ? data : []; if (isLoading) return (

{[60,80,50].map((w,i) => (

))}

); if (isError) return

⚠ {error?.message}

; if (!rows.length) return null; const cols = Object.keys(rows[0]); return (

{caption &&

{caption}

} {cols.map(c => )} {rows.map((row, i) => ( {cols.map(c => )} ))}

{c}
{String(row[c] ?? "")}

{rows.length} row{rows.length !== 1 ? "s" : ""} returned

); } function Section({ title, children }) { return (

{title}

{children}

); } // Yellow goal banner function GoalBanner({ children }) { return (

{children}

); } // Orange-accented explain callout function Explain({ children }) { return (

💬 {children}

); } // Dark tip box with yellow text function TipBox({ label, children }) { return (

💡 {label}

{children}

); } // Lesson pill nav buttons function PillNav({ examples, active, setActive }) { return (

{Object.entries(examples).map(([k, v]) => ( ))}

); } /* ─────────────────────── LESSON 1: INTRO ─────────────────────── */ function LessonIntro() { const s = useSQLQuery(`SELECT * FROM spj.suppliers ORDER BY sno`); const p = useSQLQuery(`SELECT * FROM spj.parts ORDER BY pno`); const j = useSQLQuery(`SELECT * FROM spj.projects ORDER BY jno`); const spj = useSQLQuery(`SELECT sno, pno, jno, qty FROM spj.spj ORDER BY sno, pno, jno`); return (

Welcome! This curriculum uses the classic Suppliers–Parts–Projects (SPJ) database, introduced by E.F. Codd and C.J. Date. It's small enough to understand instantly, rich enough to teach every fundamental SQL concept. Four tables. Lots of possibilities.

Who supplies things. Each supplier has a number, name, status, and city.

{`SELECT * FROM suppliers ORDER BY sno;`}

Physical components — name, color, weight, and storage city.

{`SELECT * FROM parts ORDER BY pno;`}

Engineering projects, each located in a city.

{`SELECT * FROM projects ORDER BY jno;`}

The fact table: which supplier sent which part to which project, and how many. Primary key is the combination of sno, pno, and jno.

{`SELECT sno, pno, jno, qty FROM spj ORDER BY sno, pno, jno;`}

The SPJ table connects three entities. This three-way relationship is why this dataset is so powerful for teaching JOINs, subqueries, and relational algebra.

); } /* ─────────────────────── LESSON 2: SELECT ─────────────────────── */ function LessonSelect() { const [active, setActive] = useState("a"); const examples = { a: { title: "Basic SELECT — choose your columns", sql: `SELECT sno, sname, city\nFROM suppliers;`, query: useSQLQuery(`SELECT sno, sname, city FROM spj.suppliers`), explain: "You don't have to SELECT *. Naming columns keeps results clean and explicit — a good habit from day one." }, b: { title: "WHERE — filter rows", sql: `SELECT *\nFROM parts\nWHERE color = 'Red';`, query: useSQLQuery(`SELECT * FROM spj.parts WHERE color = 'Red'`), explain: "WHERE narrows the result set. Only rows satisfying the condition are returned. String literals use single quotes in SQL." }, c: { title: "AND / OR — combine conditions", sql: `SELECT *\nFROM parts\nWHERE color = 'Red'\n AND weight > 12;`, query: useSQLQuery(`SELECT * FROM spj.parts WHERE color = 'Red' AND weight > 12`), explain: "Chain conditions with AND (both must be true) or OR (either suffices). Parentheses clarify precedence when mixing them." }, d: { title: "ORDER BY — sort your results", sql: `SELECT sno, sname, status\nFROM suppliers\nORDER BY status DESC;`, query: useSQLQuery(`SELECT sno, sname, status FROM spj.suppliers ORDER BY status DESC`), explain: "ASC (default) sorts lowest-to-highest. DESC flips it. You can sort by multiple columns: ORDER BY city, sname." }, e: { title: "LIMIT — cap your output", sql: `SELECT *\nFROM spj\nORDER BY qty DESC\nLIMIT 5;`, query: useSQLQuery(`SELECT * FROM spj.spj ORDER BY qty DESC LIMIT 5`), explain: "LIMIT is invaluable when exploring large tables. Pair it with ORDER BY to get the top-N rows meaningfully." }, }; const ex = examples[active]; return (

Goal: Retrieve exactly the data you need — right columns, right rows, right order.

{ex.sql} {ex.explain}

DuckDB lets you SELECT * EXCLUDE (city) to drop one column from a wildcard — handy when you want almost everything.

); } /* ─────────────────────── LESSON 3: AGGREGATIONS ─────────────────────── */ function LessonAgg() { const [active, setActive] = useState("a"); const examples = { a: { title: "COUNT — how many rows?", sql: `SELECT COUNT(*) AS total_shipments\nFROM spj;`, query: useSQLQuery(`SELECT COUNT(*) AS total_shipments FROM spj.spj`), explain: "COUNT(*) counts all rows. COUNT(column) counts non-NULL values in that column." }, b: { title: "SUM and AVG", sql: `SELECT\n SUM(qty) AS total_qty,\n AVG(qty) AS avg_qty,\n MIN(qty) AS min_qty,\n MAX(qty) AS max_qty\nFROM spj;`, query: useSQLQuery(`SELECT SUM(qty) AS total_qty, AVG(qty) AS avg_qty, MIN(qty) AS min_qty, MAX(qty) AS max_qty FROM spj.spj`), explain: "SUM, AVG, MIN, MAX all ignore NULLs. These aggregate the entire column into a single summary number." }, c: { title: "GROUP BY — aggregate per category", sql: `SELECT\n sno,\n COUNT(*) AS shipment_count,\n SUM(qty) AS total_qty\nFROM spj\nGROUP BY sno\nORDER BY total_qty DESC;`, query: useSQLQuery(`SELECT sno, COUNT(*) AS shipment_count, SUM(qty) AS total_qty FROM spj.spj GROUP BY sno ORDER BY total_qty DESC`), explain: "GROUP BY breaks the table into buckets — one per distinct sno value — and runs the aggregation inside each bucket." }, d: { title: "HAVING — filter groups", sql: `SELECT\n city,\n COUNT(*) AS supplier_count\nFROM suppliers\nGROUP BY city\nHAVING COUNT(*) > 1\nORDER BY supplier_count DESC;`, query: useSQLQuery(`SELECT city, COUNT(*) AS supplier_count FROM spj.suppliers GROUP BY city HAVING COUNT(*) > 1 ORDER BY supplier_count DESC`), explain: "WHERE filters rows before grouping. HAVING filters groups after. You can't use WHERE to filter aggregate results — that's what HAVING is for." }, e: { title: "COUNT DISTINCT — unique values", sql: `SELECT\n COUNT(DISTINCT sno) AS unique_suppliers,\n COUNT(DISTINCT pno) AS unique_parts,\n COUNT(DISTINCT jno) AS unique_projects\nFROM spj;`, query: useSQLQuery(`SELECT COUNT(DISTINCT sno) AS unique_suppliers, COUNT(DISTINCT pno) AS unique_parts, COUNT(DISTINCT jno) AS unique_projects FROM spj.spj`), explain: "DISTINCT inside an aggregate deduplicates first. Tells you how many different suppliers actually appear in the shipments table." }, }; const ex = examples[active]; return (

Goal: Summarize data — totals, averages, counts — across groups.

{ex.sql} {ex.explain}

DuckDB supports GROUP BY ALL — it automatically groups by every non-aggregated column in your SELECT. Great for exploratory work.

); } /* ─────────────────────── LESSON 4: JOINS ─────────────────────── */ function LessonJoin() { const [active, setActive] = useState("a"); const examples = { a: { title: "INNER JOIN — match rows across tables", sql: `SELECT\n spj.sno,\n s.sname,\n spj.pno,\n spj.jno,\n spj.qty\nFROM spj\nJOIN suppliers s ON spj.sno = s.sno\nORDER BY spj.qty DESC\nLIMIT 8;`, query: useSQLQuery(`SELECT spj.sno, s.sname, spj.pno, spj.jno, spj.qty FROM spj.spj spj JOIN spj.suppliers s ON spj.sno = s.sno ORDER BY spj.qty DESC LIMIT 8`), explain: "JOIN (same as INNER JOIN) combines rows where the ON condition matches. Rows with no match in either table are excluded." }, b: { title: "Three-table JOIN", sql: `SELECT\n s.sname,\n p.pname,\n j.jname,\n spj.qty\nFROM spj\nJOIN suppliers s ON spj.sno = s.sno\nJOIN parts p ON spj.pno = p.pno\nJOIN projects j ON spj.jno = j.jno\nORDER BY spj.qty DESC\nLIMIT 10;`, query: useSQLQuery(`SELECT s.sname, p.pname, j.jname, spj.qty FROM spj.spj spj JOIN spj.suppliers s ON spj.sno = s.sno JOIN spj.parts p ON spj.pno = p.pno JOIN spj.projects j ON spj.jno = j.jno ORDER BY spj.qty DESC LIMIT 10`), explain: "Stack multiple JOIN clauses to pull in all three dimension tables at once. The engine evaluates them left-to-right." }, c: { title: "Filtering after JOIN", sql: `SELECT\n s.sname,\n p.pname,\n j.jname,\n spj.qty\nFROM spj\nJOIN suppliers s ON spj.sno = s.sno\nJOIN parts p ON spj.pno = p.pno\nJOIN projects j ON spj.jno = j.jno\nWHERE s.city = 'London'\n AND p.color = 'Red';`, query: useSQLQuery(`SELECT s.sname, p.pname, j.jname, spj.qty FROM spj.spj spj JOIN spj.suppliers s ON spj.sno = s.sno JOIN spj.parts p ON spj.pno = p.pno JOIN spj.projects j ON spj.jno = j.jno WHERE s.city = 'London' AND p.color = 'Red'`), explain: "WHERE runs after the join. Here: London suppliers who shipped Red parts. Notice how JOIN + WHERE combine to answer rich business questions." }, d: { title: "LEFT JOIN — keep unmatched rows", sql: `SELECT\n s.sno,\n s.sname,\n COUNT(spj.pno) AS parts_shipped\nFROM suppliers s\nLEFT JOIN spj ON s.sno = spj.sno\nGROUP BY s.sno, s.sname\nORDER BY parts_shipped DESC;`, query: useSQLQuery(`SELECT s.sno, s.sname, COUNT(spj.pno) AS parts_shipped FROM spj.suppliers s LEFT JOIN spj.spj spj ON s.sno = spj.sno GROUP BY s.sno, s.sname ORDER BY parts_shipped DESC`), explain: "LEFT JOIN keeps all rows from the left table even when there's no match on the right. Unmatched columns appear as NULL — useful for finding 'missing' relationships." }, }; const ex = examples[active]; return (

Goal: Combine data from multiple tables by matching on shared keys.

{ex.sql} {ex.explain}

DuckDB supports USING (sno) when column names match: JOIN suppliers USING (sno) — shorter and avoids repeating the column name.

); } /* ─────────────────────── LESSON 5: SUBQUERIES ─────────────────────── */ function LessonSub() { const [active, setActive] = useState("a"); const examples = { a: { title: "Subquery in WHERE — IN list", sql: `-- Which suppliers ship to projects in London?\nSELECT DISTINCT sno\nFROM spj\nWHERE jno IN (\n SELECT jno\n FROM projects\n WHERE city = 'London'\n);`, query: useSQLQuery(`SELECT DISTINCT sno FROM spj.spj WHERE jno IN (SELECT jno FROM spj.projects WHERE city = 'London')`), explain: "The inner query runs first, returning a list of jno values. The outer query then filters to shipments whose jno is in that list." }, b: { title: "Subquery in WHERE — scalar", sql: `-- Parts heavier than average\nSELECT pno, pname, weight\nFROM parts\nWHERE weight > (\n SELECT AVG(weight) FROM parts\n)\nORDER BY weight DESC;`, query: useSQLQuery(`SELECT pno, pname, weight FROM spj.parts WHERE weight > (SELECT AVG(weight) FROM spj.parts) ORDER BY weight DESC`), explain: "A scalar subquery returns a single value. Here: the average weight. The outer query compares each part's weight to that single number." }, c: { title: "Subquery in FROM — derived table", sql: `SELECT sno, total_qty\nFROM (\n SELECT sno, SUM(qty) AS total_qty\n FROM spj\n GROUP BY sno\n) AS supplier_totals\nWHERE total_qty > 1000\nORDER BY total_qty DESC;`, query: useSQLQuery(`SELECT sno, total_qty FROM (SELECT sno, SUM(qty) AS total_qty FROM spj.spj GROUP BY sno) AS supplier_totals WHERE total_qty > 1000 ORDER BY total_qty DESC`), explain: "A subquery in FROM acts as a virtual table (derived table). You can then SELECT, filter, and sort its results as if it were a real table." }, d: { title: "CTE — cleaner subqueries", sql: `WITH supplier_totals AS (\n SELECT sno, SUM(qty) AS total_qty\n FROM spj\n GROUP BY sno\n)\nSELECT s.sname, st.total_qty\nFROM supplier_totals st\nJOIN suppliers s ON st.sno = s.sno\nWHERE st.total_qty > 1000\nORDER BY st.total_qty DESC;`, query: useSQLQuery(`WITH supplier_totals AS (SELECT sno, SUM(qty) AS total_qty FROM spj.spj GROUP BY sno) SELECT s.sname, st.total_qty FROM supplier_totals st JOIN spj.suppliers s ON st.sno = s.sno WHERE st.total_qty > 1000 ORDER BY st.total_qty DESC`), explain: "WITH…AS defines a Common Table Expression (CTE). The same logic as a derived table, but named and readable. Prefer CTEs over nested subqueries when possible." }, }; const ex = examples[active]; return (

Goal: Use the result of one query inside another — building up logic step by step.

{ex.sql} {ex.explain}

CTEs can reference each other: WITH a AS (…), b AS (SELECT … FROM a) — chain dependent steps cleanly.

); } /* ─────────────────────── LESSON 6: SANDBOX ─────────────────────── */ const CHALLENGES = [ { label: "Red or blue parts", sql: "SELECT * FROM parts WHERE color IN ('Red', 'Blue');" }, { label: "Total qty per part", sql: "SELECT pno, SUM(qty) AS total_qty FROM spj GROUP BY pno ORDER BY total_qty DESC;" }, { label: "Paris or London suppliers", sql: "SELECT sno, sname, city FROM suppliers WHERE city IN ('Paris', 'London');" }, { label: "Projects with part P1", sql: "SELECT DISTINCT jno FROM spj WHERE pno = 'P1';" }, { label: "Heaviest part per color", sql: "SELECT color, MAX(weight) AS max_weight FROM parts GROUP BY color ORDER BY max_weight DESC;" }, { label: "Top shipment by qty", sql: "SELECT sno, pno, jno, qty FROM spj ORDER BY qty DESC LIMIT 1;" }, ]; function LessonSandbox() { const [inputSQL, setInputSQL] = useState("SELECT * FROM suppliers;"); const [runSQL, setRunSQL] = useState(`SELECT * FROM spj.suppliers`); const result = useSQLQuery(runSQL); function rewriteSQL(raw) { // Replace bare table names in order: longest/most-specific first, // then replace bare `spj` only when NOT already preceded by `spj.` let q = raw.trim().replace(/;$/, ""); q = q.replace(/\bsuppliers\b/g, "spj.suppliers"); q = q.replace(/\bparts\b/g, "spj.parts"); q = q.replace(/\bprojects\b/g, "spj.projects"); // Only replace `spj` that is NOT already `spj.something` q = q.replace(/\bspj\b(?!\.)/g, "spj.spj"); return q; } function runQuery() { setRunSQL(rewriteSQL(inputSQL)); } function loadChallenge(sql) { setInputSQL(sql); setRunSQL(rewriteSQL(sql)); } return (

Try it yourself! Write any DuckDB query using bare table names: suppliers, parts, projects, spj.

{CHALLENGES.map((c, i) => ( ))}

setInputSQL(e.target.value)}
          rows={6}
          style={{
            width:"100%", fontFamily:"monospace", fontSize:13,
            background:"#1e1e1e", color:"#f0f0f0", border:`2px solid ${Y}`,
            borderRadius:8, padding:14, boxSizing:"border-box",
            resize:"vertical", lineHeight:1.6
          }}
        />
      </div>

<button onClick={runQuery} style={{
        background:Y, color:BK, border:`2px solid ${BK}`,
        borderRadius:6, padding:"8px 20px", fontSize:13,
        fontWeight:800, cursor:"pointer", marginBottom:16
      }}>▶ Run Query</button>

<TipBox label="Ideas to explore">
        <ul style={{margin:"4px 0 0", paddingLeft:18, lineHeight:1.9}}>
          <li>Which suppliers supply ALL parts? (Advanced: use NOT EXISTS or relational division)</li>
          <li>Which projects receive parts from more than 2 suppliers?</li>
          <li>What's the average qty per supplier city (requires a JOIN)?</li>
        </ul>
      </TipBox>
    </div>
  );
}

/* ─────────────────────── ROOT ─────────────────────── */
export default function DuckDBCurriculum() {
  const [lesson, setLesson] = useState("intro");

const lessonComponents = {
    intro:   <LessonIntro />,
    select:  <LessonSelect />,
    agg:     <LessonAgg />,
    join:    <LessonJoin />,
    sub:     <LessonSub />,
    sandbox: <LessonSandbox />,
  };

return (
    <div style={{background:BG, minHeight:"100vh", fontFamily:"system-ui, sans-serif", color:BK}}>
      {/* Header — Lemon Yellow */}
      <div style={{background:Y, color:BK, padding:"16px 24px", borderBottom:`3px solid ${BK}`}}>
        <div style={{display:"flex", alignItems:"baseline", gap:12}}>
          <h1 style={{margin:0, fontSize:20, fontWeight:900, letterSpacing:"-0.02em"}}>DuckDB Beginner's Guide</h1>
          <span style={{fontSize:13, opacity:0.65}}>via the SPJ Dataset</span>
        </div>
        <p style={{margin:"4px 0 0", fontSize:12, opacity:0.6}}>
          5 interactive lessons + a live sandbox · Suppliers · Parts · Projects
        </p>
      </div>

{/* Nav — Black bar, yellow active indicator */}
      <div style={{background:BK, padding:"0 16px", display:"flex", gap:2, overflowX:"auto"}}>
        {LESSONS.map(l => (
          <button key={l.id} onClick={() => setLesson(l.id)} style={{
            padding:"10px 16px", border:"none",
            borderBottom: lesson === l.id ? `3px solid ${Y}` : "3px solid transparent",
            background:"transparent",
            color: lesson === l.id ? Y : "#aaaaaa",
            fontWeight: lesson === l.id ? 700 : 400,
            cursor:"pointer", fontSize:13, whiteSpace:"nowrap", transition:"all 0.15s"
          }}>{l.label}</button>
        ))}
      </div>

{/* Content */}
      <div style={{maxWidth:780, margin:"0 auto", padding:"24px 20px"}}>
        {lessonComponents[lesson]}
      </div>
    </div>
  );
}
</article></body></html>

DuckDB Beginner's Guide — SPJ Dataset

Comments