Learn SQL by Building a Student Database Part 1 | FreeCodeCamp

[Music] Oh, I'm learning song, and today we're going to learn SQL by building a student database, part one. This is a 140-lesson course. We're going to use a bash script to create a SQL script, and it will enter information about computer science students into a PostgreSQL database. So let's get right into it. I haven't looked at this before, and we'll see what we have to do.

Make a GitHub account if you don't have one. By now, you should have one. This is like the, I don't know, fifth or sixth project in this database certification part of freeCodeCamp, and here we go. It's opened up. First thing I want to do is get rid of this, and then that way I have a bigger screen to work with. First thing we have to do looks like is open up our terminal. Terminal, we can do that with uh, control backtick. So I'm going to do that on my computer, and there we go. Beautiful terminal, and then we have to do an echo in here. We have to do `echo hello SQL` for us to move on. So let's do that: `echo hello SQL`, and hit enter, and there we go. Level complete, 0% Complete because we have 140, and then we can use control enter to move on, and I will do that every time because I do not want to scroll down.

All right, we're started with these two CSV files right here with info about our computer science students, and we should look at them. Top row, and each file has titles. Okay, okay. That one must be yeah, these are all all data. Yeah, yeah. These are comma-separated values, CSV files. So the first column is Major, so that these are these first ones before your comma are all majors, and then after that comma is course, and then these ones are all courses, and yeah, that's what a CSV file looks like. It's pretty standard; uh, they're pretty easy to parse, so that's a good deal. Good deal. All right, so you will be adding all that info to a PostgreSQL database. Log into the psql interactive terminal with `psql -U freecodecamp -d postgres` to get started. So let's do that. Also, I just want to maybe open these up here, and then that way I can make that smaller. There we go. Now let's do `psql -U freecodecamp -d postgres`. There we go, and now I'm logged in. Sweet. View the existing databases with `\l`. Here we go: postgres, template0, template1. All the info from the CSV files will go into a single database, creating a new database named students. Uh, how do I get rid of this? Enter. Yeah, there we go. Uh, so let's create a new database, so we just do `CREATE DATABASE students` like so, and that should create it. Awesome. View the databases again to make sure it got created: `\l`, and there we go. We see our students database. Connect to your database so you can start adding tables. So to connect to it, we do `\c`, and type in students, and now we're connected. You can tell by the prompt change. Okay, the CSV files have a bunch of students with info about them and some courses and majors. You will have four tables: one of the students and their info, one for each major, another for each course, uh, four tables, wow, okay, and a final one for showing what courses are included in each major. First create the students table. All right, so let's create the table: `CREATE TABLE students`, and I guess there's nothing in it right now, so I'm just going to do that, and we're going to probably add columns to them. The second table will be for each unique major that appears in the data. Create a table named majors. All right, `CREATE TABLE majors`. Uh, by the way, SQL is not uh, case-dependent, so you can actually have lowercase commands. The third table is for each unique course. Create another table named courses. So we can do `CREATE TABLE` like this and just do `courses`, and that also works. The final table will be a junction table for the majors and courses, created with the name `major_courses` or yeah, `major_courses`. So we can just do `CREATE TABLE` again: `CREATE TABLE major_courses`. Yeah, I used to actually just use lowercase a lot starting out, but now I use uppercase a lot more just because it's best practice. Use the display's shortcut command to view your tables to make sure you're satisfied with them. We can do that with `\d`. Yeah, here we go. On to the columns. The `students.csv` file has four fields. You will make a column for each of those as well as an ID column. Add a column to your students table named `student_id`. All right, `ALTER TABLE students ADD COLUMN`. All right, what do we want to add? We want to add the `student_id`. It's going to be a type `SERIAL`, and it's going to be the primary key, and I think primary keys are automatically `NOT NULL`, but I'm just going to say `NOT NULL` anyway, and there we go. First column is `students.csv` is first name. Add a column to the students table with that name. All right, to make it a type of `VARCHAR(50)` and give it the `NOT NULL` constraint. All right, we need to add a column called `first_name`. Yeah, okay. `ALTER TABLE students ADD COLUMN first_name`, and add a column, yeah, make it a type of `VARCHAR(50)`, `VARCHAR(50)`, and make sure it's `NOT NULL`. There we go. Awesome. Next column in the data is last name, so kind of do the same thing except instead of `first_name`, do `last_name`, and then give it the same max length, make sure it's `NOT NULL`. Yeah, that should be good. Next table is for the major. Since you will have each major in another call another table, this column will be a foreign key that references it. Aha, yes. Create a column in the students table named `major_id`. All right, so we have to add a column named `major_id`. Uh, yeah, let's do `major_id` here, `major_id`, give it a type of `INT`, and I'm guessing it can be `NULL`. Yeah, and we're going to set up the foreign key constraint later. We're just going to do that. Yeah, sweet. Create the last column, GPA. The data in the CSV shows that they are decimals with length two, and one number is to the right of the decimal, so give it a data type `NUMERIC(2,1)`. All right, so let's do `ALTER TABLE students ADD COLUMN GPA`, and it's going to be a `NUMERIC(2,1)`. Yeah, cool. All right, so if we look at the students, yeah, here we see first name, last name, major, and GPA. That's what we're putting into our database. Let's see here what else do we have to do. Use the shortcut command to display `\d`, and `\d students` to display students. There we go. Foreign key is still missing. Let's fill in the majors table next. Add a `major_id` column to it. Make sure it's a type of `SERIAL` and the primary key. All right, so we're going to do `ALTER TABLE majors ADD COLUMN major_id`. It's going to be a `SERIAL` and primary key. There we go. Cool. This table will only have one other column for the name of the major. Add a column to it named `major`. Make it a `VARCHAR` with a max length of 15 and give it `NOT NULL` constraints. All right, I have only one other column for major. Yeah, okay. Oh, it's going to be called `major_id`, and I have also have `major`, which is going to be a `VARCHAR` and not primary key, so we're going to go get rid of that. `VARCHAR` with a max length of 50, and give it `NOT NULL`, so it has to have a name. I'll view the details of the majors table to make sure you like it: `\d majors`. There we go. We like it. Now set the `major_id` column for the students table as a foreign key that references the `major_id` column from the majors table. Here's an example of how to do that. Yes, make sure to let me know, otherwise I would have probably had to look it up. All right, `ALTER TABLE` uh, major. We know we want to alter the students column, right? Yeah, students. `ALTER TABLE students ADD FOREIGN KEY`. The column name is `major_id`. It references the majors table and a `major_id` column. `major_id` column. Yeah, like so. That should be good. Yeah, sweet. All right, view the details of the students table again to make sure the key is there: `\d students`, and we see our foreign key constraints. Cool, cool. Next is the courses table. Add a `course_id` column to it. All right, so we're gonna `ALTER TABLE courses ADD COLUMN course_id`. It's going to be a `SERIAL` and primary key. There we go. Add a `course` column to the courses table that's type `VARCHAR`. Sort of add a `course` column. All right, so we're gonna add this `course` column. Of course, it's going to be a `VARCHAR` with a max length of 100, looks like, and it can't accept all values, so `NOT NULL`, and that should be good. Cool. View the details of the courses table: `\d uh, courses`. Here we go. One more table to go. The `major_courses` junction table will have two columns, each referencing the primary key from two related tables. First add a `major_id` column to it. Just give it a type of `INT` for now. Yeah, so `major_courses` is going to be the pivot table between the majors table and the courses table. It's how we associate many-to-many relationships in relational databases, so that each major can have many courses, and each course can have be related to many majors, and I think that's that. I think that holds true within the university system, especially for general classes. All right, so we're going to say `ALTER TABLE ALTER TABLE major_courses`, and we're going to first add a `major_id` column to it, so we're going to `ADD COLUMN major_id`, and this is just going to be a type `INT`. Yes, there we go, and then set the `major_id` column you just created as a foreign key that references the `major_id` column from the majors table. All right, let me look for that uh, that command that we just that we did before, probably way up here, right? I know where are you? I need uh, that references foreign key. Yeah, here we go. So instead of altering the students table, we're going to alter this major `majors` table. I know it's not the `major_id` column you just created as a foreign key. Yeah, so we're gonna actually `ALTER TABLE major_courses ADD FOREIGN KEY major_id`, which is yep, and then that references that one. That should be fine. Yep, cool. All right, next add a `course_id` column to the same table. All right, I'm going to grab this one. Yeah, this is going to be the `course_id`. `course_id`, which is an `INT`, and then we're going to probably add the foreign key constraint to it, and yep, so we're gonna instead of doing `major_id`, we're gonna do `course_id`. `course_id`, and then it references the `courses`. `COURSES`, and the `course_id` of that table. `course`, and this should be `courses`. There we go. Cool, sweet. View the details of the table you just worked on to make sure the structure is finished. All right, so `\d uh, major_courses`. There we go. Cool. There is one thing missing. This table doesn't have a primary key. The data from `courses.csv` will go in this table. A single major will be in it multiple times, and same with the course, so neither of them can be a primary key, but there will never be a row with the same two values as another row, so the two columns together are unique. You can create a composite primary key that uses more than one column as a unique pair like this. All right, so we're going to do a composite primary key that makes sure is that two columns together aren't the same. All right, so let's do that. We have to do this `ALTER TABLE` statement. `ALTER TABLE`. We're going to alter the `major_courses` table, and we're going to add this primary key, and the two columns are the `major_id` and the `course_id`. `major_id`, `course_id`. That should be good. Let's try it. Yeah, awesome. A few details, `major_courses`. Okay, I guess that worked; uh, I didn't really do it right, but whatever. Okay, now it's finished. View all the tables you ended up with: `\d`. There we go. Next you can start adding some info. Since the students table needs a `major_id`, you can add a major first. Uh, view the details of the majors table to see what info it expects next: `\d major`. There we go. There we go. It only needs them in the name of the major. Yeah, so just the major here. The ID will be automatically incremented. I just made that up; uh, well, it is true though. Add the first major from the `courses.csv` file into the majors table. It's a `VARCHAR`, so make sure to put the value in single quotes. Uh, do they just want me to insert? It's a `VARCHAR`. Do they just want me to do an insert, or do they want me to like do a script? Yeah, use `INSERT`. Okay, let's do that. All right, `INSERT INTO TABLE` or `INSERT INTO majors VALUES`. Uh, let's see here, the major, what values of Database Administration looks like. Yeah, `Database Administration`. Make sure that's in single quotes, or yeah, cool. All right, you select a view all the table reveal all the information. Yeah, `SELECT * FROM majors`. There we go. Next, insert the first course from `courses.csv` into the courses table. All right, so we're going to grab the courses. Yeah, `INSERT INTO courses`. I believe it's `course` for this one. `VALUES`, and the first course is `Data Structures and Algorithms`. All right, `Data Structures and Algorithms` like so. Does that work? Yay. If you all the data in the courses table to make sure it got added: `SELECT * FROM courses`. There we go. Next you can add a row into the junction table. View the details of it to see what it expects: `\d major_courses`. Yeah, it just needs both IDs. Add a row to use first entry in `courses.csv`. Oh yeah, it's just going to be one one because both of them can have one for their uh, IDs. So let's just add that. Let's go `INSERT INTO major_courses`. We need the `major_id` and the `course_id`. `VALUES (1, 1)`. Like that. Yeah, cool. View all the data and the table. Yep, `SELECT * FROM majors`. There we go. Looks like the row got added. Via the details of the students table to remind yourself what to expect so you can add the first student: `\d students`. There we go. First name, last name, major, and GPA available shows what the table needs. Insert the first person `students.csv` into the students table. All right, so we're going to do `INSERT INTO students`, and we need the first name, last name, the `major_id`, and the GPA, and the values are going to be whatever is this first person, Raya Kelms. All right, let's add this person here, and let's go multi-line so we can go `Raya Kelms`, `Database Administration`, and has a 2.5 GPA. All right, and that `off` should be good. Never mind. What did I do? Um, `major_id`, that's why it can't be `Database Administration`. Has to be the number one. Yes, there we go. Sweet, cool. Looks like it worked. A few all the data in students table to make sure. All right, so we're going to `SELECT * FROM students`. `SELECT * FROM students`. There we go. We see Raya Kelms. Okay, you added a row into each table. It might be wise to review the data in the database structure. Adding the rest of the info one at a time would be tedious. You're going to make a script to do it for you. Awesome. That is awesome. I recommend splitting the terminal for this part. You can do that by clicking the hamburger menu top left of the window. Go into the terminal menu. Yeah, okay, and clicking split terminal. Once you've done that, use the `touch` command to create a file named `insert_data.sh` in your project folder. So or I can just add one here. That should be fine as well, and then I can switch between them from the sidebar. All right, so I'm going to do `touch insert_data.sh`. Cool. All right, you should have two terminals open: one connected to postgres and one for entering terminal commands, and the one for terminal commands. This one, I'll use the `chmod` command with the `+x` flag to give your new script executable permissions: `+x`, and the file that we need is this `insert_data` file. So let's do that: `insert_data`, and there we go. Should be good. Yep. Open your new file and add it. Should be `#!/bin/bash`. That looks like that. So let's just copy that. Quick copy, paste, and yep, awesome. Below that, add a single line comment with the text this. All right, so the comments we need to `#` in front of them. Here we go. Cool. First, you should add all the info from the `courses.csv` file since you need the `major_id` for inserting the student info. Add all the info. `cat` is a terminal command for printing the contents of a file. Here's an example: `cat filename`. Below the comment you added, use it to print `courses.csv`. Okay, so we need `cat courses.csv` inside of here, looks like. I think `cat courses.csv`. Cool. All right, we're on the script to see if the file contents get printed. All right, so we can run it by doing `./insert_data`, and there we go. Looks like it works. It worked. Instead of printing the content, you can type that output into a `while` loop so you can go through the rows one at a time. It looks like this. This is pretty cool. Never done this before, but looks uh, looks interesting. So I think I can just copy this. Each new line will be read into the variables `major` and `course`. Add the above to your `cat` command. Uh, in the statements area, use `echo` to print the major variable. All right, print the major variable. All right, `echo $major`. I think I can just do that, or do I have to do a dollar sign in front of the dollar sign? `$major`. Yeah, there we go. Run the script, see if it worked. All right, let's run it. It looks like it did. Awesome. And slooping, but the major variable is only being set to the first word. There's a default `IFS` variable in bash. `IFS` stands for internal field separator. View it with `declare -p IFS`. Okay, whatever this is. `-p IFS` `declare IFS=" "`. Variables used to determine word boundaries. It defaults to spaces, tabs, and new lines. This is why the major variable was set to only the first word on each line from the data. Between the `while` and `read` commands, set the `IFS` to a comma separator like this. Foreign between where uh, between the `while` and `read` commands, between `while` and `read`, we need this `IFS` thing, so `IFS=`, comma, something like that. Yeah, okay, cool. Cool. Now it should use a comma in the data to separate words instead of spaces. Run the script again to see if it's working. All right, let's run the script. Oh, there we go. I see that's different. Okay, now that worked. Okay, it prints the whole major including the space. Oh, yes, I see. Yep. Print the `course` variable on the same line as where you print `major` to make sure it's all working. Print the `course` variable on the same line. All right, `$course`, so, and run at script. Run the scripts. There we go. Yeah, sweet. Okay, your loop is working. You can use the `major` and `course` variables to access the major course when you need to insert data or query the database. Delete the `echo` line so you can figure out what to do next. Delete it. Helps to plan out what do you want to happen for each loop. You will want to add the major to the database if it isn't in there yet. Same for the course, then add a row to the `major_courses` table. Add these single-line comments in your loop in this order: get `major_id` if not found, insert major, get new major. Add these single-line comments. All right, so we need to add these as comments just for note-taking purposes. Isn't that great? All right, let's go. `#`, I think I need a new line for each one of these. Uh, actually, should I try it? Nope. Okay, `#`, `#`, `#`, `#`, and `#`, and then I can get rid of the commas, I guess. Okay, that should be good. Yay, let's go. All right. You use the `psql` command to log in and interact with the database. You can use it to just run a single command and exit. Oh, okay. Above your loop, add a `psql` variable that looks like this. Okay, let's copy that and above your loop. All right, above this, let's put it here.

All right, now, sweet. I kind of want to read the rest of that book. Yeah, for now, you can set query, or now you can query your database with the psql variable like this: psql query here. Okay.

Below the get major idea comments in your loop, create a major ID variable. Set it equal to the result of a query that gets a major ID of the current major in the loop. Make sure to put your major variable in single quotes. Okay, okay. So we need to do this major ID inside of our loop underneath here. So we need major ID equals something. I think major ID equals this whatever this is. I need to grab this part and the query here, which is like select major ID from majors. I know. Yeah, no, I don't know. Make sure to put the major. Let's get a hand quick. Uh, select column name where oh, or Majors major. I see. Yeah. Okay. From majors where the uh major it equals this uh dollar sign major. Yeah, lower sign major. What's that? Is this gonna work? It does. Okay, cool. Let's go.

Below the variable you just created, use Echo to print it so you can see its value when you run the script. Questions? All right, so we need to put Echo underneath here. Echo dollar sign major ID. Major d. There we go. Run the script to see what happens. Okay, so we're not inserting into the database yet, so nothing really. Just some of them have it, so it went through each major from the CSV file and tried to find major ID for each one of the from the database. Looks like it found uh the one you mainly inserted earlier. The rest were empty.

Below your first if not found comments, add an if condition that checks if the major ID variable is empty. It can do that with this: test -z major ID. Place the next two comments in the statement area of the if. Okay, so let's if not found part of it, and then oh, this has to be an if. How do I do the if statements, man? Is it like it's if and then the end of it is fi, and then I think in here's then, and then we put these two in there. I think that's what they want. Oh yes, let's go. I remembered. Big brain. The loop will go into this if whenever a major isn't found. Here you will want to insert the major and then get the new ID. You'll need the ID for inserting data into the majors courses table later.

Below your insert major comments, create an insert major result variable. Set its value to a query that inserts the current major into the database. Don't forget to use single quotes around the value. Okay, I was like, I totally zoned out. What do I have to do? The loop will go into this if, yeah, whenever a major isn't found, yeah. Okay. You will want to insert the major and get the new ID. Yeah, you will need the ID for inserting data into the major courses table later. Below your insert major comments, create an insert major result variable. Set its value to a query that inserts the current major into the database. Okay, so let's copy this and underneath here we're going to do this, and it's going to equal the insert statement, and we can kind of use this part here for that. Our insert statement is going to be insert into major. Insert into majors the major values is going to be that major uh dollar sign major, right? Yeah, dollar sign major. Okay, and that seems to have quotes around it like that, and I think that's good. Okay, yeah, sweet. Looks like it's good.

Below the variable you just created, use Echo to print it. All right, use our go to print this. Echo insert dollar sign insert major results. Cool.

Instead of running through all the data in the CSV file, you should make some test data in the terminal. Use the copy command to copy courses.csv into a new file named courses_test.csv. All right, in this terminal, use the copy command to copy the courses into a new file, so courses.csv into courses.test.csv. Okay, cool. In your new file, remove all the data except for the first five lines. Make sure there's a single empty line at the bottom. All right, make sure there's a single empty line at the bottom, and get rid of all of them except for the top five lines. Something like that. Okay, cool, cool. Let's go back in the insert_data.sh script. Change your cat command to loop through the test file instead of the full one. Change our cat command. Loop through the test file. Yeah. Okay, so instead of courses, we're going to do courses_test. There we go, and we are almost halfway done. Run the script. It will go through the test data, insert a major into the database each time it doesn't find one already there, and printing major ID and insert major result variables. Oh, okay. We're on the script. Okay, so it inserted the field. Looks like it found an ID that was already in the database twice and inserted three new items into the database. You don't need to print the ID anymore, so delete the echo major ID line. All right, let's get rid of this Echo line uh oh major ID. Get rid of this one. Okay, cool.

In the psql prompt, you select to view all the data from the majors table. See what the script added. So we can just switch to this uh prompt and do select all from majors. There we go. We see four of them. I forgot you inserted database admin earlier. The script ran and inserted major from the top line of the file, then it added the other two that weren't already in there. You can use truncate to delete all data from a table in the psql prompt. Try to delete all the data in the majors table by entering truncate majors. Okay, can't say I've ever used this command, but here we go. Truncate majors. All right, it says you cannot trunk a table referenced in a foreign key constraint. The students and majors courses table use the major ID from Majors as a foreign key, so if you want to delete the data from Majors, you need to delete the data from those two tables at the same time. Use truncate to delete the data from those three tables. Oh man. Uh, separate the table with commas. Okay, so truncate Majors, we need students, right? Yeah, students and major students or something, right? Now courses, courses and major courses. I know the students a major. God, students and major courses. Yeah, that should be good. Okay, the all the data in the majors table. Make sure it's empty. Select all from majors. Majors. There we go. Empty. Looks like it worked. For you all the day in the major courses. Select all from major courses. Empty it is. Check the students table. Select all from students. Empty. Last check courses table. Select all from horses. Should have one. Yeah, there should still be one entry in there. Use truncate delete all the data. Okay, truncate uh courses. How you'll need to truncate any tables that use a column from it as a foreign key at the same time. Okay, so we have to actually truncate courses and students. We know courses major or Majors courses. Majors courses. Yeah, there we go. View all the data in the courses table again, and now let's go up there. It is. Now the database is completely empty. Run the script again to see what gets inserted when the database is empty. Okay, so we can go back to our bash script and or a bash terminal and then do insert_data.sh, and we see E4 get inserted. It inserted for that time. In the psql prompt, feel all the data in the majors table. All right, I can't do that here. I have to switch back. Psql. Select all from the majors table, and there we go. You won't want to add the first line from the CSV file to the database since those are just titles. In your script, add an if condition at the top of your loop that checks if major is not equal to Major. Put all the existing code in comments in your loop in its statements area so it only does any of it if it's not the first line. What? Get some hints. Here's an example of an if. Yeah, I got you. Your loop should look like this. Okay, so if major is not Major, then do this. Oh, and just kind of wrap it around everything. Okay, I can do that. Let's copy that. Copy and that is below the do. Yeah, so just kind of wrap it around everything, then and then we have an if down here or if fi, and I can I guess I can tap this over. Uh, let's put it wait, tap this stuff. Yeah, add that. Let's tab this over. Something like that. Yeah. Okay, cool. All right.

In the psql prompt, use truncate to delete all the data in the majors table. All right, go into our psql. Let's do truncate uh majors. Oh, that's so annoying. Uh, let's find that one. This one. There we go. A few other data Majors table to make sure it's empty. Select all for majors. You're on the script to make sure it's not adding the first line anymore. Okay, so I need to run the script insert_data.sh, and there it only did three. It only showed three inserts. That's a good sign. Feel the data Majors table and make sure it's it's three that you want. All right, select all from majors. Uh, yeah, we have to go back to psql. Oops. Select all for Majors. Database animate, admin, web, film, and data science. There's three unique majors in your test data. Those were the three added to the database, so it looks like it's working. Yeah, because yeah, there's two database admins. Okay. Delete the line where you print insert major results. Okay, get rid of this part here. Oops. There we go. You want a nicer message when something gets inserted so it's more informative. Below your insert major results variable, add an if statement that checks if the variable is equal to INSERT 001, which is what it was printing. Use Echo to print inserted into Majors major if in the statement area of the if. Okay, and if statement that checks if the variable is equal to INSERT 001. Okay. Below my insert major result variable. Below my insert major result variable, we have to do an if. So we have to do if our major ID or insert major insert major results is equal to is equal to INSERT 001, then then and this if fi, then what do we want to do? Then we have to Echo something, right? Then print inserted into Majors major. All right, so let's copy that. Let's do Echo. Paste that. Should be good. Okay, cool. All right.

In the psql prompt, I'll create the majors table again. Uh, truncate the majors table. Okay, cool. Check to make sure the table is empty. Oh my gosh, why do I always have to do that? There we go. Should we good? No. Select all for Majors. Come on, buddy. You can run it. Come on. Select all for Majors. You can do it. Uh, resets. Come on, buddy. Make sure the table's empty. Oh, um, I don't have to reconnect. All right, there we go, and then re-let's rerun this script. See if that helps. Okay, whatever. Just rerun the script. All right, it's starting to come together.

Below your get new major ID comment, set the major ID variable to a query that gets a new major ID from the database. Logo get new major ID. Set the major ID variable. The query that gets the new major ID. Okay. Oh, underneath this, we need to get the new major ID. So we do that with this, right? Copy paste. Hey, sweet. Let's go. All right, so the script will insert the majors correctly. Next are the courses. It will be the same steps as for the majors.

Below your get course ID comments, add a course ID variable that gets the course ID from the database. Remember that your course variable will have the current course in the loop. Below your get course ID comment, add a course ID variable. Below I get course ID, add a course ID variable. I'm guessing it's something like this, and then just change major to course, except this has to be uppercase of course. Hopefully that's good. Remember like add a course ID variable and get course ID from the database. Remember that your course variable will have the current course in the loop. Uh, why isn't that working? Oh, or courses of dollar sign course. Oops. Now isn't that it? Will work now. Yay. Let's go. Yeah, it's the same as the majors.

So below the second if not found comments, add an if statement that checks if the query was empty so you can insert the course if needed. Place the existing insert course and get new course ID comments in the statement area of the if. All right, so kind of do the same thing here. Let's just copy this down. Um, grab this. Let's go here. Paste, and then let's go insert course, and let's just change all the majors to courses here. I wish I would know which ones are uppercase and which ones are lowercase, but whatever. Course. Oh, yeah, and I'll probably have to comment out some of these. Uh, this one is the uppercase of course. Horse uppercase. This has to be uppercase, and this one has to be uppercase. Varied, and I probably have to comment that one out. This has to be uppercase, and this has to be uppercase as well. Okay, hopefully that's good. All right, just cruise empty so you can insert the course if needed. Place existing insert course and get new course ID comments in the statement area of this. Oh, I just had to do that. Whoops. Okay, so let's just hopefully I can comment this out and it's okay. Come on, buddy. You know you want to. Uh, let me just Ctrl X this. Yeah, control copy, control X, and then let's just do this. Insert course and again new course ID in there. All right, there we go.

Below the insert course comment, create an insert course results variable that inserts the course into the database. All right, so now I can just do Control Paste and then get rid of this part and then just uncomment this, and then we should be good. So tab tab, and then I can re-grab all this, and now we should be good again. Yeah, sweet. Okay. The variables should be INSERT 001 again if something gets inserted. Below a variable you just created and it's a condition that checks and print inserted into courses course using Echo. Okay, underneath what? Below the variable. Okay. All right, let's uncomment this stuff, and that should be good. Let's see if that works. Cool. It does. And the psql prompt, truncate the data from the majors tables. You can run the script again. All right. Uh, instead of on this, we need to go to psql and try and find that truncate one. There it is. Run script and see if the courses get inserted. So let's run this script again, and okay, cool. It looks like it worked. The test data has three unique courses, and three got added to the database. View the data in the courses table to make sure they are correct. All right, select all from courses. Okay, it's not printing for some reason. I'm not really sure why, but apparently it worked. Excellent.

Instead of manually deleting the data each time you want to run the script, add the command to do it for you near the top of the file. Below your psql variable, use Echo to create a database in the query truncate your four tables in the order of students, majors, courses, major courses. Use Echo to query the database in the query truncate your four tables in this order. Okay. Use Echo to query. So up here, can I do? Yeah, Ctrl J also works, so that's cool. Okay, so underneath this in the query you the top of file below psql. Below psql, we'll have to do this truncate thing, and we can use this here and Echo it out. So copy. Echo paste, and then instead of doing this stuff, we have to truncate inside the inside the commas or not commas quotes. Truncate in this order: students, majors, courses, major courses. Let's copy these, and it should be pretty much correct just because there's comma separating in them, so it should be good. So wheat run the script. All right, control j. Oh, it also brings up my downloads. That's why that's why I won't do it. All right, run the script. At first, truncate zoom, and then it inserts all the data. Awesome. That makes it easier.

Below your get new course ID comments, set the course ID to newly inserted course ID. All right, so I can just uncomment some of this. I can uncomment this. Yeah, there we go. Ah, below get new course ID, so the course ID to the newly inserted course ID. This has to be uppercase. Yes. There we go. All right. One more thing to add for this file. Below the insert into Majors courses courses comment, create an insert Majors courses result variable. Use it and the major ID and course ID variables you created to insert a row into the major courses table. Make sure the query has a major ID column first. Also, you won't need any quotes around the values for the IDs. I have literally no idea what I just read, so I'm just going to do this. Get a hint. The query you want is this. Okay, give me that, and we're gonna have to do this. God dang it. Below insert into Majors courses. Below insert. Okay, right here. Is that good? Darn it. Uh, use it. Okay, so I had to do this here as well. Copy that, and then this query has to go inside of there. Let's copy that or control X and paste that here. Now is it good? Yay. Okay, cool.

Below the variable you just created, add an if condition that checks if it's equal to INSERT 001 like the others. In its statement area, use Echo to print inserted into Majors courses major course. All right, let's copy this first. Let's go down here. Let's paste that, and we need to do this if statement. If not found, right? Or yeah, if it inserted, so this thing here. So let's copy that. Let's paste. Let's tab this over. We have to put this part in there. Insert it into Majors courses. Ctrl X. Control Paste. And then this has to be this variable. Insert majors courses results. Yay. Let's go. Uh, three quarters done. Run the script. All right, run that script. Not here. Bash. Looks like it works. You better look around to make sure via the data in the majors table. Select all for majors. Majors. It's not putting anything printing. Cool. Check courses table. Select all from courses. I also view of the day in the majors courses. It's like all from Majors horses. All right, that part of the script is done. Next, you need to add everything from the students.csv file. Make some test data again in terminal. Use the copy command to copy students.csv into file name students_test.csv. All right, cool. We can do that. Do CP and our caps lock on. CP students into students_test.csv. Beautiful. In the students_test, that's used to be file. I'll remove everything but the first five lines like we did before. The students test and remove everything about the first five lines. Move. Make sure there's a line there. Cool. All right, let's get rid of that. You want to loop through all this info like you did for the other CSV file. The process is the same. Below your existing loop, use cat to print your new test file, pipe the results into a while loop, setting the ifs to com again, and then use read to create first, last, major, and GPA variables from the data in loop. Use Echo to print the first variable. We can do this pretty easily, I think. So let's just grab this while loop here and let's grab everything and if I can I can't whatever mind. Let's just grab this first, this top part, bring it down and just go underneath everything and then have it done here, and then inside of here we're gonna have an echo. We're going to print the first variable, so dollar sign first, and then instead of majoring course, we're going to go first, last, GPA, and what was the last thing? I forgot. Major. Major is it? Major. Major in the GPA. I think. Ah, major, GPA. In loop, use Echo to print the first variable. Darn it. I thought I did that right. You done, or do I have to do this in the loop? Oh, yeah. Okay. Below your existing loop. I use cat. Oh, yeah, I have to change the name of the file. Instead of courses_test, students_test. Now it should be good. Yeah, there we go. All right, we're on the script to see what prints the first, last name. Okay, so let's open that up again. Let's run the script. Whoops. I just copied it again. Oh, shoot. Ah, yeah, it's gonna do like a ton of them, isn't it? Yeah, apparently it's okay, though. Uh, just get rid of the last. I guess again, whatever. Okay, it worked to print to the first.

item. Each row it's predicting the first line again. You will have to take care of that first. Delete the ECHO line first. Delete the echoline. Yes, okay. Just delete the echoline. Um, how did that not work? I deleted oh one one. I believe this ECHO line. There we go. And if condition to the loop that checks of the first variable is not equal to first name, so it doesn't do anything for the first line of the file. Don't put anything in the statement area for now. Okay, so kind of do the same thing as this thing here. If Majors this then, okay. Copy that inside of here. Paste that. If instead of major we're going to do uh what is it? First is not equal to first name. Okay, first it's not equal to your first name, then we should be good. Don't put anything in the statements area for now. Is that that should be good, right? That's not equal the first name. Um, since then if if I, there we go.

All the columns in the CSV file can be inserted directly into the database except for the major. You will need to get the major ID again for that. There's some null values in there as well, so you'll need to use null if the major ID isn't found. Add four single line comments in your Loop: get major ID; if not found, set to null; and insert students in that order. We need to add some comments. I should just get rid of that. I keep clicking on it. All right, so inside of here we have to do comments: # # and insert students. Cool. Below the get major ID comments, set the major ID variable to query that gets the major ID for the currents and students major. Okay, so we have to do like the same thing as this. Yeah, so let's copy this part and then we're just going to get the major ID. Ctrl d inside of a major ID variable or what? Yeah, major ID variable for the current students major. So we need major ID from students where the mate where the students where the first name is first. So first name equals first. Hopefully that works. Um, let's see what they want. The oh major ID is Major. Okay, okay. So we can do that. Major IDs major ID is that not oh I have to grab. Set the major ID variable to a query that gets a major ID for the current students major. What? Okay, apparently they want this query. I guess I can do that for you. Whatever. I don't think it's right, but apparently they think it's right. All right. Below that, use Echo to print the variable so you can see if it's working. All right, so we're gonna go Echo $majorID.

Foreign 27 28 27. Looking at the test data found the idea for all that except the null value. Below the newest if not found comment and if that checks if the variable is empty, put the set to null comment in its statement area. Below the newest if not found comment and if that checks if the variable is empty, if this variable is empty, so $majorID is empty, then we need to do something. Set put these set to null comment in the statement area. Okay, I need to help with the save statement. -Z. Ah, yeah, Z is empty -Z. There we go. When you go to insert the student data, you want to use the major ID if it's found or null if it's not. Below the set to null comment, set the major ID variable to null so you can use it to insert the data. Okay, major ID and all set to null. Major ID = null like that. Oh, sweet. Okay. Move the echo major ID line to below the if statement so you can run the script, see if it's not found or not. So let's move this underneath here and then run it. Looks like and and there we have that one null value. Okay, what should work for inserting the student. Oh, that should work. Delete the echo major ID line. No problems. One last thing to add in the psql prompt: view the details of the students table so you can see what columns to add. All right, so \d students and it doesn't really do anything. How do I like re-set this psql bash terminal thing? Uh, here we go. Let's actually let's reconnect and hopefully that works. So let's go like this: psql username and things and let's quit here and then paste. I can't paste it. Darn it. Screw you. That's just re-login --username=freecodecamp --dbname=students. All right, \c. I know I am connected to the students ready. All right, now I can do \ or I mean select all and feel the details. \d students. Ah, there we go. Now it's showing up again. Oh, sweet. All right. You will need to set the four columns when adding the student info, all of them except student ID. Below the insert student comments, create an insert student result variable that adds a student to the database. Add the columns in the order they appear in the data. Make sure they put the only two columns in single quotes. All right, yeah. We we need to do an insert. Um, I already know how to do this, so I'm just going to grab the um query that I need. Yeah, because I don't want to type it all all out because I'm lazy. Yes, I know I'm very lazy. So let's copy that. We also need the this first part here. Let's copy that and let's put that here. The query is going to be this long string here, so you're just gonna grab it and put it here. All right, that should be good. Cool. 90 done. Below the variable you just created, add an if statement that checks if it's equal to insert 01 like the others. Use an echo to print out that stuff. All right, so we need this thing here. Let's grab this. Let's go under here and let's change this variable to insert student result. All right, and then we need to change that to be this. Copy. Let's paste it here and then the first name has to be $firstName. FirstName and then first is it first? Yeah, I think it's just first and $last. I think that should be good. Yeah, cool. We're on the script. Go to bash. Let's run it. See what happens. Insert it's going it's gone and there we go. It completed. I think it's working. View all the data in students table to make sure it matches the CSV file. Feel all the data in the students. Well, obviously it's not. Oh, it actually might maybe it will. Select all from students. Select all from students or yeah. Emma can really Jimmy. It's like it works. Excellent. It added all the students from the test data. Time to try it with the original files. Change the cat versus test line to use the original file again. All right, so we need courses tests. We know oh yeah, just horses right courses. Okay. Next change the students should just be students. Okay. All right, where's the students one? Students students students. Oh, down here. Yeah, just use students. Yeah, there we go. Time for the Moment of Truth. Run the script and see if it works. All right, let's run this script. Oh yeah. Oh yeah, we're real programmers now. Look at all this automation going on. It's so beautiful. Oh yeah, we're getting Alea Goalski in there. Oh yeah, let's go. All right, that was cool. View all data in students table, see where you ended up with or who we ended up with. Haha. Select all from students. Oh, look at that. 36 rows. Wait, 31 or yeah, 31 because of the incrementations. Yeah, that's how many are in the CSV file. Perfect. Next check the major stable. All right, so select all from majors majors. Oh, it's because I did that Ctrl C. Oh, okay. So I never do Ctrl C on it otherwise stuff like this happens. Ah, but maybe I can do quit and then just grab it quick. See, I have it right here. Okay. Seven rows. There must be seven unique majors in the CSV file. View what's in the courses table. Select all from courses. I forgot to connect. \c um students. All right, now I can do select all from courses. There we go. All right. Looks like there's 17 unique courses in the CSV file. Last few the data Majors courses that this should have the same number of rows as this CSV file. Select all from majors horses. Cool. 28 rows. Same as the CSV file. I think all the data got added correctly. You don't need your test files anymore. Industry. I'm going to use the list command to check what files are in your project folder. All right, so inside of a bash terminal. Oh, whoops. I gotta use the list command right. So ls. User move command to lead the student's test CSV. Remove students test. Remove that. Remove the course test and list the contents. Okay, cool. Database is finished for now. The last thing you're going to do is make a dump. The pg_dump command can do that for you. Use the --help flag with the command to see what it can do. All right, so pg um cider psql. We know not inside that. I think I just hear pg_dump --help. Okay, yeah, cool. This is last step. There's quite a few options there. Enter pg_dump clean create inserts username freecodecamp students greater than student.sql and terminal to dump the database into a students.sql file. It will save all the commands needed to rebuild it. Take a quick look at the file when you are done. All right, so let's do this. Let's go pg_dump pg_dump that's just clean -c -C -a -U freecodecamp students > students.sql and it worked. Cool. All right, and then we can take a look at that students.sql. This is the dump and how many lines is it? Only 370 here. That's nothing. Oh, it's so beautiful. Do they want us to save this anywhere or like when we move on are we going to probably just have all this here anyways? Probably, if I had to guess. All right. Congratulations on completing Learn SQL by Building a Student Database Part One. You've reached the end of the road. All right, so we should be good with this freeCodeCamp challenge and yeah, uh the next thing we have to do is the second one, part two, uh right here. Yeah, part two. It's going to be another 140 lesson course and we'll go deeper into the SQL command. So anyways, if you like the video, make sure to give it a like, subscribe and leave a comment if you're confused or anything and I will try and answer it the best I can and I will see you next time.