Guided Project: Designing and Creating a Database

When I tried to add the person_appearance table, no error came up after I ran my codes. However, I didn’t get any information about the players into this table. Even I copied the codes from the correct answer, I still couldn’t have the players’ information inserted into person_appearance table. Anyone can help me with this problem?
My codes are listed as follow:
c0 = “DROP TABLE IF EXISTS person_appearance”
run_command(c0)
c1 = ‘’’
CREATE TABLE IF NOT EXISTS person_appearance (
appearance_id INTEGER PRIMARY KEY,
person_id TEXT,
team_id TEXT,
game_id TEXT,
appearance_type_id TEXT,
FOREIGN KEY (person_id) REFERENCES person(person_id),
FOREIGN KEY (team_id) REFERENCES team(team_id),
FOREIGN KEY (game_id) REFERENCES game(game_id),
FOREIGN KEY (appearance_type_id) REFERENCES appearance_type(appearance_type_id)
);
‘’’
c2 = ‘’’
INSERT OR IGNORE INTO person_appearance (
game_id,
team_id,
person_id,
appearance_type_id
)
SELECT
game_id,
NULL,
hp_umpire_id,
“UHP”
FROM game_log
WHERE hp_umpire_id IS NOT NULL

UNION

SELECT
    game_id,
    NULL,
    [1b_umpire_id],
    "U1B"
FROM game_log
WHERE [1b_umpire_id] IS NOT NULL

UNION

SELECT
    game_id,
    NULL,
    [2b_umpire_id],
    "U2B"
FROM game_log
WHERE [2b_umpire_id] IS NOT NULL

UNION

SELECT
    game_id,
    NULL,
    [3b_umpire_id],
    "U3B"
FROM game_log
WHERE [3b_umpire_id] IS NOT NULL

UNION

SELECT 
    game_id,
    NULL,
    lf_umpire_id,
    "ULF"
FROM game_log
WHERE lf_umpire_id IS NOT NULL

UNION

SELECT 
    game_id,
    NULL,
    rf_umpire_id,
    "URF"
FROM game_log
WHERE rf_umpire_id IS NOT NULL

UNION

SELECT
    game_id,
    v_name,
    v_manager_id,
    "MM"
FROM game_log
WHERE v_manager_id IS NOT NULL

UNION

SELECT 
    game_id,
    h_name,
    h_manager_id,
    "MM"
FROM game_log
WHERE h_manager_id IS NOT NULL

UNION

SELECT
    game_id,
    CASE
        WHEN h_score > v_score THEN h_name
        ELSE v_name
        END,
    winning_pitcher_id,
    "AWP"
FROM game_log
WHERE winning_pitcher_id IS NOT NULL

UNION

SELECT
    game_id,
    CASE
        WHEN h_score > v_score THEN v_name
        ELSE h_name
        END,
    losing_pitcher_id,
    "ALP"
FROM game_log
WHERE losing_pitcher_id IS NOT NULL

UNION

SELECT
    game_id,
    CASE
        WHEN h_score > v_score THEN h_name
        ELSE v_name
        END,
    saving_pitcher_id,
    "ASP"
FROM game_log
WHERE saving_pitcher_id IS NOT NULL

UNION

SELECT
    game_id,
    CASE
        WHEN h_score > v_score THEN h_name
        ELSE v_name
        END,
    winning_rbi_batter_id,
    "AWB"
FROM game_log
WHERE winning_rbi_batter_id IS NOT NULL

UNION

SELECT
    game_id,
    h_name,
    h_starting_pitcher_id,
    "PSP"
FROM game_log
WHERE h_starting_pitcher_id IS NOT NULL

UNION

SELECT 
    game_id,
    v_name,
    v_starting_pitcher_id,
    "PSP"
FROM game_log
WHERE v_starting_pitcher_id IS NOT NULL;
'''

template = “”"
INSERT INTO person_appearance (
game_id,
team_id,
person_id,
appearance_type_id
)
SELECT
game_id,
{hv}_name,
{hv}player{num}_id,
“O{num}”
FROM game_log
WHERE {hv}player{num}_id IS NOT NULL

UNION

SELECT
    game_id,
    {hv}_name,
    {hv}_player_{num}_id,
    "D" || CAST({hv}_player_{num}_def_pos AS INT)
FROM game_log
WHERE {hv}_player_{num}_id IS NOT NULL;

“”"

run_command(c1)
run_command(c2)

for hv in [“h”,“v”]:
for num in range(1,10):
query_vars = {
“hv”: hv,
“num”: num
}
run_command(template.format(**query_vars))

After running the codes above and creating the person_appearance table, only 5 rows are returned with each game_id when I ran select statement query. The result for one game is as follows:

It seems that there is a problem with the example I picked. For the game_id shown in the result, there are missing values for the players’ information. As shown in the picture attached.

Please where do i download baseball datasets, i would like to work on the project locally and later upload it to dataquest server. My internet keep shutting down.

1 Like

Is it possible to provide us with the original files needed, so as to run the project (and also keep it) locally?