SQL Grader

Interactive grading and review tooling for Binghamton University's Coding in SQL course and crash-course style assessments.

This project automatically runs student SQL against a reference answer key, compares outputs, generates an interactive HTML review report, and supports instructor-side manual score adjustments with saved correction files.

Table of Contents

• About
• Course Context
• Official Binghamton Links
• Features
• How It Works
• Repository Structure
• Quick Start
• Outputs
• Grading Rules
• Manual Review Workflow
• Sources
• License

🚀 About

SQL Grader is a Python-based grading pipeline built for introductory SQL instruction. It is especially well suited for workshop, bootcamp, and classroom settings where students submit one SQL response per question and instructors need:

• automatic execution against a shared SQLite database
• answer-key comparison by result set rather than string matching
• a side-by-side HTML review experience for student and reference queries
• manual score overrides for edge cases and partial credit
• exportable grading artifacts for archival or follow-up

The generated report includes:

• an interactive grading grid
• side-by-side query/result comparison
• question comments pulled from the answer file
• session-based manual score corrections
• import/export of saved correction files
• grade distribution visualization
• summary statistics including min, max, average, median, quartiles, and IQR

🎓 Course Context

This grader is designed around Coding in SQL, a Binghamton University offering described publicly as:

• a foundational SQL learning experience
• an ideal first step into data analytics
• a course where no prior programming or statistics experience is expected
• a workshop that teaches students how databases store and retrieve large amounts of information efficiently
• a learning experience focused on writing SQL to manipulate and summarize data

Public course and badge descriptions also indicate that students work on topics such as:

• filtering
• aggregate functions
• grouping
• sorting
• joins
• basic database design
• creating and organizing databases

The Credly badge page further notes that learners earn the credential by passing the assessment with 70% or better.

🔗 Official Binghamton Links

• Harpur Edge crash course page: Harpur Edge Crash Courses
• Binghamton course catalog page: DIDA 110 / Database Fundamentals (SQL)
• DIDA page: Data Science and Analytics | Digital and Data Studies

✨ Features

• Automatic SQL grading: Executes student and reference SQL against the same SQLite database.
• Result-based comparison: Grades based on returned rows instead of exact query text.
• Mismatch classification: Distinguishes between pass, fail, missing_column, extra_column, and row_count_mismatch.
• Interactive HTML review report: Lets instructors inspect each answer in a click-through grid.
• Formatted SQL display: Renders both student and answer queries in a readable multiline format.
• Question prompt comments: Pulls pre-query comments from answers.sql into the viewer.
• Manual score adjustments: Supports override points and notes for failed or warning results.
• Saved corrections: Uses browser session state plus JSON import/export for persistent review workflows.
• Grade analytics: Includes a live grade distribution chart and summary statistics table.
• Cross-platform runners: Includes both run.sh and run.bat.

🧠 How It Works

The grading flow is intentionally split into three Python modules:

• main.py: CLI entrypoint that validates inputs and coordinates the full run.
• grader.py: grading logic, SQL parsing, execution, comparison, and result construction.
• renderer.py: report generation, HTML viewer rendering, CSV/JSON exports, and manual adjustment support.

At a high level:

1. The CLI receives paths to the database, answer key, submissions CSV, and output directory.
2. The answer key is split into numbered question sections.
3. Student responses are read from the CSV and matched to numbered question columns.
4. Each student answer is executed against the SQLite database.
5. The returned result set is compared to the reference result set.
6. Per-question and per-student results are assembled.
7. A report bundle is written to disk, including the interactive viewer.

🗂 Repository Structure

.
├── main.py               # main CLI entrypoint
├── grader.py             # grading and comparison logic
├── renderer.py           # HTML/CSV/JSON report generation
├── run.sh                # macOS/Linux convenience runner
├── run.bat               # Windows convenience runner
├── course.db             # SQLite grading database
├── answers.sql           # reference answers (gitignored)
├── submissions.csv       # student submissions (gitignored)
└── grading_output/       # generated report bundle

Important

answers.sql and submissions.csv are intentionally left out of this repo. That keeps answer keys and student work out of Git history and allows you to upload yours.

🛠 Quick Start

Option 1: Use the helper scripts

On macOS or Linux:

bash run.sh

On Windows:

run.bat

Option 2: Run the grader directly

python main.py \
  --db course.db \
  --answers answers.sql \
  --responses submissions.csv \
  --output grading_output

Python environment

The helper scripts will:

• create a local virtual environment if needed
• install pandas if it is missing
• preserve grading_output/manual_adjustments.json
• regenerate the report bundle

📦 Outputs

After a successful run, the grader writes a report bundle to grading_output/:

• index.html: interactive browser-based review report
• results.json: structured grading data
• results.csv: flattened per-question grading export
• manual_adjustments.json: saved manual correction file for reuse

📏 Grading Rules

The current grading behavior is result-oriented:

• row order matters
• column order inside a row does not matter for correctness
• row counts must match exactly
• fewer columns than expected are labeled missing_column
• more columns than expected are labeled extra_column
• pass and extra_column currently receive full automatic credit

This design is useful in introductory SQL instruction because it tolerates some harmless query formulation differences while still surfacing structural mistakes clearly.

📝 Manual Review Workflow

The HTML report is built for instructor review rather than just batch scoring.

In the report you can:

• click any graded cell to inspect details
• compare the student query and reference query side by side
• inspect result tables with column headers
• review prompt comments from the answer key
• assign manual partial credit
• add a note explaining the override
• save corrections to JSON
• reload corrections into a later review session

The report also recalculates:

• student totals
• override counts
• grade distribution
• grade summary statistics

📚 Sources

• Harpur Edge: Harpur Edge Crash Courses
• Binghamton course catalog: DIDA 110 / Database Fundamentals (SQL)
• Digital and Data Studies: Data Science and Analytics
• Binghamton University Events Calendar: Coding in SQL Crash Course
• Credly badge description: Coding in SQL

The specific B-Engaged RSVP page provided as source context was not directly retrievable in the browser tool during generation, so this README uses the public Binghamton and Credly descriptions above as the authoritative references.

Back to top