Which are the most used SQL queries to get relevant insights form your database

All the queries could be found on:  https://github.com/FranciscoRiano/wwi_database_sql_queries/blob/master/wwi_sql_queries.sql

SQL (Structured Query Language) is a language used to manage and administrate relational database systems. This language allows us to create tables, assign primary keys, modify values, connect tables through columns in common, among other things.

One of the most common usages given to SQL is to create queries that allows us to explore the data based on specific and desired features. In this post we will see some queries samples that are really useful to get insights form a huge relational database system.

For this exercise a relational database system, called WideWorldImporters (WWI), has been downloaded form the following link: https://docs.microsoft.com/en-us/sql/samples/sql-samples-where-are?view=sql-server-ver15.

Once the database is downloaded, we will have to link it with our SQL Server Management Studio (SSMS).

We will have to choose the “Restor Database” option in order to proced with the conection. Once there, we will have to indicate that we want to do the link through our device given that the file has been stored there.

At the end, in our object explorer module, it has to say that the database has been added successfully.

Given that it is possible to gather several relation databases in our SSMS, we will have to indicate which database are we aiming to use. In order to do it, we can use the following code:

In this code we are just saying that all the SQL queries are going to be applied to the WideWorldImporters database.

So, after all this steps we can proceed to explore our databases. One of the most basic SQL commands is the Select command. It basically allows us to see different tables or specific columns within the database.

For instance, in the example above we are saying that our goal is to see the Purchasing.Suppliers table that it is our database. The asterisk means that we want to see all the columns of the table however, it is possible to see just some columns. If we are aiming to do it so we can use the following code:

In this case we are specifying that, from the Purchasing.Suppliers, we just want to see 3 columns that are: SupplierName, SupplierCategoryID and SupplierID. For this code, the result given by SSMS would be:

Following with our aim to explore the database, we can add others commands in order to get a more specific glance of the data that we want to see. These commands are known as clauses and the most used are:

• Where
• Order By
• Having
• Group By
• Top

We will take a glance on each of the clauses.

Where and order by:

In the previous example above there are 3 lines. As we have seen previously, in the first line we are just saying that we want to see all the columns from the sales.InvoiceLines table. In the second line we include the where clause, basically it helps us to filtrate the data based on specific conditions. In this case we are saying that we just want to see records with a unite price higher than 100 but lower than 200. The third line we include the order by clause, it allows us to order the data range based on an assigned orientation and attribute. For this example, we have said to SSMS that we want to order the data by the UnitePrice column and with a descendent orientation, it means from the higher to the lower. Just for practical purposes we have used the Order By clause with the Where clause but clearly it could be used with any other or even without other clause.

Having and group by

First of all, it is important to clarify that the Having clause must be used after the group by clause, otherwise SSMS will give us a mistake about or code. After this has been clarified, we can proceed with the code:

In this example we have made 4 lines. The first and second lines are the instructions in order to get the SuplierID column from the Purchasing.PurchaseOrders table. At the end of the first line, we have put the count function, this function returns the amount of total registers of one particular attribute within a database. In this case we have asked it to give us the number of records of each SupplierID in the database. The column with this information has been called Quantities.

The third line is an instruction in order to get al the information required grouped by the IDs of the different suppliers hosted in our database. With this code I will get two columns, the first is going to be the suppliers IDs and the second is the number of registers of each supplier IDs in the database. The fourth column, which is the clause having performs as a filter (similar to the where clause but with numeric values). So, in this case we are indicating that we want to see just the suppliers IDs with at least 5 records in the database.

Top

The Top clause is its name indicates, shows us the top x registers within one table. In the example form above, we have given the indication to show us the first 10 registers form the Sales.CustomerTransactions. Again, here we have used the Order By clause in order to get the data range organized, by TransactionAmount value, with descendent orientation.

Until now we have seen the basic usages of the main clauses in the SQL language however, there are plenty of possibilities in order to “play” and blend them to create amazing queries and therefore get relevant insights from any database. Here some examples:

All the queries could be found on: 

https://github.com/FranciscoRiano/wwi_database_sql_queries/blob/master/wwi_sql_queries.sql