Is In this video, we will start looking at how we can go about extracting data from our tables. In the previous video, we were inserting records and we'll be inserted up to probably about 10 or so records. And now we want to actually start pulling them back and manipulating the data. So we have our scripts page open. And we know that the first thing that we do is to specify which database it is that we're about to run the script against. I want to know how to show you that you have the use keyword where you say use and then you give it the database name.

And since we're using the editor, I'm going to show you that this drop down up here that says monster, and it will say monster because monster, which is a system database, is the default database that is going to be selected for any script. We can actually use this drop down box and we will see all the databases listed there. So if we click school, then it changes the context of our query without us having to write us school. So you can use either one. Personally, I prefer to write use school because if it goes on another machine, and then by default, it goes to the master, then it knows when the script executes, it uses school, as opposed to leaving it up to memory to change this when you're about to execute the script, and then you may end up executing a possibly destructive script against the wrong database.

So be mindful of that. But of course, for this course, I want you to be very versatile. So you can use either method somewhere just stick to the evil I know and use school and to get the IntelliSense you can press control and space, but you probably know that already since you've gone through all the keyboard shortcuts, and you will see that it actually gives you all this additions and helps you to complete certain statements faster. No today will be Looking at the SELECT statements, so we've gone through create, we've gone through insert into and know we're looking at select and select is doing exactly what the name suggests it's selecting the data from a table. And that is exactly what the query line looks like, select. And then what data do you want?

Do you want the first name? Do you want the last name? Or do you want everything in the table. So if you want specific columns, you can write the modes. In this situation, we want all columns, so they give us a shortcut for all in the form of an asterisk, which allows us to see select all so the asterisk is synonymous with all and that means all columns from and then we specify the table and we want students that is how we're going to give all of the student records in our students table after execution. In the query, we see here that our results have come back.

And everything is, as we saw in our previous video. However, in this view, we're not able to click in and edit any of these records. So the Select is really just to read the beta. So when you run a select command, you're just reading the data for a presentation made before reports, it's just giving you in a grid format. And you can actually change that the options are here to change the results the file or to change the results and things so I can just pick that and then re execute the query and then just show you the difference in the results. So you actually get it in text form instead of a table format, and then file would actually export it to an RPT file, so we can just leave it on grid as the most that's the most common and that's the default one and we execute and we see it in grid format.

Once again, it is not editable. Now let's start looking at some variations. of this SELECT statements. So I'm just going to remove this one. And then what if I wanted all the students, I only wanted first names. And last names, I didn't want to see the ID that's auto generated, I didn't want to see their date of birth or the enrollment date.

I just wanted to see first name and last name. So I would still use select. And in this occasion, I don't want to see all the columns, I just want two columns. So I want first name. And then i comma separates all of the columns that I'm specifying. So first name, comma, last name, and then we see from students, and then all of those errors go away because now it knows that first name and last name from from students to execute once again, and we see that our results set not only reflects those two columns, first name, and last name, though, if we're actually pulling these data points for reports, and I'm just Going to add enrollment beats to this.

So the enrollment dates and we want to see all of the students and their enrollment beats. But then we want to export this to excel. And we want sensible looking column names. First Name is not, you know, reader friendly. It has a common F and come on and it's one word first name is not one word last name is not one word. But because we're discouraged from using spaces.

In creating our column names, we didn't write first space name, but when we print it out in our grid, we actually want the column names to look more presentable. So what we can do is say, as and then we can write the string that we would like to be printed for the column name. You actually can write it without the as and using the square bracket, just see last name, so I'm doing it both ways so you can see all of your options. So the as keyword is actually optional. You can Put it here. All right, and then enrollment dates like we discussed could look kind of convoluted and confusing.

So I'm just going to bring that to the next line. And I'll say and roll meant eight. And then we execute this. And then if we look at the headings will see first space name, last space name, and enrollment date all looking more presentable and human readable. Once again, we can always break our lines because SQL will know where statement starts on end. So if you need to do this, just to see everything more clearly, you have a keyword column and it's alias.

This is what we'll call an alias comma column alias called column alias. And then that's one of the advantages of that square bracket because if it is a case where you inadvertently created a column with a space in the name, then you could actually use the square bracket to still operate, but then that's not good practice. I don't like doing it and I'm not recommending it. So just treat your your column names like this either use one word like this in all lowercase, use your underscore to separate the names, the words or camelcase them. But then ultimately for presentation, you will want to use the aliases so that your columns come out looking better. So let's say you have to run this report your administrators saying hey, I need this report, you print it, you put on all the aliases, and then you need it in Excel, you can actually just right click here in the corner.

So by clicking in the corner, it actually selects all the data and then you can just right click, and you can copy and it will copy it in tabular format and you can paste it in Excel or you can actually see the results as and the most common thing is CSV, which can be opened in Excel anyway. So that's how you would go about it. exporting an instruction from the database to a file for reporting purposes. So let's try another thing. And you can actually run multiple selects in one script, what it will do is separates the results. So I want to say select star from courses.

I didn't enter any courses. Not sure if you did, but when I execute this, I'm expecting to see something saying there's no record in the database. So you see here, when if you and you have more multiple selects, you will see the different sections for each grid results. So we see up here, that's our first select. And then down here, you see the results from our second selects, which we have nothing and also take note of the fact that all of the column names are coming back as they were created. So by using this star, you're you're not afforded the opportunity to set some aliases, so you probably want to spill over Your columns and set is for when you're going to be exporting the data for another human being to read.

So when we get back we'll start looking at more advanced extraction techniques and how we can go about filtering and selecting only some records and excluding others.