From the course: Learning Data Analytics Part 2: Extending and Applying Core Knowledge

Building basic select queries

From the course: Learning Data Analytics Part 2: Extending and Applying Core Knowledge

Building basic select queries

“

- [Instructor] Query concepts are pretty universal, but the way a particular software queries data is controlled by how that software is designed. For example, the way we query data in SQL and the way we query in Power BI, we end up with the same fields, the same types of joins, but we build the queries in entirely different interfaces. For this course, we're focused on the universal query concepts and how to apply them. Then you can apply those concepts anywhere a tool has a querying component. To teach concepts, I've used Microsoft Access for years because it's a database program, it has both tables and queries, it's widely available to anyone who has Microsoft Office on a PC, and its interface is less intimidating than SQL Server Management Studio. What you learn and discover, here, is applicable to any database tool that has a querying component. So here's the ask, who are the top customers over time? It's important to remember, before we have an answer to this question we have to generate data so that we can answer the question. First I must identify who are all the potential customers, and, remember, the business rules will define who is considered a customer. When we include the word top customers, we have to turn to the people who make the rules and confirm what they mean by top customers. Answers to this question could range from single order amounts to number of orders placed, or if they've ordered consistently over a period of time. Our goal is to identify the answer to those questions, that way we'll have a clearer direction to achieve the answer. The last part of the question was over time. So we really need to dig in deep. What do we really mean by over time? We may just be confirming that they meant all of the years they've been in business, or they may say we just want to look at the last three years. Again, we have to ask these details to know what type of criteria to include. Only when these questions are answered, conceptually, are we ready to gather the data that is needed to answer the original question. We're going to work in the database, in our data folder, in the chapter two files. Let's open up the query data database. Let's go ahead and open up Table Customers. Note that there are 663 rows of potential customers listed in this table, and a good many of them at the top say Tailspin Toys at different locations. If we scroll further through the records we'll see other customer names listed, they may be listed consistently with their name and their locations. It's important to note that there are 663 rows of information. I would make note of this number, even if we're not going to display it because someone will ask how many potential customers we had to choose from. If you show someone top 50 of something, they are immediately going to ask, "Top 50 of what?" And, in our case, the answer is 663 potential customers in the customer table. Let's go ahead and close this, so I'll hit the little X there. I'll go to Create, and I'll choose Query Design. Let's generate a query that uses the asterisks command, which is a query command that will pull all of the fields from the table. I'll go ahead and add customers, I'll just drag it over there, several different ways to do this. And then you notice at the top, there's an asterisk, I'll go ahead and double-click that. Okay, and then I'll run that query. I see the same 663 records, and I see all of the fields because I used the asterisk. Let's take a look at the SQL statement behind this query. I'll go to My View and choose SQL View. Now after watching the foundations of the course, previously, you learned about SQL, Structured Query Language, and I wanted to show you the SQL statement that's generated by Access. And you notice it says select customers from customers, and you can tell that it's pulling all the fields because you see the asterisk in the select statement. Okay, let's go ahead and run it again. And, again, we notice a lot of noise, information that we don't need in our query result. Let's go ahead and change our view to Design View, and we can just choose what we want from the table. I'll go ahead and hit that column and press delete. I want a list of CustomerID and CustomerName and then I'll run my query. And I have just the fields that I selected from my table. Okay, let's go ahead and save this query. I'll right-click the Query tab and choose Save. We're going to call this CustomersList and I'll click OK. And now, you'll notice, I have my tables at the top and I have my query CustomerList in my Queries objects. Correctly answering the question that we've been asked, with a query or two, can be complex if you don't break the questions down and build the correct select queries to support the answer. So now we have a list of customers, we're well on our way.

Contents