SQL Alien Invasion Challenge: Defend Earth

Project Overview

Welcome to the SQL Alien Invasion Challenge: Defend Earth! As Earth’s data analyst, you are at the forefront of humanity’s defense against an imminent alien invasion. Your mission is to analyze and manage crucial data related to alien activities, Earth’s defenses, resource allocations, and population safety. Through strategic SQL queries, you’ll uncover patterns, optimize defenses, and ensure the survival of humanity.

Embark on this thrilling adventure to apply your SQL skills in a high-stakes, intergalactic scenario!

Objective

By completing this project, you will:

• Understand Complex Database Structures: Navigate relationships between various entities involved in Earth’s defense.
• Execute Advanced SQL Queries: Perform data manipulation and analysis to support strategic decision-making.
• Optimize Resource Allocation: Ensure efficient distribution of limited resources to bolster Earth’s defenses.
• Monitor Alien Activities: Analyze patterns in alien movements and attacks to anticipate future threats.
• Ensure Population Safety: Manage and analyze data to protect Earth’s population from alien incursions.

Tasks

Embark on the following tasks to defend Earth against the alien invasion. 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 Alien Species Invading Earth

Objective: Retrieve a list of all alien species currently involved in the invasion.

Instructions:

• Query the aliens table to display all alien IDs, species names, home planets, arrival dates, and threat levels.

Task 2: Monitor Alien Ship Movements

Objective: Track the movements of alien ships to anticipate their next targets.

Instructions:

• Query the alien_ship_movements table to display all movements, including origin, destination, departure, and arrival dates.

Task 3: Allocate Resources to Defenses

Objective: Ensure that each defense system has sufficient resources to operate effectively.

Instructions:

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

Task 4: Identify Cities Under Highest Threat

Objective: Determine which cities are experiencing the most severe alien attacks.

Instructions:

• Analyze the attacks table to find cities with the highest number of severe damage levels.
• List the city names, number of severe attacks, and total damage inflicted.

Task 5: Analyze Resource Consumption Rates

Objective: Understand how quickly resources are being consumed to plan future allocations.

Instructions:

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

Task 6: Upgrade Defense Systems

Objective: Enhance defense systems to better counteract the alien threats.

Instructions:

• Update the defenses table by implementing upgrades from the defense_upgrades table.
• Ensure that the new power levels reflect the upgrades.

Task 7: Ensure Population Safety

Objective: Monitor and improve the safety of Earth’s population in affected cities.

Instructions:

• Query the citizen_safety table to identify cities with high casualty rates.
• Recommend strategies to reduce casualties based on data insights.

Task 8: Predict Future Alien Attacks

Objective: Use data trends to forecast potential future attacks and prepare defenses accordingly.

Instructions:

• Analyze patterns in alien ship movements and previous attacks.
• Forecast the likelihood of attacks on specific cities in the next month.