Do you have “no” knowledge of SQL?
Think SQL is hard?
Well, if you want to use Datadeck’s new data blending feature you will need to know some basic SQL. However, if you don’t, we got you covered.
In this article I’m showing you how easy SQL and I’m including a SQL Cheat Sheet so you don’t have to remember anything just copy – paste!
Datadeck now has the power to blend data from multiple data sources into one chart/card and because of that, it’s time to improve your basic SQL query skills to make the most out of this new feature.
For this sample I’d like to use the Tableau – Superstore Demo spreadsheet. (If you want to practice, download it and upload it into your Datadeck account)
Let’s start with some basics.
What is SQL?
SQL stands for Structured Query Language, which is the language we use in our Data blending feature. SQL is used to talk with tables and data sources.
What is a SQL query?
A SQL query or SQL statement is nothing more than a sentence we use to talk to Datadeck and let it get the data we need. So think about like this… “Hey Datadeck, get me the latest sales from the superstore excel file for Toy cars.”
This would be plain English but in a SQL query it’s actually not very different.
SQL Basics
We always start with:
SELECT
FROM
We know both of these words. SELECT will represent the actual result you would like to see. In the sample I used SELECT Sales.
FROM is referring to the column of the source… so FROM Orders.
The full SQL query will look like this:
SELECT Sales FROM Orders
Here you go! You just created your first query.
In my Sample I also used November 2018. Adding that is actually quite easy. For that we can use WHERE
WHERE Productname = "Toy cars"
The new SQL query will be:
SELECT Sales FROM Orders WHERE Productname = "Toy cars"
Datadeck’s SQL Cheat Sheet
We created an overview of some easy to use to SQL queries that will help you make the most out of our Data Blending feature in Datadeck. Feel free to ask us for help or submit some extra queries we can share with our users.
Select all rows in “Orders” table, include all fields
SELECT * FROM Orders
Select all rows in “Orders” table, sort by “Order Date” field
SELECT * FROM Orders ORDER BY Orders.`Order Date`
Select all rows in “Orders” table, reversely sort by “Sales” field
SELECT * FROM Orders ORDER BY Orders.Sales DESC
Select rows in “Orders” table where “Sales” field value is greater than 1000, include selected fields
SELECT Orders.`Order ID`, Orders.`Order Date`, Orders.Sales FROM Orders WHERE Orders.Sales > 1000
Select rows in “Orders” table and add a calculated field
SELECT Orders.*, (Orders.Sales / Orders.Discount) AS BeforeDiscount FROM Orders
Group rows in “Orders” table by “Country” field, include sum of sales and profit
SELECT Orders.Country, SUM(Orders.Sales) AS TotalSales, SUM(Orders.Profit) AS TotalProfit FROM Orders GROUP BY Orders.Country
Join “Orders” table and “Returns” table by matching on “Order ID” field, only keep rows that exist in both tables
SELECT Orders.* FROM Orders INNER JOIN Returned ON Orders.`Order ID` = Returned.`Order ID`
Join “Orders” table and “Returns” table by matching on “Order ID” field, keep all rows that exist in the “Orders” table
SELECT Orders.*, Returned.Returned FROM Orders LEFT JOIN Returned ON Orders.`Order ID` = Returned.`Order ID`
Everything together: group all returned orders by country and get total sales and profit with sorting
SELECT Orders.Country, SUM(Orders.Sales) AS TotalSales, SUM(Orders.Profit) AS TotalProfit FROM Orders INNER JOIN Returned ON Orders.`Order ID` = Returned.`Order ID` GROUP BY Orders.Country ORDER BY TotalSales DESC
