No-code Ways of Generating SQL Queries.

In today’s business world, data analysis is the lifeline of any business. To remain competitive, businesses and organizations must analyze data to understand market trends, customer behavior, and more.

---

Tools used in this tutorial

DbVisualizer, top rated database management tool and SQL client

---

To analyze data, you need the ability to write SQL queries. However, writing queries from scratch is time-consuming especially if you have limited coding experience. Fortunately, some tools enable you to generate SQL queries easily without needing to write them from scratch.

In this article, you will learn how to use an SQL client to automatically generate SQL queries.

Prerequisites

You need a SQL client and some database knowledge to follow through with this article. In this case, we will use Postgres as the database management system and DbVisualizer as the database SQL client.

To install PostgreSQL, navigate to the PostgreSQL download page and download it for your operating system. You can follow this guide to install PostgreSQL on Windows, this guide to install it on Linux, and this guide to install it on macOS.

To install DbVisualizer, navigate to the DbVisualizer download page. Then download the recommended installer for your operating system. Follow the user guide here to learn how to get started with DbVisualizer.

Connecting Postgres to DbVisualizer

Step 1: Start DbVisualizer and click Create a Connection button as shown below.

Creating a connection in DbVisualizer.

Step 2: Search for and double-tap the Postgres driver from the popup menu on the left side of your screen, as shown below.

Searching for the Postgres Driver in DbVisualizer.

An object view tab for the Postgres connection is opened.

The PostgreSQL connection Object View Tab.

Step 3: Fill in the database connection name field with “PostgresQueryBuilder,” as shown below.

Filling the Database Connection Name Field in DbVisualizer.

Fill in the Database Userid field with “postgres” and the Database Password field with your password (we use “test123”) as shown below.

Filling the Authentication Tab in DbVisualizer.

Once done, click the Connect button at the bottom, and if there are any issues, they will be displayed under the Connection Message section.

Postgres Connection Success Message in DbVisualizer.

If the connection is successful, you should see the newly created PostgresQueryBuilder connection on the left sidebar of your screen.

Importing Data Into a PostgreSQL Database

Step 1: Navigate to this Kaggle web page and download the Data Science Salaries 2023 dataset. You can also use other datasets available on Kaggle.

Data Science Salaries 2023 dataset in Kaggle.

Step 2: Open the database connection PostgresQueryBuilder on DbVisualizer. Right-click the Tables tab tree and select Import Table Data as shown below.

Selecting the Import Table Data Option in DbVisualizer.

Step 3: Open the Data Science Salaries 2023 CSV file through the window that opens up.

Opening a File in DbVisualizer.

Step 4: Keep clicking the Next button below the popup window until you reach the window below where you need to create a New Database Table.

Creating a New Database Table in DbVisualizer.

Step 5: In the popup window, give the table that will hold the inventory dataset a name. In this case, I have named my tabledatasciencedataas shown below.

Giving a Table a Name in DbVisualizer.

Step 6: Next, check the Use Delimited Identifiers checkbox at the bottom of the pop-up window to help PostgreSQL differentiate any column name from its reserved keywords.

Checking the Use Delimited Identifiers Checkbox.

Step 7: Click the Next button and then import the dataset into your PostgreSQL database. If the data is imported successfully, you will get a Success message as shown below.

Successful Data Import in DbVisualizer.

Once the dataset is imported, right-click on the table tab and refresh to see the table created.

Refreshing the Objects Tree to See the Table Created in DbVisualizer.

You should be able to see the datasciencedata table created in the Postgres database as shown below.

The datasciencedata table.

Generating SQL Queries Using the DbVisualizer Query Builder

The DbVisualizer Query Builder provides an easy way to develop SQL queries by providing a point-and-click interface that does not require in-depth knowledge of the syntax.

Here are the steps to follow to generate SQL queries without coding.

Step 1: Create a new SQL commander tab using (CTRL+T) keys.

Newly Created SQL Commander Tab in DbVisualizer.

Step 2: Open the Query Builder by clicking the vertical Query Builder button on the right.

Query Builder in DbVisualizer.

Step 3: To create a query, drag and drop tables to the Query Builder view.

Drag-and-dropping a Table to the Query Builder View in DbVisualizer.

Step 4: To generate a SQL query, right-click in the column list in the table Window and choose Select All. In this example, we add all columns from the datasciencedata table to the select list:

Selecting All Columns In a Table on Query Builder in DbVisualizer.

Step 5: Click the first button above the Query Builder on the left to execute the generated SQL code through the SQL Editor.

Copying the Generated SQL Code to the SQL Editor and Executing It in DbVisualizer.

You should now see the generated SQL query in the SQL editor – the results of the query should be visible below.

The results of the SQL query in the datasciencedata table.

Refining the Query Using The Query Builder

To refine your query, you can use the tabs below the diagram area. When refining the query, keep in mind that we can work with Columns, Conditions, Grouping, and Sorting.

Refining Your SQL Query.

Each of those tabs represents various parts of an SQL query. These parts are explained below:

SELECT <Columns>
FROM <Tables>
WHERE <Conditions>
GROUP BY <Columns>
HAVING <Grouping>
ORDER BY <Sorting>

For example, The Data Science Job Salaries dataset imported into the PostgreSQL database contains eleven columns, which are:

1. work_year: the year the salary was paid.
2. experience_level: the experience level in the job during the year.
3. employment_type: the type of employment for the role.
4. job_title: the role worked during the year.
5. salary: the total gross salary amount paid.
6. salary_currency: the currency of the salary paid as an ISO 4217 currency code.
7. salaryinusd: the salary in USD.
8. employee_residence: employee's primary country of residence during the work year.
9. remote_ratio: the overall amount of work done remotely.
10. company_location: the country of the employer's main office or contracting branch.
11. company_size: the median number of people that worked for the company during the year.

Let’s say you want to generate a SQL query that finds the average salary for each experience level in the dataset. You can do so by using a Query Builder and following the steps below:

Step 1: To find the average salary for each experience level, select the experience_level column on the table added to the Query Builder.

Selecting a Column on the Table Added to the Query Builder in DbVisualizer.

You can now see that the selected column appears under the Columns tab.

Selected Column Appearing Under the Columns Tab in DbVisualizer.

You can preview the SQL code generated so far by switching from Edit Details to SQL Preview on the drop-down menu.

Previewing the SQL Query Generated by the Query Builder in DbVisualizer.

Step 2: Select the salary column from the table on the Query Builder.

Selecting a Column on the Table Added to the Query Builder in DbVisualizer.

To get the average salary, select the AVG aggregation function under the Aggregate field for the selected salary column.

Selecting an Aggregate Function to Generate a SQL Query.

Let’s give the column that will hold the average salary for each experience level a name. You can call the column average_salary under the Alias field for the salary column.

Giving a Column an Alias Name.

Step 3: Check the box under the Group By field on the experience_level column to ensure that the average salary is calculated for each unique experience level separately.

Grouping Data in the Generated SQL Query by Query Builder.

Switch from Edit Details to SQL Preview and you will be able to see the SQL query generated by the Query Builder.

Previewing the SQL Query Generated by the Query Builder in DbVisualizer.

Step 4: Click the first button above the Query Builder on the left to copy the generated SQL code to the SQL Editor and execute it. The generated SQL query results in a table with the average salary for each experience level.

DbVisualizer Providing SQL Query Execution Results.

That’s it! You’ve now generated a working SQL query all by using features of a powerful SQL client.

Conclusion

Look at this article as a guide on how to generate SQL queries without coding. This article has successfully demonstrated how to build and work with queries using DbVisualizer and provided a couple of examples to generate SQL queries.

If you’ve successfully completed all of the steps outlined in the article, you can now generate SQL queries without much coding knowledge making data analysis operations more accessible and efficient.

Make sure to try the DbVisualizer SQL client today and until next time.

About the author

Bonnie is a web developer and technical writer creating easy-to-understand technical articles.