SQL, or Structured Query Language, is a programming language used to manage and manipulate data stored in relational database management systems (RDBMS). It is the standard language for interacting with databases and is used by a wide range of applications and systems.
One of the most useful features of SQL is the ability to perform various functions on data stored in a database. These functions can be used to perform a wide range of tasks, including calculating values, formatting data, and searching for specific information.
In this blog, we will take a look at some of the basic SQL functions that are commonly used in database management.
Note: We will be using the superstore dataset found in tableau for the purposes of the examples with the coding being done on a snowflake worksheet.
- Aggregate functions
Aggregate functions are used to perform calculations on a set of values and return a single result. Some of the most common aggregate functions include:
- AVG: calculates the average value of a set of values
- COUNT: counts the number of rows in a table
- MAX: returns the maximum value in a set of values
- MIN: returns the minimum value in a set of values
- SUM: calculates the sum of a set of values
For example, you can use the AVG function to calculate the average profit of all products in a table:
Results:
- String functions
String functions are used to manipulate and format string data in a database. Some of the most common string functions include:
- CONCAT: combines two or more strings into a single string
- LENGTH: returns the number of characters in a string
- LOWER: converts a string to lowercase
- UPPER: converts a string to uppercase
- SUBSTR: extracts a specific portion of a string
For example, you can use the LOWER function to convert the names of all categories to lowercase:
Results:
2. Date and time functions
Date and time functions are used to manipulate and format date and time data in a database. Some of the most common date and time functions include:
- CURDATE: returns the current date
- CURTIME: returns the current time
- NOW: returns the current date and time
- MONTH: returns the month of a given date
- YEAR: returns the year of a given date
- GETDATE: returns the current system date and time
For example, you can use the GETDATE function to return the difference in days between the order date and todays date - we can also order this by the difference in days.
Results:
3. Join functions
SQL joins are used to combine rows from two or more tables, based on a common field between them.
- INNER JOIN: Returns all rows when there is at least one match in BOTH tables
- LEFT JOIN: Return all rows from the left table, and the matched rows from the right table
- RIGHT JOIN: Return all rows from the right table, and the matched rows from the left table
- FULL JOIN: Return all rows when there is a match in ONE of the tables
- The UNION operator is used to combine the result-set of two or more SELECT statements. Notice that each SELECT statement within the UNION must have the same number of columns. The columns must also have similar data types. Also, the columns in each SELECT statement must be in the same order.
For example, you can use the INNER JOIN function to return the totals sales per person sorted by sales in descending order with sales rounded to the nearest whole number.
Results:
These are just a few examples of the basic SQL functions that can be used. With the right functions, you can manipulate and analyze data in your database with ease.