Learn SQL by Building a Student Database: Part 1 - Build a Student Database: Part 1

Backend Development
1

Tell us what’s happening:
So I’m at this part of the course. When I ran the insert_data.sh, only 2 rows were generated incorrectly (Major & Web Development). I tried looking over the code, based on all the hints, and the script should be fine(?) But I probably messed up the query lines somewhere. Lol please help this noob identify the issue.

I tried to run the issue through chatGPT (see quoted area below), but I don’t trust the provided answer.

It seems like the script is working correctly, as the table majors has three rows after running the script. The INSERT 0 1 message indicates that one row was inserted successfully. The first column of the result, major_id, is the primary key of the table, and it is automatically generated by the database. The second column, major, contains the major name.

The echo $MAJOR_ID line should print the major_id of the inserted major. However, it seems that the MAJOR_ID variable is not being set correctly. In this case, the MAJOR_ID variable is assigned the result of the psql command, which is not what you expect. To fix this, you need to capture the output of the psql command and store it in the MAJOR_ID variable.

To do this, you can use a command substitution, like this:

> MAJOR_ID=$(echo $(psql -X --username=freecodecamp --dbname=students --no-align --tuples-only -c "SELECT major_id FROM majors WHERE major='$MAJOR';"))

You can also modify the PSQL variable to include the echo command, like this:


> PSQL="psql -X --username=freecodecamp --dbname=students --no-align --tuples-only -c 'SELECT major_id FROM majors WHERE major='\''$MAJOR'\'';'"
> MAJOR_ID=$(eval $PSQL)

Your code so far
Edit:: Let me know if you need me to copy/paste lines of code from the script or wherever.

Screenshot of exercise prompt, script, and split terminal results for better viewing:

1
11618×863 182 KB

Screenshot of courses_test.csv

2
2823×465 27.6 KB

Your browser information:

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

Challenge: Learn SQL by Building a Student Database: Part 1 - Build a Student Database: Part 1

Link to the challenge:

2

Please post the actual code and post the instructions you were trying to complete or follow.

3

Instructions:
Run the script. It will go through the test data and insert a major into the database each time it doesn’t find one already there and print the MAJOR_ID and INSERT_MAJOR_RESULT variables.

  1. Run your insert_data.sh script by executing it

  2. Type ./insert_data.sh in the terminal and press enter

  3. The majors table should have four rows after running the script. If it doesn’t, there might be something wrong in the script. You can use the reset button to reset the lesson and run the script again

The script is supposed to pull data from courses_test.csv and insert “into the database each time it doesn’t find one already there”.

Problem is - when I checked the data, the first line of the courses_test.csv “major, course” was pulled and inserted, but not the other “major,courses”'s (Database Administration,Database Systems &
Data Science,Data Structures and Algorithms)

inser_data.sh

#!/bin/bash

# Script to insert data from courses.csv and students.csv into students database

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

cat courses_test.csv | while IFS="," read MAJOR COURSE
do
  # get major_id
  MAJOR_ID=$($PSQL "SELECT major_id FROM majors WHERE major='$MAJOR'")
  echo $MAJOR_ID

  # if not found
  if [[ -z $MAJOR_ID ]]
  then
    # insert major
    INSERT_MAJOR_RESULT=$($PSQL "INSERT INTO majors(major) VALUES('$MAJOR')")
    echo $INSERT_MAJOR_RESULT

    # get new major_id

  fi

  # get course_id

  # if not found

  # insert course

  # get new course_id

  # insert into majors_courses

done

courses_test.csv

major,course
Database Administration,Data Structures and Algorithms
Web Development,Web Programming
Database Administration,Database Systems
Data Science,Data Structures and Algorithms
4

Have you tried to reset the step at any point?

Edit: after resetting, I would do a select on the majors table to make sure it is reset also. (It should not have any rows)

1 Like
5

Lol I was hoping to resolve it before resetting, but you’re right. I was stuck on this for too long and shouldn’t need to get stuck on this. Thank you

6

did it work (the reset)? I hope it did.

1 Like
7

Lol yes, the reset worked. I was only hoping to identify any issues with my script (since I figured the error wasn’t too serious) before resetting it to “the tutorial’s standard progress”.

8

oh well if it was the script then you could easily diff the script you shared above against the reset version. (and see if there were actually changes in the script that could explain this)

1 Like
9

OMG I didn’t even think of this :man_facepalming:
THANK YOU AGAIN (As always)!

1 Like
10

let me know if you find any…I’m as curious as you are.

1 Like
11

Well, when reviewing for the differences, I couldn’t see anything superficially. So I tried pushing the working version of insert_data.sh & my insert_data.sh (that wasn’t working) through chatGPT to see if there were small punctuation/extra spaces/transposed data errors that I might’ve missed.

It’s response:

It looks like the two scripts are the same. If the first script is working correctly, then the second script should work as well.

If the second script is failing, it might be because of issues with the data being inserted, the database configuration, or permissions.

It is also possible that there is an issue with the PSQL command being used. You can try printing out the PSQL command and its output to see if there are any errors being returned.

The data being inserted should have been fine since it was only 5 lines being copied from the original courses.csv. However, since I already exited and reset the exercise, I failed to capture my database configurations and permissions to cross-reference with the working data.

Lol for now though, I’m just gonna categorize this as a ‘glitch in the system’, and deal with it later if it comes up again :sweat_smile:

12

yeah honestly I couldn’t see a reason for the error in the script. So I’m not surprised there was no differences.

1 Like
13

Since the script was failing to INSERT the 4th row and only INSERTed “major” in the 3rd row from my original script, I simply thought I did something wrong. Lol I realized after going deeper into the lessons, that “major” was supposed to show up in the ‘major’ column.

> +----------+-------------------------+
> | major_id |          major          |
> +----------+-------------------------+
> |        1 | Database Administration |
> |        2 | major                   |
> |        3 | Web Development         |
> |        4 | Data Science            |
> +----------+-------------------------+

Was too busy trying to figure out where I went wrong, instead of using my understanding of the script to identify the (now obvious) missing if [[ CONDITION ]] to check/skip the title name (which will be provided a few lessons later in this course). Would have spent less time wracking my brain over this by focusing more on “why the 4th row didn’t generate”, but I hope my failed logic helps someone get through this part of the exercise and identify issues better than I did lol