SELECT FROM

Video Transcription

Alright, so now that we have reviewed some of the basics, let’s go back to writing some actual queries. So we’ve already ran some basic select queries before, but we didn’t really break them down into into what each part actually contains. So we’ll start out first by looking at a select, but without the from clause. So the from clause is actually optional. But the Select is required. So this is the SQL syntax here. And I will show more things like this as we add more statements and elements to our SQL query. But this is about as basic as you can go. And then the square brackets there, that denotes an optional element. So the from clause is optional. But let’s without further ado, take a look at select select is the only thing required in a SELECT clause. So we don’t actually have to have a table to pull from, we can actually project data just without any initial data source, though usually, and the vast majority of queries you’ll actually use, we will have a From clause associated with the select. So you select these columns from this table, or from this data source, essentially was what that boils out to, can also rename the columns that we actually select. If our database design is done correctly, or done well, so to speak, we shouldn’t have to add a lot of aliasing here. But column alias aliases are really helpful when we are pulling data from multiple tables. And then also when we’re actually showcasing query results back to the actual user. Because a lot of times the database column names aren’t necessarily super user friendly. As far as in the user, it may be fine for a database engineer designer, but not so much for the end end result. I’ll showcase some aliasing here in just a second.

But the SELECT clause is going to be the projection operation within aren’t you know, if we want to use database terms here, now are sorry, sets. So with sets, we have projection and selection, and for some reason, when the SQL language was being designed, they chose the select keyword to set to for the projection operation, and the from clause to be part of the selection operation. And so it’s it’s kind of backwards as far as the actual SQL statements go. What it really boils down to for the Select or projection operation is that we are picking out which columns or attributes of our data that we actually want to have come out and the end results. So if we have five columns in a table or a set, we are going to project or select a, either all of those columns or a subset of those columns. So maybe column one, two, and five, and we’ll skip the other two. That’s what projection is going to do. And here in a few here in a moment, as well, I’ll show you the from clause, which is the selection operation, which deals with rows instead of columns. Without further ado, let’s take a look at a couple of examples of our select. Now, as I was mentioning earlier, we do not have to have an actual table to select from. So I can go in here and say select seven and run it and I actually get a result. So I end up with one row one column. I don’t have a column name yet. But there it is. There’s my data that from my for my query result.

Now a lot of times you won’t actually see queries as simple, but sometimes they’re actually pretty useful. But nonetheless, I can actually go back in here and this is where aliasing becomes very useful. And so we will say as, and then we can, let’s say number here. Cool. Now, as I run that now, I actually get a readable column name out, so I get a number. There. Also notice here, when I look at number, you’ll notice that it is is highlighted in blue. So things that are kind of reserved words just kind of like as you’re typing and your favorite programming environment, it highlights key words as part of the language. So if you have that situation here, or you end up wanting to have a space and your column name, then you will need to do something like this. So we want to actually denote the actual name either using square brackets, which is going to be the way that SQL Server usually will prefer it, you’ll see that and other database languages or other SQL flavors, you will see this as a double quotes. So either one works. For me, I do not mind either way, which way you go. Most of my examples, you will see me using the square brackets, just because it’s more SQL Server II ish. But the double quotes are also perfectly acceptable and perfectly acceptable syntax.

Now we can do all sorts of things here as well, like I can add more columns, so I can do. Right, and I actually get a text column back out, I can even put a date. So state time offset, this function will actually pull out the current time on my local computer or the SQL Server instance, the time from that server server. Now see, if I do a space here, it doesn’t really work out so well. But if I actually wrap that in either quotes, or the square bracket to denote the actual name, I can actually have a space now on my column name, this is really as simple as a, an expression as you can get as far as being a complete and full query. Okay. Now, a couple other random thoughts here, the capitalization of the actual SELECT statement or or clause elements, doesn’t really matter, right, it is not case sensitive as far as select. So I could go in here and do all all lowercase if I want it to, so I could go select. And that will still execute. It is common syntax for people to use all caps for any SQL elements, and queries, because that helps denote it from the rest of the content. So it helps to note away from column names, values, conditions, all sorts of other things that are not reserved words in a SQL query. So that is the primary reason why we use all caps for any SQL statements. That just helps us pick out the keywords a little bit easier, especially if we don’t have an IDE that is doing all of the coloring for us.

Okay, so let’s keep on moving forward here on to the from clause. So from is going to actually denote what table or tables your query is actually pulling data from. So in other words, it tells the query where all the data is coming from, you’re going to use from a lot of different contexts. And tables can be defined relatively loosely. For for now, our simple queries are going to pull from one single table inside of our, our schema or schema database. But there is a lot more that can kind of fall underneath the from clause. As we’ll see later in the class. We can use aliases as well for the from clause. So we can say from table XYZ as x, you know, whatever name we actually want to put for the table. So the aliases work the same way as a SELECT clause. And the names though that are available to the select clause, are are extracted from or inherited from the result of the from clause. So that’s how select happens right? So we actually even though we list select first in the SQL, the from clause actually has to execute first because the Select has to know what columns are available to it to actually purchase So we’ll take the the columns from the from clause, so all the columns from the table or tables that are in the from clause get passed to the select clause.

For projecting, let’s take a look at an example of this and action here. So we had this piece of SQL way earlier in these video series, so select star from sales orders. And you can notice here I get 1000s of rows in this sales orders table is from the worldwide importers database. If you don’t quite have that selected, please see the the setup video that’s out there, or feel free to reach out. And I can help you get that worldwide importers database set up for you. Because sometimes it can be a little bit tricky. But this database was provided by Microsoft, as an example database. So we’ll be using a lot of this database a lot in the lecture notes. So but as you can see here, lots of different sales information, we’ve got orders, we’ve got customer, the customer that made that order, who sold to that customer, and then all sorts of other things here as well order date and a variety of other things. So if I go down here, there’s actually quite a lot of columns in this table. So select star can be kind of kind of annoying, as far as as far as that goes, he very rarely need all of the columns from a given table. So as far as efficiency goes, it is far more efficient to select the specific table or the the specific columns from the specific table that you’re looking for.

Right. So for example, I maybe I only want something, say orders, maybe I only want the order ID and then the date that that order was made. And then the customer that made it, do customer ID. Now if I run this again, aha, my my results are far more clean, right, there’s a lot less information there that I need to actually consume. So column names default to whatever the tables column names actually are in the database. If it’s a direct reference, you can actually put the column name without the table that’s associated with it. That is perfectly fine, perfectly valid syntax. However, when you start to do multi table queries, it becomes less clear which column comes from which table. So it is more more common and better practice to always specify the table that that particular that particular column or attribute actually came from. So or dot. And there we go. We can also do the same thing here, right if with an alias, so if I did, as, and then if I really wanted to be shorthand, I can say, oh, but notice now that my syntax no longer works. Because the Orders table does not is not does not exist. It does exist in the database. But it does not exist as far as an option or an available source of information. Because it’s no longer included in my FROM clause. The table that’s included included in my FROM clause, as far as the Select knows, is just Oh. So let’s change that to oh, oh, and we’ll also there we go.

So now, I run that we’re back in business. So things become a little bit easier to actually do there with aliases, especially if you have like, a really long, like long schema name or a long table name. Sometimes the aliases are really nice. Just add a little shortcut there. And that’s relatively common practice. And as long as you don’t have a ton of tables, single character aliases are fine. But if you have a really big complicated query, this is you use good practice and naming your variables in your code. You want to use good practice in naming things like aliases inside of your SQL for our order date here, we can actually change this up a little bit. We can repeat, we can project a column more than once. And what do I mean by that? Well, what if I add here? The order year? Right. So there is this handy dandy function called year. And again, we’ll use I’ll show more more functionality with the date time format stuff here later, but I can say year here, and if I run that, I get the order year so just the year out of the date that’s full date is still there. Although I should probably add a alias here. Just to be a little bit easier to easier to run. That is just the basic select from here and the next series of videos. The next video, we’ll look at expanding our simple query