Learn SQL by Building a Student Database Part 2 | FreeCodeCamp

[Music] foreign [Music]

And today we are doing Learn SQL by building a student database, part two. This is the second part of this—part one—of building the student database. It's another 140-lesson course, and let's get right into it. So create a GitHub account if you don't have one, and we should all have one by now. And run with CodeAlly, and we should get into the environment and get coding.

All right, here's what it opened up for me. I'm going to get rid of this, and here we have our students, the SQL file. I think that's what we created in the last one, and here we go. The first thing we have to do is start the terminal, so we can do that with either Control backtick or Control J. And here we go. Open up the terminal, and they want us to type in Echo hello SQL and press Enter. So let's do that: Echo hello SQL, and then there we go. And then to move on, we can just press Ctrl+Enter and then go on to the second challenge.

So now we need to log into psql. We can do that with `psql -U username=freecodecamp -d DBname=postgres`. There we go. List the database; we can do that with backslash l. Yeah, backslash L. There we go. Your database isn't here. You can use the SQL file you created at the end of part one to rebuild it. Recommend splitting the terminal; you can do this with the—I'm just going to use this plus sign here—in order to create a new terminal. And then we can do `psql -U postgres < students.sql`, and then it will recreate the database for us, really solve the tables and data. So let's do that. Uh, actually, it'll do the whole database, yeah, I will. So `postgres < students.sql`, and there we go, and it went through all of them, and there we go. Now we have our database, and we should be able to connect to it. Let's see. View the databases again, so we do that with backslash l, and let's see what's in there. Looks like we have our students database in there now. Okay, I'm gonna make this a little bit bigger. Here we go. There's your students database. Connect to it: backslash c students. Awesome.

Now that you're connected, display the tables and relations that are here to see if it's all correct: backslash d. Here we go. That all looks right. Few of the details of the students table to make sure the structure is right. All right, backslash d students. Here we go. Looks good. Make sure all the data is in the table as well: `SELECT * FROM students` from the student's table this time, not the database. The data is all there. You should take a look at the details of the other tables and the data in them to make sure they look good. When you're done, use `touch` in bash terminal to create `student_info.sh`. I'm just going to do that because I don't want to check everything. `student_info`. Oh, I have to make this file. That's right. `touch student_info.sh`. We're going to make a script about the students. Give your new file executable permissions, so we do that with `chmod +x student_info.sh`. Yeah, here we go. Got a shebang at the top of the style. Let's grab that, put it here, and close this up again. And we have to add this shebang, which is like a hashtag thing. Yeah, here it is. Just grab that, put it at the top, which—again—go back. Hello, the shebang. Add a comment that says—put this right here—so we just have to do hashtag. That should be good. Can I click Ctrl+Tab to like switch these? No, I can't. Do you Shift+Tab? What about one? No. Earn it. Maybe there was a better way to do that, but in the new script, use Echo to print out this. All right, so let's copy that. Use the `-e` flag with it to put a new line at the beginning and end of the text. Okay, let's copy this. Let's do `echo -e`, and then paste, except I think we need quotes around this stuff and then like backslash n as well before and after. So let's do that. See if that works. Darn it. What do they want? Yeah, I know, I know. The hell is that not what I did? I need a—how's this not right? Uh, run it. Okay, I just had to run it, I guess. Put on the script to make sure it's working. All right, `./student_info.sh`. Here we go. Awesome.

You will want to query the database again to get info about the students. Display—add the same psql variable to use in your `insert_data.sh` script. It looked like this. All right, so let's copy that and put it in here. Okay, cool. Although the psql variable you just added, use Echo to print out the first name, last name, and GPA of the students with the 4.0 GPA. Okay, use the `-e` flag to put a new line at the beginning of the sentence. Okay, I forgot how to do this, so I'm pretty sure the hints will help me out here. I need to do—where is it? Oh, that's what they want. Oh, never mind. I—I thought they wanted us to do an actual query, SQL query. Anyway, we can just do that; it should be good. All right. You will want to print what the sentence is asking for. You should know how to make that query, yeah, but let's practice a little first. I just—yeah, I don't know at the start of it. Okay, SQL stands for Structured Query Language, the language you have been using to manage your relational databases in this psql prompt. The all the data in the students table like you have done many times. View all the data in the students table: `SELECT * FROM students`. Okay, and you should look at the query or look at the column titles that were returned. Yeah, okay. The star gets all columns on the table. To query, you can return specific columns by putting the column name in the query instead of star. In the PS go prompt, view just the first name and column from your students table. Yep. All right, `SELECT first_name FROM students`. There we go. Whenever you just get the first name, just the first name column was returned this time. You can specify as many columns you want returned by supporting them with commas. View the first name, last name, and GPA: `SELECT first_name, last_name, GPA FROM students`. Here we go, and we get those three. And then we can add a condition where GPA is less than 2.5. So let's do that: `WHERE GPA < 2.5`. Awesome. The less than only returns rows where the GPA column was less than 2.5. Some of other operators are less than, greater than, less than or equal. Yeah, with the same columns but only rows for students with GPA greater than or equal to 3.8. All right, so greater than or equal to 3.8. So that's what we want. Wonderful. That only returns students with GPA of 3.8 or better. There's equal and not equal operators as well. If you're the same columns for students that don't have a 4.0 GPA, that's me. All right, we don't have a 4.0: `!= 4.0`. There we go. Beautiful. Right query will get you only the data you were looking for. Back in your `student_info.sh` file, add an echo command to the bottom that prints with the sentence above it asks for. Place double quotes around it like this. Okay, yeah, this is what I wanted, and then I need to put the query there. Yeah, okay, let's go here. We need to `SELECT first_name, last_name, GPA FROM students WHERE GPA = 4.0`, and that should be fine. Let's try that. Here we go. Cool. Run the script to see the students with the highest GPA. So switch back to this bash scripts. There we go. Carissa, Vanya, and Dijon. Yeah. Another Echo statement at the bottom of the script. Make it print this. Okay, put a new line in front of it like the first sentence. All right, copy. Let's do this Echo thing. Let's go `-e \n`. There's another paste. Awesome. Should be fine. Practice first in the SQL prompt. View all the data in the majors table. All right, so `SELECT * FROM majors`. Beautiful. Operators you used with numbers in the last section can be used on text as well. Use the equal to view all majors named game design. All right, `SELECT * FROM majors WHERE major = 'game design'`. I need single quotes around text values as well: `game design`. There we go. Just get that one. Next, view all the rows not equal to design. So it'll be all of them except for that one. Really wow. Use the greater than operator to see majors that come after it alphabetically. Okay, so now we're getting a little bit more interesting: `>` Oh wow, `game design` was not including the results because it's not greater than `game design`. Try it with a greater than or equal; then it will show up. Oh yeah, yes it will. Oh yeah, look at that. It included `game design` in the results that time. So if you want to see the results, let's start with a G or after. You could use `major >= 'g'`. Few the majors that come before G. All right, a few the majors that come before G. So does that mean we move to new lesson? `+` then G. Here also we can put spaces in between this. That worked as well. In your script, and Echo at the bottom to print the suggested info you did before. Make sure to use double quotes where it's needed. Right, all course names whose first letters before D in the alphabet. All right, so we're getting it. `course_name`. Okay, `SELECT all course names`. So `SELECT * FROM courses`. So I figure out what is in here. What do we have? This, it's `course`. So `SELECT course FROM courses WHERE course < 'd'`. Yeah, something like that. Let's try it. Hey, that does work. Cool. Run the script to see what course names come before the letter d. All right, `computer networks`, `computer systems`. Yeah, it seems like it does. All right, looks like there is five of them. Add another sentence like the others that says this. All right, let's copy that. We need to do this Echo statement. So let's bring that down. Alt+Shift+Down. We'll copy it, and let's paste this in without the next lesson. There we go. Should be good. Great. Run, run, run. Yeah, there we go. Find that. Start by using the SQL prompt to view all the data in students table again. Okay, because what do we have to do? First name, last name, GPA whose last name begins with an r or after and have a GPA greater than 3.8 or less than 2. Okay, yeah, so I gotta find a bunch of students data. So `SELECT * FROM students`. There we go. Return 31 rows. Use the same command but only return the rows for students whose name comes before whom in the alphabet where the last name is less than m. Okay, that's 18. You can use multiple conditions after where with and or or among others. Just add a keyword and another condition. In the SQL prompt, to use the same command as before, I add an or. Okay, or GPA is less than 3.9. With a three, four equals 3.9, equals 3.9. Okay, 19 rows. Code rows where one of the conditions was true. There was one more than last time. Enter the previous command but use and to view only students that meet both conditions. All right, so now it'll be just one. Yeah. All right, now it only shows rows where both conditions are true. One person. Enter the previous command but add a third condition for GPA or less than or GPA is less than 2.3. It'll show none of them. Aggressing GPA is less than 2.3. Oh, actually, it does show more. If this showed all students whose GPA is less than 2.3, because the final or condition was true for them, it didn't matter what their last name started with. You can group conditions together with parentheses like this. This would only return rows where condition is true and one of the others is true. If you students whose last name is before M that have a GPA of 3.90 or less than 2.3. So yeah, we just have to add parentheses so that this gets evaluated before the and. Now that's two of them. Okay, cool. Two students meet those conditions. Back in the `student_info` file, add an equity under the bottom to print suggested rows. All right, I wonder if I can copy this. Hopefully, copy this, and then we're going to bring this down. I'm just going to paste it here. Does work. Okay, so I need it. First name, last name, and GPA. First name, last name, GPA, GPA. Oh my gosh, can type. And then I need word last name begins with an r or after or after. So that would be less, greater than or equal to, right? And GPA is greater than 3.8, greater than 3.8, or less than 2.0, less than 2.0. Okay, let's see if that works. Hey, let's go from the script. See the result. Go to bash and run it. Cool, cool. All right, moving along. Add another Echo command like dealers. We're just going to do a bunch of queries. Looks like this is much easier than last time. I think—I think so. At least hopefully you guys think the same. Paste. There we go. I can deal with queries. Queries are easy. There we go. You can start by viewing everything from the courses table. Okay. All right, so it `SELECT * FROM courses`. Here we go. Thank you. Oh my gosh, what did I do? `SELECT * FROM courses`. There we go. There's a few that contain the word algorithms. You can use `LIKE` to find patterns in text like this: `WHERE column LIKE pattern`. An underscore and a pattern will return rows that have any character in that spot. View the rows in this table with a course name that matches the pattern. The rooms, anything algorithms basically. All right, so we need to do `SELECT * FROM courses WHERE course LIKE '_algorithms_'`. There we go, and it's just one. Okay, that pattern matched only rows that had exactly one character followed by algorithms. Another character, another pattern character is the percentage. It means anything can be there. To find names that start with W, you could use `W%`. View the courses that end in algorithms. All right, that end in that. Okay, so just percentage this time. Okay, `data structures and algorithms`. We found two that time. Try viewing courses I start with web. Okay, so we want the ones that start with web. So in that case, we can just do `web%`. Yeah. I've got a percentage. Combine the two pattern matching characters to show courses I have a second letter of E. Second letter of e. Okay, so `_%e%`. Yeah, yeah. Beautiful. Look at that. Nice job. Driving the courses with the space and their names. Try viewing the courses with a space in their name. So I guess we can do `% %`. Something like that. Yeah, there they are. You can use `NOT LIKE` to find things that don't match a pattern. A few courses that don't contain a space. All right, a few courses that don't contain a space. So we can do another `LIKE`. Yeah, there we go. The opposite. Five courses with own space. Try finding the ones that contain an a. Brilliant. So to contain an a, look like—oh oops, oops. That I'll attack. There we go. All right, six rows. This showed. Okay, this showed all the courses with capital A. I like will ignore the case of the letters when matching. Use it to see the courses with an A or a. So we can use `ILIKE` to be case insensitive. I found 11 rows at a time. You can put `NOT` in front of the `ILIKE` as well. Use it; you can see the courses that don't contain any or lowercase a. So I can say `NOT ILIKE`. Oh my gosh. There we go. Uh, let's move on before are we 39? You combine these like any other conditions. View the courses that don't have a capital or lowercase a and have a space. Well, where they don't have a capital or lowercase a and they have a space and they have a space. Okay, so and `LIKE '% %'`. Wrong type. `LIKE` does not type. `LIKE` does not exist. And `LIKE`. I thought you could do that. Okay, maybe not. Let's get a hint quick. And `course`. Well, of course, I have to add `course` here. `course`. Yeah, there we go. Cool, cool. In your `student_info.sh` script, add an echo statement at the bottom like the other to print the results of the suggested query. Okay, so we have to go to `student_info` and print the results of this just query. Okay. All right, so last name is students whose name. Okay, yeah, yeah. Okay, just—I was trying to make sure that these two are different. Okay, last name from the students whose last name contains a case and sensitive s a. Okay, where the last name, last name `tanes`. So `LIKE '%s%'`. Oh, that's—I keep doing that. Word last name. I need to re-specify the column that we're working with. Fun. I'm gonna need help. Or and its name. Oh, I forgot a little apostrophe there. There we go. Right. No, what? Um, last name of students whose last name contains cases. Okay. Yeah, I know what happens when I run my script. `%r_` does not like that. Oh, I need a—I forgot another apostrophe right here. There we go. Now that you work. Hey, there we go. Components. Yeah. Okay. I guess I were in it. Looks—looks like five students meet those conditions. Um, yep. Another Echo command at the bottom. All right, let's grab this, copy, and we're gonna Echo this. `echo -e \n`. Paste. Here we go. Oh, hopefully that's saved. Okay, I like accidentally went back on my browser. Okay, thankfully it's saved. Okay, start by looking at the data in the students table. Did it save? Oh shoot. My shield Lisa. Foreign. Just like weirdly. Okay, I got back to it. I just gotta add another terminal. There we go. All right, all the fields that are empty are blank are null. You can access them using `IS NULL` as a condition like this: `WHERE column IS NULL`. View the students who don't have a GPA. All right, so we need to do `SELECT * FROM students WHERE GPA IS NULL`. Here we go. Three of them. Inversely, you can use `IS NOT NULL` to see the rows that aren't null. That do not have a GPA: `IS NOT NULL`. There we go. Those are the ones that don't have a GPA. View all the info on students you haven't chosen a major. Um, so we're the major ID is null. There we go. A few students who don't have a major but don't include students without a GPA. Through the students but don't include. Okay, or oh, and GPA is not null. What the heck, man? And but don't include students without a GPA or GPA is not null. I see. There we go. One more. View the students you don't have a major and GPA. I don't have a major and GPA. Oh, where it is an alphabet. Okay. There we go. In your script and Echo command at the bottom to print the results. The sentence is looking for. Alrighty, let's come up with this beautiful line. Oh, and they formatted it for me. Thank you. All right, so we need to grab the first name, last name, and GPA. First name, last name, and GPA from students where who have not selected a major and either their first name begins with D or they have a GPA greater than 3.0. Okay, so we're a major ID. Let's get rid of this quick or your major ID, you know, and either their first name, first name begins with, so like and then it begins with the D. So we need a percent. Uh, I know `D%`. Yeah, it begins with a D. Sounds good. For they have a GPA at greater than 3.0. `GPA > 3.0`. I think that should work unless I'm stupid. It does work. Let's go. I'm not stupid. Run the script to see the students that meet those conditions. Great. Whoopsies. Quit.

Quit there. We go. I can do uh dot slash student info. There we go. Beautiful. It's no Dawn and Hugo. There's three of them. Add another sentence like the others that says this. Okay, copy that bad boy. Let's go down here. Let's do this. Echo -e x/n paste. Good. Go. You can specify the order you want the results to be in by adding order by at the end of the query.

And that SQL prompt to view all the info in the students table in order by GPA. All right, select all from students. Left all from students where not order by at the end of the query. In order by gpas. Order by GPA. Here we go. This puts uh loads GPA at the top. Yep, okay. And that would be Migriano with the lowest. And when using order by, it will be an ascending order by default. Add descending at the end of the last group, the highest ones at the top, so we can do e-s-c at the end, and then it will be in first order. All right, now the highest GPAs are at the top. You can add more columns to the order by separating them with a comma like this. Any matching values in the first order column will then be ordered by the next. View all the students info with the highest GPA at the top and an alphabetical order by first name. Okay, so first we have to do GPA with the highest at the top, so descending, and then add a comma, and we need first name. There we go. Beautiful.

Many times you only want to return a certain number of rows. You can add limits at the end of the query to only get the amount you want. View the students in the same order as Alaska command but only return the first 10 rows. Right, so limit of 10. There we go. Now we only get 10. The order of the keywords in your query matter. You cannot put limit before order by or either of them before where. Do the same number of students in the same order but don't get the ones who don't have a GPA. Okay, so if they add a where clause in here, so where uh GPA is not null. Yeah, order by GPA. There we go. In your script add the echo command to print the rows of sentence asking for okay. So that helps us with this. Let's see what we need. Bring this down. We need to get the course name. Let's get rid of this query and start from scratch. So I'll select and then course name of the first five courses. All right, in Reverse alphabetical order. So we need to select the course from courses uh where that have an e as a second letter. Okay, so we have to do this part first uh where e is the second letter, so like where first course like and then we present. Now we need a philosophy percent plus v. We have an e as a second letter, so I guess I could do like that and then e and then percent or end with an s or end with an s or end with an S. 4 of course like and then it ends with an s, so we need this s. Okay, and then we need it for its five courses in Reverse alphabetical order, so order by. Order by Force um it's not descending. Hopefully, hopefully is. I'm just guessing and then we need to limit. Limit of negative. Let's try that. I think that's right. I think I have my apostrophes correct. It is. Let's go run script and see the course. Okay, it's these ones. Web programming, web applications, and all those awesome. And another Echo command at the bottom of scripts like the others. I'm going to make this one say this. I like that little Emoji. It's cute. All right, let's um let's make some room here. Echo -e backslash n paste.

There's a number of mathematic functions to use with numeric forms. One of them is min. You can use it when selecting a call like this. Select Min call from table. It will find the lowest value in the column. And the SQL prompt view the lowest value in the GPA column student stable. All right, so switch back to here um maybe I can show that. Okay, sure. So we need to find the lowest in lowest GPU in the students. So Min GPA move from students. What's a minimum? 1.8. Another one is Max. Use it to see the largest from the students table. There we go. It's 4.0. In the same fashion use the sum function to find out what the values add up to. All right, so we can use sum to add them all up. Sum 86.5. What? Oh, major ID. What? Why would they make us do this? Major ID. Create your ID. That's stupid. Whatever. Average will give you the average of all the columns.com um use it to see the average of the same column. Average of major ID I guess average. All right. You can round decimals up or down to the nearest whole number with ceil and floor respectively. You sealed to round the average Major ID up to the nearest whole number. Here's an example um I think they're all whole numbers aren't they anyways you know whatever um what we just seal here that was around the average so we just put seal around this c e l and throw this around the average. Yeah, there we go. 39. Or you can round a number to the nearest whole number with a round. Okay, so round this to the nearest whole number. Round sum 38. You can round to a specific number of decimal places by adding a comma and number two round like this. Greater only average of the majority to five decimal places. Okay, so we just have to add a comma and five there we go. You should be able to find what your script is asking for now. Add the command to print it. Okay. All right. Average GPA of all students rounded to two decimal places. Beautiful. Some useful information actually though. All right, so we need to select the average round it up so we need to go around as well. Ground the average of GPA to two decimal places from students internal students. That's all. Yeah, yeah. Let's go run the script to see the GPA 3.09. That's pretty cool. They're doing pretty good. Add another command to print to this. All right, so here's another build statement. Let's copy that. Echo -e backslash n paste. Go. Another function is Count. You can use it to last count of teams. It will tell you how many entries are in a table for the column. Try it out and the single prompt by using count star. Let's see how many majors there are. All right, so select count of actually I should make that uppercase and I don't think it matters though uh count star from uh majors. Seven. Cool. Using the same method check how many students you have. All right, check how many students you have. Select count of students account star students from students. Let's try that. 31. Yep. Using star like that told you how many total rows are in the table. View the count of the major ID column and the students table. See how many of your students have picked a major. Um okay, the count of major ID okay major ID 23 only. Okay. Using major ID didn't count the null values in that column. 23 students have a major. Distinct is a function that will show you only unique values. You can use it like this. Distinct column. View the unique major ID values in the students table. All right, so for this we just need to do distinct around here be distinct major idea from students. Feel the unique majority values. Yeah, okay. 42 41. Oh, also null is part of that. There's six unique major ID values on the students table. You can give get the same results with Group by using the example file to use it. It's like column from table Group by column. Use this method to view the unique major ID values in the student table again. All right, so we need to select instead of distinct what the heck is happening uh okay what the fun there we go. So let's view the unique major ID values from students Group by major ID. Yeah, grouped by Major ID. It should give me the same stuff. Yeah, okay. Yep. It was the same as distinct but with group value. You can add any of the aggregate functions min max count to it to find more information. For instance, if you wanted to see how many students were in each major you could use select count star from students Group by major and D view the major D column and number of students in each major. I mean, you know, I'm just going to copy this actually I can't because it doesn't allow copying from there to the prompt. It's annoying. Let's just do this then from students Group by look at your idea. Wait, what? That's not what the major D column and number of students in each major. I have to add major ID here. There we go. Cool, cool. When using Group by any columns in the select area must be included in the group by area. Other columns must be used with any of the aggregate functions Max average count Etc. Do the unique major ID values with Group by again but see what the lowest GPAs is in each of them. All right, so we need to other columns must be used with any of the aggregate functions. You see the lowest GPA in each of them. Okay, so we need to go min of GPA. Right, I don't know what did you want succulent. Yeah, oh I just need to get rid of the count. Okay, there we go. Q-q. Nice job. Enter the same query but add a column that shows you know the highest GPA in each major as well. The add a column that shows you the highest as well. So we also need Max GPA. Min and Max GPA. Here we go. Cuckoo. Another option with groupies having. You can add at the end like this. So Group by and then having the condition must be an aggregate function with a test an example two might be to use an example two might be to use yes having count star greater than zero to only show what whatever columns grouped that have at least one row. Use having to only show rows from the last query that have a maximum GPA. You know what I need to do here an example um use having to only show rows from last query for the last query. So Group by having maximum GPA 4.0. Having GPA move go Max GPA of equal to 4.0. Oh, there we go. So those two majors two of your Majors have at least one student with a 4.0 GPA working at the results. The column is named min. You can rename a column with ads like this. Select okay yep. Enter the same command but rename Min to Min GPA. All right, so we're going to go back over here and we're going to go as Min GPA like so. Cool, cool. Now the column has a better name. Enter the same command but rename Max to Max GPA as well. Okay, let's go back to that smacks and Max GPA. Here we go. Beautiful. In a Max that's more descriptive. View the major ID and number of students in each major ID in a call named 24 students. Okay, let's go back here. Let's go. We need the count of major IDs. Major ID um a number of students has number is new dance. There we go. So they wanted why not a number of students each major IDE for each major ID for each major ID having no I don't know help me. Group by yeah okay just give me the answer. Oh my gosh select yeah oh I just need count they don't want me to have them in stuff in there anymore why not come on man so I just made your ID and count okay but Group by Major ID oh and I have to get rid of this okay by Major ID there we go what am I doing wrong I don't get it select major ID count star that's what I'm missing I have to do count star apparently there we go beautiful. Use having with the last creature let me show the rows with less than eight students in the major. All right. Use having let me move myself up a little bit. I'll use having with the last query to only show the rows with less than eight students in the major. So having uh count uh star less than eight. Okay, cool. All done. Back in your script add the command that the print suggests. Okay, I'm not very good at uh the having and stuff but we'll we'll try it out. Let's see. Major ID total number of students. Okay, let's get rid of this here if I can get rid of that. Okay. Select major ID major ID total number of students and a column name number students. So we need count of all and I think in a column named number of students as number of students and average GPA. So we also need the average GPA. So average GPA rounded to two decimal places. Round it to two decimal places in a column named average GPA as average GPA and then for each major ID in the students table having a student count greater than one. Okay, so having each major ID so having a student count so count uh major ID or count star I don't know how to beat your ID greater than one. Let's try that. Darn it. Why isn't it working? What does it give me? I messed this up. Major D does not exist. Oh, I forgot to do a from. Okay, I need from here. From students. Okay, now let's try it. No function matches a given name and argument types average. Oh, really? You might need to add explicit Typecast. Really? Wait, what? I can't do average like that. So Major D count star average what do I need to do? Oh yeah, what I need a hint man if you're on the script last year I'll go just print that. Yeah, I know from students Group by yeah I know round average GPA oh that's why that doesn't round it this does frown leverage and then GPA I see I see now let's try that select major ID disappear in the group by clause or an agree of function a group by a major ID having oh I need to group by major ID have encount I see group y major ID have encount of star greater than one count of star there we go that should work yeah there we go cool cool little script see the outputs and we get a bunch of numbers it doesn't really mean much and Echo command to your script like the others that print this. All right, let's copy that. I'm sure we'll get into joins and stuff too. I'm sure that's coming later. Backslash n paste. Let's see what that comes up with. Cool, cool. Majors and students table are linked with the major ID foreign key. If you want to see the name of a major that a student is taking you need to join yeah yeah look at that the two tables into one. Here's an example of how to do that. Select all from table one full join on a table equals that yep. In SQL prompt join the two tables together with the above method uh majors and students. Okay, so let's select all from majors uh full join we want our majors all right which we'll join our students on our Majors dot major ID equals our students students dot majority and that should work right no this was a mash command God during it oh my God I gotta retype it shoot select all from majors full joined on students students on select all for Majors full joint students on majors that page your ID equals students dot major ID there we go um does that not work it seems like it worked to me um why did that correct command do they want me to do students first oh maybe when we did two students first you think you're funny huh you think you're funny okay fine I'll do it your way select all from students full join Majors on students dot major ID equals Majors dot major ID whatever it's showing all the students from showing all the columns from both sides the two major ID columns are the same in each row. We can see that there are some students without a major and some Majors without any students. Full join you use will include all the rows. We can use the left joint to join the same two tables in from there you could use any of the previous methods narrow down group order. Use left join to join those same two tables in the same way. Okay, so instead of full join we're going to do left join and then they're going to explain what happened. There's a few less rows then last query and left join you use the students table was left table since it was on the left side of the join. Majors was on the right table. The left join gets all rows from the left table but only rows from the right table that are linked to from the left one. Looking at the data you can see that every student was returned but the majors without any students were not. Majors without any students were not returned. Okay. Join the same two tables with the right join this time. Right. Let's go right join. Great at time it grabbed all the majors yeah but only rows that were connected to the students. There's one more type you should know about. Join the two tables with an inner join. Ah, this is where they both of it I don't know we need to do in we're doing inner here we go but now it's a full table all right the inner join only returns students if they have a major and Majors I have a student in other words if you could only returned rows if they have value in the foreign key call that's the opposite table you should know a little about the four main types of joints now try using left join to show all the majors but only students that have a major uh to show all the majors okay whatever let's go left joined why are they making us do this again I don't know um are you kidding me they're making us do a different way aren't they come on man just give me the answer foreign majors yeah I know select all from Majors yeah I have to do Majors now burn it they're making up me do it in reverse screw you oh left join students none Majors major ID equals students dot major ID there we go excellent all the majors are there next use the appropriate join to show only students that are enrolled in a major okay and only Majors that have a student enrolled England okay that's inner join isn't that's inner join cuckoo try using right join all right we'll do great Dooku that showed all the students since it was the right table of the right joining use the appropriate join with the same two tables show all rows in both tables whether they have a value in the foreign key column or not there so that's just full join give me that full join cuckoo let's do some more experiments with join say you want to find a list of Majors that students are taking use the most efficient joins to join the two tables you need when we join the tables for now don't use any other conditions let's say you wanted to find a list of Majors that students are taking a list of Majors that the students are taking so we need to get Majors the students are taking would that not be uh interjoined inner join or actually well the most efficient would be a right join no left join deck oh my gosh why is there anything like lagging so bad okay I don't know what you want inner join you want an inner join must be those the other two didn't work okay cool good to get the list you don't need all the columns though enter the same command but just get the column you need uh what column was that I forgot major yeah major okay cool you also don't have want any duplicates use distinct to return the unique ones okay yeah select distinct major this thing cool database admin game design data science okay there's a list of Majors that students are taking yay next say you went to the list of Majors that students aren't taking use the most efficient join to join the two tables you need a list of a list of Majors that students aren't taking okay they aren't taking can I just um as they aren't taking only enjoying the tables for now don't use any other conditions all right so let's do a full join then for now let's see what that comes up with okay I don't know what they want help me choose select all from students right join Majors on sale it's just a right join how but it's students ah yeah see this video will take forever if I actually like try and figure this out on my own so that's why I'm being a piece of crap right now and just copying on students dot major ID equals majors dot major ID for the main one I I won't try and cheat it

That got you all the majors. You can see the ones that don't have any students. At aware condition to only see the majors without students, you need student ID. In this condition units where student students dot student ID um is no Arc to without students. Yeah. Oh, I thought that would work to only see the majors without students.

Foreign [Music] I know. Oh, just student ID, not student style. Student ID is that what they want? Okay, I can do that. Um, this thing's being a piece of crap right now. Um, queer students or student ID. Okay, it's just being a piece of crap all right now. You only have the rows you need. Only get the columns you need with it to see the list of Majors without students. Only get the columns you need with it to see the list of Majors without students. Okay, so just grab the major. Just grab the major. Select major. Hey, you're doing great. Thank you.

Next, use the most efficient join to join the tables you would need if you were asked to get the first name, last name, major, and GPA of students who are taking data science or have a GPA of 3.8 or greater. Only join the tables for now; don't use any other conditions. Oh God, okay. What do we need here? You get the first name, last name, major, and GPA of students who are taking data science. Okay, so first we need to select the first name, last name, uh, major ID and GPA. Um, maybe I can grab Majors as well. Yeah, I can describe major and not major ID. Yeah, we're going to do a join anyways. And then from I'm going to grab a students, probably, you know, students inner join. I'm gonna do an inner join. We're taking data science inner join Majors on students dot major ID equals Majors or major. Yeah, Majors dot major ID. And then we're taking data science where the major equals data science and they have a GPA which is greater than 3.8. And that wasn't so hard, was it? None of them. None of them. This is bullcrap. Um, what did I do wrong? I have no idea. You can see, but they say I need to do a right join. Huh. You want to use the left join with students as a lift table. Students left join Majors. Okay, thank you for that. I will promptly change what I did wrong. Left join. Never mind, that does not write either. Enter. Let's join Majors on students.hr.d SQL prompt. Yeah, I know. Oh, they don't want any conditions. Really? No conditions? Huh. No conditions, even though you said taking data science and a 3.8 or greater. Okay, okay. I see how it is. Let's do that. Are you happy? No, I didn't think you were going to be. Okay, help me out. Why aren't you helping me? I'm majorly confused. Why is this not work? Let's try it a bunch of times. Maybe it'll work eventually. We're taking data science or have a GPA of 3.8 or greater. Greater than or equal to you. No, darn it. Oh, only join the tables for now; don't use any other conditions. That's why. And it says select star. Gosh darn it. Why am I such a dumbo? You guys are probably screaming at me like, "Just you store. How can you not see it? Are you blind?" Yes, I am blind. Yeah, there we go. Enter the same command but use where. I swear, I swear, I swear. But then do the conditions this time. Yeah, is that it? Right the first time, but no, no. They want to do in steps. Hey, never mind. As a reminder to only get the students, send me the requirements. Yeah, for major is data science and GPAs greater than 3.8. Oh, I think I have to actually specify where this one is coming from, so it has to be a Majors major ID for majors.mager I mean. And then GPA has to be students dot GPA. I think I have to make that distinction. No, never mind. Help me out. Left join this time, really. Oh, we we are doing left join or or. Okay, what did I do? Data science or GPA. Okay, I'll get rid of the students and stuff, even though it doesn't change anything. Okay, great. Now you have narratives down on the rows you are looking for. Enter the same command but only get the columns you need. Yep, yep, yep, yep, yep, yep, yep. Therefore, we need name the first name, last name, stuff like that. First name, last name, GPA, major, GPA. Great. From there, you could put them in a specific order if you wanted or limit the results to a certain number, among other things. Lastly, use the most efficient join to join the tables you need. You would need if you were asked to get the first name and major for students whose first name or the major contains RI. Only join the tables for now; don't use any other conditions. So let's just do this join. And then we also um only join, don't do any other. So select all, then is that what you want? Select all. Let's do that. I need to use the most efficient join. I forgot bats to join you would need if you're asked to get the first name and major for students. Okay, I first. Okay, so full join. I know, probably inner. Want to interjoin like 95% of the time to use inner joins, but I guess not for this. Help me out, bro. Full join. Yeah, I do have to do a full join. Okay, great. Full drawing. Cuckoo. Add aware to the previous query so you only get the rows you need. The rosie wanted were the ones with first name or major containing RI. Where the major like percent RI percent apostrophe. Amazing. What? How's that not right? Hmm. First name or major. Okay. Where first name also like RI percent RI percent. There we go. Beautiful. Finally. You won't only want to display the first name and major columns. Okay, yeah. Only get first name and major. Up here we need the first name, first name and major. Yeah. How far are we? Almost done. 88. In your script at the command to print the sentence you're looking for. Okay, here we go. No cheating. No cheating on this one. So let's bring this down quick. Actually, yeah, let's just print it out. Let go dollar sign uh parentheses dollar sign psql and then more posturies or the clothes. Yeah, quotes. Select what do we need? We need a list of majors in alphabetical order, either no student is taking or has a student whose first name contains a case-insensitive Ma. All right, so we only need a list of Majors, so we'll just select a major in alphabetical order from Majors. Right? From Majors interjoin. Let's do our inner join first or yeah, let's try interjoin first. It's probably not going to be interjoin, but we'll try interjoin the majors dot. We know inner join the students students on these students dot major ID equals the majors dot major ID. And then that either no student is taking in alphabetical order. Okay, um, yeah, so we do that at the end. So first we have to do where where either no student is taking. So no student is taking. Okay, or has a student whose first name contains a case-insensitive. Okay, so let's do the first thing first. So first name contains, so like and that quote percent uh case-insensitive Ma. So we need a I like in there. Um, no student is taking. We have to do a group buy for that, hopefully not, because I really don't want to. Well, I might have to cheat on this one. Let's see what this gives us if anything. So it gave us these two right here. I don't think it's right, which is not um how's the first name. Okay, either no student is taking. So we also we need an order here somewhere, right? Now the student is taking. No student is taking. So where student is null. Yeah, okay. So full join where students is null. Yeah, let's try that. Um, we're students ID is null. Um, yeah, let's try that. England a full join. Let's do that full join. Okay, let's try running that. See what that gives us. Okay, so four of them. Is that what they want? No. Help me. Okay, we need these four: database admin, web develop. It is that is what I'm getting. Screw you. Foreign or first name I like quarterback. Oh, I forgot to order them. That's why. Order by order by the alphabetical order of Majors, so major. Order by Major. Now let's try it. Hey, let's go. I'm a genius. We're on the script again. Cuckoo. Almost done. In your script add a command to print a sentence like the others. Hopefully this is our last one. Let's grab this. Go that's e tons of times before already. That should be good. Let's go over a few more things before you figure out how to see the courses a student is taking. Start by doing a full join on your students and Majors table. All right, so we need to do this full join thing again. Now let's go back up here. Let's do this full join. Hey, I just found it by tabbing up. All right. If you look at the column names, it shows two major ID columns, one from the students table and one from the majors table. Major ID, major ID. Yep. If you were to try and query using major ID, you would get an error. You would need to specify what table. Yeah, with table.com. Enter the same join but only get the major D column from the students table. Foreign from the students table, right? Okay, students dot major ID. There we go. Earlier you used it. Has to rename columns. You can use it to rename tables or give them aliases as well. Here's an example. Select all from table as. Okay. Enter the same query you just enter, but rename Majors to M. Yep. So this is how we can shorten up our queries a bit. Uh, from students as M or as s. Um, I guess they want us to do something else going outside. Oh, darn it. But rename major stem. Oh, yeah, let's do that instead. Um, renate majors Majors to m that has M there we go. But select all. Enter the same query you just entered, but rename the majors table to m. Oh, and then I have to use M instead of yeah, so do m dot major ID here, and then it should be good. Yeah, there we go. This doesn't affect the output. It can just make some Curious easier to read. Enter the same query but rename the students table to S as well. All right, so we need the students to be s and as s. Ierra. There we go. Cool. There's a short keyword shortcut keyword using to join tables if the foreign key column has the same name in both tables. Here's an example. Select all from table one folder on table two using column. Oh, that's very cool. You didn't know that. Use this method to see all the columns in the students and Majors table. So I'm using any aliases. All right, so we don't want to use any aliases. Let's just do a little full select then. Select all from the students full join. Come on. Yeah, majors using major ID. And it shortens it up a bit. You know what? That two major D columns were turned into one with using. In order to find out what courses a student is taking, you will need to join all the tables together. Um, you can add a third table to a join like this. So select all from table one full joint table two using column full join Table Three using column. This example will join the first two tables into one, turning it into uh the left table for the second join. Use this method to join two tables from the previous query with the majors courses table. All right, so we also need two full join our Majors courses features courses using the major ID column of that table as well. Nature ID. I think right. Majors courses using major ID full join that's why this will join. There we go. Very cool. 108 rows. You may need to adjust the terminal size to align the outputs. What you're seeing is very is every unique combination of rows in a database. Students with a major are listed multiple times, one for each course included in the major. The majors without any students are there along with the courses for them. The students without major are included. They have no courses and are only listed once. You can join as many tables together as you want. Join the last table to the previous command to get the names of the courses with all this info. Join the last table to the previous command. Look at the names of all the courses with all this info. Uh, join the last table. You can join it. Okay, to get the names of the courses. Oh, oh, I need courses in there as well. I see. Okay, so I need to also full join courses courses using the course ID. Whoa. Awesome. Same amount of rows, but you get the course names now. In your script add the command to print the suggested info. Okay, cool. Let's let's do that. Um, let's see this Echo or sign parentheses psql. And then we need to select what do we need to select here? List of unique courses in Reverse alphabetical order. So we need to select distinct courses. Distinct versus of course in Reverse alphabet order that no student or Obi hilberton taking. Okay. And then like so we have to do a bunch of joins. Yeah. So from let's grab our students table first. Students enter or full join our majors. Uh, do we have to do majors courses? Right? Neat courses. Yeah, I only have to do the courses. Let's first do the courses. Majors or Majors courses. I think it's Majors courses. Majors courses using the major IDE page your ID and then full join our courses using the course ID that comes from the majors courses table. Yeah, I'm using. Oh, wait, I forgot to full join using. I forgot to do that right. From students full join Majors coursing using using full join courses. Oh my gosh, this is actually kind of hard to read. Full joint courses using the course ID. Okay, let's see what that gives us quick. Okay, gives us all these. And then that student or OB Hilbert is taking where note student where student ID for students ID is null and student first name and last name is not Opie Hilbert. Something like that. Something like that. And first name equals OB and last name equals Hilbert. Hilbert hilpert. Let's see what that gives us. Nothing that I expected. I think we have to do an or here maybe. You know, or let's try that. Okay, that did not help. I guess or did I, you know. Oh, and I have to do a reverse alphabetical order, right? Uh, for course, right? Okay, so order by the course and that's descending. Let's see. Let's try that. E in Reverse alphabetical. Yeah. Oh, let's go. Here we go. So this query select distinct course from students full joint Majors courses using major ID full join courses using course ID where student ID is null or first name because it'll be last name equals Hilbert order by course descending. So much fun. Let's go. All right. Run the script to see the courses described. I did. And there it is. Let's go. Last one. Attic command that prints list of courses in alphabetical order with one student enrolled. All right, let's do that. Thank you. Let's go down here. Let's do this Echo again. Oh my gosh, I want some more space. Give me some space. Let's paste this. Okay. And then I have to do this Echo here. All right, let's do this last one. Okay. And okay, that should be good. We need to select a list of courses in alphabetical order with only one student enrolled. Okay, so actually I think I can kind of copy this one actually. So let's do that. And instead of distinct we don't need distincts. We can just do course and it's alphabetical order, so we don't need the descending. All right. With only one student enrolled, so we need like a group by. Yeah, we need a group by saying in here, don't we? So let's do that. Let's get rid of that. Uh, Group by the student like Group by student. Yeah, student ID. Group by student ID having count of all equal to one. Maybe, maybe, maybe. Let's try it. Oh my gosh, I can't believe it. It actually worked. All right, so the last one select course from students will join Majors courses using major ID full joint courses using course ID Group by student ID having count calls one order by course. And that ends it off. And go for it. Never mind. Courses.course versus that course and ended up. Never mind. Um, calm Force must appear in the group by Clause. Ah, where's the goodbye? Group by student ID course. Let's try that. Okay, I don't know why is it not working. Oh me. Oh, I see it actually wasn't right. They just wanted me to put the ECHO line. I'm not sure. Let's get rid of student ID here. Just do cores seem like that worked. Network security. I know I have three of them. We need four. Oh shoot. Okay, I need some help here. You can do this. Give it another try, man. Really? I don't know. What do I need here? With only one student enrolled. Uh, the group by maybe that's not how you're supposed to do it. I don't know. I need help. I don't know how to get it either. Oh, okay. Never mind. Uh, using course ID. Okay, they're doing inner joins. Oh, I guess I should do inner joins. Try that. Inner join and inner joint here. Let's try that quick. Uh, same thing. Oh, actually not. It actually worked. Yeah, that is what we wanted. Cool. Let's go. This is the last step. You have done really well. Run the script one last time. Yippee. 100 complete. Congratulations on completing Learn SQL by building a student database part two. You've reached the end of the road. All right. There we go. That's all we have for today. We completed the part two of the student database. Uh, what do we have up next? Let's check quick. The next thing we have is the World Cup database, so stick around for that and I will see you next time. Make sure to subscribe and leave a comment. Bye.