Hey guys, in this video we will be looking at some advanced querying techniques. So yes, we went through some select in previous videos and we went through some filtering and other more advanced topics. But then when it comes to having multiple tables that share data across the tables, we need to employ certain other SQL commands and functions in order in order to facilitate that kind of query. So I'm just going to go ahead and create a new query window. And we can select our database to be used, which is school. And of course, I always say use school above here in the script so that we can be absolutely sure, even if we move it to another computer that we're using the right database.

Alright, so let's start off by running a few selects. So select star from teacher and we have three to four tables. So I'm just going to I'll just Ctrl C and V and that will We'll actually duplicate this line. And I'm just going to run these sorry, not school courses, and students. Alright, so if I have these three commands and I say execute, it will actually execute all three and give us different results for each different result pins. And remember that you can always just highlight the one that you want to execute and it will give you just that.

Now I want to run a select star from my enrollments table. And we saw how messy and confusing that looked do that oh, sorry, I left over from that's embarrassing. So excuse me, and then we see that we have all of these numbers and literally they mean nothing to us sitting down and looking at them because all we have to do now is go back and we have to try and find who is teacher to. And then we go back and find out which student three and that backing and for thing is Really just tedious and absolutely not necessary. Because in SQL, we have a construct called joints. So we can do joins in our queries.

Alright, so this script file will be included in the resources for this video. And I'm going to show you how you can go about doing a join, join pretty much says, I want to literally join the data from two different tables based on the one thing that they have in common. And based on the fact that we just went through relationships and foreign keys and primary keys and those constraints, we can always assume that what the two tables have in common would be the value in the primary key and the value in the foreign key. All right, so the value in the foreign key column for enrollments is what is something that it has in common with the teacher table, right or in the student ID with the student table. Alright, so the syntax for Join is use a select so the Select part stays the same star from and you can choose your primary table enrollments since that's the real table, we want to see the details of all of these enrollments, we want to see which teacher is teaching this course.

So who is teacher to want to see the details of teacher two, on all the courses that teacher number two is teaching. So we select star from enrollments. And then we say, inner join. And you have multiple joins, you see inner, you'll see left, you see right, but for now, let's just do enter. And you'll say which table is is it that you want to join the data onto? Which table is it that you want to look for the details in?

So we want the details of the teacher? Id two. So obviously, the details of anything from teacher ID would be in our teacher table. So he's a teacher, and then we want to see on So join us On teacher table on the condition that which column from teacher column is ID, and remember that what they have in common is the value in the ID column of the teacher table. And the teacher ID column of enrollments table. So inner join this new table teacher on the condition that ID from teacher table.

And you can always see something like teacher dots just to make sure you're dealing with the right one in case both of them have the same name. All right, so you can see a teacher dots and then the IntelliSense is asking which column is it, and we'd already wrote ID is equal to and I can just say enrollments, dots, and I will say teacher ID. And then what this will do is say, give me everything from enrollments and also join on the details from the teacher table, where the ID matches that so it will bring back the details. So teacher to where teacher two matches the ID and teacher table will bring back all of the details for teacher three, where those two values match, etc. So that is why if we had 10 in the room and stabilize the teacher ID, it would cause a problem because we don't have those would never match.

Alright, so when I run this query, now we see and I said select star. Now we see that the details are coming back. So for teacher ID two, once it's two in the enrollments table, then it's bringing back the corresponding record for to where it's three. And then Romans is bringing about the corresponding records for three. And the same goes on and on. So teacher one, teacher one so we can know see which teacher is actually teaching these courses.

But we have more details that we need to see because we don't know the student IDs. We don't know the courses. So I can enter join us As many tables as I need to, so I can now go ahead and enter join. And let's say we want the student details alongside the teacher details. And then I can see the student ID. So the student ID is coming from the students table.

And I'm going to enter join that on the condition that students, so that's another thing you see how teacher ended up students, and then of course is, so it's good to just keep those things uniform. So that's, that's my design flaw right there. And then students.id is equal to and romans.io. And in this case, I want students student ID, and then I can execute this again. And what will happen is that you'll see know that the teachers details are still there. And then to the right, it's appending columns for the students.

So where their students ID three, were seeing the details for the students. Alright, I'm going to do it one more time. And at this point, it's probably less or more confusing, because at least we're seeing the details. No. But then we're seeing all of these irrelevant information because we don't need to see the IDs. And for institution, that is, we probably don't want to see the date join, and the date of birth of the students.

I know it's stuck on the course. And we see more possibly irrelevant data. So we'll go ahead and refine that as we go along. So I'm just going to, you know, join courses on and what do courses, what does the courses table have in common with our enrollments course ID, though, remember, I did say that it could get confusing when you have the same name across the table. so in this situation, it would be extremely good to see a courses dot course ID, so we know exactly which table this course ID is coming from versus which table the other one is coming from enrollments, dots, and then we can go ahead and do this and we use See, we're getting back all of the courses and the number of credits on the course score. So we're getting back all the details off everything.

So that's why I'm saying that we don't need to repeat the details in more than one tables, right? Because if we have all the details, or at least repetitions one place, we can always cross reference it with another table. And in a setting where you're selecting, then repetition is quite fine. What you don't want is a repetition when you're storing. All right. So you don't want to have john McDonald five different places in five different tables, you just have his details one place, and you make reference to him by his ID.

And so when you need to find more details on him, you just enter join that table with his details based on that foreign key primary key relationship that you would have set up so we can start refining this a bit. So I'm going to leave this query and I'm going to right Another one, where we're going to select the specific columns that we actually want for our report. Now, I did start off by saying we only wanted the courses being taught by john Gibson. So I want to bring about john McDonnell, sorry. So we only want to bring back where the teacher ID is two, we want to bring about the first and last name of the lecture, as well as the students first and last name, and the title of the course being talks. Alright, so that's all we want.

So we can actually go ahead, and I'll just copy these Inner Joins since I already have them here. But it would be good if you just re typed them for practice purposes. So I'm going to actually just go ahead and select that specific columns. And then this is where this query and kind of becomes more tedious because then you'll see that first name is present in two different tables. So we need to be very specific Which first name it is that we want. We're Alright.

So if we want to first name, last name, and then first name, last name, again, whose first name is whose last name is it the students or the teachers, those kind of things. So what is a common practice in database design is in ss, where when you have a table, you can actually just press space. So in this inner join line, here, I have teacher, I'm just going to press space. And I'm going to say something like T. All right, and students I'm going to call an S, and courses I'm going to call it C. And what this does is wherever we would have said teacher dot some column, we can actually just say, alias dot, that column, in this column, in this dot that column. So this makes writing the query much easier when when we when it comes to this situation, so we don't have to write Oh teacher dots first name, and then write student dot first name, we can just see t dots first name.

And sometimes IntelliSense may not necessarily chip in for this part. So I'm just going to run the first square with this. So select T dot first name from, and I'm just going to say enrollments. And even this can get them in this right here, I can just see E. Alright, so everywhere I had the word enrollments, I can replace it with E. And so this query just shrunk in size. Look at that. Look at the difference there.

Alright. So once you have a table, you can just press space and put on that alias. And then I'm just going to run this query quickly. So when I bring this back, I should only be seeing john or whatever is in the first name column, often lectures coming back, and there we go. So first name is what is coming back. And only the teacher first names because we did specify this alias and we're reusing it here.

I did say, let me just rerun this query. I did see that we would be bringing back the first and last name of The teacher. And then that no brings up another problem because if I'm only saying first name, last name, first name, last name, I still don't know who is so so I have to put an alias on the column itself so that it prints back. So teacher first name, and I'm just going to do this, I'm going to break the line and just do it in a cascading form. So teacher first name, comma, this will be the next one, Li Li, my core is being very readable. So it's good when you have readable queries.

And I can just duplicate this since a lot of this is going to repeat last name. So let's see the IntelliSense is chipping in no because it does realize that okay, T is the teacher table set as T right. So remember that as keyword we could have used as so last name, and they'll say last name here. And the reason for the square brackets is that because we have spaces in between what we want as a column headers SQL or any SQL, just take it literally as it is. Without that it would try to create certain keywords. Also, if someone saying don't use any keywords, just do this literally as we have it typed.

So have the teachers first name and last name. And we also want the students first name and last name. So as I said, we can just duplicate these lines because it will repeat. And we just refined so students student, this will be last name. And then finally, and remember we use commas for every column separation, a bunch of the last column. And finally, we want the course title.

So of course, would have the aliases, I can just see c dot title, and then we can give it a custom header Of course, title. And then this is a this is essentially how you go about running reports. You know you're working in an organization they ask you to pull a report directly from the database. This is how you would go about writing these squares. When you have multiple tables with multiple data across the place, you know, you just find out what they have in common, write up your inner joints, and then select the data that is absolutely necessary to the report that the person asked for. Because if your superiors asked for this report, you really don't need to show them the IDs and the, you know, the course code, maybe you want the course code.

So let's, let's include coursework. So I'll just duplicate this, add that comma. And I'll say C course code. And this is course for all right. And then also when you're when you're going to be render reports, you know, on missing names, you want something human readable. I think we went through this when we went through our select queries, you want to make sure that the person can read.

It's, you know, it looks neat. So we just try to keep some uniformity and some quality, some standard because your supervisor or whoever is reading the report won't see a database not all the time, so they won't know enrollment data is here as one word. And last, but not least when they get it in a dump in, you know, in a presentable form, you want it to look good. And so we run this query and we see teacher first name, teacher, last name, student. So we can clearly distinguish who is who we see that john is teaching Judy, or I have an error here because of Judy appearing in last name and first name, so I can discard that. And didn't change that.

All right. So we can just re execute this. And that's why it's good to check your work. So we have john McDonald, teaching Judy McIntosh, and all of those sports titles. And then if we only wanted to see John's students, which is the way I think we started off, then we can add a where clause so went through the where clause when we looked at filtering on our we're queries, and the condition would be, it could be that the lecturer his first name is Maybe, john, but I wouldn't do it that way. I would prefer to filter on the ID.

So I would say ID is two. Alright. So I mean, maybe a supervisor came to you and said, Hey, find all the students that john is teaching. You know, if you said, john, maybe I have more than one John's in the database, so he should maybe in the conversation, you know, it's john McDonald that is being spoken off. But if you just try to do it on that, then you may have more than one john McDonald's in your database. So the ID is the most unique thing to separate the tool, john McDonnell is that maybe it is, so I would just find what is the ID of the specific person being asked for, and it is true in this case, so I'll just say we're t.id using a my alias is equal to two and so this will actually go ahead and filter everything thing Oh, it's just to show me, john McDonald's and all of his students.

So you see that Jody is taking HTML, SQL, PHP with him. petroi is doing HTML and PHP. And Trisha is doing SQL and Petra is also doing SQL. And those are the course codes as you go along. So of course, there there are different ways to run this kind of reports, I've got all of these repetitions and refine it. But at the end of the day, you want to ensure that you have a good understanding of how you go about doing your Inner Joins when you have relationships, or you bring back data from different sources.

And finally, how you go about filtering know the filter in the where clause should, most often than more often than not be last. All right, so we could have ordered by so you do an order by after all of this and let's say we want to order by a course title. So we say C dot and we can say title. So on the order by The title, and that will come after you we're and then here we have, okay, john is teaching Troy and God in HTML is teaching those, those are his PhD students, and these are his SQL students. So based on the way or the data, you can get different things from it, because you know, it's done all the time. But then the students seem are seemingly random and all over the place, but at least we can now see why is HTML students?

Yes, to course, PHP students he has to and he has three SQL students. And so once again, based on how you write your query, you can get different data from it and you want it to be nice and clean and ready for export. So if we want to export this, we can always just right click, and we can say save results as and then we choose the data type that we're more comfortable with. So we can leave it as CSV because CSV can open in Excel, and then we just say student enrollment report. And then we save that and then we can send that off to our supervisors. Of course the title didn't give the full story because it's really students and john and john students and enrollments read and truly based on the data we have here, so this script file will be included in the resources for this video stick are owned.

See you next time.