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.
Watch your training video below:
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.
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 |
|---|---|---|
| alien_id | INTEGER | Unique identifier for each alien |
| species_name | TEXT | Name of the alien species |
| home_planet | TEXT | Origin planet of the alien |
| threat_level | TEXT | Threat level of the alien |
| Column Name | Data Type | Description |
|---|---|---|
| city_id | INTEGER | Unique identifier for each city |
| city_name | TEXT | Name of the city |
| population | INTEGER | Population of the city |
| shelters_available | INTEGER | Number of shelters available |
| defense_level | INTEGER | Defense level of the city |
| Column Name | Data Type | Description |
|---|---|---|
| resource_id | INTEGER | Unique identifier for each resource |
| city_id | INTEGER | City where the resource is available |
| resource_name | TEXT | Name of the resource |
| quantity | INTEGER | Quantity of the resource available |
| Column Name | Data Type | Description |
|---|---|---|
| defense_id | INTEGER | Unique identifier for each defense |
| city_id | INTEGER | City where the defense is located |
| defense_name | TEXT | Name of the defense mechanism |
| power_level | INTEGER | Power level of the defense mechanism |
| Column Name | Data Type | Description |
|---|---|---|
| movement_id | INTEGER | Unique identifier for each movement |
| alien_id | INTEGER | Alien associated with the movement |
| city_id | INTEGER | Destination city for the movement |
| departure_date | DATE | Date the alien ship departed |
| arrival_date | DATE | Date the alien ship arrived |
| Column Name | Data Type | Description |
|---|---|---|
| attack_id | INTEGER | Unique identifier for each attack |
| alien_id | INTEGER | Alien responsible for the attack |
| city_id | INTEGER | City where the attack occurred |
| attack_date | DATE | Date of the attack |
| damage_level | TEXT | Severity of the attack |
| casualties | INTEGER | Number of casualties caused |
Introduction for the SQL Tasks
Welcome to the SQL Tasks section! These exercises are designed to enhance your SQL skills through practical, real-world scenarios. Each task focuses on a specific objective, helping you build expertise in querying and analyzing data.
For each task, you will find:
- Task Description: A clear objective explaining what data you need to retrieve and the exact columns required in the output.
- Expected Output: The structure and sample rows of the expected result to guide you in writing your query.
To complete a task:
- Write your SQL query in the provided editor.
- Click “Run” to execute your query and see the output.
- Use the “Check Answer” button to compare your output with the expected result. This step provides instant feedback to ensure your query is correct.
If you need to start over, click “Reset” to clear your query and output. These tasks are designed to be engaging and progressively challenging, so take your time and enjoy the process of learning SQL!
