SQL Fundamentals and examples on parallels with Tableau prep

by Laura Scavino

SQL or Structured Query language is a domain-specific language used in programming and designed for managing data held in a relational database management system, or for stream processing in a relational data stream management system.

This article aims at explaining the fundamentals of the SQL language to retrieve data and it also shows some examples on how to do it in Tableau and Tableau prep as well. A few things to remember is that SQL language is not case sensitive and there is a specific order of operation to follow.

First step: insert the SELECT followed by the desired field name in squared brackets or just write star to retrieve all field

Second step: specify the data source FROM and run the query by clicking on the green arrow pointing right

When selecting a field name, only that data field is retrieved as shown in the second example.

It is possible to add comments or title to the queries by typing:  /* INSERT COMMENT */

 

Filter a field  How to retrieve: Sales by product name for the Central Region

In Tableau

-Drag and drop product name to rows and sum of sales to columns

-Drag and drop Region to filter and select Central region only

    

 

In Tableau Prep

-Add an aggregate step to group by product name and region with sum of sales

-Add a new step to filter the Central region: [Region]= ‘CENTRAL’

 

In RazorSQL

SELECT all the fields with  squared bracket and rename the aliases with AS

FROM the chosen data source

WHERE functions as a filter. If where it is followed by IN  or = it filters the chosen field.

GROUP BY the dimensions 

ORDER BY the measure using the alias created and sort either by ASC or DESC. If not specified the sorting is automatically done Ascending

End the query by adding a semi-colon at the end ;

 

Select a range How to retrieve: Which days have an average profit greater than 500?

In Tableau

-Drag and drop order date to rows and avg profit to the mark labels

-Ctrl click average profit and drag it to filter, select at least 500

 

 

In Tableau Prep

-Add an aggregate and group by Order date the Average profit

-Add a step to clean the profit by selecting on range of values, type minimum 500

 

In SQL 

SELECT the order date, average profit and rename the alias with AS

FROM the chosen data source

GROUP BY  the date

HAVING followed by an operator will give the desired range for the measure

 

Order of the syntax in SQL

SELECT TOP 1 [FIELD] , [FIELD] , SUM[FIELD], COUNT[FIELD]

FROM DATABASE

WHERE IN TO FILTER OR LIKE TO SEARCH FOR SPECIFIC  PATTERNS

Examples: Where [Region] in (‘Central’) will filter only the Central Region

In Tableau it is possible to just use the filter

 

Examples: Where [Region] like ‘%W%’ will select the Region which contains the W

In Tableau it is possible to write the following formula: Contains([Region], ‘w’).

 

Examples: Where [Region] like ‘%W’ will select the Region which starts with the W

In Tableau it is possible to write the following formula: Endswith([Region], ‘w’).

 

Examples: Where [Region] like ‘W%’ will select the Region which ends the W

In Tableau it is possible to write the following formula: Startswith([Region], ‘w’).

 

GROUP BY all the dimensions to group by

HAVING followed by the operator to give a specific range, minimum or maximum values. Used for aggregate functions.

ORDER BY the sorting option 

 

 

In SQL, as in Tableau, there is also the Scalar function, which returns a single value based on the input value. These are the UPPER and LOWER functions, which returns the different cases.

There is also the SUBSTRING which extracts the selected characters from a text field: SUBSTRING ([name], start, length).

As in Tableau, also SQL has the REPLACE function: REPLACE ([name], ‘light’, ‘dark’) 

LEN returns the lengths of the value in a text field: i.e. WHERE LEN[name]>9

ROUND i.e SELECT ROUND ([name], decimals).

GET DATE () is the equivalent of Tableau Now() or Today()

DATEDIFF(‘datepart’, [date1], [date2]

 

I hope this could be useful for everyone using multiple tools!

 

 

What did I learn in the last 16 weeks at the Data School?

5 mins read

Sun 03 Jun 2018

Have a look at my top 5 Tableau Tips

5 mins read

Fri 01 Jun 2018

Create a grid map in Tableau

2 mins read

Tue 29 May 2018

A way of using shapes in Tableau

2 mins read

Thu 17 May 2018