As part of the training, we were introduced into Snowflake and SQL which was very interesting.
We started with SQL basics. So to begin, what is SQL?
SQL stands for Structured Query Language. It allows you to access databases and manipulate them the way you would want to. Also, SQL is American National Standard Institute standard.
SQL can do many things with the databases, starting from executing queries to creating views and storing permissions.
Most of the actions that we would want to perform on the databases would come from SQL statements. Also, SQL is not case sensitive, but when we write SQL keywords, we should write them in upper case for good practice. So let's have a look at some basic SQL statements.
SELECT:
A SELECT statement in SQL allows you to pick data from the databases you want. You can either pick certain columns or all of them using a wildcard which is written as *. You would write this statement as something like this:
SELECT "column_name","column_name1","column_name2" (or SELECT * )
FROM "table_name"
It is important to put column names and table names in double quotation marks as that will check for those things. If they were written in single quotation marks, it would call a text value instead so it will give you an error. Also, another important thing is to write each SQL keyword in a new line. This is because if there is a mistake somewhere in the statement, it will be evident as the message will pop up in where the error is, i.e. within what line.
SELECT keyword matches up to the select tool in Alteryx as this is where we pick certain columns we want to see from the dataset.
A SELECT DISTINCT keyword will give a unique list of whatever data you are trying to get. The statement would look something like this:
SELECT DISTINCT "column_name",
FROM "table_name"
You can use more than one column to get a unique list of things but that would give a unique list that applies to those columns, which means you have to be very careful as you might not get what you might expect.
WHERE:
A WHERE clause is used to get the specific data that applies to those conditions. As mentioned before, SQL calls fields in "". For text values, it has to have single quotation marks '', whereas numeric fields should be left as it is, no quotation marks at all. The statement would look something like this:
SELECT "column_name", "column_name1" (or SELECT * )
FROM "table_name"
WHERE "column_name" operator value
WHERE clause matches up with a Filter tool in Alteryx as we are picking certain records from those columns that fit the description whatever you're putting into into WHERE clause.