Hey guys, in this video we will be looking at more advanced SELECT statements and we will start looking at how our editor helps us with creating these statements. So firstly, I am going to instead of creating a script to extract the records and read records from our school database and students table, I'm going to use the editors function by drilling down into the database into the tables and finding the table that I would like to select rows from and then I'm going to right click and go to Select top 1000 rules. Once that is done, it will actually generate a select statement and bring back all of the results accordingly. Now let us take some time and dissect the select statement that is generated for us. We see here we know our keywords select and from now we know that in between the selects and the Fromme, we really have a list of columns then we Have an additional keyword being introduced here in the form of top.

And what this really does is to say give me the first X number of records. So the default option gives us 1000. Of course, we only have 11 Records in our database. So 1000 is more than sufficient to get back all the records in our database. However, if we had a database of 10,000 Records, then this would really just give us the first 1000 Records. So if I wanted only five records, then I would change this value to five and say, give me the top five.

And there we go. Another thing I want to point out is the fully qualified name for our table. Now, if we dissect this slide, we see that we have the database name, dots, the context or schema name, which if it's in default settings would be DB O, and another dot, and then the table object. So this is actually the fully qualified table name, which means that if this database script is not within the context of the specific database, you can actually just write all this entire line here. And it will know to contextualize this table to this database. So I'm going to change school, up top here to school v2.

And then if I execute this again, recall that we have no data in our school v two students table. So I'm just going to quickly bring up that table and show you that that has no records. And we can see here that we would have school v2 dot students dot dbo dot students. So if I run this query, even though I have contextualized this scripts to school underscore v two. If I execute this query right now, it will know that I want the students table that is found in the school database. And that is the advantage of using that fully qualified database table name.

So this would actually allow us to be able to query different tables from different databases all in one script file. Now, let's expand our scope a bit and actually start looking at filtering. Now there are times when you want only certain records, I'm just going to remove this constraint and execute for all columns once again, and do recall that if you wanted all columns without having to type them out, you could replace all of that with Star over the auto generated code gave us every column. So if we wanted to filter and find let's see, only the students whose last name is Williams are we wanted to see all of the Williams is in our database. That is a form of filtering because now we're narrowing down the results at a specific what we call condition. So SQL gives us a keyword in the form of we're, and then it allows us to specify a condition afterwards, a condition could be like a column being equal to a certain value, or being greater than less than or looking like it.

Those are all conditions. so in this situation, once again, the scenario is that we want only the students whose last names are Williams, we see we only have one, so we should only expect one record to come back. So I'm just going to read a new query down here and with a comment this one out, so I can just highlight all of this and use this button up here or once again, hold on key control and press key. And see. So once again, our objective is to retrieve only the students with the last name Williams. So I'm going to add a new keyword where and then I go about adding the condition, the condition is hinged on the column last name, so I say last name, and then my condition stipulates that the last name column should be equal to a specific value.

And I'm just going to go ahead and produce this value here. So because we're comparing a string column or an N var char column, it's a word, we have to make sure that we're comparing it with also a word our string. So if I go ahead and execute this, then we will see that we have filtered out all of the data to only retrieve the students with the last name Williams. In this case, we only have one, but then let's try a more popular one. So we had a bunch of test students and I want it Any student with the last name, student, someone to execute, we only got about one. Now that's kind of strange, but it is very accurate.

Because if we once again, and I'm just going to uncomment this. And by highlighting this and clicking execute, I can specify that I only want to run this query. And I see that I had quite a few students with the last name students, except only one had students, everybody else had a number attached to the word students. Which then brings us to another type of filtering, where we can filter on what we call a wild card. A wild card is essentially posts looking for a portion of text that meets our requirements and bringing back anything that meets that pattern. So I'm just going to go down here and create another query.

So in this scenario, we want all the students With the word students in their last name, which means we need to say where last name once again, the column that's in contention for the condition what then instead of seeing equal we're going to see like, though like is grayed out, but it's also a key word after our like keyword, we're going to employ the use of what we call a wild card. Now our wildcard symbol is in the form of a modulus or percentage sign. And this allows us to actually specify corrector sequence that we are looking for in any block of text, which means if I want every last name that has the letter I in it, I can see I so that's open quotation mark percentage sign or modulus I and then modulus. And then this is really going to say I don't care what comes before.

I don't care what comes after, as well. As an AI is somewhere in a blob of text that is found in the last name column, then I should bring it back. And we can identify this by looking at the results that that at least three records should be returned. So let's just test that theory. And there we go. We got Macintosh, Williamson, and Williams.

So it just looked to see it didn't care if MC came before the eye. And you can notice that it is not case sensitive, as this is a common eye that I search for. And this is a capital I in the block of text. And it really didn't care what came after the AI as long as the block of text contained and I On the flip side, you can also say you want anything that begins with an AI, meaning it's going to see I am looking for an AI and I don't care what comes afterwards. Now based on the results that I don't think we'll get back any good We don't have any names that begin with AI. And in the same vein, we can actually see we want anything ending in AI by putting the wildcard character before the letter.

So I can say wildcard, but the last character should be an AI. And then we also should not get any results here as you don't have anything ending in I know this is not limited to a letter, we can actually once again, look for any character sequence. So our objective, according to this comment above here is to look for any name that contains the word students. So if we write wildcard, we don't care what mi come before this character sequence which spells the word students and we don't care what comes after it's. So if I execute this, then we should get back every students with the last name containing the word students. If I look for the car to sequence that spells the word Williams, then I should get back at least two results because we have one student with Williams, and one with Williamson.

And that is how you go about using that wildcard character. Now our next task involves us retrieving the full names of the students in our database and their respective enrollment dates. And I already drafted up the query, where we said select specify the columns that we wanted from and the table that we want them from. We discussed in a previous video, how we can go about making our column names a bit more presentable. What we're going to be discussing no is actually creating our own columns to represent our own data. Now, we didn't say full names.

Yes, the first name and the last name comprise a full name, but then they're still in two entirely different columns. I want one column that stores the full name. So I'm going to go about creating a column. And I do so I can actually, no SQL actually allows us to input any value that we want to be coming back with each record. So for instance, if I wanted to money whether print the number one, or I wanted to print the words, hello, class, or rather Hello students, as long as i comma separate, it's a one, so the last column, then I can actually do this. And then you see that we have the first name, the last name, random character that I put in just ones with no column name.

And this text that says Hello students, no column name and our enrollment dates. We discussed that if we wanted to give the column a specific name, we could just say as or just right on with square brackets, the name, so random number, and then I can execute this again. And we see that no, we actually get that column with that column name, random number. So this can actually be useful in certain situations. But based on our objective for this activity, we want a column that is storing full name of the students. So I'm going to what we call concatenate the string or join the strings together, or join the values in these two columns together to give us a full name.

So I can actually say something like first name, and then in SQL, we use the plus sign to say this columns value clause, that columns value. And because it's in between two commas, SQL is actually going to see whatever comes by here as one value, or one column for the results if so I'm just going to execute that. And we can see we get first name, last name and our custom column that we just put in that we did not give a name so no column name But then if we look at the values that are coming back, we see that we don't have any space, it's still it looks more chemical cased than than human readable. So we actually need to put in manually the space that we would expect. So if I say first name, and then concatenate an empty string, so I just opened a quotation mark, press the space bar, and close the quotation mark.

And then I'm concatenating. Once again, the last name, then we will see something that looks more reader friendly. So we have the first name to the left and the last name to the right. And once again, we still have no column name, so we can just add the column name, full name. And then because we have the full name, we can actually do away with the first name column and the last name column. So now we will have the full name and I'm going to give enrollment dates Pretty name, so enrollment dates and full name and then we execute.

And here we have full name and enrollment date. And you can always become as descriptive as you want. So you can see a student full name so that any reader looking at this report can know exactly who is listed to the left and what the dates are relating to them to the right is. So I'm just showing you how you can go about using certain SELECT statements to customize how you present your data. Okay, so up until now I have been focusing on the students now let us run some queries with our courses table. I didn't create any video creating content in the courses table.

I did however, give you a script that populates some records in your courses table and I hope that's in your experimentation you would have populated your courses table with some data so you have enough to play around with for these few exercises. Now, up until now, we have been focusing On our students table, I hope you would have populated your courses table foil you are experimenting. Either way, a strip file is attached to this video, which gives you some data to populate your courses table with so that at least these activities you can go through with some test data. So, we've been focusing on manipulating strings and n var char values. And now I want to just look at how we can go about filtering on number values. With number of values, we actually can carry out a numerical comparison was greater than less than we can look for equality.

And these two examples will bring us through some of those. So we want all the courses in our courses table with the number of credits being greater than two. And if you take a peek at the design of our courses table You'll recall that we are tied to number of credits which is an integer or numerical value and core score. So everything except number of credits, and the ID are invar chars. So we'll just quickly say select, and we want star from, and we have to fully qualify our table. And then we see where number of credits, which is our column is greater than two.

So I want all of the courses where the number of credits is greater than two. So if I execute this, I see that I get about four courses. And they all have values that are greater than two, I could easily have said equals two to find out if there is even a two credit course and if there were multiple, then we'll get back multiple. So I know that multiple three credit courses so I can just change it to three and execute Now we see we have all of the three credit courses. In contrast, I am going to select and I just copied this query down here. And I'm going to just modify it to say select courses with the number of credits three, and let's so we want anything with a number of credits, maximum three, but anything less than three is acceptable.

So what we did here was to just say less than or equal to three, the number of credits should be less than or equal to three. And then if we execute this, we should see our three credit courses as well as our two credit course. And if we had a one credit course it would also show up but nothing above three is appearing.