Creating a simple SQL query with Active Query Builder
Introduction
Have you ever been confused about how to get insights from a database fast?
SQL is a powerful weapon to cope with this task, but you will need plenty of time to master it if you don’t have a solid SQL background. Is there a way to get an instant result?
Scientists proved long ago that visual information is absorbed by the human brain more efficiently than any other type of data representation. That’s why you should consider a graphic query builder, especially if data analysts are busy and you need to get a report as soon as possible. As a side effect, you’ll get to grips with SQL quicker.
Making first steps in SQL learning, keep your eye out for mistakes, such as missed commas, brackets and quotes, misspelled commands, etc. They can ruin your query, and all you get in return is puzzling error messages from the server. So you might not even be able to figure out where the mistake is.
A visual query builder handles the task of building query text so that non-technical users can focus on the data they want to pull out of the database. Even advanced users like analysts and developers can benefit from a graphical query builder, decreasing the time spent building a query.
This article will show you the process of creating a simple SELECT query to get the information you need.
Let’s take John. He is interested in getting sales analytics for the East Coast for the last three months. He works in a large company, in which a team of analysts performs this kind of task. However, the working time of any analyst in the team is quite expensive. What’s more, the team promises to process his task only in a week.
John knows SQL a bit. He has a desktop app, which he can use to send queries to the database. The key issue is that he only uses it to run pre-built queries provided by one of his colleagues with a technical background. John can read and understand these queries, but it’s next to impossible for him to change one. Every time he tries to apply some changes to a query, the server returns an error.
Of course, John can spend a while on this task and finally understand SQL better, but the problem is, he must provide his suggestions on procurement for the next quarter tomorrow, so he needs those analytics today. So John decides to try Active Query Builder, which his friend, a developer, suggested yesterday.
SQL query creation workflow
John is happy to try the new solution, as his job may be in the balance. His friend decides to help him and explains what John can do by employing the solution.
On the left side of the window, John sees a list of all the objects. There he can pick out the tables and views containing the required information (Database Schema View). Next, he sees an area to drop the selected objects and mark the needed fields (Design Pane). Having done that, John can specify his conditions to choose only the records he needs and set up sorting and grouping in the grid below (Query Columns List).
In brief, writing an SQL query in Active Query Builder comprises three key steps:
- Select data sources, which can be tables or views.
- Select the data you need for your report; choose existing columns, or create expressions for new ones.
- Choose the format of data representation by setting up sorting, grouping, filtering, etc. After that, you will get the data you need.
John is full of enthusiasm and ready to start. Now, let’s review the process in more detail.
Test database
First, let’s look at the database structure with which John will be working. Let’s use the Northwind database to show how easily John can create SQL queries in Active Query Builder. The Northwind database is a sample database developed by Microsoft which has been used for tutorials on various database products for decades. It contains the sales data for a fictitious company called “Northwind Traders,” which imports and exports specialty foods worldwide.We will use the Customers, Orders, Products, Suppliers, and Employees tables out of this database.
- Customers: Customers who buy products from Northwind
- Orders: Sales order transactions taking place between the customers & the company.
- Products: Product informationSuppliers: Suppliers and vendors of Northwind
- Employees: Details about Northwind employees
Step 1: Data source selection
Let’s imagine that John needs to generate a report that will show the results of selling some products in a specific location. Here, he’ll have to deal with the Products table. He can drag and drop it onto the visual editor on the right side or just double-click on it.
At the bottom of the screen, he sees the SQL text that matches his selection. These editors are interlinked, and Active Query Builder simultaneously reflects all changes made in another editor. Thus, John can edit a query text in the traditional manner and observe the query structure.
In Visual Query Builder, he can see the list of fields and their data types. Some fields that point to values in other tables have a little link icon next to their name. They are called foreign keys. Primary keys, like ProductID here, are marked with a key sign.
In most cases, the information of interest is spread across multiple objects, so John has to combine it in the query to get the required results. John needs three objects: Products, which stores all the items that have been sold, Orders, and Order Details.
When he adds several objects to the designer pane, he needs to link them to each other. Otherwise, he will end up having a massive set of data that includes all rows from all items multiplied by all rows from all orders. However, his goal is to see only the items corresponding to specific orders.
There are a few ways to join two objects in Active Query Builder. The first method is to use the link buttons next to key fields. John sees that the Orders table is linked with the Employees and Customers tables, and next to OrderID is the Order Details table he needs.
John clicks on the link button and sees a child table. The link type is one-to-many because Order Details is joined with the primary key of the Orders table.
After that, he needs to join the Products and Order Details tables. John joins these objects with the ProductID key field. He can join them with the link button, or he can drag the ProductID field from the Products table and drop it to the field in the destination table with the same name.
John looks at the query text below. It now contains a From expression that includes three tables interlinked within the Inner Join clause. Next, he switches to the Data tab to see the data preview updated in real-time. For now, it includes all the fields of all the tables.
Step 2: Picking the data for your report
Next, John can select the columns to create a report. He marks the checkboxes next to the fields he needs. He chooses the product name, price, and the items left in stock from the first table. Then he marks the region where these items were shipped from the Orders table.
In the visual editor, he can see the list of columns in the query. John can also switch back to Data, and he will see more selective data that is easier to review.
Step 3: Choosing the format of data representation
The last step is to filter particular records and choose the order in which to display them. First, John filters out Shipping regions to East Coast locations only. Then, next to Orders.ShipRegion, in the Criteria column, he types equal, WA, and the Data preview now shows only the items shipped to the required area. The criteria value was auto-quoted according to SQL rules, so John doesn’t have to worry about that.
There’s no point in having a column with identical data in all the rows, so he makes it invisible by clicking the visibility checkbox in the ShipRegion row. The filter will remain active, but the data will look cleaner.
Then he sorts the data by the Products column, selecting the ascending sort type in the ProductName row.
That’s great, but still not perfect because there are a few duplicates, as there were multiple shipments of the same goods. To get rid of them, John goes to the Properties in the upper right corner and checks the “Select only unique records” checkbox.
That’s it. John sees a clean, sorted, filtered list of products without duplicates, and he can always switch back to the SQL tab to see the query.
Conclusion
In this article, we presented an SQL query building process in the form of a story about John. Now let’s summarize what you can get using Active Query Builder. Active Query Builder helps you to get the data you need quickly and makes the process easy. You get a visual representation of the query structure, which can be helpful, especially with a complex query. In addition, the tool helps prevent SQL errors, so you won’t need to waste your time looking for missing commas or any other possible errors.
As for John, the hero of our story, it stunned him how quickly he got the information despite his lack of SQL knowledge. He’s glad to have such a tool at hand. Still, he’s frustrated with his colleague, who didn’t tell him about the tool the day before. John was behind schedule with his report, and there was a significant chance he wouldn’t manage to present his suggestions on time. However, with Active Query Builder at his disposal, he made valuable proposals and prepared a perfect slide deck.
