SQL Survival Challenge: Last City on Earth

Project Overview

Welcome to the SQL Survival Challenge: Last City on Earth! In this adventure-themed project, you will step into the shoes of a data analyst tasked with managing and analyzing the data of humanity’s final refuge—the Last City. As the world faces a cataclysmic event, effective data management becomes crucial to ensure the survival and prosperity of the city’s inhabitants.

Your mission is to navigate through various data scenarios, optimize resource allocations, respond to emerging threats, and maintain the city’s infrastructure. Through this hands-on experience, you’ll enhance your SQL skills while contributing to the Last City’s resilience.

---

Objective

By the end of this project, you will be able to:

• Design and Understand Complex Database Schemas: Grasp the relationships between different entities essential for city management.
• Execute Advanced SQL Queries: Perform data manipulation and analysis to solve real-world problems.
• Optimize Resource Allocation: Ensure efficient distribution of limited resources to various city facilities.
• Respond to Dynamic Threats: Analyze and react to potential threats to maintain city safety.
• Predict Future Needs: Use data trends to forecast and prepare for upcoming challenges.

Tasks

Embark on the following tasks to ensure the Last City’s survival. Each task is designed to guide you through critical data analysis scenarios using SQL. After completing each task, you can check your answers using the provided sample queries.

Task 1: List All Citizens in the City

Objective: Retrieve a list of all citizens residing in the Last City.

Instructions:

• Query the citizens table to display all citizens’ IDs, first names, last names, ages, roles, and registration dates.

Task 2: Allocate Resources to Facilities

Objective: Allocate necessary resources to each facility based on their requirements.

Instructions:

• Update the resource_allocations table by allocating resources to each facility.
• Ensure that resource allocations do not exceed available quantities in the resources table.

Task 3: Identify Facilities Low on Resources

Objective: Determine which facilities are low on specific resources to prioritize allocations.

Instructions:

• Find facilities that have received less than 50% of their allocated resources.
• Focus on resources such as Food, Water, and Medicine.

Task 4: Respond to Imminent Threats

Objective: Assign appropriate staff members to respond to high-severity threats.

Instructions:

• For threats with a severity of ‘High’, update the threat_responses table by assigning staff members to address them.
• Ensure that the handled_by field references valid staff IDs.

Task 5: Analyze Resource Consumption Rates

Objective: Analyze how quickly resources are being consumed to predict future needs.

Instructions:

• Calculate the daily consumption rate of each resource based on allocations over the past month.
• Identify resources with the highest consumption rates.

Task 6: Plan for Future Resource Needs

Objective: Based on consumption rates, forecast the required quantities of resources for the next month.

Instructions:

• Use the consumption rates calculated in Task 5 to project resource needs.
• Update the resources table with the projected replenishment rates.

Task 7: Maintain Facility Operations

Objective: Ensure that all facilities are operational by scheduling necessary maintenance.

Instructions:

• Query the facilities table to identify facilities under maintenance.
• Review the facilities_maintenance records to confirm completion.

Task 8: Ensure Security of Resources

Objective: Monitor and enhance the security of critical resources to prevent theft or sabotage.

Instructions:

• Analyze the threat_responses table to identify patterns in threat handling.
• Recommend additional security measures based on data insights.