Database Detective: Minor Crimes Division - A Little From Column A, And A Little From Column B

Overview

The video is a walkthrough/playthrough of Database Detective: Minor Crimes Division, using it as a way to learn SQL concepts. The “minor crimes” theme demonstrates core database operations: filtering, joining tables, aggregations, computed columns, and progressively more complex multi-step queries across related datasets.

Key SQL concepts & techniques demonstrated

1) Basic selection + filtering on time ranges

• The first case requires identifying a person who could have been present at the crime time.
• The player selects badge number from a time sheet table using conditions like:
  • check-in time < crime time
  • check-out time > crime time

Concepts shown: SELECT, WHERE with comparison operators, time-based filtering.

---

2) Joining tables to enrich results (badge number → person name)

• After getting matching badge numbers from time sheets, the player needs the full name.
• This requires joining:
  • time sheet ↔ cops
• The join key is badge number on both tables.

Concepts shown: JOIN ... ON time_sheet.badge_number = cops.badge_number, merged result set usage.

---

3) Additional filters (security level, weapon ownership)

The query is refined further:

• security_level = 3
• guns_issued > 0 (ensuring the suspect has a gun)

Concepts shown: compound WHERE clauses with logical AND (implied).

---

4) Multi-table dataset merging + computed metrics (tip percentage)

A second case uses internet-accessible datasets (from a site like “pizza slices.net”) and involves:

• Tables for orders, tips, customers

The player merges them by order number to compute:

• tip percentage = dollars_tipped / price

Concepts shown:

• Multi-table JOINs across shared keys (order number)
• Creating computed columns (tip ratio/percentage)
• Iteration and debugging join conditions (correct matching of order_number fields)

---

5) Aggregation pitfalls and correction: per-order vs per-item pricing

The walkthrough reveals a subtle data modeling issue:

• tips are for the entire order, but prices are per item

Initial attempts mistakenly compare tip ratio using item price. The fix involves:

• grouping items by order number
• summing item prices into a total
• calculating tip / total

Concepts shown:

• GROUP BY order_number
• SUM(price) into a “total”
• recomputing ratios using aggregated totals

---

6) Complex clue investigation using web search + database lookups

Later segments simulate an investigative workflow using database querying.

a) Filtering flagged reviews by context/time

Access to a system (“raeldictator.gov/admin”) yields:

• flagged reviews
• timestamps

The player narrows candidates based on what must logically match the timing.

Concepts shown: filtering candidate records, reasoning about data correlation.

b) Using web-search history as joinable identifiers

• The player collects web search sites visited by a target user.
• A dating-site identifier (e.g., login/name like “Ribbit 78”) is found and correlated with profile data.

Concepts shown: treating identity fields as join keys across datasets (conceptually).

---

7) Advanced SQL: “HAVING” and set intersection for eligibility constraints

To find the matching profile (Mega Corp medal rule), the player:

• downloads a movie dataset
• identifies directors whose top film rating is below 8.5
• uses HAVING to filter after aggregation
• then intersects:
  • directors from the “below 8.5” set
  • directors referenced in the profile’s film list

Concepts shown:

• Aggregation + HAVING filters
• Director-level grouping (top film logic)
• Merging/intersecting derived tables

---

8) Another aggregation: nutrient-based smoothie matching

A final question deduces a person’s choices from what they ordered:

• nutrient composition is stored per ingredient
• each ingredient has a quantity

The player computes nutrient totals using:

• sum(quantity_purchased * nutrient_quantity)

Then nutrient totals are mapped to the intended smoothie answer (e.g., “Happiness”).

Concepts shown:

• Multi-step joining of “orders → ingredients → nutrient quantities”
• Aggregation over calculated expressions
• Debugging mismatched ingredient lists and quantities

---

Outcome / results from the walkthrough

• The player successfully solves multiple “minor crime” scenarios:
  • Finds a culprit based on time-window, join keys, and attribute filters (badge/name/security/weapon).
  • Identifies a “war criminal” based on tip-percentage—corrected by grouping per order total.
  • Tracks a dictator-review suspect by combining review timestamps with browsing/profile datasets.
  • Uses HAVING and aggregated “top rating” logic to narrow candidates.
  • Computes nutrient totals to answer a multi-part questionnaire (answers include Happiness, and additional inferred movie choices; final identification uses age and constraints).

---

Main speakers / sources

• John (host; “many a true nerd”) — primary narrator/player guiding the SQL learning.
• Mippy the Mole — in-game assistant referenced during the tutorial/playthrough.
• In-game databases / websites used as data sources (notably “pizza slices.net” and an admin site for the dictator-review scenario).