SQL Editor is a powerful tool for creating and executing various SQL scripts. Today, our heroes will learn how it can be used to speed up and simplify their work with databases in CloudBeaver.
Writing simple queries will be the main challenge for Louis’ marketers. Today they will be writing their first SELECT queries. By creating a script based on SELECT, newcomers will be able to use this query for different tasks in the future. For example, for researching and exporting data for an advertising report.
How to start working with SQL Editor
First, they need to figure out how to open this SQL Editor and select the right database. They will need the Invoice table from our example database. Users can open the editor by clicking on the three horizontal lines next to the connection name in the object tree and selecting SQL Editor from the context menu.
To open the SQL Editor, users can also left-click on the desired connection and then click the SQL icon in the main menu.
Then the SQL query console opens in a box on the right side of the screen.
If the user clicks on … next to the name or presses Alt+T, the SQL Editor will open in a new browser window.
Writing simple queries
When the SQL Editor is already open, users can move on to simple queries. Columns with the SELECT statement and their names added/written afterwards must/can/should be selected. The script follows with a FROM statement and a representation of the Invoice table. It contains all the orders fulfilled by the company. So the query looks like ‘SELECT * FROM Invoice’. They will see the result if they click on the Run icon in the left toolbar or use the Ctrl+Enter shortcut.
To learn how to write the SELECT statement, check out the video on our YouTube channel.
After running the query, the Invoice result is set. Marketers can edit or filter data from it manually. But they will go through today’s process a few more times to generate the advertising report. Louis shows them how to find, via SQL queries, which countries’ order volumes did not exceed 2.
They enter BillingCountry and Total after SELECT. Then they use the FROM keyword and the Invoice table. Finally, the WHERE clause helps marketers filter out the volume. It is enough to specify Total < 2. Then they click Run and see the result as a filtered table.
We explained how to work with Data Editor in CloudBeaver in a previous article.
But what if marketers only need to find out about small volume orders from a particular country? For example, the USA. This is where specifying a WHERE clause will help. That is, to the already entered Total < 2, users should add AND BillingCountry = 'USA'. If the script is written without errors, Louis’ students will see the updated result of running the script.
Saving the script for the future
The function of saving SQL scripts and then loading it back into the SQL Editor is especially important. Users can run a previously prepared script instead of going through the same process from the beginning. Also, by doing so, they will no longer have to prescribe it manually.
Users can save it in CloudBeaver. To do this they need to click on the Save icon and specify the script name in the context window that opens.
The script tree will open in the right-hand corner of the window immediately after saving. They will also be able to find their script there. Newcomers can check or uncheck Show Scripts in Tools in the CloudBeaver main menu. This manipulation is to use this SQL script in the future or if users wish to close the context menu.
Today Louis showed the marketers how to write simple SELECT queries. They also learned how a script of a few lines can convert a large table into ready-to-use material for an advertising report. Stay tuned, as future articles on our blog will tell you more about CloudBeaver’s capabilities.
