Pipeline — Databases

A progressive journey through SQL and MongoDB — from basic table creation and CRUD operations to multi-table joins, aggregations, constraints, triggers, and NoSQL document operations with Python/PyMongo.

---

Learning Objectives

---

Task-by-Task Reference

Each task below highlights the unique challenge it posed and the new technique introduced — techniques from earlier tasks are not repeated.

---

Task 0 — Create Database (0-create_database_if_missing.sql)

Challenge: Set up the foundational container for all subsequent work — the database itself — with idempotent execution.

Approach: CREATE DATABASE IF NOT EXISTS db_0 — safely re-runnable without errors.

New techniques introduced:

Key takeaway: Every SQL workflow starts with database creation. IF NOT EXISTS makes scripts safe for repeated execution.

---

Task 1 — Create Table (1-first_table.sql)

Challenge: Define a table structure with typed columns — introducing DDL and data type selection.

Approach: CREATE TABLE IF NOT EXISTS first_table (id INT, name VARCHAR(256)) — two columns with specific types, backtick-quoted identifiers.

New techniques introduced:

Key takeaway: Every table column needs an explicit data type. Choose INT for numbers, VARCHAR(N) for strings — the type constrains what data can be stored.

---

Task 2 — List All Rows (2-list_values.sql)

Challenge: Retrieve the entire contents of a table — the simplest possible read operation.

Approach: SELECT * FROM first_table — the wildcard * pulls every column.

New techniques introduced:

Key takeaway: SELECT * is the simplest read operation — it returns every row and every column from the specified table.

---

Task 3 — Insert Row (3-insert_value.sql)

Challenge: Add data to the table — introducing the write side of CRUD.

Approach: INSERT INTO first_table (id, name) VALUES (89, 'Best School') — explicit column naming makes the intent clear.

New techniques introduced:

Key takeaway: Named columns in INSERT make the operation explicit and maintainable — you're not relying on column order.

---

Task 4 — Filter and Sort (4-best_score.sql)

Challenge: Retrieve only records meeting a condition, ordered by a specific column — introducing WHERE and ORDER BY.

Approach: SELECT score, name FROM second_table WHERE score >= 10 ORDER BY score DESC — filter first, then sort.

New techniques introduced:

Key takeaway: WHERE filters rows before they're returned; ORDER BY sequences the surviving rows. Both operate on the result set, not the table.

---

Task 5 — Average (5-average.sql)

Challenge: Compute a single aggregate value across all rows — introducing aggregate functions.

Approach: SELECT AVG(score) AS average FROM second_table — collapses all rows into one number.

New techniques introduced:

Key takeaway: Aggregate functions like AVG() collapse multiple rows into a single computed value — the result is one row, not one per input row.

---

Task 6 — Aggregate with Grouping (6-avg_temperatures.sql)

Challenge: Compute averages per city — introducing GROUP BY for partitioned aggregation.

Approach: SELECT city, AVG(value) AS avg_temp FROM temperatures GROUP BY city ORDER BY avg_temp DESC — group, aggregate within each group, then sort groups.

New techniques introduced:

Key takeaway: GROUP BY partitions data before aggregation — each group gets its own AVG(), MAX(), or COUNT(). Without it, aggregates apply globally.

---

Task 7 — Maximum with Grouping (7-max_state.sql)

Challenge: Find the maximum temperature per state — applying MAX() in a grouped context.

Approach: SELECT state, MAX(value) AS max_temp FROM temperatures GROUP BY state ORDER BY state — same pattern as Task 6, with MAX() instead of AVG().

New techniques introduced:

Key takeaway: MAX() finds the highest value per group — same GROUP BY pattern, different aggregate. The grouping logic is identical across all aggregates.

---

Task 8 — Inner Join (8-genre_id_by_show.sql)

Challenge: Connect two related tables to list shows that have genres — introducing INNER JOIN.

Approach: SELECT tv_shows.title, tv_show_genres.genre_id FROM tv_shows INNER JOIN tv_show_genres ON tv_shows.id = tv_show_genres.show_id ORDER BY title, genre_id.

New techniques introduced:

Key takeaway: INNER JOIN returns only rows where a match exists in BOTH tables. If a show has no genre, it's excluded from the result.

---

Task 9 — Left Join: Missing Genres (9-no_genre.sql)

Challenge: List shows that have NO linked genre — the anti-join pattern using LEFT JOIN + IS NULL.

Approach: SELECT tv_shows.title, tv_show_genres.genre_id FROM tv_shows LEFT JOIN tv_show_genres ON tv_shows.id = tv_show_genres.show_id WHERE tv_show_genres.genre_id IS NULL ORDER BY title.

New techniques introduced:

Key takeaway: LEFT JOIN preserves every row from the left table. When there's no match, right-table columns are NULL — filter on that to find orphans.

---

Task 10 — Count with Grouping (10-count_shows_by_genre.sql)

Challenge: Count how many shows belong to each genre — introducing COUNT() with joins and grouping.

Approach: SELECT tv_genres.name AS genre, COUNT(tv_show_genres.genre_id) AS number_of_shows FROM tv_genres LEFT JOIN tv_show_genres ON tv_genres.id = tv_show_genres.genre_id GROUP BY tv_genres.name ORDER BY number_of_shows DESC.

New techniques introduced:

Key takeaway: COUNT() with LEFT JOIN ensures even empty categories appear (with count 0). INNER JOIN + COUNT() would silently drop them.

---

Task 11 — Sum Ratings (11-rating_shows.sql)

Challenge: Sum ratings for each show — introducing SUM() aggregate.

Approach: SELECT tv_shows.title, SUM(tv_show_ratings.rate) AS rating FROM tv_shows LEFT JOIN tv_show_ratings ON tv_shows.id = tv_show_ratings.show_id GROUP BY tv_shows.title ORDER BY rating DESC.

New techniques introduced:

Key takeaway: SUM() totals numeric values. With LEFT JOIN, shows without ratings return NULL — use COALESCE(SUM(...), 0) if you want 0 instead.

---

Task 12 — Sum with Chained Joins (12-rating_genres.sql)

Challenge: Sum ratings by genre — navigating three tables in one query with chained LEFT JOINs.

Approach: SELECT tv_genres.name, SUM(tv_show_ratings.rate) AS rating FROM tv_genres LEFT JOIN tv_show_genres ON tv_genres.id = tv_show_genres.genre_id LEFT JOIN tv_show_ratings ON tv_show_genres.show_id = tv_show_ratings.show_id GROUP BY tv_genres.name ORDER BY rating DESC.

New techniques introduced:

Key takeaway: Chain multiple joins to traverse complex relationships. Each join adds one more hop through the schema — the query grows linearly with the path length.

---

Task 13 — Unique Constraints (13-uniq_users.sql)

Challenge: Create a users table that prevents duplicate emails — introducing data integrity constraints.

Approach: CREATE TABLE IF NOT EXISTS users (id INT NOT NULL AUTO_INCREMENT, email VARCHAR(256) NOT NULL UNIQUE, name VARCHAR(256), PRIMARY KEY (id)).

New techniques introduced:

Key takeaway: Constraints like NOT NULL, UNIQUE, and PRIMARY KEY enforce data integrity at the database level — bad data is rejected before it enters the table.

---

Task 14 — ENUM Constraint (14-country_users.sql)

Challenge: Extend the users table with a country column restricted to three specific values — introducing enumerated types.

Approach: CREATE TABLE ... country ENUM('US', 'CO', 'TN') NOT NULL DEFAULT 'US' — only these three values are accepted.

New techniques introduced:

Key takeaway: ENUM acts as a check constraint on strings — the database rejects any value not in the list. Use it for columns with a fixed, known set of valid options.

---

Task 15 — Real-World Aggregation (15-fans.sql)

Challenge: Rank band origins by total fans from a real dataset — applying grouping and summing to non-trivial data.

Approach: SELECT origin, SUM(fans) AS nb_fans FROM metal_bands GROUP BY origin ORDER BY nb_fans DESC — group by text column, sum numeric.

New techniques introduced:

Key takeaway: GROUP BY and SUM() work with any data type — string columns for grouping, numeric columns for aggregation. The pattern is universal.

---

Task 16 — Pattern Matching and COALESCE (16-glam_rock.sql)

Challenge: Find Glam rock bands, compute their lifespan, and handle active bands (no split year) — introducing LIKE and COALESCE.

Approach: SELECT band_name, COALESCE(split, 2020) - formed AS lifespan FROM metal_bands WHERE style LIKE '%Glam rock%' ORDER BY lifespan DESC.

New techniques introduced:

Key takeaway: COALESCE() provides fallback values for NULL — use it to handle missing data gracefully. Arithmetic in SELECT creates computed columns on the fly.

---

Task 17 — AFTER INSERT Trigger (17-store.sql)

Challenge: Automatically decrement item quantity when an order is placed — introducing database triggers for reactive logic.

Approach: CREATE TRIGGER decrease_quantity AFTER INSERT ON orders FOR EACH ROW UPDATE items SET quantity = quantity - NEW.number WHERE name = NEW.item_name.

New techniques introduced:

Key takeaway: Triggers automate reactive operations — when an order is placed, inventory updates itself. NEW refers to the row being inserted; OLD refers to the row being updated/deleted.

---

Task 18 — BEFORE UPDATE Trigger (18-valid_email.sql)

Challenge: Reset the valid_email flag whenever a user's email address changes — introducing conditional trigger logic with BEFORE UPDATE.

Approach: CREATE TRIGGER reset_valid_email BEFORE UPDATE ON users FOR EACH ROW IF OLD.email != NEW.email THEN SET NEW.valid_email = 0; END IF.

New techniques introduced:

Key takeaway: BEFORE UPDATE triggers can inspect and modify data before it hits the table. Compare OLD vs NEW to detect changes, then adjust NEW to enforce business rules.

---

Schema Reference

hbtn_0d_tvshows.sql — TV Shows + Genres (Many-to-Many)

Pattern: Junction table with composite foreign keys enables many-to-many relationships.

hbtn_0d_tvshows_rate.sql — TV Shows + Genres + Ratings

Adds tv_show_ratings (show_id, rate) — a fact table allowing multiple ratings per show.

metal_bands.sql — Bands Dataset

Single flat table: metal_bands (id, band_name, fans, formed, origin, split, style). Denormalized for simple queries. YEAR data type for formed/split. split is NULL for active bands.

temperatures.sql — Weather Time-Series

Flat table: temperatures (city, state, year, month, value). Designed for efficient GROUP BY queries on city and time dimensions.

---

Technique Inventory

---

MongoDB Shell (Tasks 22–29)

Each task below highlights the unique challenge it posed and the new technique introduced — techniques from MySQL tasks are not repeated.

---

Task 22 — List Databases (22-list_databases)

Challenge: Discover what databases exist on the MongoDB server — the first interaction with a new NoSQL environment.

Approach: show dbs — MongoDB shell's equivalent of SHOW DATABASES in MySQL. Returns all database names and their sizes.

New techniques introduced:

Key takeaway: show dbs is your first command in MongoDB — it confirms the server is reachable and shows available databases. Unlike MySQL, databases are lazily created on first insert.

---

Task 23 — Select/Create Database (23-use_or_create_database)

Challenge: Switch to (or implicitly create) a database — MongoDB has no explicit CREATE DATABASE.

Approach: use my_db — MongoDB creates the database automatically when you first insert data into it. The use command switches context without requiring the database to already exist.

New techniques introduced:

Key takeaway: In MongoDB, use switches context; the database isn't physically created until you insert a document. This is fundamentally different from MySQL's explicit CREATE DATABASE.

---

Task 24 — Insert Document (24-insert)

Challenge: Add data to a MongoDB collection — introducing document insertion with JSON-like syntax.

Approach: db.school.insert({ name: "Holberton school" }) — insert a BSON document (JavaScript object) into the school collection. Collections are created implicitly on first insert.

New techniques introduced:

Key takeaway: MongoDB stores JSON-like documents. insert() adds a document to a collection — both the collection and database are created implicitly if they don't exist.

---

Task 25 — Find All Documents (25-all)

Challenge: Retrieve all documents from a collection — the MongoDB equivalent of SELECT *.

Approach: db.school.find() — without arguments, returns every document in the collection. Outputs documents in readable JSON format.

New techniques introduced:

Key takeaway: find() with no arguments returns all documents — MongoDB's SELECT *. The shell automatically iterates the cursor and pretty-prints results.

---

Task 26 — Find with Filter (26-match)

Challenge: Retrieve only documents matching a specific condition — introducing query filters.

Approach: db.school.find({ name: "Holberton school" }) — pass a query document to filter results. Only documents whose name field matches the value are returned.

New techniques introduced:

Key takeaway: MongoDB queries are themselves JSON-like documents. find({name: "X"}) is equivalent to WHERE name = 'X' in SQL — the filter is declarative.

---

Task 27 — Count Documents (27-count)

Challenge: Count how many documents exist in a collection — introducing document counting.

Approach: db.school.count() — returns the total number of documents. Can also accept a filter: db.school.count({name: "Holberton school"}).

New techniques introduced:

Key takeaway: count() is MongoDB's COUNT(*). It's a simple, fast way to check collection size or count matching documents.

---

Task 28 — Update Documents (28-update)

Challenge: Modify existing documents by adding new fields — introducing the $set operator and multi option.

Approach: db.school.update({ name: "Holberton school" }, { $set: { address: "972 Mission street" } }, { multi: true }) — match documents by name, then add an address field via $set. multi: true updates all matching documents, not just the first.

New techniques introduced:

Key takeaway: $set modifies only specified fields — the rest of the document is untouched. Always use { multi: true } when you intend to update multiple documents; without it, only the first match is changed.

---

Task 29 — Delete Documents (29-delete)

Challenge: Remove documents from a collection — introducing bulk deletion.

Approach: db.school.deleteMany({ name: "Holberton school" }) — delete all documents matching the filter. deleteMany() removes every match; deleteOne() would remove only the first.

New techniques introduced:

Key takeaway: deleteMany() is MongoDB's DELETE FROM ... WHERE ... — it removes all matching documents in one operation. Use deleteOne() when you want to limit removal to a single document.

---

Python & PyMongo (Tasks 30–34)

Transitioning from the MongoDB shell to programmatic database access with Python's pymongo driver.

---

Task 30 — List All Documents in Python (30-all.py)

Challenge: Retrieve all documents from a MongoDB collection and return them as a Python list — bridging the shell-to-Python gap.

Approach: list(mongo_collection.find()) — find() returns a Cursor; wrapping it in list() materializes all documents into a Python list of dicts.

New techniques introduced:

Key takeaway: PyMongo's API mirrors the shell — collection.find() is equivalent to db.collection.find(). Wrap the cursor in list() to get all documents at once.

---

Task 31 — Insert with Keyword Arguments (31-insert_school.py)

Challenge: Accept arbitrary keyword arguments and insert them as a MongoDB document — introducing dynamic document creation.

Approach: mongo_collection.insert(kwargs) — Python's **kwargs captures all keyword arguments as a dictionary, then insert() stores it directly as a MongoDB document.

New techniques introduced:

Key takeaway: **kwargs + insert() lets you pass any field-value pairs from Python directly into MongoDB — the Python dict maps 1:1 to a BSON document.

---

Task 32 — Update Many with $set (32-update_topics.py)

Challenge: Update the topics field for ALL schools matching a given name — translating the shell update pattern to Python.

Approach: mongo_collection.update_many({'name': name}, {'$set': {'topics': topics}}) — filter by name, then use $set to add/overwrite the topics field. update_many() ensures all matches are updated.

New techniques introduced:

Key takeaway: PyMongo update operations use the same MongoDB operators ($set, $inc, etc.) — just expressed as Python dicts. update_many() is the safe default; update_one() only touches the first match.

---

Task 33 — Find by Array Field (33-schools_by_topic.py)

Challenge: Find all schools whose topics array contains a specific value — introducing array field matching in MongoDB.

Approach: mongo_collection.find({'topics': topic}) — when the field is an array, MongoDB automatically matches documents where ANY element equals the query value.

New techniques introduced:

Key takeaway: MongoDB intelligently matches array fields — find({'topics': 'math'}) finds documents where topics is an array containing 'math'. No special syntax needed.

---

Task 34 — Nginx Log Analysis (34-log_stats.py)

Challenge: Connect to a real MongoDB instance and analyze Nginx access logs — introducing MongoClient, database/collection access, and count_documents().

Approach: MongoClient('mongodb://127.0.0.1:27017') connects to the local server. Then client.logs.nginx navigates to the nginx collection in the logs database. count_documents({}) counts total logs; count_documents({'method': 'GET'}) counts per HTTP method.

New techniques introduced:

Key takeaway: MongoClient is the entry point — it connects to MongoDB, and from there you navigate databases and collections with dot notation. count_documents() is the modern, preferred way to count.

---

Python & PyMongo — Aggregation (Tasks 105–106)

Advanced data analysis using MongoDB's aggregation pipeline — grouping, sorting, and limiting results.

---

Task 105 — Students Sorted by Average Score (105-students.py)

Challenge: Compute each student's average score from a nested topics array and sort them by that average — introducing the aggregation pipeline.

Approach: A two-stage pipeline: $addFields creates a new averageScore field by averaging $topics.score with $avg, then $sort orders by averageScore descending. aggregate(pipeline) executes and returns a cursor.

New techniques introduced:

Key takeaway: The aggregation pipeline processes documents through stages in sequence — each stage transforms the data flowing from the previous one. $addFields enriches documents; $sort reorders them. Together they form a composable data processing chain.

---

Task 106 — Top 10 IPs (106-log_stats.py)

Challenge: Extend the Nginx log analysis (Task 34) to find the 10 most frequent IP addresses — introducing $group, $sum, and $limit.

Approach: A three-stage pipeline appended to the existing script: $group clusters documents by $ip and counts each with $sum: 1, $sort orders by count descending, $limit: 10 keeps only the top 10.

New techniques introduced:

Key takeaway: $group is MongoDB's GROUP BY — _id defines the bucket, accumulator expressions ($sum, $avg, $max) compute per-group values. $limit caps results, making it perfect for "top N" queries.

---

Technique Inventory — MongoDB

---

Resources

MySQL

• MySQL CREATE DATABASE — Official Docs
• MySQL CREATE TABLE — Official Docs
• MySQL JOIN Syntax
• MySQL Aggregate Functions
• MySQL Triggers — Official Docs
• MySQL ENUM — Official Docs
• SQL COALESCE — Official Docs

MongoDB

• MongoDB Shell (mongosh) — Official Docs
• MongoDB CRUD Operations
• MongoDB Query Documents
• MongoDB Update Operators ($set)
• MongoDB Aggregation Pipeline
• PyMongo Documentation