Homework 04

Data Management with SQL

As per usual, the repository hold several template files, one for each problem. Running the provided HW4.sql file in the repository will create a handful of new tables in a hw4 schema, which will show up to the left (separate from the public schema). As we get to using more and more tables, I think placing them in a container like this will help you track them. The price you pay, though, is that when you refer to the table names in that schema, they must start with the non-standard schema name. For instance, the superheroes table would be referred to as hw4.superheroes. Given that you will likely be aliasing the table names anyway, this seems like a small price to pay for not having tables just scattered all throughout your database. Let me know if this is causing you issues. Follow the link below to accept the assignment and get access to the starting materials.

Accept Assignment


Problem 1: Super Joins

Running the provided HW4.sql script in the repository will have created a selection of tables all related to superheroes. I am including an image of the tables and their relations below, as well as descriptions of each table in Table 1.

Table 1: A table of all the tables that make up the superhero dataset (how meta!) with accompanying descriptions of the table’s contents.
Table name Description
superheroes The main list of superheroes. Includes their superhero name, full/real name, a list of IDs linking to other tables, and their height (in centimeters) and weight (in kilograms)
sexes The sex of the superhero: Male, Female, or N/A
colors Contains color options for eye color, skin color, and hair color
races Contains all the different superhero races, such as Human
publishers All the publishers that publish different superhero comics
alignments Shows how a superhero is aligned: Good, Neutral, or Bad
attributes List of possible attributes (such as intelligence) that a superhero can possess
hero_attributes This table is an intersection of attributes and superheros. A superhero can have multiple attributes, and for each attribute, they can have a value from 0 to 100 indicating their rating. This table contains those ratings in the attribute_value column.
superpowers All the available abilities or superpowers that someone can have.
hero_powers Lists the pairings of superhero with possible powers, since a superhero can have more than one superpower.

Use these tables to answer the following questions:

  1. What percentage of bad or evil superheroes have (purely) red eyes?
  2. What is the average intelligence of (purely) human superheroes?
  3. What is the most common superpower for superheros with blond (or blondish) hair?
  4. How many unique combinations of race and sex have no corresponding superheroes?

Problem 2: A Family Problem

Running the provided HW4.sql script in the repository will have also created a family_tree table in your database (within the hw4 schema). This table includes informamtion from several generations of simulated families, including information about marriages and children. A short description of the columns is below in Table 2.

Table 2: The family_tree table columns and corresponding descriptions.
Column Description
pid A unique personal identification number of an individual
name The given name of the individual
spouse_id The pid of this individual’s spouse. To prevent duplication, this number is only assigned to the individual that marries into the family
parent1_id The pid of this individual’s first parent. These are not provided for individuals marrying into the family
parent2_id The pid of this individual’s second parent. These are not provided for individuals marying into the family
yr_birth The year this individual was born
yr_death The year this individual died. NULL if they are still alive
yr_married The year this individual married. NULL if they never married
sex M for male or F for female

Use this table to answer the following questions:

  1. How many married couples are represented in the data?
  2. What names were duplicated across the family tree, where individuals have the same name despite being different people?
  3. What married partners have the greatest age difference between them? What is that age difference?
  4. What is the youngest age at which someone became a grandparent? Who became a grandparent at this age?