DBeaver's Visual Query Builder

 Quick! You need to create a complex query, but you don't remember how to join the tables. And you need to get it done ASAP.

You are in luck if you have an enterprise edition of DBeaver handy.  You can drag and drop the tables and then adjust the clauses as needed without having to dig into the table definitions.

1. Open the database 

The PostgreSQL DVD Rental database. 

I am using the PostgreSQL DVDRental dataset for these examples. I have opened the connection and listed the tables.

As you can see, the database contains numerous tables. 

We are going to build a query visually to get information about the customers.










2. Open a SQL Console 

Open SQL Console

The second step requires an SQL Console. You will find it under the SQL Editor tab.





3. Select Builder 

On the right side, Select Builder

On the right side of the console pane, tilted 90 degrees, is a tab labeled Builder. 


4. You are ready to drag and drop tables

 

The Query Builder Is Ready to use. 
You have three new panes. The top one is where you drop the tables.  The bottom left is where we tailor the query. The query will then appear in the bottom right pane.

Now drag the payment, rental, and customer tables from step 1 separately to the top pane, the one that says 'no active query',


You can rearrange the tables to create a harmonious relationship map that aligns with your artistic aesthetic.


You should have something like this: 

The tables are joined and ready for querying.









4. The query

The lower right display will have the query.

select

*

from

public.payment p

inner join public.customer c on

p.customer_id = c.customer_id

inner join public.rental r on

c.customer_id = r.customer_id

inner join public.payment p on

r.rental_id = p.rental_id

Not bad, considering you did not have to look up how to join the table.


Refine here

5. Refine the query 

Now we get to the lower left window.  This is where we refine.  Select the Where tab on the left and then the icon with the plus sign on the right. 

We want to skip the customers who have not made a payment. 

Making sure that payment.amount is greater than zero.






Note that the query is automatically adjusted to match the changes.


And we will switch to the sort tab, add an expression that asks for the payments be listed in descending order. 

Add a sort on the amount column, in descending order. 







And again, the query is adjusted to match the sorting demand.

6. The result

The query is ready to run.

select

*

from

public.payment p1

inner join public.customer c on

p1.customer_id = c.customer_id

inner join public.rental r on

c.customer_id = r.customer_id

inner join public.payment p2 on

r.rental_id = p2.rental_id

where

p1.amount > 0

order by

p1.amount desc


And the result.

The Results













We can eliminate excess data by removing the SELECT * statement and specifying the desired columns.



Specifying specific columns

















If you find yourself fighting the syntax of SQL, the Query Builder lets you concentrate on the output and saves you from the frustration.



Comments

Post a Comment

Popular posts from this blog

Incremental Backups in PostgreSQL 17

 The old adage that a DBA or SRE is only as good as their last backup is true.  PostgreSQL 17 added the ability to combine multiple incremental backups with a full backup to provide a complete data dictionary to recover a failed system. It is very easy to use. This is a quick example of using incrementals. I recommend watching this video by Robert Haas for more details and some discussion of backup strategies. Step 1 - Enable WALL Summerization The incremental backups require Write Ahead Log Summarization. It is likely not on your instance by default. But it is easy to enable. demo=# alter system set summerize_wal = 'on'; ALTER SYSTEM demo=# select pg_reload_conf(); pg_reload_conf  ----------------  t (1 row) Step 2 - Full Backup The examples below are much simpler than you will run into in real life.  The scenario is that we make a full backup on Sunday and do incrementals the rest of the week. Wednesday through Saturday are omitted below.  stoker@ThinkPa...
Read more

How PostgreSQL's Aggregate FILTER Will Spoil You

Using PostgreSQL will definitely spoil you. I recently had to use another database and found, to my chagrin, that aggregate filters were not included in their implementation of Structured Query Language. Specifically, there was no FILTER clause available. FILTER? To illustrate my dependency, let me start with some simple data. demo=# create table z (a int, b int, c int); CREATE TABLE demo=# insert into z values (1,10,100),(2,20,200),(3,30,300),(4,40,400); INSERT 0 4 demo=# select a,b,c from z;  a | b  |  c   ---+----+-----  1 | 10 | 100  2 | 20 | 200  3 | 30 | 300  4 | 40 | 400 (4 rows) demo=#  If I wanted the overall number of rows, I could use a simple COUNT(*). If I wanted an overall number of rows where one of the columns was of a certain value, I could add the logic for getting those rows to the WHERE clause. What if I wanted both?  Do I need two queries to do the work separately? Maybe a materialized view or a CTE (Common Tabl...
Read more

Can Artificial Intelligence Created Better Tables Than You?

 Artificial Intelligence is one of those conundrums where the ability to have some tasks handled for you contrasts with a mix of ego and pride that it may be better than you at those tasks.  I recently wrote a blog using another database about an AI-generated SQL that was quite sophisticated . But what about asking an AI to create a table with a specification like a DBA/SRE/Analyst might receive? I used Grok and entered the following prompt: Write the SQL to create a table on a PostgreSQL version 17 server to record data on customers. The data will include a first name, a last name, an address, a birthdate, and a unique UUID primary key. Partition the data by the year of birth. And produce some test data of at least ten records. I am sure many of us have started large projects given less instruction.  Notice: I did not denote the format for the address (US Post Office's format, or UK, or other). Nor did I set a name length. I wanted to see what assumptions were made by t...
Read more