Introduction
Welcome to “SQL Mystery Challenge: Uncover the Secret Recipe Thief”! A renowned chef’s secret recipe has mysteriously disappeared, and it’s up to you to use your SQL skills to solve the case. Dive into the database, analyze the clues, and find out who stole the secret recipe!
Challenge
Investigate the data to find out who stole the secret recipe.
Keep these hints in mind:
- The secret recipe is stored in the chef’s office.
- Only those who entered the office could have taken it.
- The theft occurred between 10:00 AM and 11:00 AM on 2023-10-30.
- Examine activities and logs for clues.
Your Task
- Step 1: Identify employees who entered the office between 10:00 AM and 11:00 AM.
- Step 2: Look for any suspicious activities or patterns.
- Step 3: Determine who took the secret recipe based on your findings.
- Enter your answer below the SQL Editor to check if you are correct!
Database Schema
1. employees Table Schema
| employee_id | name | position |
|-------------|---------------|---------------|
| 1 | Alice Johnson | Sous Chef |
| 2 | Bob Smith | Pastry Chef |
| 3 | Carol Lee | Line Cook |
| 4 | David Brown | Dishwasher |
| 5 | Eva Green | Intern |
2. activities Table Schema
| activity_id | employee_id | activity | activity_time |
|-------------|-------------|----------------|----------------------|
| 1 | 1 | Arrived | 2023-10-30 08:00:00 |
| 2 | 2 | Arrived | 2023-10-30 08:15:00 |
| 3 | 3 | Arrived | 2023-10-30 08:30:00 |
| 4 | 4 | Arrived | 2023-10-30 09:00:00 |
| 5 | 5 | Arrived | 2023-10-30 09:30:00 |
| 6 | 2 | Baking | 2023-10-30 09:00:00 |
| 7 | 1 | Prepping | 2023-10-30 09:00:00 |
| 8 | 3 | Cooking | 2023-10-30 09:30:00 |
| 9 | 5 | Observing | 2023-10-30 09:45:00 |
| 10 | 4 | Cleaning | 2023-10-30 10:00:00 |
| 11 | 2 | Break | 2023-10-30 10:30:00 |
| 12 | 5 | Left Kitchen | 2023-10-30 10:45:00 |
| 13 | 3 | Break | 2023-10-30 11:00:00 |
| 14 | 4 | Left Kitchen | 2023-10-30 11:15:00 |
| 15 | 1 | Meeting | 2023-10-30 11:30:00 |
3. recipes Table Schema
| recipe_id | recipe_name | is_secret |
|-----------|---------------------|-----------|
| 1 | Chocolate Cake | 0 |
| 2 | Spaghetti Carbonara | 0 |
| 3 | Secret Sauce | 1 |
| 4 | Caesar Salad | 0 |
4. kitchen_logs Table Schema
| log_id | employee_id | action | log_time |
|--------|-------------|--------------------|----------------------|
| 1 | 1 | Opened Fridge | 2023-10-30 09:05:00 |
| 2 | 2 | Used Oven | 2023-10-30 09:10:00 |
| 3 | 5 | Entered Office | 2023-10-30 10:00:00 |
| 4 | 5 | Exited Office | 2023-10-30 10:10:00 |
| 5 | 3 | Used Stove | 2023-10-30 10:15:00 |
| 6 | 4 | Cleaned Dishes | 2023-10-30 10:20:00 |
| 7 | 2 | Took Break | 2023-10-30 10:30:00 |
| 8 | 1 | Entered Office | 2023-10-30 11:00:00 |
| 9 | 1 | Exited Office | 2023-10-30 11:10:00 |
| 10 | 5 | Left Building | 2023-10-30 11:15:00 |