All right. This is one of my favorite labs in this first course, and here is where you get to see the struggle of the data analysts as you work against very, very common SQL troubleshooting errors. So in this lab, you will be practicing a lot of SQL syntax, we will also be working with a few more devious errors, we are query will execute and run perfectly fine, but the data result that's returned is still not the right answer that you want. So, let's take a look at some of these common eCommerce queries, and see if you can spot what's wrong with them. Let's give it a go. All right. So, here we find ourselves back inside of the Qwiklabs dataset, and after clicking on that link to get the data into insights project listed here, the one we're going to be focusing on for this particular lab, again is eCommerce for your dataset, and then click on the data table within that dataset for revenue transactions. If you've forgotten what are some of the elements in the Schema, again, like we saw before, you've got the data fields, you got the size of the table, and you've got some of the sample data. But now, we're actually going to try to get some insights. Let's see what we can query out of the table. So, if you haven't already click on Open the Query Window, disable the Legacy SQL options. I always like to start with Standard SQL mode enabled, or if you want to, you can actually start by click on Querying Tables, so you get that table name there. So you don't have to worry. That's the first error. If you have something like this, there you go error number one, is the table name is not recognized. So, the table name is not recognize, you want it to be in blooms that's actually a table that's represented there, and one of the other really nifty things that I'll point out now, if you're on a Mac, it's the command key. If you hold that down or on a Windows machine it's the Windows key, and pretty much locked up the Action key. If you hold that down, your datasets will actually be highlighted. So if you have a literary long query, and if you have multiple different datasets there, if you hold down that key highlight, they become a hyperlink. You can actually click them, and that'll take you to the corresponding dataset. So you can actually not worry about having to find which table in the dataset that is, you can actually get it from the query. So that's something that I actually do pretty often. Sets a tip and trick. So the first thing if you have a table name that's missed named, or here's another one, you might see this a lot in queries that are written online, and you're say you're Googling around and you're trying to look for help for for SQL, and you're selecting all of the rows and columns like Star which is another, bad practice that you don't want to do is running that query, and you're wondering, "I got into Standard SQL mode, I saw these brackets that were on there." Anytime you see those brackets that indicates the query was poorly written inside of Legacy SQL. So, I'll just show you actually what that looks like. They are limited to just 10 records or I'll just do it to a COUNT star to be best practices here. Because even when you limit things, if you're doing a lot of aggregations it'll still scan all of the rows. So if I'm executing this, and I Show Options, I'll just show you what that looks like, and Legacy SQL, hopefully I've got this. My Legacy SQL knowledge is still there. It'll actually run. So, the point being here is if you see brackets that immediately indicates to you that the query is written in Legacy SQL. So, where you can see this, is if you click on Show Options and you didn't untick Legacy SQL, so I'm going to clear this. You didn't untick that, you click Hide Options. Then, you clicked on query that particular table. So, if you didn't Query table without unchecking Legacy SQL first, It'll bring you the enumerated table name in the Legacy's SQL format with the brackets. So, that's why I will clear that out. It's a very common error. Then uncheck Use Legacy SQL, query the table and then boom. You'll get the back ticks and the period instead of a colon there.
