SQL Mystery Challenge: The Case of the Vanishing Artifacts

Project Overview

Welcome, time traveler! The year is 1824, and the Heritage Haven museum in London has reported the disappearance of several priceless artifacts. As agents of the Time Investigators Guild, you have been sent back in time to solve this mystery and ensure history remains unaltered. The fate of these artifacts could have significant implications for future generations!

Objective

  • Master SQL Fundamentals:
    • Practice essential SQL skills, such as SELECT statements, filtering, grouping, and ordering, while tackling real-world data challenges.
  • Develop Analytical Thinking:
    • Learn how to interpret data across multiple tables, draw connections, and uncover hidden patterns.
  • Understand Data Relationships:
    • Gain hands-on experience working with joins, correlations, and cross-references between tables to derive meaningful insights.
  • Build a Story from Data:
    • Piece together a timeline and narrative from raw data to solve a complex mystery.
  • Learn to Think Like a Detective:
    • Develop investigative skills by identifying key data points, analyzing suspicious patterns, and making data-driven conclusions.
  • Find the Missing Artifact:
    • Identify which artifacts are missing, when they disappeared, and how they were taken.
  • Identify the Culprit:
    • Use the provided museum datasets to analyze staff, visitor, and security logs to determine who is responsible.

Watch your training video below:

Schema Overview

Below is a detailed schema of the database you will be working with in the exercises. The schema provides the structure for the tables, including their column names, data types, and a description of the purpose of each column. Familiarize yourself with this schema before starting the tasks, as it will help you write accurate SQL queries and understand the relationships between the tables.

Each table schema is collapsible for better navigation. Click on a table name to view its columns and details.

Schema Information

Column Name Data Type Description
artifact_idINTEGERUnique identifier for each artifact
artifact_nameTEXTName of the artifact
artifact_valueINTEGEREstimated value of the artifact
current_locationTEXTCurrent location of the artifact
Column Name Data Type Description
log_idINTEGERUnique identifier for each log entry
artifact_idINTEGERIdentifier of the artifact
actionTEXTAction performed (e.g., ‘checked_out’, ‘returned’)
timestampDATETIMEDate and time of the action
staff_idINTEGERStaff involved in the action
Column Name Data Type Description
log_idINTEGERUnique identifier for each security log
person_idINTEGERIdentifier of the person (staff/visitor)
person_typeTEXTType of person (‘staff’ or ‘visitor’)
locationTEXTLocation accessed
timestampDATETIMETime of access
Column Name Data Type Description
movement_idINTEGERUnique identifier for each movement log
artifact_idINTEGERIdentifier of the artifact
from_locationTEXTLocation artifact was moved from
to_locationTEXTLocation artifact was moved to
timestampDATETIMEDate and time of the movement
Column Name Data Type Description
interaction_idINTEGERUnique identifier for each interaction
visitor_idINTEGERIdentifier of the visitor
staff_idINTEGERIdentifier of the staff
locationTEXTLocation of the interaction
timestampDATETIMETime of the interaction

SQL Premium Project: Vanishing Artifacts

SQL Premium Project: Vanishing Artifacts

Ask Tutor
Tutor Chat