Solve a Diamond Heist Mystery Using SQL

The Case: Missing Golden Diamond

London, Present Day

The priceless “Sunrise Diamond” vanished during a blackout at the Natural History Museum’s charity gala. Security cameras caught nothing during the blackout. Can you find the thief using SQL clues? The Diamond was stolen from the museum between 21:45 and 22:15, during the blackout. Security confirms:

• 5 individuals had exhibit access
• No exits during theft window
• Thief needed high access (4+ level) and late exit
• It takes 30 minutes to finish the heist

Database Setup

1. Create Tables

CREATE TABLE guests (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    job VARCHAR(50),
    access_level INT
);

CREATE TABLE evidence (
    id INT PRIMARY KEY,
    item VARCHAR(50),
    location VARCHAR(50),
    guest_id INT
);

CREATE TABLE timelog (
    guest_id INT,
    time_in TIME,
    time_out TIME
);

2. Data

-- Guests (access_level 1-5, 5=highest)
INSERT INTO guests VALUES
(1, 'Emma Clark', 'Journalist', 2),
(2, 'Mike Brown', 'Security Guard', 4),
(3, 'Sarah Wilson', 'Historian', 3),
(4, 'John Doe', 'Electrician', 5),  
(5, 'Lisa Green', 'Curator', 4);

-- Evidence
INSERT INTO evidence VALUES
(101, 'Glove', 'Display Case', 2),
(102, 'Screwdriver', 'Hallway', 4),
(103, 'Blueprints', 'Security Room', 4),  
(104, 'Wire Cutters', 'Exit Door', 4);

-- Timelog (all times in 24h format)
INSERT INTO timelog VALUES
(1, '21:00', '22:30'),  -- Emma
(2, '20:45', '23:00'),  -- Mike
(3, '21:55', '22:20'),  -- Sarah
(4, '20:00', '22:50'),  -- John 
(5, '22:10', '22:25');  -- Lisa

Investigation Questions

1. List all suspects

SELECT * FROM guests;

What We’re Checking: Basic information about all potential suspects
Explanation: This gives us the complete list of 5 guests who had access to the exhibit.

Result:
All 5 guests with correct access levels

2. High-access suspects (level 4+)

SELECT name, job 
FROM guests 
WHERE access_level >= 4;

What We’re Checking: Who had high enough access to bypass security
Explanation:

• Access levels determine what areas someone can enter
• Level 4+ needed to disable security systems

Result:

Mike Brown     | Security Guard
John Doe      | Electrician
Lisa Green    | Curator

3. Evidence ownership

SELECT e.item, g.name
FROM evidence e
JOIN guests g ON e.guest_id = g.id;

What We’re Checking: Which suspect is linked to each clue
Explanation:

• JOIN connects evidence to guests via guest_id
• Shows who had suspicious items

Result:

Glove        | Mike Brown
Screwdriver  | John Doe
Blueprints   | John Doe
Wire Cutters | John Doe

4. Present during theft

SELECT g.name
FROM guests g
JOIN timelog t ON g.id = t.guest_id
WHERE t.time_in <= '21:45' 
AND t.time_out >= '22:15';

What We’re Checking: Who was present throughout the theft window
Explanation:

• Time_in <= 21:45: Entered before theft started
• Time_out >= 22:15: Left after theft ended
• This window is 30 minutes long (21:45 to 22:15), which is the time it takes to finish the heist

Result:

Emma Clark
Mike Brown
John Doe

5. Multiple evidence carriers

SELECT g.name, COUNT(e.id) AS evidence_count
FROM guests g
JOIN evidence e ON g.id = e.guest_id
GROUP BY g.name
HAVING COUNT(e.id) > 1;

What We’re Checking: Who had multiple suspicious items
Explanation:

• COUNT(e.id) counts how many items each guest had
• HAVING COUNT(e.id) > 1 filters to only show those with more than 1 item

Result:

John Doe | 3

6. Final Solution

SELECT g.name AS thief
FROM guests g
JOIN evidence e ON g.id = e.guest_id
JOIN timelog t ON g.id = t.guest_id
WHERE e.item IN ('Wire Cutters', 'Blueprints')
AND t.time_out >= '22:30'
AND g.access_level >= 4;

What We’re Checking: Who meets all 3 criteria:

1. Had specific tools
2. Stayed late enough
3. Had maximum access

Explanation Breakdown:

1. Wire Cutters + Blueprints = Could disable security systems
2. Stayed until 22:50 = Time to erase security footage
3. Access Level 5 = Only John could access all areas

Result:

John Doe

Case Solved!

The Thief: John Doe (Electrician)
Evidence Chain:

1. Only one with access_level 5 (needs atleast 4 to access the exhibit)
2. Carried 3 tools (wire cutters, screwdriver, blueprints)
3. Stayed until 22:50 (Was in the exhibit area until through the blackout which is 30 minutes)
4. Blueprints matched security room modifications

Learning Guide

1. WHERE filters rows
2. JOIN connects tables
3. GROUP BY aggregates data
4. HAVING filters groups
5. Combine conditions logically