World Cup Database - everything working except two queries

ChiselD · August 14, 2025, 1:22pm

I’ve been working on the World Cup Database project for several days now and my queries.sh file is almost complete. I’m getting the expected results for every question except two:

“List of teams who played in the 2014 ‘Eighth-Final’ round”
“List of unique winning team names in the whole data set”

For the first of those two prompts, I’m supposed to get a list of 16 team names, but I’m only getting six:
Algeria
Chile
Greece
Netherlands
Nigeria
United States

For the second one, I’m supposed to get a list of 13 team names, but I’m only getting four:
Netherlands
Germany
Belgium
France

I suspect the problem might be with my insert_data.sh file, because when I run it, I get “successfully inserted into database” messages for only four teams - the same four that appear in the second list above.

I’ve already tried adding “RESTART IDENTITY” to my TRUNCATE statement to fix the serial number assignation problem, and increasing the timeout in the package.json file (both suggestions found in other forum posts).

Does anyone have an idea why these two queries are not giving the expected answers for me? I’ll include all my code below.

insert_data.sh

#! /bin/bash

if [[ $1 == "test" ]]
then
  PSQL="psql --username=postgres --dbname=worldcuptest -t --no-align -c"
else
  PSQL="psql --username=freecodecamp --dbname=worldcup -t --no-align -c"
fi

# Do not change code above this line. Use the PSQL variable above to query your database.
echo $($PSQL "TRUNCATE TABLE games, teams RESTART IDENTITY;")

cat games.csv | while IFS="," read YEAR ROUND WINNER OPPONENT WINNER_GOALS OPPONENT_GOALS
do
  # First we need to populate the "teams" table (from the "winner" and "opponent" columns)
  # Skip the first line, which contains the column titles
  if [[ $WINNER != winner ]]
  then
    # Loop through the winners column first...
    # Check if each team name is already in the "teams" table
    TEAM=$($PSQL "SELECT name FROM teams WHERE name='$WINNER';")
    # if not found...
    if [[ -z $TEAM ]]
    then
      # add team name to "teams" table
      INSERT_TEAM_RESULT=$($PSQL "INSERT INTO teams(name) VALUES('$WINNER');")
      # if it succeeds...
      if [[ $INSERT_TEAM_RESULT == "INSERT 0 1" ]]
      then
        echo Inserted into teams: $WINNER
      fi
      # Get new winner_id
      WINNER_ID=$($PSQL "SELECT team_id FROM teams WHERE name='$WINNER';")
    fi
    # Loop through the "opponents" column next
    # Check if each team name is already in the "teams" table
    TEAM=$($PSQL "SELECT name FROM teams WHERE name='$OPPONENT';")
    # if not found...
    if [[ -z $TEAM ]]
    then
      # add team name to "teams" table
      INSERT_TEAM_RESULT=$($PSQL "INSERT INTO teams(name) VALUES('$OPPONENT');")
      # if it succeeds...
      if [[ INSERT_TEAM_RESULT == "INSERT 0 1" ]]
      then
        echo Inserted into teams: $OPPONENT
      fi
      # Get new opponent_id
      OPPONENT_ID=$($PSQL "SELECT team_id FROM teams WHERE name='$OPPONENT';")
    fi
    # Finally, add game to "games" table
    INSERT_GAME_RESULT=$($PSQL "INSERT INTO games(year, round, winner_id, opponent_id, winner_goals, opponent_goals) VALUES($YEAR, '$ROUND', $WINNER_ID, $OPPONENT_ID, $WINNER_GOALS, $OPPONENT_GOALS);")
    # if it succeeds...
    if [[ INSERT_GAME_RESULT == "INSERT 0 1" ]]
    then
      echo Inserted into games: $YEAR $WINNER_ID vs $OPPONENT_ID
    fi
  fi
done

queries.sh

#! /bin/bash

PSQL="psql --username=freecodecamp --dbname=worldcup --no-align --tuples-only -c"

# Do not change code above this line. Use the PSQL variable above to query your database.

echo -e "\nTotal number of goals in all games from winning teams:"
echo "$($PSQL "SELECT SUM(winner_goals) FROM games;")"

echo -e "\nTotal number of goals in all games from both teams combined:"
TOTAL_WINNER_GOALS="$($PSQL "SELECT SUM(winner_goals) FROM games;")"
TOTAL_OPPONENT_GOALS="$($PSQL "SELECT SUM(opponent_goals) FROM games;")"
echo $[$TOTAL_WINNER_GOALS + $TOTAL_OPPONENT_GOALS]

echo -e "\nAverage number of goals in all games from the winning teams:"
echo "$($PSQL "SELECT AVG(winner_goals) FROM games;")"

echo -e "\nAverage number of goals in all games from the winning teams rounded to two decimal places:"
echo "$($PSQL "SELECT ROUND(AVG(winner_goals), 2) FROM games;")"

echo -e "\nAverage number of goals in all games from both teams:"
echo "$($PSQL "SELECT AVG(winner_goals + opponent_goals) FROM games;")"

echo -e "\nMost goals scored in a single game by one team:"
echo "$($PSQL "SELECT MAX(winner_goals) FROM games;")"

echo -e "\nNumber of games where the winning team scored more than two goals:"
echo "$($PSQL "SELECT COUNT(winner_goals) FROM games WHERE (winner_goals > 2);")"

echo -e "\nWinner of the 2018 tournament team name:"
WINNER_ID="$($PSQL "SELECT winner_id FROM games WHERE (year=2018) AND (round='Final');")"
echo "$($PSQL "SELECT name FROM teams WHERE team_id='$WINNER_ID';")"

echo -e "\nList of teams who played in the 2014 'Eighth-Final' round:"
echo "$($PSQL "SELECT DISTINCT teams.name FROM teams, games WHERE (teams.team_id=games.winner_id OR teams.team_id=games.opponent_id) AND games.round='Eighth-Final' AND games.year=2014;")"

echo -e "\nList of unique winning team names in the whole data set:"
echo "$($PSQL "SELECT DISTINCT teams.name FROM teams INNER JOIN games ON teams.team_id=games.winner_id WHERE teams.team_id=games.winner_id;")"

echo -e "\nYear and team name of all the champions:"
echo "$($PSQL "SELECT games.year, teams.name FROM games INNER JOIN teams ON games.winner_id=teams.team_id WHERE round='Final' ORDER BY year;")"

echo -e "\nList of teams that start with 'Co':"
echo "$($PSQL "SELECT name FROM teams WHERE name LIKE 'Co%';")"

database dump

--
-- PostgreSQL database dump
--

-- Dumped from database version 12.22 (Ubuntu 12.22-0ubuntu0.20.04.4)
-- Dumped by pg_dump version 12.22 (Ubuntu 12.22-0ubuntu0.20.04.4)

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

DROP DATABASE worldcup;
--
-- Name: worldcup; Type: DATABASE; Schema: -; Owner: freecodecamp
--

CREATE DATABASE worldcup WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'C.UTF-8' LC_CTYPE = 'C.UTF-8';


ALTER DATABASE worldcup OWNER TO freecodecamp;

\connect worldcup

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

SET default_tablespace = '';

SET default_table_access_method = heap;

--
-- Name: games; Type: TABLE; Schema: public; Owner: freecodecamp
--

CREATE TABLE public.games (
    game_id integer NOT NULL,
    year integer NOT NULL,
    round character varying(30) NOT NULL,
    winner_id integer NOT NULL,
    opponent_id integer NOT NULL,
    winner_goals integer NOT NULL,
    opponent_goals integer NOT NULL
);


ALTER TABLE public.games OWNER TO freecodecamp;

--
-- Name: games_game_id_seq; Type: SEQUENCE; Schema: public; Owner: freecodecamp
--

CREATE SEQUENCE public.games_game_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.games_game_id_seq OWNER TO freecodecamp;

--
-- Name: games_game_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: freecodecamp
--

ALTER SEQUENCE public.games_game_id_seq OWNED BY public.games.game_id;


--
-- Name: teams; Type: TABLE; Schema: public; Owner: freecodecamp
--

CREATE TABLE public.teams (
    team_id integer NOT NULL,
    name character varying(30) NOT NULL
);


ALTER TABLE public.teams OWNER TO freecodecamp;

--
-- Name: teams_team_id_seq; Type: SEQUENCE; Schema: public; Owner: freecodecamp
--

CREATE SEQUENCE public.teams_team_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.teams_team_id_seq OWNER TO freecodecamp;

--
-- Name: teams_team_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: freecodecamp
--

ALTER SEQUENCE public.teams_team_id_seq OWNED BY public.teams.team_id;


--
-- Name: games game_id; Type: DEFAULT; Schema: public; Owner: freecodecamp
--

ALTER TABLE ONLY public.games ALTER COLUMN game_id SET DEFAULT nextval('public.games_game_id_seq'::regclass);


--
-- Name: teams team_id; Type: DEFAULT; Schema: public; Owner: freecodecamp
--

ALTER TABLE ONLY public.teams ALTER COLUMN team_id SET DEFAULT nextval('public.teams_team_id_seq'::regclass);


--
-- Data for Name: games; Type: TABLE DATA; Schema: public; Owner: freecodecamp
--

INSERT INTO public.games VALUES (1, 2018, 'Final', 1, 2, 4, 2);
INSERT INTO public.games VALUES (2, 2018, 'Third Place', 3, 4, 2, 0);
INSERT INTO public.games VALUES (3, 2018, 'Semi-Final', 3, 4, 2, 1);
INSERT INTO public.games VALUES (4, 2018, 'Semi-Final', 3, 4, 1, 0);
INSERT INTO public.games VALUES (5, 2018, 'Quarter-Final', 3, 5, 3, 2);
INSERT INTO public.games VALUES (6, 2018, 'Quarter-Final', 3, 6, 2, 0);
INSERT INTO public.games VALUES (7, 2018, 'Quarter-Final', 3, 7, 2, 1);
INSERT INTO public.games VALUES (8, 2018, 'Quarter-Final', 3, 8, 2, 0);
INSERT INTO public.games VALUES (9, 2018, 'Eighth-Final', 3, 9, 2, 1);
INSERT INTO public.games VALUES (10, 2018, 'Eighth-Final', 3, 10, 1, 0);
INSERT INTO public.games VALUES (11, 2018, 'Eighth-Final', 3, 11, 3, 2);
INSERT INTO public.games VALUES (12, 2018, 'Eighth-Final', 3, 12, 2, 0);
INSERT INTO public.games VALUES (13, 2018, 'Eighth-Final', 3, 13, 2, 1);
INSERT INTO public.games VALUES (14, 2018, 'Eighth-Final', 3, 14, 2, 1);
INSERT INTO public.games VALUES (15, 2018, 'Eighth-Final', 3, 15, 2, 1);
INSERT INTO public.games VALUES (16, 2018, 'Eighth-Final', 3, 16, 4, 3);
INSERT INTO public.games VALUES (17, 2014, 'Final', 17, 16, 1, 0);
INSERT INTO public.games VALUES (18, 2014, 'Third Place', 18, 16, 3, 0);
INSERT INTO public.games VALUES (19, 2014, 'Semi-Final', 18, 16, 1, 0);
INSERT INTO public.games VALUES (20, 2014, 'Semi-Final', 18, 16, 7, 1);
INSERT INTO public.games VALUES (21, 2014, 'Quarter-Final', 18, 19, 1, 0);
INSERT INTO public.games VALUES (22, 2014, 'Quarter-Final', 18, 19, 1, 0);
INSERT INTO public.games VALUES (23, 2014, 'Quarter-Final', 18, 19, 2, 1);
INSERT INTO public.games VALUES (24, 2014, 'Quarter-Final', 18, 19, 1, 0);
INSERT INTO public.games VALUES (25, 2014, 'Eighth-Final', 18, 20, 2, 1);
INSERT INTO public.games VALUES (26, 2014, 'Eighth-Final', 18, 20, 2, 0);
INSERT INTO public.games VALUES (27, 2014, 'Eighth-Final', 18, 21, 2, 0);
INSERT INTO public.games VALUES (28, 2014, 'Eighth-Final', 18, 22, 2, 1);
INSERT INTO public.games VALUES (29, 2014, 'Eighth-Final', 18, 22, 2, 1);
INSERT INTO public.games VALUES (30, 2014, 'Eighth-Final', 18, 23, 2, 1);
INSERT INTO public.games VALUES (31, 2014, 'Eighth-Final', 18, 23, 1, 0);
INSERT INTO public.games VALUES (32, 2014, 'Eighth-Final', 18, 24, 2, 1);


--
-- Data for Name: teams; Type: TABLE DATA; Schema: public; Owner: freecodecamp
--

INSERT INTO public.teams VALUES (1, 'France');
INSERT INTO public.teams VALUES (2, 'Croatia');
INSERT INTO public.teams VALUES (3, 'Belgium');
INSERT INTO public.teams VALUES (4, 'England');
INSERT INTO public.teams VALUES (5, 'Russia');
INSERT INTO public.teams VALUES (6, 'Sweden');
INSERT INTO public.teams VALUES (7, 'Brazil');
INSERT INTO public.teams VALUES (8, 'Uruguay');
INSERT INTO public.teams VALUES (9, 'Colombia');
INSERT INTO public.teams VALUES (10, 'Switzerland');
INSERT INTO public.teams VALUES (11, 'Japan');
INSERT INTO public.teams VALUES (12, 'Mexico');
INSERT INTO public.teams VALUES (13, 'Denmark');
INSERT INTO public.teams VALUES (14, 'Spain');
INSERT INTO public.teams VALUES (15, 'Portugal');
INSERT INTO public.teams VALUES (16, 'Argentina');
INSERT INTO public.teams VALUES (17, 'Germany');
INSERT INTO public.teams VALUES (18, 'Netherlands');
INSERT INTO public.teams VALUES (19, 'Costa Rica');
INSERT INTO public.teams VALUES (20, 'Chile');
INSERT INTO public.teams VALUES (21, 'Nigeria');
INSERT INTO public.teams VALUES (22, 'Algeria');
INSERT INTO public.teams VALUES (23, 'Greece');
INSERT INTO public.teams VALUES (24, 'United States');


--
-- Name: games_game_id_seq; Type: SEQUENCE SET; Schema: public; Owner: freecodecamp
--

SELECT pg_catalog.setval('public.games_game_id_seq', 32, true);


--
-- Name: teams_team_id_seq; Type: SEQUENCE SET; Schema: public; Owner: freecodecamp
--

SELECT pg_catalog.setval('public.teams_team_id_seq', 24, true);


--
-- Name: games games_pkey; Type: CONSTRAINT; Schema: public; Owner: freecodecamp
--

ALTER TABLE ONLY public.games
    ADD CONSTRAINT games_pkey PRIMARY KEY (game_id);


--
-- Name: teams teams_name_key; Type: CONSTRAINT; Schema: public; Owner: freecodecamp
--

ALTER TABLE ONLY public.teams
    ADD CONSTRAINT teams_name_key UNIQUE (name);


--
-- Name: teams teams_pkey; Type: CONSTRAINT; Schema: public; Owner: freecodecamp
--

ALTER TABLE ONLY public.teams
    ADD CONSTRAINT teams_pkey PRIMARY KEY (team_id);


--
-- Name: games games_opponent_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: freecodecamp
--

ALTER TABLE ONLY public.games
    ADD CONSTRAINT games_opponent_id_fkey FOREIGN KEY (opponent_id) REFERENCES public.teams(team_id);


--
-- Name: games games_winner_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: freecodecamp
--

ALTER TABLE ONLY public.games
    ADD CONSTRAINT games_winner_id_fkey FOREIGN KEY (winner_id) REFERENCES public.teams(team_id);


--
-- PostgreSQL database dump complete
--

hbar1st · August 15, 2025, 1:21am

ChiselD:

echo -e "\nList of teams who played in the 2014 'Eighth-Final' round:"
echo "$($PSQL "SELECT DISTINCT teams.name FROM teams, games WHERE (teams.team_id=games.winner_id OR teams.team_id=games.opponent_id) AND games.round='Eighth-Final' AND games.year=2014;")"

don’t you think that there is something you can do to combine the teams and games tables so that you can a list of all ‘Eighth-Final’ round teams? (if you were doing this manually yourself, which table would be the first one you’d need to read?)

system · October 14, 2025, 1:22am

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.

Topic		Replies	Views
Stuck on Build a World Cup Database challenge Backend Development	11	1729	September 8, 2022
World Cup Database Output Correct but wont pass Backend Development	3	369	January 7, 2025
World Cup Database - "You should correctly complete the 'queries.sh' file" but all tests match expected outcomes in the terminal Backend Development	3	944	January 7, 2025
World Cup Database - Build a World Cup Database SUBTASKS 1.1 “before all” hook for “:1 “worldcup” database should exist Backend Development	3	645	January 7, 2025
Worldcup database: script erro Backend Development	35	2051	April 28, 2023

World Cup Database - everything working except two queries

Related topics