This is a guide to basic SQL commands. In this article I’ll cover:
Why learn SQL
If you work in digital marketing, using tools such as Google Analytics, Google Ads, Facebook ads, Data Studio, may be a daily task. However, most of these marketing tools are made to be extremely user-friendly and not require knowledge of actually working with raw databases.
Eventually, there may come a time where you want to take a leap forward and start manipulating all the data you have, connect databases to ad platform APIs, or store them in a single database, such as BigQuery.
I have recently made this leap to exploring databases in BigQuery. I found that the best way to learn SQL is through practice. So, I found some tutorials to learn SQL for free, and these basic commands gave me some ground to move forward in the right direction.
So, if you currently find yourself in a similar situation, this article may give you a push in the right direction.
How SQL works
The first thing you may notice about SQL is how similar it is to data storage with Google Sheets or Excel.
Most likely, you are already no stranger to exporting reports in .csv format and analyzing them.
So, identifying similarities between Google Sheets and SQL may help you make an easy transition. Switching from working with spreadsheets to working with databases may be easier than you think.
In a way, I look at SQL as Google Sheets on steroids.
This is how we can easily compare the data structures between the two platforms:
I think of the database as my Google Sheets document.
It contains multiple pages (tables) and the columns in these pages can be connected together to carry out calculations, pivots, and reports.
A table is like a certain page in my Google Sheets document.
Each table consists of rows and columns.
A row defines a dimension of data, such as a company or a client’s name, location, user ID, etc.
A column holds data to describe a certain aspect or metric of particular row.
It looks exactly like a column in a Google Sheets table.
It is important to note that values inside a particular column are always formatted in only one data type. The data is either quantitative, discrete, or a string.
Entity Relationship Diagram (ERD)
The ERD serves as a map of all the connections inside the database.
The key to SQL is creating, manipulating, and retrieving data from the database.
This is done with running statements.
A few example statements include:
The ‘CREATE TABLE’ statement creates a new table in the database.
The ‘DROP TABLE’ statement removes a table from the database.
Allows you to retrieve data and display it. This is called a query.
‘SELECT’ is the command you will use the most when starting to learn SQL.
How SQL queries work
Running queries will return and display data from the database.
Information retrieved through queries are read-only. They do not insert or manipulate any new data in the database.
You can think of it as having read-only access to a Google Sheet.
Except in SQL you are also able to filter, hide, and sort columns in the tables without changing the “Sheet” for everyone else.
In this chapter I will go through all of the basic SQL commands that you will need in order to be able to run analyses in your database.
SELECT * FROM
The SELECT command indicates which columns you want to be given data for.
FROM specifies which table you want to select the columns from.
If you want to retrieve data for all columns in the table, you use the wildcard symbol ‘*’.
The most common query you will see in SQL is:
SELECT * FROM table;
This query will return all data from the table named ‘products’:
SELECT * FROM products;
Databases can be large and returning a high number of rows can be time consuming.
The LIMIT function tells the database to return only a certain number of rows from the table.
For example, you may want to check the top 10 revenue-generating products in your e-shop , but you may have 100,000 products.
Loading all 100,000 of them may take a lot of time. Limiting your results to only 10 rows will help you get the data faster.
This query will return all data for the first 10 rows in the table ‘products’:
SELECT * FROM products LIMIT 10;
Note that the LIMIT command is always the last part of a query.
If you need to sort the data in ascending or descending order according to the values in a certain column, you can use the ORDER BY command.
It’s similar to sorting data in a Google Sheet. The only difference is that in a Google Sheet you change the data for everyone, while in SQL the sorted results will be visible for your eyes only.
This query will return all data from the ‘product’ table, ordered by how much products are left in stock in ascending order:
SELECT * FROM products ORDER BY stock;
How to order a column by descending order (ORDER BY, DESC)
By default, data in SQL is sorted in ascending order.
If you wish to sort a column in descending order, simply add DESC to the end of the command.
This query will return all data from the ‘products’ table, ordered by the most expensive products:
SELECT * FROM products ORDER BY price DESC;
In order to filter our data and only display results which fit a certain criteria, we use the WHERE command.
These symbols are used in WHERE statements:
> (greater than)
< (less than)
>= (greater than or equal to)
<= (less than or equal to)
= (equal to)
!= (not equal to)
You can filter data according to both numeric and non-numeric values.
This query will return data for all products in the category ‘jeans’:
SELECT * FROM products WHERE category = ‘jeans’;
This query will return data for all products which cost more than 24.99:
SELECT * FROM products WHERE price > 24.99;
When we want to filter results for data in a certain numeric range, we can use the BETWEEN command.
This query will return data for products which cost between 20 and 30 (including exact amounts of 20 and 30):
SELECT * FROM products WHERE price BETWEEN 20 and 30;
In this instance, using BETWEEN is an easier option than carrying out the same query with the AND command.
The AND command combines two or other commands and groups them into a single requirement.
For example, the `WHERE column BETWEEN 10 AND 100` query from earlier may be written as `WHERE price >= 20 AND price <= 30`.
The AND command is also useful for filtering data in columns with non-numeric values.
This query will return the same results as the previously run query with the BETWEEN command:
SELECT * FROM products WHERE price >= 20 AND price <= 30;
The LIKE operator is used together with the WHERE command to filter data which contains a certain required string.
In fact, I think of it as a synonym to the word CONTAINS.
The required string may contain the symbol ‘%’ as a wildcard symbol, which would mean that there can be any symbols where the ‘%’ is located in the required string.
This query will return the prices for all products where the color column contains the word ‘blue’ anywhere in the column:
SELECT product_name, color, price FROM products WHERE color LIKE ‘%blue%’;
This query would return the prices for all products where the color starts with the word ‘blue’:
SELECT product_name, color, price FROM products WHERE color LIKE ‘blue%’;
One thing to note: in SQL, the values inside the cells are case-sensitive.
Therefore, the above query would not return data for colors which start with ‘Blue’.
The OR operator may be pretty much self-explanatory.
It works in a similar logic to the AND operator – except instead of combining the two criteria into a single requirement, the criteria is to fit either one of the provided requirements.
So, for example, this query would return results for Google Analytics website traffic that came from either ‘organic’ or ‘referral’ channels:
SELECT * FROM ga_sessions_* WHERE medium = ‘organic’ OR medium = ‘referral’;
The IN operator is used with both numeric and text columns.
It is similar to using an ‘=’ operator, but more convenient if we need to return results for multiple separate requirements.
I think of it as combining the ‘=’ and ‘OR’ operators into one simple query.
This query would return results for traffic from both organic and referral channels:
SELECT * FROM ga_sessions_* WHERE medium IN (‘organic’,’referral’);
The NOT operator is extremely useful when we wish to exclude results according to a certain criteria.
For example, this query would return results for all products which do not contain ‘blue’ in their name:
SELECT product_name, color, price FROM products WHERE color NOT LIKE ‘%blue%’;
This query would return results for website traffic that came from all channels except ‘organic’ and ‘referral’:
SELECT * FROM ga_sessions_* WHERE medium NOT IN (‘organic’,’referral’);
When running a query, it is possible to manipulate the results using standard mathematical operators: “+”, “-”, “/”, “*”.
Let’s say we wanted to sum up the values of two columns. For example, revenue from the price of a product and VAT sales tax of that product in order to figure out the final sum paid by our customers.
We could simply run a query which would sum these columns up for us and display the information in a derived column.
The query & result would look like this:
SELECT product_cost, vat, product_cost + vat FROM products;
Note that the new column name was automatically created as ‘?column?’.
Next, let’s see how we can make our new column easier to read by using the AS operator.
The AS operator allows us to name our newly derived column from our query.
To return a column with the desired name, our query would look like this:
SELECT product_cost, vat, product_cost + vat AS price FROM products;
Note that returning a new column in the results will not actually create that column in the database.
The derived column is only temporary and will disappear once you run the next query.
SQL best practices
In order to keep your work clean and easy-to-read for your colleagues, it is a great idea to follow some best practices. They are not mandatory, but can make your workflow much smoother. Here they are:
Type all SQL commands in UPPERCASE letters, i.e. SELECT, WHERE, LIMIT, etc.
Type all database data in lowercase, i.e. `database_name`, column_name, etc.
Note: even though SQL commands are not case-sensitive (writing them in UPPERCASE letter is not mandatory), data in columns are. Meaning, that if the column data inside the database were not created in lowercase, then you’re stuck with typing it the way it is found in the database itself.
This is why following this best practice of lowercase letters for columns inside the database is especially useful when one is entering data into the database.
Learn SQL with free courses
SQL is a very useful and interesting language to learn. The entry-point of starting to learn SQL is not that high, which is why I advise everyone to at least learn the basics. This may open new opportunities you didn’t know where possible.
There are different types of SQL databases. Some of the most popular databases include:
Microsoft SQL Server
You can also write SQL within other programming frameworks like Python, Scala, and HaDoop.
Here are some courses to learn basic SQL for free: