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.power_name FROM hero_power AS T1 INNER JOIN superpower AS T2 ON T1.power_id = T2.id GROUP BY T2.power_name ORDER BY COUNT(T1.hero_id) DESC LIMIT 1
Evidence:

power that superheroes have the most refers to MAX(COUNT(power_name))

Difficulty: simple

SQL Query:
SELECT T1.superhero_name FROM superhero AS T1 INNER JOIN publisher AS T2 ON T1.publisher_id = T2.id WHERE T2.publisher_name = 'DC Comics' ORDER BY T1.weight_kg DESC LIMIT 1
Evidence:

heaviest refers to MAX(weight_kg); DC Comics refers to publisher_name = 'DC Comics'; superhero refers to superhero_name;

Difficulty: simple

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

which superheroes refers to superhero_name; blue eyes refers to color = 'Blue' and color.id = eye_colour_id; brown hair refers to color = 'Brown' and color.id = hair_colour_id;

Difficulty: moderate

SQL Query:
SELECT T2.colour FROM superhero AS T1 INNER JOIN colour AS T2 ON T1.eye_colour_id = T2.id WHERE T1.full_name = 'Karen Beecher-Duncan'
Evidence:

eye colour refers to colour.colour where eye_colour_id = colour.id; Karen Beecher-Duncan is the full name of superhero;

Difficulty: simple

SQL Query:
SELECT colour.colour AS EyeColor, COUNT(superhero.id) AS Count, RANK() OVER (ORDER BY COUNT(superhero.id) DESC) AS PopularityRank FROM superhero INNER JOIN colour ON superhero.eye_colour_id = colour.id INNER JOIN publisher ON superhero.publisher_id = publisher.id WHERE publisher.publisher_name = 'Marvel Comics' GROUP BY colour.colour
Evidence:

the superheroes from Marvel Comics refers to publisher_name = 'Marvel Comics'; most common color refers to COUNT(superhero.id) DESC;

Difficulty: moderate
Back to Database Explorer