Introduction to databases and persistence

The Relational Model

SQL Basics, DDL, and DML

Querying Data

Advanced Concepts

Sqlite

Glossary of Database Terms

Practice

Assignment

• Teachers

Core program

Content

Exercise 1: The Todo List (Warmup)

Goal: Practice creating a single table and doing basic CRUD (Create, Read, Update, Delete) operations.

1. Create a file todo.db using sqlite3.
2. Create a table named todos with:
   • id (Integer, Primary Key)
   • task (Text, Not Null)
   • priority (Integer, Default 1)
   • completed (Boolean/Integer, Default 0)
3. Insert 3 tasks: "Buy Milk", "Wash Car" (High Priority), "Learn SQL".
4. Select all tasks.
5. Update "Buy Milk" to be completed.
6. Delete "Wash Car".

Exercise 2: The School (Relations & Joins)

Goal: Work with Foreign Keys and Joins.

1. Create the 3 tables: students, classes, enrollments (refer to Module 3 for the schema).

2. Insert:

   • 3 Students (Harry, Hermione, Ron)
   • 2 Classes (Potions, Transfiguration)

3. Enroll:

   • Harry in Potions and Transfiguration.
   • Hermione in Potions and Transfiguration.
   • Ron in Potions.

4. Query 1: List all students in "Potions".

5. Query 2: List all classes that "Harry" is taking.

6. Query 3: Count how many students are in each class.

Exercise 3: Design Challenge - The Recipe App

Goal: Design a schema from scratch for a real-world problem.

Scenario: You are building a recipe website.

• A Recipe has a name, description, and instructions.
• An Ingredient has a name (e.g., "Egg", "Flour").
• A Recipe needs many Ingredients.
• An Ingredient can be used in many Recipes.
• Crucial: When listing an ingredient for a recipe, you need the Quantity and Unit (e.g., "2 Eggs", "500g Flour").

Task:

1. Design the tables on paper or a text file. (Hint: You need a Join Table!).
2. Write the CREATE TABLE statements.
3. Insert "Pancakes":
   • Ingredients: 1 Cup Flour, 2 Eggs, 1 Cup Milk.
4. Write a query to get the full shopping list for "Pancakes".

Helpful Resources

• SQLite Documentation
• SQL Zoo (Interactive Tutorials)
• Khan Academy SQL Course

The HackYourFuture curriculum is licensed under CC BY-NC-SA 4.0

*https://hackyourfuture.net/*

Found a mistake or have a suggestion? Let us know in the feedback form.