Teachers

Week 12 Assignment

Task 1 — SQL queries on the books database

Open books_library.db in DBeaver or the SQLite CLI. Write a SQL query to answer each question below. Save your queries in a file called queries.sql with a comment above each one indicating which question it answers.

<aside> 💡

If you completed the practice exercises you will already be familiar with this database. The schema is: authors (id, first_name, last_name, nationality, birth_year) and books (id, title, published_year, genre, author_id).

</aside>

Queries

Question 1 — List the title and published year of every book in the 'Science Fiction' genre, ordered by published year (oldest first).

Question 2 — Show every book published before 1950. Display the title and year only.

Question 3 — Show every book in the database along with its author's full name. Combine first_name and last_name into a single column called author. (Hint: you will need a JOIN.)

Question 4 — List all books written by Stephen King. Show the title and published year, ordered by year. (Hint: JOIN the two tables and filter on the author's name.)

Question 5 — Add yourself as a new author. Use your real name, or make one up. Pick any nationality and birth year.

Question 6 — Add one book for the author you just inserted. It can be a real book or a made-up one.

Question 7 — The genre for "The Dark Tower: The Gunslinger" was entered incorrectly as 'Fantasy'. It should be 'Horror'. Write an UPDATE to fix it, then verify the change with a SELECT.

Question 8 — Delete the book you added in Question 6. Make sure your query targets only that specific row.

Bonus questions (optional)

These cover topics slightly beyond the core material. Have a go if you finish early.

Bonus A — How many books are there per genre? Show the genre name and the count, ordered from most to fewest books.

Bonus B — Find any authors in the database who have no books at all. (Hint: you will need a LEFT JOIN and check for NULL.)

Task 2 — Refactor the flashcard app

You are given a small Node.js app that manages study flashcards. It currently stores all data in a JSON file. Your job is to replace that file-based storage layer with SQLite, one function at a time.

Getting started

Run npm install to install dependencies.
Run node app.js — you should see the app working, printing decks, cards, and operations to the console. Take a note of this output. When you are done with the refactor, running node app.js should produce the same result.

Understand the structure

Before changing anything, read through the two files:

src/storage.js — all data access lives here. Each function reads or writes data/data.json. This is the only file you will change.
app.js — calls the storage functions to demonstrate each operation. You will not change this file.

The functions in storage.js are:

Function	What it does
`getAllDecks()`	Returns every deck
`getDeckById(id)`	Returns one deck by id
`addDeck(name, description)`	Inserts a new deck, returns it
`getAllCardsForDeck(deckId)`	Returns all cards belonging to a deck
`addCard(question, answer, deckId)`	Inserts a new card, returns it
`markCardLearned(cardId)`	Sets `learned = true` on a card, returns it
`deleteCard(cardId)`	Deletes a card, returns true/false

Step 1 — Create the database schema

Create a new file called setup.sql. In it, write the DDL to create two tables:

decks

Column	Type	Constraints
id	INTEGER	PRIMARY KEY AUTOINCREMENT
name	TEXT	NOT NULL
description	TEXT

cards

Column	Type	Constraints
id	INTEGER	PRIMARY KEY AUTOINCREMENT
question	TEXT	NOT NULL
answer	TEXT	NOT NULL
learned	INTEGER	NOT NULL DEFAULT 0
deck_id	INTEGER	NOT NULL, FOREIGN KEY → decks

Note: SQLite does not have a boolean type. Use INTEGER with 0 for false and 1 for true.

Create the database by running:

sqlite3 flashcards.db < setup.sql

Or load it through DBeaver as you did on the SQL Scripts page.

Step 2 — Migrate the existing data

Create a new file called migrate.js. It should:

Read data/data.json
Open flashcards.db using better-sqlite3
Insert every deck from the JSON into the decks table
Insert every card from the JSON into the cards table (remember learned is 0/1, not false/true)

Run it once:

node migrate.js

Open the database in DBeaver and confirm the data is there before moving on.

Step 3 — Replace the storage functions one by one

Install better-sqlite3:

npm install better-sqlite3

At the top of storage.js, open the database:

import Database from 'better-sqlite3';
import { fileURLToPath } from 'url';
import { dirname, join } from 'path';

const __dirname = dirname(fileURLToPath(import.meta.url));
const db = new Database(join(__dirname, '../flashcards.db'));

Now replace each function, one at a time, running node app.js after each one to confirm it still works.

getAllDecks() — replace with a SELECT * FROM decks query using .all().

getDeckById(id) — replace with a SELECT ... WHERE id = ? query using .get().

addDeck(name, description) — replace with an INSERT using .run(). Return an object with id (from info.lastInsertRowid), name, and description.

getAllCardsForDeck(deckId) — replace with a SELECT ... WHERE deck_id = ? query. Note the column is deck_id in the database but deckId in the JSON — use AS to alias it so the rest of the app still works:

SELECT id, question, answer, learned, deck_id AS deckId FROM cards WHERE deck_id = ?

addCard(question, answer, deckId) — replace with an INSERT. Return the new card object including its id.

markCardLearned(cardId) — replace with an UPDATE cards SET learned = 1 WHERE id = ?. Then fetch and return the updated card.

deleteCard(cardId) — replace with a DELETE FROM cards WHERE id = ?. Return true if a row was deleted (info.changes > 0), false otherwise.

Step 4 — Final check

Once all functions are replaced:

Run node app.js and compare the output to the notes you took in Step 1. It should be identical.
Open flashcards.db in DBeaver and confirm the data looks correct.
You can now delete data/data.json and the readData/writeData helpers from storage.js — they are no longer needed.

<aside> 💡

</aside>