superhero Database Schema

Database Description

Database containing superhero information including powers, attributes, and publishers.

Schema Structure
superhero
Column Name Description
id -
superhero_name -
full_name -
gender_id -
eye_colour_id -
hair_colour_id -
skin_colour_id -
race_id -
publisher_id -
alignment_id -
height_cm -
weight_kg -
gender
Column Name Description
id -
gender -
colour
Column Name Description
id -
colour -
race
Column Name Description
id -
race -
publisher
Column Name Description
id -
publisher_name -
alignment
Column Name Description
id -
alignment -
superpower
Column Name Description
id -
power_name -
attribute
Column Name Description
id -
attribute_name -
hero_power
Column Name Description
hero_id -
power_id -
hero_attribute
Column Name Description
hero_id -
attribute_id -
attribute_value -
Example Queries

SQL Query:
SELECT T2.publisher_name FROM superhero AS T1 INNER JOIN publisher AS T2 ON T1.publisher_id = T2.id INNER JOIN hero_attribute AS T3 ON T1.id = T3.hero_id INNER JOIN attribute AS T4 ON T3.attribute_id = T4.id WHERE T4.attribute_name = 'Speed' ORDER BY T3.attribute_value LIMIT 1
Evidence:

the slowest superhero refers to attribute_name = 'Speed' where MIN(attribute_value); publisher refers to publisher_name

Difficulty: moderate

SQL Query:
SELECT CAST(COUNT(CASE WHEN T3.gender = 'Female' THEN T1.id ELSE NULL END) AS REAL) * 100 / COUNT(T1.id) FROM superhero AS T1 INNER JOIN alignment AS T2 ON T1.alignment_id = T2.id INNER JOIN gender AS T3 ON T1.gender_id = T3.id WHERE T2.alignment = 'Bad'
Evidence:

bad superheroes refers to alignment.id = 2; percentage = MULTIPLY(DIVIDE(SUM(gender.id = 2 WHERE alignment.id = 2), COUNT(alignment.id = 2)), 100.0); female refers to gender.id = 2;

Difficulty: challenging

SQL Query:
SELECT T1.superhero_name FROM superhero AS T1 INNER JOIN colour AS T2 ON T1.eye_colour_id = T2.id AND T1.hair_colour_id = T2.id WHERE T2.colour = 'Black'
Evidence:

heroes' names refers to superhero_name; eyes and hair colours are both black refers to eye_colour_id AND hair_colour_id WHERE colour.colour = 'Black';

Difficulty: moderate

SQL Query:
SELECT T4.gender FROM superhero AS T1 INNER JOIN hero_power AS T2 ON T1.id = T2.hero_id INNER JOIN superpower AS T3 ON T2.power_id = T3.id INNER JOIN gender AS T4 ON T1.gender_id = T4.id WHERE T3.power_name = 'Phoenix Force'
Evidence:

ability of Phoenix Force refers to power_name = 'Phoenix Force';

Difficulty: moderate

SQL Query:
SELECT superhero_name, height_cm, RANK() OVER (ORDER BY height_cm DESC) AS HeightRank FROM superhero INNER JOIN publisher ON superhero.publisher_id = publisher.id WHERE publisher.publisher_name = 'Marvel Comics'
Evidence:

name refers to superhero_name; the tallest hero refers to MAX(height_cm); published by Marvel Comics refers to publisher_name = 'Marvel Comics'

Difficulty: moderate
Back to Database Explorer