World Cup Database - Build a World Cup Database

Backend Development
1

Tell us what’s happening:

First test is failing, and none of the others are getting checked. In the screenshot below, it is very clear that I am connected to the DB titled “worldcup”, but the error is telling me that it is not there. I have completed the course and would like to finish it up / post my git link.

image
image816×865 45.3 KB

Does anyone know how I should proceed?

Your code so far

Your browser information:

User Agent is: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/113.0.0.0 Safari/537.36

Challenge: World Cup Database - Build a World Cup Database

Link to the challenge:

2

same issue by me, I think it is because low performance of the code
if it is taking too long to query and insert data into the database, the test should take max 20 seconds, it is written there. I am still not able to make the code more efficient by now, I will explore

Part 2: Insert the data

Complete the insert_data.sh script to correctly insert all the data from games.csv into the database. The file is started for you. Do not modify any of the code you start with. Using the PSQL variable defined, you can make database queries like this: $($PSQL "<query_here>"). The tests have a 20 second limit, so try to make your script efficient. The less you have to query the database, the faster it will be. You can empty the rows in the tables of your database with TRUNCATE TABLE games, teams;

3

In order for it to pass the first test the code must be optimized by limiting the number of PSQL calls. I was able to reduce the insert_data.sh file to a single PSQL command by combining all of the SQL statements into one command:

echo "$($PSQL 
"INSERT INTO teams(name) VALUES ('$WINNER') ON CONFLICT (name) DO NOTHING;
INSERT INTO teams(name) VALUES ('$OPPONENT') ON CONFLICT (name) DO NOTHING;
INSERT INTO games(year, round, winner_id, opponent_id, winner_goals, opponent_goals) VALUES($YEAR, '$ROUND', (SELECT team_id FROM teams WHERE name = '$WINNER'), (SELECT team_id FROM teams WHERE name = '$OPPONENT'), $WINSCORE, $OPPSCORE);")"
4

that is really cool , I even combined the first insert into. I figured out it was not necessary to check if the team existed before, because the table was truncated from the beginning , that is why it did not matter to check those conditions, if my understanding of the logic is correct, just insert everything and use nested queries for the games table :grinning:

 if [[ $YEAR != "year" ]]
  then
  echo "$($PSQL "INSERT INTO teams(name) VALUES ('$WINNER'),('$OPPONENT') ON CONFLICT (name) DO NOTHING;
  INSERT INTO games(year, round, winner_id, opponent_id, winner_goals, opponent_goals) VALUES($YEAR, '$ROUND', (SELECT team_id FROM teams WHERE name = '$WINNER'), (SELECT team_id FROM teams WHERE name = '$OPPONENT'), $WINNER_GOALS, $OPPONENT_GOALS);")"
  fi


done