All right well, going back to the previous slide for just a moment. I can't really tell you if that's four billion, four trillion, and that will be super useful if we had some comments there. So what we're going to introduce is this quick formatting function. Now, what the function is, is you can take a column like total revenue, pass it into this kind of magical operator called Format, pass also some other parameters, or some other values that are inputs to this function, in this particular case it's a formatting parameter that we have here, and it will create a new result for us. So just adding that gives us a different value. So, a key point here is that you pass things into functions and there's some action that's performed on them. In this particular case, we're formatting it to add a coma. What does that actually look like? It looks like this. So, that's 45 billion. So, it's much easier to read, but now we have something that's very. So, it's much easier to read. But there may be a couple of caveats that come with that. So, we're introducing functions in many different types of functions. We're covering just the formatting ones to begin with, but again as I like to do I'm going to introduce you concepts first, and then trick you into realizing that it might not be the best way to do things. So, here we go. When we applied this function, an interesting piece to know is that, that 45 billion that you see there with the commas, is a fundamentally different data value than what you saw before. A lot of you might argue, and you could say like,"Hey wait a minute, 45 billion, is 45 billion, is 45 billion, but the fact that we performed a calculation, or an operation on it means that it actually loses its original identity, and that's exactly what you see there at the column name, you no longer see tot revenue, or total revenue you see FO underscore, which is just a big Corries placeholder. So, what we need to do is give it a new name. And you give a column a new name by using the AS keyword and that invokes, renames it, and this is called using an alias. So tot revenue, say that wasn't the most readable, we're just going to say, "Hey that's revenue." So, select total revenue, and then rename it as revenue. So, here's the key pitfall right. So, if you're using stylistic formatting inside of BigQuery, you're adding commas to things, you could be unintentionally having this data be treated like a string in this particular case. And if you haven't worked with adding two strings together, it might not necessarily do what you'd expect it to do. So, say adding rows one and two, 45 billion and 20 billion, that might not give you 65 billion, that might give you 45 plus 10 in quotes or something like that, concatenating strings together. So again, to be very careful with unintentionally converting your data types through some fun formatting functions, and leave the formatting and stylistic elements to your visualization tools, or if you have a final reporting table. They are not doing anything downstream of, you can do it better. Okay, getting a little bit more advanced. So, here's a really interesting concept. There's two things that are presented here. So what we want to do first, is create a calculated field that represents income, and to do that much like using a calculator, and you can do revenue, minus expenses, and we're throwing that into parentheses. And we're saying, here are the result of those subtractions, and again BigQuery is going to go row by row, and calculate that for you on the fly, and these calculations can be much more complex than you see here, and the result of that is going to be income. Now, here's the really interesting part. Say if we wanted to filter for everything, or income is greater than zero. Well, the interesting part where we're using the filter is BigQuery does not know yet of what this field called income is, when it first looks at the dataset and attempts to filter out those results. So how the actual query engine works is, it doesn't read through your query from top to bottom, it will rip through it in kind of a programmatic way. So, immediately it's going to go, all right what data table am I looking at, and immediately it's going to jumped to the IRS 9902015 table. Once, it's inside into there, it says, okay, well before looking at all the columns that I need to return, let's see if I can just save some work by not including a ton of these rows on my resolve. So, I want to filter this thing as early as possible. Which spoiler alert when we get into the performance section on our later, of course, is one of the things that you're going to be practicing. So, immediately it looks and says, okay, cool, I'm going to find this income field, and I'm going to filter it for everything greater than zero and it's going to save me a lot of work. And then he says, man there's no income field that's here. What's going on? And that's because that field doesn't exist yet. That's something that you've calculated, and it happens kind of after the filtering has taken place. So, long story short, in most cases you can actually use that alias later on in the query, as you see there you can order by income descending. But, if you actually wanted to filter on incoming the Where Clause, take a guess on what's one of the things that you can do. And if you guessed copying and pasting total revenue minus total functional expenses that entire parentheses field there into the workflows, you'd be correct. In later courses in the Achieving Advanced Insights with big career of course in the specialization. A lot of you might be thinking that, on the previous slide of hard coding something like that twice violates kind of the don't repeat yourself mantra of good development practices and that's when we'll introduce things like breaking apart queries, into separate components and doing things like sub queries. So, stick around for that, if that's something that really interests you. Okay, so as you might expect just doing one column, returning one column, isn't that fun. Let's see if we can get some more data. So we're going to bring back revenue, we're going to bring back the employer identification numbers, we know who's responsible for this revenue and we also want to see whether or not there at school. And operates schools 170 CD is not that appealing to the eye. So we're just going to rename that using an alias called is_school. But, there's something wrong with this query after you executed, and if you're best friend with the query validator, it might help you out. But, take a look and see if you see anything that's missing. So, if you said comma, you are exactly correct. So, we're missing a comma, at the end of revenue. That first column that we're pulling out, all these columns are going to become separated, and if you said that we had an extra comma at the end, you're exactly correct. So the number one most common error, that everyone including myself, who's been writing SQL for many, many years, are going to run into, is your trailing commas and your missing commas. So, make sure that you actually update those. So here you select one column, comma, a second column comma, a third column and then no additional comma after that. And again, we'll be using the Query Evaluator in one of your labs, as you're going to see. And as you see here, now in addition to the revenue showing up, we also have the two additional data fields that we included in our Select Clause. And here you have one school, it looks like row 10, out of those as well. That's interesting insight. So, from there you can immediately glin that for 2015 sorting from revenue highest to lowest. This is whatever that EIN number is, that school has the highest revenue of all schools, for 2015 because there's no EIN, so above that they're flagged as schools. One of the things you want to avoid, because it's not necessarily the best practice is, you could have had a friend that said, "Hey I know SQL, just instead of adding on one column at a time that's going to take an extremely long time, just return all the data." So, there's a wild card that says, give me every single column, and that's called the star. So, if you did select star or the asterisk, I call it the star, to return all of your columns. You're going to get all of that data back, but performance wise, your queries are going to be slower, because it's going to have to process all that data and return it to your output, and because BigQuery is consumption based, even if you don't care about the additional data in those columns, you're still going to be consuming that resource, or returning more bytes of data than you might actually want to use. So avoid using Select Star.
