By the end of this blog post, you should have a sound understanding of any questions you may have on SQL and also a good understanding of the fundamentals to be able to write a query.
What is it?
SQL stands for Structured Query Language. It is a universal coding language used to work with many databases and database technologies - though there are minor differences in the syntax across databases (e.g. oracle, vs snowflake).
What can it do?
- You can execute queries in SQL to retrieve data from a database
- You can insert, update and delete data
- More specifically you can transform and clean data
- You can create new databases, tables, views and stored procedures
- You can set permissions
SQL Workflow - Why SQL?
A typical workflow using SQL will often look like the below diagram
You can think of SQL as a data prepping tool - in the above workflow it takes the position of the data prep step. This often where Alteryx, Tableau Prep or some other tool might be used. SQL can be considered an affordable alternative, as some of the other data prep tools require licenses, which can be costly.
SQL Order of Operations
The order in which you write operations in an SQL query matters! The general order is as follows:
- SELECT - this is where you call the field names you want from a table e.g. Order ID, Product Name, Price)
- FROM - this where you specify the name of the table you want the fields from
- WHERE - this is an optional operation, it's essentially where you can apply a filter for some or all of the selected columns e.g. where price>100
- GROUP BY - another optional operation, this is where you can aggregate the data
- HAVING - also optional, this is where you can filter an aggregation that you've called in the query e.g. HAVING SUM(sales)>100. Since the GROUP BY (aggregation operation comes after the initial filter (WHERE), it won't apply to anything you have in GROUP BY
- ORDER BY - this is also optional, and allows you to sort the data you are querying. By default when included in a query it will order it sort it in ascending order, but you can also specify ASC (for ascending) and DESC (for descending)
SQL in Snowflake
Example of a query
A basic query in SQL will call specified fields from a table, all of which must be separated with a comma. If you wish to call all the fields in a table, you can do this by using an asterisk (*), as per the below image.
The power of a comma
Failure to use a comma to separate the field names you want in your SELECT query will result in the fields being renamed - note this only happens when there are two fields being called in your query, anything more than this will result in an error.
In the below query, we have called 100 rows of the 'order_id' and 'order_date' fields from the orders table.
Notice how there is no comma separating the 'order_id' and 'order_date' field names. The below image is the output that we get from the query, instead of a column for order_id and a separate column for order_date.
Separating queries in snowflake
It is likely that you may have multiple queries in a tab, especially when playing around with SQL to see what different queries return. In such cases, you must make it clear where each query ends with the use of a semi-colon - see the below image for a demonstration.
Failure to do this will result in an error, as snowflake won't know where your query starts due to the multiple 'SELECT' queries - see below image of error code.
