How to build a query in Toad Data Point

Hello, folks. My name is Robert Pound, and I am a Product Technologist from Quest. In this video, I'd like to talk to you about how to build queries in the visual Query Builder of Toad Data Point. Understanding and combining data are two of the major endeavors at the front end of any data analysis task. Toad Data Point addresses these issues in a number of ways. However, today we will focus on the visual Query Builder. Highlights of the Query Builder include working with the spirit data sources to form a more complete result set, including all sources available in Toad Data Point; a visual interface allowing users to quickly interact with their data through mouse clicks; being able to quickly and easily analyze and understand your data; as well as the ability to collaborate around your data sources. Now let's walk through a more detailed example of how to build a query in the Query Builder. So to start with, we will open a new Query Builder. You can right click on an object in the Object Explorer and choose Query Builder. Or you can, from the Tools menu, choose the Query Builder. Or, in the ribbon bar itself, you can choose the Build icon. And you can either choose a standard query builder or, if you have the Pro edition, you can choose a cross-connection query builder. I'm going to choose the standard query builder. And since I had my Access database in focus, that is what I'll be using. If I wanted to create a cross-connection, I could first drop an item into the canvas, and then change the focus. So in this case, to MySQL. And then, again, drop an object from the other data source. And now you can see the cross-connection Query Mode banner on the side. So now we have access to both sets of objects in the Object Explorer. So before I begin building out this query, let's take a brief look at the toolbar at the top. So I won't cover all of these, but some of them include the ability to export data once you've collected it. You can send this query, once you have it fleshed out, to automation. You can create calculated columns from here, by simply adding a name and then defining it at the bottom. And you also have the ability to execute and stop execution. The toolbar at the bottom-- you have access to tutorials. You have the ability to publish this file to Toad Intelligence Central server, if you have a Toad Intelligence Central server. And again, you can push to automation here as well. So looking at our two tables that we have on the canvas, obviously, since they're different sources, there is no primary foreign key relationship. But in Toad, you can create a relationship by simply clicking and dragging. So in this case, I want to link the address ID in the Address table to the address ID in Warehouse. So I can simply click and hold, and move it over to address ID, and it will create a join. So from here, we can simply click on this magnifying glass and it will generate a Venn diagram for us, doing an analysis of the join. We can also add columns by simply clicking the checkboxes. And as I click these, you can see it building out in the field list below. So I could choose the Wildcard, or I could choose Select All Columns. The difference is with the wildcard, you don't have the ability to manipulate the columns below. So in the field list, now that we've selected a few items from each, the first thing you'll notice is that unique rows, or a sample-- the top 10, the top 20, whatever you choose-- if you set this to zero or select all rows, depending on what your default settings are for pulling data in. The top two items are Table and Database. These just tell you where the objects come from for each column. If you need to change the order of the result set, you can drag and drop columns and it will manipulate the order. And you can also close these out and they will disappear from the query list, and they will be de-selected from the visual Query Builder portion. So you have the ability to filter and aggregate, just like you would in a standard query. So you can aggregate on any one of the columns. Obviously, it depends on the data type-- what sort of aggregations you can do. And for the WHERE clause, it'll open up a dialog box where you can either enter in a formula or you can choose some of the predefined operators to enter in your filters. To do the Group By, if you do an aggregation, you will want to do a Group By. So if I aggregate it on this column, for instance, I would group by the rest. So it's as easy as plus-- if I wanted to group this one first, and then following with, in this case, state and city. You can add them. And to remove them or change the order, you can simply check the minus, and it will move the grouping. And having worked similar to the WHERE dialog box, you can also sort any of the columns ascending and descending. You can choose to query a column but not make it visible in the result set by unchecking Visible. The field aliasing-- you can see that this column got a field alias because I aggregated on it. You can change the aggregation. You can change the aggregation by simply entering in a value in this field. And table aliasing has to do with the query itself. So we haven't looked at the Query tab yet, but we can switch over right now and see, as we're selecting things, it's building out a query. So you can see