Database Detective Case Guide: Clue Logic for Cases 1-10

Database Detective: Minor Crimes Division randomizes the culprit data, so a copied name is not a solution. Translate each clue into a condition, identify the table that can test it, preserve the matching key, and join only after every branch has a reason to exist. If a query returns one row but you cannot explain why that row satisfies the clues, do not submit the arrest.

The ten-case full release was current through Update 1.14.2, published August 29, 2026. Since the early 1.02 build, the game has added browser history and bookmarks, persistent saved tables, arrest tracking by case, multiple query tabs, query text that survives a failed run, and several parser fixes. If an older video shows one query window or loses a table after an error, its interface no longer matches the current build.

Culprit names and many clue values can change between saves, so use a repeatable query plan for Cases 1-10 instead of copying ten final suspects. Replace sample column labels with the names in your current table browser, and let your own evidence supply every generated value.

The culprit changes; your join keys should not#

Before writing SQL, divide the case into three passes.

1. Extract facts without querying. Read both sides of photos, brochures, cards, diary pages, website copy, map legends, and message timestamps. Write each fact as a testable statement: “present at the crime time,” “security level III,” “did not attend the crowded convention,” or “paid more than the premium threshold.”
2. Build one candidate set per fact family. Time, inventory, attendance, payments, identity, and location usually belong in separate intermediate results. Run each result by itself. A zero-row result tells you which assumption failed; a single enormous query does not.
3. Bridge the sets with a stable key. Early cases use suspect_number or badge_number. Later cases use order numbers, attendee IDs, student IDs, account names, flight numbers, addresses, or company ownership. A shared key is evidence. Similar-looking names are not.

Keep a small case ledger in the in-game Notepad with four columns: clue, interpretation, table, and join key. Mark an interpretation as provisional until a query or website confirms it. This prevents a later discovery from forcing you to reconstruct an hour of reasoning from minimized windows.

Cases 1-3 teach filters, intervals, and intersections#

Case 1 is primarily an observation test. The crime-scene clue points to blue paint, and one of the four suspect photos supplies the matching face and suspect number. The database step is only an identity lookup. If you are searching descriptions or names before comparing the images, you are doing the case in reverse.

Case 2 turns the ID card into an AND checklist. District, join date, birth date, eye color, height, and weight are independent constraints. Start with two high-value fields such as height and weight, inspect the result count, then add another field until one record remains. Adding all fields at once is faster only when every date format and spelling has already been copied correctly.

SELECT *
FROM teachers
WHERE height = /* value on the ID card */
  AND weight = /* value on the ID card */
  AND eye_color = /* exact value shown in the table */;

If that returns no rows, remove the last condition instead of rewriting the whole statement. Compare the table’s date and color formatting with the clue before deciding that the evidence is wrong.

Case 3 is the first true intersection. The timestamp defines who was clocked in when the crime occurred. The visible gun and III card define a second set in the police table: officers with the required issued-gun count and security level. Preserve badge_number in both results, then join or compare on that key.

SELECT t.badge_number, c.name
FROM timesheet AS t
JOIN cops AS c
  ON c.badge_number = t.badge_number
WHERE t.check_in <= /* crime timestamp */
  AND t.check_out >= /* crime timestamp */
  AND c.guns_issued = 1
  AND c.security_level = 3;

The exact time-column names may differ from this pattern. The important test is interval containment: the suspect must have arrived no later than the crime and left no earlier than it. Filtering for rows whose check-in time merely resembles the photograph’s time will include people who were no longer present.

Case 4 is an elimination puzzle before it is a join#

The fourth case supplies four convention brochures, a photo with three visible item counts, an “exact average” hint for dumbbells, and a diary describing two missed events and two attended events. Do not join all four attendee tables immediately. First decide which two conventions can still be true.

1. Copy the Forms, Lamps, and Bottles counts from the photograph.
2. Calculate AVG(dumbs_brought) from DumbCon. Use the value produced by your current case, not a number from a video.
3. For each convention, calculate the maximum relevant item count. The diary says the suspect would have won one event if they had arrived, so their photographed count must beat that event’s winning value.
4. Count attendees in each convention. The event with more than 300 visitors is another non-attended event.
5. The two remaining conventions are the attendance pair. Join only those two on attendee_id, then apply the item amounts associated with them.

SELECT AVG(dumbs_brought) AS clue_average
FROM dumbcon;

SELECT COUNT(attendee_id) AS visitor_count
FROM /* one convention table */;

SELECT MAX(/* that convention's item column */) AS winning_amount
FROM /* the same convention table */;

A restart can generate a different average and a different event over 300. That is not a contradiction; it is why the diary logic must be rerun. If your final join produces several people, audit the two eliminated conventions before adding arbitrary personal details. The wrong event pair cannot be repaired with a more complicated identity filter.

Cases 5-6 turn browser trails into database evidence#

Case 5 introduces downloadable website data. The pizzeria card leads to the orders and tips tables. The waitress describes the lowest percentage tip, not the smallest cash tip, so each order needs a total before its tip can be compared.

Group item rows by order number, sum their prices, join the tip recorded for the same order, and calculate tip / total. Sort or find the minimum rate. Then use the photo as a verification step: the selected order must contain both pepperoni and pineapple. Finally, bridge the order number to the customer table.

SELECT o.order_number,
       SUM(o.item_price) AS order_total,
       MAX(t.tip_amount) / SUM(o.item_price) AS tip_rate
FROM orders AS o
JOIN tips AS t
  ON t.order_number = o.order_number
GROUP BY o.order_number
ORDER BY tip_rate ASC;

Treat that as a shape, not guaranteed column spelling. If the parser objects, first run the grouped order total without the tips join. Then join the smaller saved result to tips. The lowest rate is a candidate until the toppings confirm it.

Case 6 is a profile reconstruction. Use the timestamp on the government admin page to isolate the matching visit in web_searches, which gives one IP address. Gather every visit made by that IP before following individual sites. The trail should let you rebuild three quiz answers and an age:

• combine the grocery order with nutrition data and the recipe descriptions to identify the desired life benefit;
• compare the user’s movie ratings with the site’s medal rule to isolate the favorite movie directed by a non-medalist;
• use the government review’s historical claim to calculate the age implied by Zoran’s time in power;
• use dietary comments as a final check on the favorite-food answer rather than as the first identity filter.

Do not arrest the IP address. The IP is only the pivot that discovers the person’s web accounts. The final row must still be an identity supported by the quiz answers and age.

Case 7 needs five linked checks#

The role-playing-game murder is where a one-query mindset fails. The victim screenshot, the Legends of New Hampshire wiki, the damage log, two guild forums, donation records, election data, and external accounts describe different parts of one event. Work in this order:

1. Gain forum access. Use the wiki to answer the Lore Lovin’ Merchants quiz. The questions about explorers, swamp sludge, weapon effects, Hamp words, and locations are a reading test. Update 1.02 clarified that Skeletal Warriors have the same base stats as Warriors, so use the current description when deriving the Cursed-equivalent effect.
2. Identify the event. Read the victim’s damage log to find the killing weapon, lost weapon, and final location. Search the log rather than assuming a rare label alone proves ownership.
3. Check access and motive. Guild inventory identifies who could obtain the weapon. Donation rows with membership or donation notes identify premium members; the current wording was revised in Update 1.01. Election results identify people whose vote opposed the victim.
4. Check class and presence. Use weapon modifiers, class damage ranges, and the first-strike rule to derive the attacker’s class. Then use the unavoidable encounters on the route from New Shire City through Manchester Grasslands to the Dark Forest to find candidates who reached the murder location and remained there through the fatal timestamp.
5. Translate avatar to person. The killer’s payment history links the game account to donation, rent, and driving records. Donation tier narrows one list, monthly rent identifies a neighborhood, and the driver table provides the final real-world identity bridge.

None of access, motive, class, or location is sufficient alone. If three candidates survive the premium/election filter, that is expected; the travel window should remove anyone who left the Dark Forest before the murder. If the real-name cross-check still returns several drivers, recheck whether you used an available rental instead of the occupied rental implied by a monthly payment.

Case 8 is a classroom reconstruction, not a password hunt#

The four screenshots describe three roles: the phone-using suspect, the classmate who supplied the screenshots, and a student whose exam score exceeds the other two combined. The university website offers attendance, seating, homework, and score tables for Economics, Philosophy, and Science. Treat each course as a self-contained test before combining student records.

First identify the shared exam and classes by solving enough of the course questions to match the screenshot. Some questions require ratios; others require a self-join that compares neighboring years. Keep each answer with the course that produced it. A correct number attached to the wrong subject will send the later student join in the wrong direction.

The professor’s page explains the password pattern: a lowercase childhood street plus the final two digits of the year they began at LZU. Campus-history maps and the first-day photograph constrain those two parts. Use that reasoning to enter the teacher portal; do not copy a fixed password from somebody else’s run.

Once the student table is available, build the identity in layers:

• use the Art page and the conversation translation to identify the screenshot-sharing classmate;
• use that student’s score to set the maximum possible score for the suspect and the minimum for the higher-scoring student;
• join scores to seating and keep pairs in the same row and adjacent seats;
• apply the clue that the suspect attended fewer than half of the relevant class sessions;
• intersect the seating/score result with the attendance result on student_id.

The phrase “less than half” applies to classes, not calendar days. Count the sessions represented in the relevant attendance table and compare the suspect’s attended rows with that total.

Case 9 is three identities connected by one scheme#

The newspaper, LZPPP pension site, Jerry Webb’s Big Leeks notes, and broker portal initially look like separate stories. The useful organizing document is the three-stage market plan: borrow and consolidate shares, sell and later repurchase them, then send the profit to the coordinator. Do not begin with the faces in the photographs. First prove which companies participated.

1. Limit company records to 1993 and to businesses headquartered in Los Zorangeles, matching LZPPP’s stated investment scope.
2. Compare each company’s largest transaction with its normal transaction size. The coordinated companies should show an extreme anomaly rather than an ordinary busy day.
3. Find transfers from LZPPP whose share movement did not create a corresponding price change, then look for the common receiving account across the affected companies.
4. Use the receiver’s company page to reach its two co-owners: Miss Suit and Mister Tie.

For Miss Suit, intersect the dates on which the orange-uniform BigBall team played with flights that could show the game live. The river orientation, left-side window, drink color, seat layout, and passenger in the row ahead reduce the flight and seat possibilities. Use weather only as a tie-breaker if your generated data still leaves more than one flight; it should not replace the stronger date, route, class, and seat conditions.

For Mister Tie, find the season in which all three photographed outcomes are true: a team scores zero buckets across the season, the Uncles win a game by more than five, and no team exceeds 35. Apply the photographed stake and multiply the three odds. Match that payout to the winners history.

Finally, compare the outgoing package histories of Miss Suit and Mister Tie. Their shared destination is the coordinator bridge. If it maps to a company address, use the broker site’s ownership rule and shareholder data; if it maps to property, use the estate records. The family-tree clue then asks for Charles’s eldest surviving grand-nephew. Update 1.02 fixed an occasional Case 9 floating-point crash, so update the game before treating a crash during these calculations as bad SQL.

Case 10 joins a food route to a manufacturing route#

The final investigation asks you to identify the animal behind the secret organization. Begin with every link on the resume and CV. The Social Services job page uses a password-recovery sequence: the family tree from Case 9 supplies the mother’s maiden name, while the pet-clinic material supplies the childhood friend and birth hospital. After signing in, read every filled job description. Those descriptions define the constraints and point to the databases used in the two main branches.

Food branch: find the crop supply

1. Use squadrons and soldiers to total the soldiers assigned to each squadron and retain the squadron coordinates.
2. Read the logistics rule literally: the crop supply must serve at least 1,000 soldiers within 500 meters. Compare qualifying squadron coordinates with the farms listed in farm_locations.
3. Join each qualifying farm to farm_crops, then use crops and crop_traits to turn crop names into food groups and dietary traits.

Keep the result as a small farm-and-crop table. The distance formula on the Yargoslavia site is enough for the range test; do not add ferry-position calculations unless your own clue set actually requires them.

Manufacturing branch: trace the diverted material

1. Follow the Manufacturing Supervisor’s three-stage chain: raw material → component → finished product. Work backward from each product so every part and raw material remains attached to its final use.
2. Use the car-manufacturing tables such as car_materials, part_costs, and car_prices to calculate the material and part expense expected for the cars sold.
3. Compare that expected expense with the relevant entries in the income and expense statements. An unexplained excess identifies the material being diverted rather than consumed by legitimate production.
4. Map that material back through the component chain to the products that require it.

The food branch gives you diet and habitat clues; the manufacturing branch gives you product needs. Cross-reference both sets with the animal requirements on the pet-clinic site. The answer is the species that satisfies the crop traits, environment, and manufactured-product evidence at the same time. Names and quantities may vary, so submit the animal supported by your run instead of copying a fixed culprit.

When SQL fails, reduce it to the first false assumption#

• Zero rows: run the base table, then add one WHERE condition at a time. Check spaces, capitalization in data values, date format, and whether “more than” excludes equality.
• Too many rows: keep the key column visible and ask which independent clue has not yet been tested. Do not add a random field merely to reach one row.
• Duplicate rows after a join: inspect the relationship. Orders contain many items, students contain many attendance rows, and players contain many transactions. Aggregate the many-side before joining when the question expects one row per person or order.
• Aggregate error: every selected non-aggregate column normally belongs in GROUP BY. Test the aggregate alone before adding identity columns.
• Exploding result or rejected query: confirm every table has an explicit JOIN ... ON path. The game’s parser intentionally discourages unconnected cross joins.
• Floating-point mismatch: compare ranges or rounded values when the evidence permits. The Case 9 floating-point crash was fixed in 1.02, so install the current update before diagnosing an old build.

The parser supports useful shortcuts including IN, CONCAT, aliases, and bracketed or backtick identifiers. Support does not make a long query easier to audit. Save a meaningful intermediate table when it represents a real piece of evidence, then join that smaller result to the next source.

Use time travel and query tabs as a case lab#

The assistant can switch among cases you have already unlocked. Use that feature to revisit an earlier mechanic without deleting progress: Case 2 for incremental filters, Case 3 for joins, Case 4 for aggregates, and Case 8 for self-joins. Current builds also provide multiple query tabs and preserve query text after a failed run, so keep one tab per independent clue instead of repeatedly overwriting a working query. Restart a case only when you intend to regenerate its data. A regenerated case invalidates copied averages, crowded-event results, names, and other values in your notes, but the clue-to-query method remains valid.

Before submitting any arrest, trace each clue to a condition, each condition to a table, and that table to a stable identity key. When a case becomes large, add more small proofs instead of making one larger guess.

Official game and update links#

The official Steam page covers the ten-case game and its SQL investigation systems. Check the official announcements for interface and parser changes after Update 1.14.2. Because case data is randomized, use any walkthrough for its reasoning path rather than its names or numeric results.