Mastering Advanced SQL Queries With DbVisualizer And PostgreSQL

In this article, we tell you how to best use some of the advanced SQL Querying techniques to manipulate and analyze data while making use of PostgreSQL and DbVisualizer - an advanced database client and SQL editor.¨

Tools used in this tutorial

DbVisualizer, top rated database management tool and SQL client
The PostgreSQL database.

This article will delve into the details of writing complex SQL queries using the techniques below.

Why SQL?

SQL (Structured Query Language) is a programming language specifically designed for managing and manipulating data stored in relational databases. It is used to create, modify, and query databases, as well as to control user access to the data.

In data analysis, SQL is often used in conjunction with other tools and techniques, such as data visualization software and statistical analysis tools and packages. It is also commonly used to prepare data for machine learning algorithms, as well as to perform ad-hoc analysis and also to generate reports.

In order to be successful in today's competitive business environment, it is essential for companies to use data analysis to make informed, strategic decisions and optimize their operations for maximum efficiency and profitability.

DbVisualizer and PostgreSQL

DbVisualizer is a versatile database management and analysis tool that can be used to connect to and manage a wide variety of database management systems, including Oracle, PostgreSQL, and more. It provides a range of features for managing and manipulating data, including a SQL editor, data visualization tools, and support for database design and development. PostgreSQL, also known as Postgres, is a free and open source advanced relational database system.

Setting Up PostgreSQL

To set up PostgreSQL on your computer, you’ll have to download and install Postgres. The installation process will vary depending on your operating system.

Run the command below in the terminal of your computer to confirm the successful installation of Postgres on your computer.

$ psql -U postgres

When the prompt opens, set a password for PostgreSQL from the prompt given. Now, let us create a PostgreSQL database to contain the data of a random hypothetical e-commerce business. Run the command below in the terminal of your computer to create the ecommerce database.

$ CREATE database ecommerce;

To check if the database has been created successfully in the list of databases, run the command below.

$ \list

The DbVisualizer SQL Editor

Open DbVisualizer once it has been installed, Click on the “Create a Database Connection” button.

Integrating the DbVisualizer SQL Editor With Postgres

• Name: Ecommerce business
• Database: postgres
• Database Userid: kwamegyamfismbp
• Database Password: Postgres password

Creating Tables in DbVisualizer

In this article, we will use an online shopping sample data from the kaggle webpage and this ecommerce data. The kaggle webpage dataset has been customized for the purpose of this article and the customized version can be downloaded here.

Now, enter the query below.

1 CREATE TABLE people (
2     InvoiceNo VARCHAR(255),
3    Description VARCHAR(255),
4    Quantity INT,
5    UnitPrice DECIMAL(10,2),
6    Customer_ID INT,
7    Country VARCHAR(255)
8 );

Now, open the ‘tables’ data tree to see the addition of the ‘people’ table to the Tables Schema.

Importing XLSX Files Into the Database Using DbVisualizer

It is important to have data to work with. In this section, we will cover the process of importing XLSX files into the database. To do this, let us add data to the created tables by doing the following:

• Navigate to the schema containing the table where you want to import the data

• Right-click on the particular table you want to import the data into. Now, select "Import Table Data" from the context menu.
  
  
  

• In the "Import" dialog that appears, navigate to the file directory on your local machine and select the peoples’ data file you want to import and click on ‘open’.
  
  
  

• Import the data by clicking on ‘Import’ in the final window
  
  
  

  
  
  Once the import is successful, you should see the status in the import log like below:
  
  
  

  
  
  To view the data, click on the ‘people’ table and then switch to the ‘data’ tab located beneath the ‘people’ table database directory at the upper part of the DbVisualizer window. The imported data should be available as shown below.
  
  
  

Advanced SQL Querying Techniques

Having covered the essential preparation steps, let us explore some advanced SQL querying techniques.

(A) Joins: Inner and Outer Joins & Self and Cross Joins

A join is a way to combine rows from two or more tables based on a related column. It allows you to retrieve data from multiple tables and merge it into a single result set to perform more complex queries and analytics operations. The several types of joins in SQL include: inner join, outer join, self, and cross join.

Inner Joins

Inner joins return only the rows that match the join condition in both tables. Inner joins combine rows from two or more tables based on a common column between them. The result includes only the rows that have matching values in both tables. Inner joins are performed using an equality operator (=) in the join condition. The common column acts as a key that links the two tables together, and the result of the join is a new table that includes all the columns from both tables.

For example: Let us use an inner join to retrieve all the invoices for customers in a specific country with the following query:

1 SELECT *
2 FROM people p
3 INNER JOIN customers c
4 ON p.CustomerID = CustomerID
5 WHERE Country = 'United States';

An inner join and a ‘GROUP BY’ clause can also be used to calculate the total quantity and total cost of each type of product for each customer. Let us execute the query below:

1 SELECT customer_id, p.description, SUM(p.quantity) AS total_quantity, SUM(p.quantity * p.unitprice) AS total_cost
2 FROM people p
3 INNER JOIN customers c
4 ON p.customer_id = customer_id
5 GROUP BY customer_id, p.description;

Outer Joins

Outer joins return all rows from both tables, including the rows that do not match the join condition. There are three types of outer joins: Left outer join, or right outer join, and full outer join.

• A left outer join returns all the rows from the left table (the first table in the FROM clause), and any matching rows from the right table (the second table in the FROM clause.)

An example of the left outer join querying technique is shown below:

1 SELECT *
2 FROM people p
3 LEFT JOIN customers c
4 ON p.customer_id = customer_id;

A right outer join returns all the rows from the right table, and any matching rows from the left table. If there is no match, NULL values are returned for the left table's columns.
An example of the right outer join querying technique is shown below:

1 SELECT *
2 FROM people p
3 RIGHT JOIN customers c
4 ON p.customer_id = customer_id;

Right and left outer joins specifically allow for retrieving the data from one or both tables in a query, even if there is no matching data in the joined table. Understanding these types of joins can greatly enhance the efficiency and accuracy of retrieving data in your SQL queries.

Self Joins

Self joins are joins that allow you to join a table to itself. A self join is useful when you want to compare rows within a single table, or when you want to retrieve data from a table that has a tree-like structure.

To find pairs of people with the same country, you can use a self-join with a join condition that compares the ‘country’ column in two copies of the ‘people’ table. Run the query below:

1 SELECT p.*, c.*
2 FROM people p
3 JOIN customers c
4 ON p.country = country
5 AND p.customer_id = customer_id;

Self joins allow you to join a table to itself and compare data within the same table. They are often used to break down complex data structures into simpler components and make it easier to understand and analyze the data. It is important to remember to properly alias the table names when performing a self join, to avoid confusion and ensure accurate results.

Cross Joins

Cross joins return all possible combinations of rows from the two tables. It does not use a join condition and is generally used for testing or when no meaningful relationship exists between the two tables.

Here is an illustration of an advanced cross-join technique.

To create a cartesian product of the people and customers tables, you can use a cross join without a join condition:

1 SELECT *
2 FROM people
3 CROSS JOIN customers

To create a cartesian product of the ‘people’ and ‘customers’ tables, but only include rows where the ‘customer_id’ column in the people table is not NULL, you can use a cross join with a filter:

1 SELECT *
2 FROM people
3 CROSS JOIN customers
4 WHERE people.customer_id IS NOT NULL

Cross joins allow the combination of every row from one table with every row from another table, resulting in a Cartesian product. While this type of join can produce large result sets, it can be useful for specific scenarios such as generating all possible combinations.

(B) Advanced Grouping and Filtering Methods

Using the ‘GROUP BY’ clause with multiple columns and aggregate functions.

The GROUP BY clause is used in a SELECT statement to group together rows that have the same values in one or more columns. It is typically used in conjunction with aggregate functions, such as SUM(), AVG(), MIN(), and MAX(), to compute aggregated values for each group.

For example, consider a table called people with the following columns: customer_id, description, quantity, and unitprice. To find the total quantity and total price for each combination of customer and description (product), you can use the following query:

1 SELECT customer_id, description, SUM(quantity) as total_quantity, SUM(unitprice) as total_price
2 FROM people
3 GROUP BY customer_id, description

Another way is to use aggregate functions in the SELECT clause, such as AVG(), MIN(), and MAX(). For example, to find the average quantity and average price for each combination of customer and product, you can use the following query:

1 SELECT customer_id, description, AVG(quantity) as avg_quantity, AVG(unitprice) as avg_price
2 FROM people
3 GROUP BY customer_id, description

Using the CASE statement for conditional filtering
The CASE statement is a control flow statement that allows you to add conditional logic to a SELECT, UPDATE, INSERT, or DELETE statement. It can be used to perform conditional filtering in a SELECT statement by including it in the WHERE clause.

For example, to find all invoices where the quantity is greater than 10 and the unitprice is greater than $7, you can use the following query:

1 SELECT *
2 FROM people
3 WHERE
4    CASE
5        WHEN quantity > 10 THEN 1
6        WHEN unitprice > 7 THEN 1
7        ELSE 0
8    END = 1

The use of the "GROUP BY" clause with multiple columns and aggregate functions, as well as the CASE statement for conditional filtering, are powerful tools in advanced SQL grouping and filtering techniques. These techniques allow you to group data based on multiple columns and apply aggregate functions to the grouped data, while the CASE statement enables you to perform conditional filtering of your data, making it easier to extract meaningful insights from your database.

(C) Advanced Set Operation Methods (UNION, INTERSECT & EXCEPT)

The UNION, INTERSECT, and EXCEPT clauses are set operations that allow you to combine the results of two SELECT statements into a single result set.

The UNION clause combines the results of two SELECT statements and returns all rows that appear in either result set. It removes duplicates, so each row is returned only once, even if it appears in both result sets.

For example, to find all invoices and customers who live in the 'United States', you can use the following query:

1 SELECT invoiceno, description, quantity, unitprice, country
2 FROM people
3 WHERE country = 'United States'
4 UNION
5 SELECT id, name, address, phone, country
6 FROM customers
7 WHERE country = 'United States'

The INTERSECT clause combines the results of two SELECT statements and returns only rows that appear in both result sets. For example, to find all invoices and customers who are both from the 'United States' and have the same name, you can use the following query:

1 SELECT invoiceno, description, quantity, unitprice, country
2 FROM people
3 WHERE country = 'United States'
4 INTERSECT
5 SELECT id, name, address, phone, country
6 FROM customers
7 WHERE country = 'United States'

The EXCEPT clause combines the results of two SELECT statements and returns only rows that appear in the first result set but not the second. For example, to find all invoices from the ‘people’ table that do not have a matching customer in the ‘customers’ table, you can use the following query:

1 SELECT invoiceno, description, quantity, unitprice, country
2 FROM people
3 EXCEPT
4 SELECT id, name, address, phone, country
5 FROM customers

This query will return all rows from the people table that do not have a matching row in the customers table.

(D) Using Advanced Queries to Insert, Update and Delete Data.

Advanced queries can be used to insert, update, and delete data in a database.

Inserting data into a table

For example, to insert a new row into the people table, you can use the following query:

1 INSERT INTO people (invoiceno, description, quantity, unitprice, country)
2 VALUES (6666, 'phone', 15, 25, 'Sweden')

Updating a table data

To update data in a table, you can use the UPDATE statement. For example, to update the quantity and unitprice of an invoice in the people table, you can use the following query:

1 UPDATE people
2 SET quantity = 8, unitprice = 71
3 WHERE invoiceno = 6666

This query will update the quantity and unitprice of the invoice with invoiceno 6666 to 8 and 71, in that order.

Deleting data from a table

To delete data from a table, you can use the DELETE FROM statement. For example, to delete an invoice from the people table, you can use the following query:

1 DELETE FROM people
2 WHERE invoiceno = 6666

This query will delete the invoice with invoiceno 6666 from the ‘people’ table.

Conclusion

Advanced SQL querying techniques allow developers and data analysts to work with their databases in a more efficient and effective manner. These techniques, such as self-joins, outer joins, inner joins, cross joins, grouping, and filtering, are essential tools in retrieving and manipulating data in complex databases. By using these techniques, you can extract meaningful insights, make better decisions, and improve the accuracy and reliability of your data.

You have learned how to implement advanced SQL querying techniques. Along the way, you have also learned to set up DbVisualizer and integrate it with your database management system. DbVisualizer is a universal database management and analysis tool and preferred tool for the world’s leading database professionals that helps users manage and analyze data in almost all databases. It offers a wide range of features. Learn more about DbVisualizer here.

Frequently Asked Questions

What is an inner join in SQL and how does it work?

An inner join in SQL combines rows from two or more tables based on a matching column between the tables. It only returns rows where there is a match in the specified columns in both tables.

How does an outer join differ from an inner join in SQL?

An outer join in SQL includes rows from one or both tables that do not have a matching value in the joined column but an inner join only returns rows where there is a match in the specified columns in both tables.

How do advanced grouping and filtering methods in SQL differ from basic techniques?

Advanced grouping and filtering methods in SQL allow users to more precisely control the data that is returned in their queries, such as using the "HAVING" clause to filter the results of a query based on the values in a group, rather than on individual rows. These methods offer more flexibility and control than basic techniques.

What is the purpose of set operations in SQL, such as UNION, INTERSECT, and EXCEPT?

Set operations in SQL allow users to combine the results of multiple queries into a single result set. The UNION operator combines the results of two or more queries and returns all rows that are unique across all queries. The INTERSECT operator returns only rows that are common to both queries. The EXCEPT operator returns rows that are in the first query, but not in the second.

Can you provide an example of a self-join in SQL?

For example, if a table named "employees" has a column named "manager," a self-join could be used to create a query that returns a list of employees and the name of their manager. The self-join would join the "employees" table to itself, using the "manager" column to match the rows.

About the author

Leslie Gyamfi is a mobile/web app developer with a passion for creating innovative solutions. He is dedicated to delivering high-quality products and technical articles. You can connect with him on LinkedIn