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
bookstable with columns:id,title,author,price,published_year - Create an
orderstable 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