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!
