Section 4: SQL Functions

In this section, we'll explore SQL functions that provide powerful tools for data manipulation, including aggregate functions for summarizing data, string functions for text manipulation, and date and time functions for handling temporal data.

Aggregate Functions

Aggregate functions perform operations on a set of values and return a single value. They are often used with the GROUP BY clause to summarize data in groups.

Common Aggregate Functions (SUM, AVG, COUNT, MIN, MAX)

Let's consider an example where we use these aggregate functions on a hypothetical sales table:

-- Calculate the total sales

SELECT SUM(amount) AS total_sales

FROM sales;

-- Calculate the average sales amount

SELECT AVG(amount) AS average_sales

FROM sales;

-- Count the number of sales transactions

SELECT COUNT(*) AS transaction_count

FROM sales;

-- Find the minimum and maximum sales amounts

SELECT MIN(amount) AS min_sales, MAX(amount) AS max_sales

FROM sales;

These queries showcase how aggregate functions can provide valuable insights into the summarized data.

String Functions

String functions allow manipulation and processing of text data within SQL queries.

Manipulating Text Data with Functions (CONCAT, SUBSTRING, LENGTH)

-- Concatenate first and last names

SELECT CONCAT(first_name, ' ', last_name) AS full_name

FROM employees;

-- Extract a substring from a column

SELECT SUBSTRING(product_name, 1, 5) AS short_name

FROM products;

-- Determine the length of a text column

SELECT product_name, LENGTH(product_name) AS name_length

FROM products;

These examples demonstrate how to concatenate strings, extract substrings, and calculate string lengths.

Changing Case with UPPER and LOWER

-- Convert product names to uppercase

SELECT product_name, UPPER(product_name) AS uppercase_name

FROM products;

-- Convert employee last names to lowercase

SELECT last_name, LOWER(last_name) AS lowercase_name

FROM employees;

These queries illustrate how to change the case of text data using the UPPER and LOWER functions.

Date and Time Functions

Date and time functions help with various operations related to temporal data.

Working with Date and Time Data

-- Retrieve the current date and time

SELECT CURRENT_DATE AS current_date, CURRENT_TIME AS current_time;

-- Extract the year and month from a date column

SELECT order_date, EXTRACT(YEAR FROM order_date) AS order_year, EXTRACT(MONTH FROM order_date) AS order_month

FROM orders;

-- Calculate the difference between two dates

SELECT start_date, end_date, (end_date - start_date) AS date_difference

FROM projects;

These examples showcase the use of date and time functions for various scenarios.

SQL functions are powerful tools for manipulating and analyzing data within your database. Understanding how to leverage aggregate functions, string functions, and date and time functions will enhance your ability to extract valuable insights from your datasets. Keep practicing to reinforce your skills in using these functions effectively.