The main goal of our apps is to simplify SQL scripting for users in various professions who have different skill levels. That is why we have the Visual Query Builder – a tool with which you can create SQL queries based on the visual representation of the part of your database schema. Previously, we have briefly talked about this feature and its capabilities in our blog. However, we want to dive a little deeper and show you how you can use VQB for your tasks.
How to create a SELECT query with VQB
Let’s go back to Louis, our database specialist, who uses DBeaver and CloudBeaver on a regular basis. He has already mastered SQL scripting but continues to use Visual Query Builder from time to time. This feature allows him to speed up the process of creating queries, and it turns out to be useful for his film-related hobby.
Louis is a big movie fan, and in his free time, he maintains a website dedicated to films and TV series. This time, Louis has decided to create separate film pages, divided by language. He wants such pages to display the film names, release years, and all the actors who starred in the film. Let’s see how Visual Query Builder can quickly help him collect the necessary data.
To begin with, Louis creates a new script and opens a Query Builder tab inside the SQL Editor. Then he drags and drops the Film, Language and Actor tables inside the VQB area. All the relationships between the tables are automatically displayed.
The next step is to mark the necessary checkboxes with the columns that contain the data Louis needs. In the Conditions tab, he sets the language equal to “English”.
Louis checks that the joins are exactly what he needs and receives a ready-made query. Now he can run it and look at the result.
Louis notices that the result set is sorted alphabetically by titles. He decides it would be a good idea to sort it by a release year. In the sorting tab, he adds the condition he needs and runs the query again.
Good job, Louis! He has saved time on writing queries manually, and received the required data set which can be used on a website.
How to check and edit SQL query using VQB
Sometimes Louis has to use scripts created by his colleagues. And in this case, Visual Query Builder also turns out to be useful.
Louis’s colleague has created a query to determine the top 100 customers by payment amount. He said that his query works correctly, but he does not know how to sort the result set correctly.
To begin with, Louis opened a query in the SQL Editor.
When Louis ran the query, he saw that the default result set had not been sorted. He decided to quickly switch to the Visual Query Builder and immediately saw a visual model of the existing query.
Louis needs to add sorting to determine the top one hundred customers. To do this, he goes to the Sorting tab, adds the condition “amount” and changes the order from Ascending to Descending.
After rerunning the query, he sees that he has achieved the desired effect. The result set is now sorted perfectly.
Now Louis can send the finished query to his colleague and show him how to adjust his queries using VQB himself. And, of course, you can also use both ways of working with Visual Query Builder to simplify SQL scripting and solve your tasks faster.