Homework 1

• Due: Friday, March 27 at 11:55pm

• Points: 100 (20 questions × 5 points each)

• Submission: via GitHub Classroom

Overview¶

In this assignment you will write your first SQL queries against a manga library database. The goal is to get comfortable with the core building blocks of SQL: selecting data, filtering it, and sorting it.

You will work with a PostgreSQL database in your Coder workspace (or locally), write your queries in VSCode, and submit your work through GitHub Classroom.

Learning Objectives¶

By the end of this assignment you will be able to:

• Write SELECT statements to retrieve specific columns from a table

• Use WHERE to filter rows based on conditions

• Use ORDER BY to sort results in ascending and descending order

• Use DISTINCT to eliminate duplicate values

• Combine conditions with AND, OR, and NOT

• Use LIKE to match patterns in text

• Use BETWEEN to filter within a range

Accepting the Assignment¶

1. Click the GitHub Classroom invitation link.

2. Accept the assignment. This creates a private repository for you.

3. Open your Coder workspace at coder.cs.calvin.edu.

4. Clone your repository in the terminal:

   git clone <your-repo-url>

   Replace <your-repo-url> with the URL GitHub gives you after accepting.

5. Open the cloned folder in VSCode.

What You Are Building¶

You will write 20 SQL queries against a manga database. The database has one table:

Setup¶

Step 1 — Install VSCode Extensions¶

Open VSCode and install these two extensions (if you do not already have them):

• SQLTools — search mtxr.sqltools in the Extensions sidebar

• SQLTools PostgreSQL/Cockroach Driver — search mtxr.sqltools-driver-pg

Step 2 — Set Up Python¶

In the VSCode terminal, create and activate a virtual environment:

python3 -m venv venv
source venv/bin/activate
pip install -r requirements.txt

You should see (venv) at the start of your terminal prompt.

Important: Activate the virtual environment every time you open a new terminal window before running any Python commands.

Step 3 — Create the Database¶

With the virtual environment active, run the autograder once:

python3 grade.py

The first run will automatically create the manga database and load all the data. You will see the message:

Database not found — creating and loading data...

This is expected. After that, every question will show a red X because you have not written any queries yet.

Step 4 — Connect SQLTools¶

1. Click the database icon in the left sidebar to open SQLTools.

2. Click Add New Connection.

3. Choose PostgreSQL.

4. Fill in:

   • Server: localhost

   • Port: 5432

   • Database: manga

   • Username: admin

   • Password: admin

5. Click Test Connection, then Save Connection.

You can now run queries interactively from within VSCode.

Writing Your Queries¶

Your repository contains 20 SQL files: q1.sql through q20.sql.

Each file has a comment at the top describing the question and the expected output columns. Write your SQL query on the blank line below the comments.

Example — if q1.sql looks like this:

-- Question 1: List all manga titles in the database, ordered alphabetically.
-- Expected columns: title

You would add your query so the file becomes:

-- Question 1: List all manga titles in the database, ordered alphabetically.
-- Expected columns: title
SELECT title FROM manga ORDER BY title;

Running a Query Interactively¶

1. Open a .sql file.

2. Select (highlight) your query.

3. Press Cmd+Shift+P (Mac) or Ctrl+Shift+P (Windows/Linux).

4. Type and select SQLTools: Run Selected Query.

5. The results will appear in a panel below.

This lets you test and debug your queries before grading.

Checking Your Score¶

Run the autograder at any time:

python3 grade.py

You will see output like:

==================================================
  Homework 1 — Autograder
==================================================

  Q01: ✅  (5/5 pts)
  Q02: ❌  (0/5 pts)
  ...

==================================================
  Total: 5/100 pts
==================================================

Each question is all-or-nothing: your query must return exactly the correct columns and rows to earn the 5 points.

Questions Summary¶

Grading¶

The autograder also runs automatically on GitHub each time you push. You can view your results in the Actions tab of your repository.

Submitting¶

When you are done (or want to save progress):

git add -A
git commit -m "Submit Homework 1"
git push

You can push as many times as you like. Only your latest push is graded.

Troubleshooting¶

The database got messed up¶

If you accidentally ran INSERT, UPDATE, or DELETE and the data is wrong, reset it:

bash reset.sh

This drops and recreates the database from scratch. You do not need this for normal use.

grade.py says “file not found”¶

Make sure your query files are named q1.sql through q20.sql and are in the root of your repository (not in a subfolder).

SQLTools cannot connect¶

Make sure you ran python3 grade.py at least once first to create the database. Verify the connection settings match: localhost, port 5432, database manga, user admin, password admin.