Lab 07: Working with Databases

Objective

Practice creating a simple database, writing SQL queries, and understanding the difference between relational and document data models using free online tools.

Duration

20 to 30 minutes

Prerequisites

All platforms: A web browser (for SQLBolt) and a text editor
Optional: Python 3 installed (for Part 3)

💡Note: This lab uses browser-based tools, so it works identically on Windows, macOS, and Linux.

Instructions

Part 1: SQL Basics with SQLBolt

Open SQLBolt in your browser.
Complete Lessons 1 through 4:
- Lesson 1: SELECT queries
- Lesson 2: Queries with constraints (WHERE)
- Lesson 3: Queries with constraints (continued)
- Lesson 4: Filtering and sorting
Complete Lesson 13: Inserting rows (INSERT)
Complete Lesson 14: Updating rows (UPDATE)
Complete Lesson 15: Deleting rows (DELETE)

✅Tip: Don't rush through these. Type the queries yourself rather than copying the answers. Muscle memory matters for SQL.

Part 2: Design a Simple Schema

Using any text editor (VS Code, Notepad, nano, or even paper), design a database schema for a simple bookstore:

Create a books table with columns: id, title, author, price, published_year
Create an orders table with columns: id, book_id (foreign key), customer_name, order_date, quantity
Write the SQL to:
- Insert 3 books
- Insert 2 orders
- Find all books published after 2020
- Find all orders for a specific customer

Example answer (try it yourself first):

-- Create tables
CREATE TABLE books (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  author TEXT NOT NULL,
  price DECIMAL(10,2),
  published_year INTEGER
);

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  book_id INTEGER REFERENCES books(id),
  customer_name TEXT NOT NULL,
  order_date DATE,
  quantity INTEGER DEFAULT 1
);

-- Insert books
INSERT INTO books (id, title, author, price, published_year)
VALUES (1, 'Cloud Architecture Patterns', 'Jane Smith', 45.99, 2023);

INSERT INTO books (id, title, author, price, published_year)
VALUES (2, 'AWS in Action', 'John Doe', 39.99, 2022);

INSERT INTO books (id, title, author, price, published_year)
VALUES (3, 'Linux Administration', 'Maria Garcia', 29.99, 2021);

-- Insert orders
INSERT INTO orders (id, book_id, customer_name, order_date, quantity)
VALUES (1, 1, 'Alice Johnson', '2026-05-01', 1);

INSERT INTO orders (id, book_id, customer_name, order_date, quantity)
VALUES (2, 2, 'Alice Johnson', '2026-05-03', 2);

-- Query: books after 2020
SELECT title, author, published_year FROM books WHERE published_year > 2020;

-- Query: orders for Alice
SELECT * FROM orders WHERE customer_name = 'Alice Johnson';

Part 3: Document Data Model

Convert your bookstore schema to a document (JSON) model. Create a file called bookstore.json:

macOS / Linux / WSL / Git Bash:

cat > bookstore.json << 'EOF'
{
  "books": [
    {
      "id": 1,
      "title": "Cloud Architecture Patterns",
      "author": "Jane Smith",
      "price": 45.99,
      "published_year": 2023,
      "orders": [
        {"customer": "Alice Johnson", "date": "2026-05-01", "quantity": 1}
      ]
    },
    {
      "id": 2,
      "title": "AWS in Action",
      "author": "John Doe",
      "price": 39.99,
      "published_year": 2022,
      "orders": [
        {"customer": "Alice Johnson", "date": "2026-05-03", "quantity": 2}
      ]
    }
  ]
}
EOF

Windows PowerShell:

@'
{
  "books": [
    {
      "id": 1,
      "title": "Cloud Architecture Patterns",
      "author": "Jane Smith",
      "price": 45.99,
      "published_year": 2023,
      "orders": [
        {"customer": "Alice Johnson", "date": "2026-05-01", "quantity": 1}
      ]
    },
    {
      "id": 2,
      "title": "AWS in Action",
      "author": "John Doe",
      "price": 39.99,
      "published_year": 2022,
      "orders": [
        {"customer": "Alice Johnson", "date": "2026-05-03", "quantity": 2}
      ]
    }
  ]
}
'@ | Set-Content bookstore.json

Validate the JSON:

macOS / Linux / WSL / Git Bash:

python3 -m json.tool < bookstore.json

Windows PowerShell:

Get-Content bookstore.json | python -m json.tool

Consider: When would the relational model be better? When would the document model be better?

Relational wins: When you need to query orders independently of books, join across tables, or enforce referential integrity
Document wins: When you always access a book with its orders together, need flexible schemas, or want to avoid joins

Part 4: Query with Python (Optional)

Create a file called query_books.py:

import json

# Load the document database
with open('bookstore.json', 'r') as f:
    data = json.load(f)

# Find books published after 2021
recent_books = [b for b in data['books'] if b['published_year'] > 2021]
print("Books published after 2021:")
for book in recent_books:
    print(f"  - {book['title']} ({book['published_year']}) - ${book['price']}")

# Find all orders by Alice
print("\nAlice's orders:")
for book in data['books']:
    for order in book.get('orders', []):
        if order['customer'] == 'Alice Johnson':
            print(f"  - {book['title']} x{order['quantity']} on {order['date']}")

Run it:

python3 query_books.py       # macOS / Linux / WSL
python query_books.py        # Windows

Validation

Completed SQLBolt Lessons 1-4, 13-15
Designed a relational schema with two tables and a foreign key
Wrote INSERT, SELECT, UPDATE, and DELETE queries
Created a JSON document model and validated it
Identified when relational vs. document models are appropriate
(Optional) Queried JSON data with Python

AWS Bootcamp: From Novice to Architect Author: Samuel Ogunti License: CC BY-NC 4.0

Lab: Module 08: Databases and Data

Lab 07: Working with Databases