Hey guys, in our previous video, we looked at selecting records using SQL statements that we wrote. In this video, we will be looking at how workbench generates some of these things for us. And also we'll be looking at more advanced queries in terms of custom columns and filters. So the first thing that we want to do when we want to see data from the table and expedite the process is just find the table over here in the schema listing, and then we just click this grid with a lightning bolt that is in the same row once the row is highlighted, and then that will actually generate for us a select star from statements which is a good place to start from, and we will get the queries that the results associated with that query. Now if you notice, instead of using the use statement like we would have done in our previous script This generated query actually says it uses dot notation to say, database dot table.

And that's just a standard thing that you'll see in most database management systems where it is seen database name, dot table name. So in essence, if we forgot to use school dB, and we had two school DVDs or two sets of databases with a table with a table called students, then what we could do is just say we want the students table from this specific database, and it will know that that is the context within which you should run that query. Alright, so I'm just going to comment that so it doesn't cause any errors. And that is the way that this generates it for us. So if you want to write it this way, then that's fine. And as we get into some more advanced spring, you'll see the value in writing it down.

Because then you may end up writing for cross databases. And this is a good way to know exactly which database. The table that is being referenced applies to know to explore some of the filtering exercises are requirements. I have included some activities here in the script file. And if we look at them, we see that we want to retrieve only records that meet a certain standard. And so we will have to amend our select query because I know the Select queries are scaring back every single thing that is in the table.

I also failed to mention that you can actually limit the rules so you can actually tell workbench that you don't want to limit or you want to limit to just 1000 or to 5000 so that when you click this button here, the subgrade with the lightning bolts, it will Actually limit according to what you want here. Now if we were to actually write that limit constraints on our query, then we would append it to the end of our table clause. So it's Select star from, and then we say database dot table, and then we would say limit and the number so I say two, then it would just give me the top two rows in the table. All right? If I say limit 10, then it will just give me the top 10. Okay, so that is the power of that limit keyword right there.

Now back to our task at hand, we want to retrieve only the students with the last name William, so I'm just going to remove the limit. And we take a look at our entire data set once again, and we see that we only have one students named Williams but Williams is a pretty prominence name. So you would probably have multiple students with the name Williams and for whatever reason, the Dean of discipline is asking you to give him all of the persons with the last name Williams, who are currently in the school. So no, you need to modify your SELECT statement to add a filter to this request for data. So to do this, what you would write is select and then remember that star really means all the columns or all the data points. So if it is that you want everything about the student, they can see star or you can only select the colors that you want.

So I'm just going to say last name on this and comma first, underscore name. And I only want the first and last name from the students table. So I could just write students but I didn't have the use statement up top so I just conform to the school DB dot notation syntax. And I'm going to say, dot students. And then a new keyword that we will look at is we're so we're seeing select these columns from this table in this database, where and then after Where is a condition or condition based on our requirements would be that the last name must be Williams. So I can see where the column last name has the value Williams and the easiest way to say has value Williams would be equals, and then I type whatever it should be equal to, and it should be equal to Williams.

Alright, so that is a new parts to our selection statement. So essentially, we see we're once again and then we specify our column And then what we expect so he can have different operators Siri can have equals, he can have less than we can have less than or greater than or greater than equals less than n equals, we can also have not equal to. All right. So, of course, the less than, greater than, and so on would apply to numbers as opposed to words. But then when we want to match a specific word, we could just use the column equals, and then some static text inside of quotation marks, which will be used to evaluate every last name and only bring back the records that meet this condition. So we can just go ahead and execute this as selected and executed by itself.

And there we go. So we're only bringing about the first name and last name of all students with the last name was And if we look at the entire data sets, then we can determine or deduce that that is actually accurate. All right. So what if I wanted the students with the last name test? So we see that we have one with test, but we have many variations of tests. And I just want to ask you to ask yourself right now, how many rules would come back if I specified that the last name should be equal to test.

So if I execute this, I only get back one rule. Now you may have said more than one rules, or you may have said as many rules as there is a word test in the last name, and you would have been wrong because test one is not the same as test this is saying give me We're all of them are equal to test. So any variation of the word test will not be brought back. So that brings us to our next activity. We're We're going to retrieve only students with the word test in their last name. Now, we just attempted something like that.

I'm just going to copy this query and paste it down here, and we'll just modify it accordingly. No, let's take a look at the entire data set once again, and we see that all of these from one through nine, IDs one through nine have the word test in their name. So that means whatever query we write for this requirement should bring back IDs one through nine, not bringing back 20 through 22. So then we need to modify or conditioned capture exactly what our requirement is. And to do that, we will use our keyword called like, no like allows us to specify what we call a wild card, and much a pattern as opposed to an A how and accept much. So in more simple terms, when we say equal, it will look for an exact match.

So let me just put back Williams. If the name, if the column doesn't exactly match this value, then this condition fails. So that is what the equal sign does. However, we want a partial match, we want anything that has the word test in it. And so we need to change from using any sign to using the keyword like and then when we have the keyword like inside of the string, we can specify what we call a wild card, which is represented by a modulus or percentage sign. All right.

So this is how modulus works. It will see give me anything that is before this or anything that is after this. And I'm going to start small before we actually fulfill this requirement. So let's say I wanted a name with the letter E, any record with the letter E in it, that means I should bring back every test. And also this rule 20 with read, which also has an E. So I can see, give me these columns from this table where this column, which is last name has something like an E. And to do that I'm going to say modulus, which means I don't care what comes before an E. and I were to say modular, so again, because I don't care what comes after the E. So that's what the model is really does. It says I don't really care what comes before this pattern.

And I don't really care what comes after this pattern, as long as this pattern is met. In this case, the pattern is just one letter, which is he. So if I execute this, then it will sift through and find out The last names in it. And that's why read came back. So let's try that with the letter I, if I said give me about all the last names of the letter I in it, then only Williams and read combat because there's an iron read and there's there's at least one I mean, William. So let's move away from letters What if I wanted anything that had the pattern l in it so any column that has two L's in it, I don't care what comes before, ELLs, I don't care what comes after the L so give me anything with L's in it.

And of course, you should only get back all small, I forgot that smallest there. So we're getting back small because small has something before the L but it has an L and just the same for Williams. It doesn't care what's in front of the LR after it as long as there's an L present. In the same way that we can actually match patterns inside of the string. We can actually use this to specify if the word starts or ends with a letter. So let's see, I wanted any name, last name that ends with the letter.

It's alright, so I will just say models, it's so that means I don't care what comes before. This give me anything that ends with an S. All right. And then if I execute that, I should only get back, Williams. And then if I see I want anything that begins with an S, I can see s and then Model S. And I hope you notice by know that it is not case sensitive, because I'm searching using common letters, and actually getting back values with capital letters that meet their criteria. So the evening executing the query and searching with a static string is not very case sensitive in my SQL, but when we're selecting here, and we just want to see any name that starts With s, then we see that letter or that sequence, if I wanted s m, or SNP B, whatever it is, what this is saying is that give me any sequence that matches this, regardless of what comes afterwards.

All right, so I want any pattern, any last name that has an S, regardless of what comes after the s, ri, but we're not putting the wildcard before because we want it to start with an S. And when I do that, we get back small. If I did that with t, then we get back all of the test student records in the database. All right. So actually, this would have kind of fulfilled this requirement to retrieve all the students with the word test, but then this would not still not work because then if I had somebody with the last name, field or something like that, with Tea, Thomas pharmacy, then that person would come back and that would void the results. So once again, I can wrap the expression inside of wildcards. And I can just write out the whole world, the whole word test to see, I don't care what comes before the word test.

And I don't care what ops comes after the word test. I just want back all the records with the word test in it. So I do that and then execute and the results it stays fairly the same. But if there was some other record coming back before, no, it would be sifted out. And that is essentially how wildcards work. Now, our next requirement tells us that we want about the full name and the enrollment date for all the students.

Now you'd be wondering, why am I specifying full name, because I'm already bringing back the full name here. But then notice that last name, and first Those are two entirely different columns, we want back one column with just the full name. Some, we'll just start off with the basic Select star from and just copy this code. And when I get these queries, I like to do what I call a process of elimination, where I start off with all of the data, all of the potential columns, and then one by one I sift through and take out what I don't need. So I know I need the last underscore name and first underscore name to make full name. And I also want the enrollment.

It's alright. And so when I execute this, I'm getting about the three columns that I want. But then once again, I'm not getting back a full name here, and I really want to rename those columns. Now to accomplish sprinting up first Name and Last Name in one column, we have to employ what we call one of the string manipulation functions or knee to string functions of my SQL. And that comes in the form of Han cat concat is short for concatenate, which is the process by which we join two words are blocks of text together in general programming terms. so in this situation, what I'm going to do is write the expression on contracts and they're auto completed for me, but then inside of the parentheses, I need to specify the strings to be concatenated.

So I can see I want to concatenate first name Alright, and then I can see last name. Alright, and then as you as we discussed before, one size, specify the column, I can always rename it. So I can just say full name. So that we know that this is the full name. And then when I select this, and this is not going to come out very cleanly, so we'll just refine it as we go along. So when I execute this, then you see that full name is being printed, but then you look at it and you see that it's chopped up.

Alright, so we need a space in between the Beverly and the small and the student is anticipated. So with the Concord, you actually have to specify every single space, in addition to the values, so because we have this column here, and this column here, it's just crunching them together so we can just crunch another space in between. So I can see space, open quotation mark, spacebar, and then comma. So what that's going to do is join the first name to us face to the last name, and then our end result will be A full name that is far more user friendly user readable students space tests traverse space Williams, and that is how our data would look. And once again, our column is named appropriately so we can just go ahead and rename enrollment date to ensure that everything looks uniform. The next two activities are based on the courses table.

So by now you should have inserted some records into the courses table. And, you know, we'll be tasked with selecting those records with some filters. Alright, so you can always just put in some test data that would kind of help you to fulfill these requirements set up here for you and leave you two hints as to why You can use to fulfill these. So when you're writing the filter, you'd want to see where credits column. And I'm not, I'm being very vague here, because I want you to actually give that practice is greater than three. And in the same way, where credits column is less than or equal to three, I'm sorry, this would be two, based on the question being asked, right?

So it's where it's greater than two, as outlined here, and where it is less than or equal to three. So you can go ahead and complete that I will upload this file with the solution, but I implore you to practice it on your own.