Join us on SlackTry for freeWhether you're a beginner at coding or an advanced data professional, this article will help you learn the most important SQL functions, such as CASE-WHEN, AS..
In our SQL 101 and Advanced SQL articles, we looked at how basic SQL queries work. In them, we saw special keywords (SELECT, INSERT, UPDATE, etc.) known as clauses which allow us to perform different tasks. In this article we’re going to cover two more important aspects of SQL queries: Functions and Operators.
Functions are a very common concept in programming that you might hear being used quite often. Functions make redundant, complicated tasks simple and easy to execute. If something takes 100 lines of code to implement, a function can repeat the purpose of that code segment with only one line. They clean up your code and make things efficient. The task a function performs only needs to be written out once. From then on, you simply reference the function to execute the same task.
SQL has an arsenal of functions that make your life easier. In some versions of SQL, you can even make your own functions. In SQL, self-made functions are known as procedures.
Operators essentially allow us to make our queries work on certain conditions. In normal programming, operators include mathematical arithmetic as well. In SQL, that arithmetic is rarely encountered and instead makes use of conditions mostly. The most commonly encountered conditional operators are called AND and OR. They allow us to add more constraints to our queries.
When we combine clauses, functions, and operators together, we can produce incredibly powerful queries. Let’s now examine the most useful SQL Functions, Clauses, and Operators you should know.
NOTE: SQLite can be used for the below examples.
In the previous articles, we discussed the power of the WHERE clause and how it allows us to produce results based on specific conditions. In those examples, we had only one WHERE clause in every query.
With the AND and OR operators, we can have an infinite number of conditions added to every query. These operators work just like they do in the English language. When we use the word ‘and’, we mean to include everything. ‘Or’ means one or the other. The conditions can be made more complex with the use of parentheses.
When an AND operator is used, the results produced from the query make sure that all conditions are satisfied. If we are to add 2 different WHERE clauses separated by the AND operator, the results produced will satisfy both conditions. If one of them is false for a certain row, it won’t show up in the results. All of them need to be true.
In the following query, the store name doesn’t exist and no results were given
In the case of the OR operator, the results produced from the query make sure that at least one of the conditions is true. If we had, again, 2 different WHERE clauses separated with the OR operator, the results produced will satisfy at least one of the conditions. Both the conditions can be true as well.
Here, both conditions are false and nothing shows.
AS is a clause that allows our results to bear different names. When we normally select columns in our queries, they show exactly as they exist in the database. With AS, we can assign aliases to columns to give more clarity. A good use case of this clause is to explain the meaning of the results to someone who isn’t familiar with your database structure. In this case, we’ll change the ‘Item’ column to ‘Item_count’.
CASE-WHEN is a clause which works based on a set of conditions. Through this clause, we can assign different results to each row that is produced in the report. The results are declared in a text format and are used to convey some information about the specific row. The syntax looks like:
SELECT *
CASE WHEN condition-A THEN ‘X’
WHEN condition-B THEN ‘Y’
ELSE ‘Z’
END AS column name
FROM table name;
CASE-WHEN is SQL’s version of an IF-THEN statement. If condition A is true, then show X. If condition B is true, then show Y. If neither is true, then show Z. The placeholders for ‘WHEN condition’ are essentially WHERE clauses with a different name. Every row will be inspected and each condition will be in an interactive manner. Whenever a true condition is found, the other cases will never get checked. That means if condition-A is satisfied, condition-B will never get checked. Likewise, if condition-B is true, that means condition-A was false for this row. If none of the conditions are true, the text after ELSE will be inserted in the new column.
BETWEEN is an operator that selects values based on the range given. The data types that can be included in a range are numbers, dates, and text. It’s a simple way to test whether certain values belong in a given range (inclusive of starting and ending values) and produce results accordingly. The syntax looks like:
SELECT + column1 name + column2 name + … + FROM table name + WHERE + column name + BETWEEN + value1 + AND + value2;
The following examples show queries being run for the above-mentioned data types.
![SQLite program with the code "SELECT dateissued, totalprice FROM Receipts WHERE dateissued BETWEEN [date 1] AND [date 2]" with table of Dateissued and Totalprice underneath](https://assets-global.website-files.com/62b4c5fb2654ca30abd9b38f/62b4c5fb2654ca6e45d9bdb0_5ee11dd2754b381351a97560_cZCh_BBB619DBBGz_pBzI2bDDz-1LoxHaLH3XFPH0BFZb20ENwe3ngcLDidXZVXgX0RgemmQpTWepEju5czdUmaBvZJlbZ4NGrVgCf5A0shhMlGNxiWNh-MBfQzuNEd-C0r5z472.png)
An operator called NOT can also be inserted with BETWEEN so that everything outside the specified range is selected. Here is an example of the NOT operator being added into the query.
The ORDER BY clause assists in organizing our reports either in ascending or descending order. When the order is not specified, ascending is chosen by default. We can order our data on the basis of numbers, dates, and text. The syntax looks like:
SELECT + column1 name + column2 name + … + FROM table name + ORDER BY + column name ASC| DESC;
The ASC keyword is for ascending and DESC is for descending. Example query:
In this query, we use the DESC keyword.
We can also order our results based on several columns. This is useful if rows can have the same values for one column, but still need to be prioritized.
The next query shows multiple columns being ordered with DESC.
We can also sort individual columns with ASC or DESC in one query.
MAX and MIN are functions that return us the maximum and minimum values of the specified columns respectively.
These functions can be used with numbers, dates, and text. The concept of the maximum value from text is complicated, so we won’t cover it. If you’d like, you can read about it here. The syntax looks like:
SELECT + MAX/MIN(column name) + FROM + table name;
Now, here's a sample query with MAX on a numeric column:
Example with a date data type:
Example with MIN on a numeric value:
Query with MIN applied to a date column:
When dealing with a large number of rows, you run the risk of slowing your system if your reports are too big. In the worst case, you can freeze it too. To cater to that, the LIMIT clause restricts the number of rows you want to be displayed. The syntax looks like:
SELECT + column1 name + column2 name + ... + FROM table name + LIMIT + number;
The ‘number’ placeholder will be replaced by a numeric value. The number you choose will determine the number of rows that will be displayed. If you choose 3, only the first 3 rows will be displayed.
You can also choose to skip any number of rows before displaying the chosen amount. To do that, simply add ‘, number’ to the above query. We skip the first row and select the next 3 here.
COUNT is a function that returns the number of rows in a table based on the specified conditions. If no conditions are given, a count of all the rows will be provided. The syntax looks like:
SELECT + COUNT(*) + FROM + table name;
The above query will return the count of all the rows in the table with no conditions:
In order to know the count of unique, non-empty values in a column, include DISTINCT in the query: SELECT COUNT(DISTINCT column name) FROM table name.
Before we can understand the HAVING clause, we need to understand the GROUP BY clause. To recap on its usage, visit the [SQL 101 article] (link). When we’ve successfully made groups of similar values, we can use the HAVING clause to filter them. We used the WHERE clause to filter rows and we use the HAVING clause to filter groups made with GROUP BY. An important point to remember is the HAVING clause cannot be used without GROUP BY. The syntax looks like:
SELECT + COUNT(column1 name) + column1 name + FROM + table name + GROUP BY + column1 name + HAVING condition;
SUM and AVG provide the sum and average of columns, respectively. They can either be used independently or with the GROUP BY clause to give more meaningful insights. SUM and AVG only work with columns of numbers, not text. The syntax looks like:
SELECT + SUM/AVG (column name) + FROM + table name;
This basic syntax will net you the sum or average of a column:
To include GROUP BY into the query, use the following syntax: SELECT + SUM/AVG (column1 name) + column2 name + … + FROM + table name + GROUP BY + column2 name:
We’ve just barely scratched the surface of all the tools available in SQL. Once you practice the above, you’ll be able to produce more complex queries very easily. There are also many optional constraints that you can add to these clauses. Covering all of them would take a book.
If you’re interested in learning in more detail about SQLite, check out the following links:
