World Cup Database - "You should correctly complete the 'queries.sh' file" but all tests match expected outcomes in the terminal

Backend Development
1

Hello friends. I sincerely hope I’ve properly formatted this code as to not wreak havoc on this poor little forum thread.

  • User Agent: Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/105.0.0.0 Safari/537.36

  • Lesson page

I’ve solved all the queries, but the test won’t pass. Am I making a silly mistake or is it the machine’s fault?

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.

cat games.csv | while IFS="," read YEAR ROUND WINNER OPPONENT WINNER_GOALS OPPONENT_GOALS
do
  #do not add the heading to the table
  if [[ $YEAR != "year" ]]; then
    # check if team already exists
    OPPONENT_ID=$($PSQL "SELECT team_id FROM teams WHERE name='$OPPONENT'")
    if [[ -z $OPPONENT_ID ]]; then
      #if not, we gotta assign it
      echo -e "\nTEAM_ID not found for TEAM: $OPPONENT. Inserting now."
      INSERT_OPPONENT_TEAM=$($PSQL "INSERT INTO teams (name) VALUES('$OPPONENT')")
      if [[ $INSERT_OPPONENT_TEAM == "INSERT 0 1" ]]; then
        OPPONENT_ID=$($PSQL "SELECT team_id FROM teams WHERE name='$OPPONENT'")
        echo -e "\nSuccess! TEAM: $OPPONENT added with TEAM_ID of $OPPONENT_ID"
      fi
    fi
    #check if winner already exists
    WINNER_ID=$($PSQL "SELECT team_id FROM teams WHERE name='$WINNER'")
    if [[ -z $WINNER_ID  ]]; then
      #if not, assign it
      echo -e "\nTEAM_ID not found for TEAM: $WINNER. Inserting now."
      INSERT_WINNER_TEAM=$($PSQL "INSERT INTO teams (name) VALUES('$WINNER')")
      if [[ $INSERT_WINNER_TEAM == 'INSERT 0 1' ]]; then
        WINNER_ID=$($PSQL "SELECT team_id FROM teams WHERE name='$WINNER'")
        echo -e "\nSuccess! TEAM: $WINNER added with TEAM_ID of $WINNER_ID"
      fi
    fi
    LOG_GAME=$($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 [[ $LOG_GAME == "INSERT 0 1" ]]; then
      echo -e "\nAdded GAME: $YEAR $ROUND: WINNER WAS $WINNER ($WINNER_ID) WITH $WINNER_GOALS GOALS AGAINST $OPPONENT ($OPPONENT_ID) WITH $OPPONENT_GOALS GOAL"
    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:"
echo "$($PSQL "SELECT SUM(winner_goals + opponent_goals) FROM games")"

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) + AVG(opponent_goals) FROM games")"

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

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

echo -e "\nWinner of the 2018 tournament team name:"
echo "$($PSQL "SELECT name FROM teams FULL JOIN games ON teams.team_id=games.winner_id WHERE year=2018 AND round='Final' ORDER BY name ASC")"

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

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

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

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

worldcup.sql dump:

--
-- PostgreSQL database dump
--

-- Dumped from database version 12.9 (Ubuntu 12.9-2.pgdg20.04+1)
-- Dumped by pg_dump version 12.9 (Ubuntu 12.9-2.pgdg20.04+1)

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(50) 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(80) 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 (33, 2018, 'Final', 379, 378, 4, 2);
INSERT INTO public.games VALUES (34, 2018, 'Third Place', 381, 380, 2, 0);
INSERT INTO public.games VALUES (35, 2018, 'Semi-Final', 378, 380, 2, 1);
INSERT INTO public.games VALUES (36, 2018, 'Semi-Final', 379, 381, 1, 0);
INSERT INTO public.games VALUES (37, 2018, 'Quarter-Final', 378, 382, 3, 2);
INSERT INTO public.games VALUES (38, 2018, 'Quarter-Final', 380, 383, 2, 0);
INSERT INTO public.games VALUES (39, 2018, 'Quarter-Final', 381, 384, 2, 1);
INSERT INTO public.games VALUES (40, 2018, 'Quarter-Final', 379, 385, 2, 0);
INSERT INTO public.games VALUES (41, 2018, 'Eighth-Final', 380, 386, 2, 1);
INSERT INTO public.games VALUES (42, 2018, 'Eighth-Final', 383, 387, 1, 0);
INSERT INTO public.games VALUES (43, 2018, 'Eighth-Final', 381, 388, 3, 2);
INSERT INTO public.games VALUES (44, 2018, 'Eighth-Final', 384, 389, 2, 0);
INSERT INTO public.games VALUES (45, 2018, 'Eighth-Final', 378, 390, 2, 1);
INSERT INTO public.games VALUES (46, 2018, 'Eighth-Final', 382, 391, 2, 1);
INSERT INTO public.games VALUES (47, 2018, 'Eighth-Final', 385, 392, 2, 1);
INSERT INTO public.games VALUES (48, 2018, 'Eighth-Final', 379, 393, 4, 3);
INSERT INTO public.games VALUES (49, 2014, 'Final', 394, 393, 1, 0);
INSERT INTO public.games VALUES (50, 2014, 'Third Place', 395, 384, 3, 0);
INSERT INTO public.games VALUES (51, 2014, 'Semi-Final', 393, 395, 1, 0);
INSERT INTO public.games VALUES (52, 2014, 'Semi-Final', 394, 384, 7, 1);
INSERT INTO public.games VALUES (53, 2014, 'Quarter-Final', 395, 396, 1, 0);
INSERT INTO public.games VALUES (54, 2014, 'Quarter-Final', 393, 381, 1, 0);
INSERT INTO public.games VALUES (55, 2014, 'Quarter-Final', 384, 386, 2, 1);
INSERT INTO public.games VALUES (56, 2014, 'Quarter-Final', 394, 379, 1, 0);
INSERT INTO public.games VALUES (57, 2014, 'Eighth-Final', 384, 397, 2, 1);
INSERT INTO public.games VALUES (58, 2014, 'Eighth-Final', 386, 385, 2, 0);
INSERT INTO public.games VALUES (59, 2014, 'Eighth-Final', 379, 398, 2, 0);
INSERT INTO public.games VALUES (60, 2014, 'Eighth-Final', 394, 399, 2, 1);
INSERT INTO public.games VALUES (61, 2014, 'Eighth-Final', 395, 389, 2, 1);
INSERT INTO public.games VALUES (62, 2014, 'Eighth-Final', 396, 400, 2, 1);
INSERT INTO public.games VALUES (63, 2014, 'Eighth-Final', 393, 387, 1, 0);
INSERT INTO public.games VALUES (64, 2014, 'Eighth-Final', 381, 401, 2, 1);


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

INSERT INTO public.teams VALUES (378, 'Croatia');
INSERT INTO public.teams VALUES (379, 'France');
INSERT INTO public.teams VALUES (380, 'England');
INSERT INTO public.teams VALUES (381, 'Belgium');
INSERT INTO public.teams VALUES (382, 'Russia');
INSERT INTO public.teams VALUES (383, 'Sweden');
INSERT INTO public.teams VALUES (384, 'Brazil');
INSERT INTO public.teams VALUES (385, 'Uruguay');
INSERT INTO public.teams VALUES (386, 'Colombia');
INSERT INTO public.teams VALUES (387, 'Switzerland');
INSERT INTO public.teams VALUES (388, 'Japan');
INSERT INTO public.teams VALUES (389, 'Mexico');
INSERT INTO public.teams VALUES (390, 'Denmark');
INSERT INTO public.teams VALUES (391, 'Spain');
INSERT INTO public.teams VALUES (392, 'Portugal');
INSERT INTO public.teams VALUES (393, 'Argentina');
INSERT INTO public.teams VALUES (394, 'Germany');
INSERT INTO public.teams VALUES (395, 'Netherlands');
INSERT INTO public.teams VALUES (396, 'Costa Rica');
INSERT INTO public.teams VALUES (397, 'Chile');
INSERT INTO public.teams VALUES (398, 'Nigeria');
INSERT INTO public.teams VALUES (399, 'Algeria');
INSERT INTO public.teams VALUES (400, 'Greece');
INSERT INTO public.teams VALUES (401, 'United States');


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

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


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

SELECT pg_catalog.setval('public.teams_team_id_seq', 401, 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
--
2 Likes
2

UPDATE: I solved the problem.

I used SELECT AVG(winner_goals) + AVG(opponent_goals) FROM games, the result of which has three more 0’s than SELECT AVG(winner_goals + opponent_goals) FROM games.

Seems a bit too strict.

4 Likes
3

I agree. A little bit too strict, and thank you for figuring this out! I was having the exact same problem.

2 Likes