SQL-Bash-Scripts/sql_data_entry.sh at main · HeisSteve/SQL-Bash-Scripts · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
#!/bin/bash
# This is a bash script based off of freeCodeCamp's relational database lessons
# It is a 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"
echo $($PSQL "TRUNCATE students, majors, courses, majors_courses")
cat courses.csv | while IFS="," read MAJOR COURSE
do
  if [[ $MAJOR != major ]]
    then
    # get major_id
    MAJOR_ID=$($PSQL "SELECT major_id FROM majors WHERE major='$MAJOR' ")

    #if not found
    if [[ -z $MAJOR_ID ]]
    then
      #insert major
      INSERT_MAJOR_RESULT=$($PSQL "INSERT INTO majors(major) VALUES('$MAJOR')")
      if [[ $INSERT_MAJOR_RESULT == "INSERT 0 1" ]]
      then
        echo Inserted into majors, $MAJOR
      fi
      #get new major_id
      MAJOR_ID=$($PSQL "SELECT major_id FROM majors WHERE major='$MAJOR' ")
    fi

  # get course_id
  COURSE_ID=$($PSQL "SELECT course_id FROM courses WHERE course='$COURSE' ")
  # if not found
  if [[ -z $COURSE_ID ]]
  then
    #insert course
    INSERT_COURSE_RESULT=$($PSQL "INSERT INTO courses(course) VALUES('$COURSE')")
    if [[ $INSERT_COURSE_RESULT == "INSERT 0 1" ]]
      then
        echo Inserted into courses, $COURSE
      fi
    #get new course_id
    COURSE_ID=$($PSQL "SELECT course_id FROM courses WHERE course='$COURSE' ")
    fi
    #insert into majors_courses
    INSERT_MAJORS_COURSES_RESULT=$($PSQL "INSERT INTO majors_courses(major_id,course_id) VALUES('$MAJOR_ID' , '$COURSE_ID')")
    if [[ $INSERT_MAJORS_COURSES_RESULT == "INSERT 0 1" ]]
      then
        echo Inserted into majors_courses, $MAJOR : $COURSE
      fi
  fi
done

cat students.csv | while IFS="," read FIRST LAST MAJOR GPA
do
 if [[ $FIRST != "first_name" ]]
 then
  #get major_id
  MAJOR_ID=$($PSQL "SELECT major_id FROM majors WHERE major='$MAJOR' ")
  #if not found
  if [[ -z $MAJOR_ID ]]
  then
  #set to null
  MAJOR_ID=null
  fi
  # insert student
  INSERT_STUDENT_RESULT=$($PSQL "INSERT INTO students(first_name,last_name,major_id,gpa) VALUES('$FIRST', '$LAST', $MAJOR_ID, $GPA)")
  if [[ $INSERT_STUDENT_RESULT == "INSERT 0 1" ]]
    then
    echo Inserted into students, $FIRST $LAST
    fi
 fi
done