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 T3.power_name 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 WHERE T1.superhero_name = 'Deathlok'
Evidence:

superpowers refers to power_name; Deathlok refers to superhero_name = 'Deathlok'

Difficulty: simple

SQL Query:
SELECT id FROM publisher WHERE publisher_name = 'Star Trek'
Evidence:

Star Trek is the publisher_name;

Difficulty: simple

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

percent = MULTIPLY(DIVIDE(SUM(gender = 'Female' WHERE publisher_name = 'Marvel Comics'), COUNT(publisher_name = 'Marvel Comics')), 100); female heroes refers to gender = 'Female'; Marvel Comics refers to publisher_name = 'Marvel Comics';

Difficulty: challenging

SQL Query:
SELECT T1.superhero_name FROM superhero AS T1 INNER JOIN gender AS T2 ON T1.gender_id = T2.id WHERE T2.gender = 'Male' AND T1.weight_kg * 100 > ( SELECT AVG(weight_kg) FROM superhero ) * 79
Evidence:

super hero names refers to superhero_name;male superheros refers to gender = 'Male';Calculation = weight_kg > MULTIPLY(AVG(weight_kg), 0.79)

Difficulty: moderate

SQL Query:
SELECT COUNT(T1.superhero_name) FROM superhero AS T1 INNER JOIN race AS T2 ON T1.race_id = T2.id WHERE T2.race = 'Vampire'
Evidence:

vampire superheroes refers to race = 'Vampire'

Difficulty: simple
Back to Database Explorer