In the SQL Editor in CloudBever, users can write a query to any connected database. But what if they need assistance with scripting? Then Visual Query Builder (VQB) will help anyone to cope with these tasks. With its interface, any user can write the necessary query without writing a single line of code.
Today Louis’ boss has asked him to help one of the company’s marketers with a quarterly report. To do this, he must create a query with data about the orders, customers, and partner stores from the tables’ new_leads, partner_stores, and partners. Of course, Louis knows how to solve this task without the skills to write complex SQL queries. They will explore the capabilities of VQB together in practice.
How to access VQB in CloudBeaver
First, they need to go into the corporate account. There they find the connection with the necessary tables as it is already in the Data Navigator. Then they open the SQL Editor. To open the Visual Query Builder, click the corresponding tab in the SQL Editor right toolbar.
Learn how to write the first simple queries in CloudBeaver’s SQL Editor in our blog article
.How to make queries in Visual Query Builder
They select the new_leads, partner_stores, and partners tables in the Data Navigator and drag-and-drop them into the VQB area.
From the first table they will take data from the order dates, contacts, and the total from the second table, the number of orders from the partners, and the third, which is the partners’ names and their contacts. No other data is required for the quarterly report, so they do not need to touch the other columns from the tables’ new_leads, the partner_stores, or the partners.
When they added the tables to the VQB field, they will see that the SQL query on the right side of the screen was also filled with several lines within the SELECT statement. A miracle? No, they will notice more than once that the SQL expression to the right of the diagram reflects the actions they perform graphically. And selecting the specific columns from all three tables is a good example.
When moving the tables to the corresponding tab, they noticed that there are connecting lines between them. This is an Inner Join, which is the merging of records from two tables. The linking column does this in this case because it contains the same values in both tables. It is also automatically put into the text script.
Users can choose to display a Join type in the table titles, as well as other visual settings in the Setting menu
from the SQL Editor on the bottom toolbar.
How to filter data and run a query
The statement created in the VQB is almost complete. The only thing left to do is to filter the selected columns using the filter field.
In the query builder, this function works in conjunction with what users do with the diagram. They will see the result later, but the WHERE clause has already appeared in the window with the statement.
So, since Louis’ colleague does the quarterly report, the best way to filter the totals is by order date. So in the filter field, they enter the column name, operation sign BETWEEN with the start and end dates of the quarter.
After carefully checking the completed statement in the window on the right under Louis’ supervision, the hero launches a query. They can press the button in the left side menu or run it with the Ctrl+Enter shortcut.
How to export the result set and save the script
The marketer exports the new table made from the result set via the Export button on the bottom toolbar to send to the company’s boss as an Excel file.
And a student of Louis is happy to save the script with the SELECT statement because it will come in handy for future quarterly reports. To do this, they need to click on the Save icon and specify the script name in the context window that opens.
Louis helped a colleague with his main task of making a quarterly report. At the same time, the marketing specialist learned to use Visual Query Builder and saw how to write queries with Join clauses in them. Follow our blog updates because more valuable materials about CloudBeaver’s capabilities will be coming up.
