Basic Functions and Calculations

Ever wondered how to perform calculations or manipulate data directly in SQL? Today, we're going to explore basic functions and calculations that can make your queries more powerful.

Table of Contents

1. Arithmetic Operations
2. String Functions
3. Date and Time Functions
4. CASE Statement

Arithmetic Operations

Let's start with something simple: arithmetic operations. You can perform calculations right in your queries.

Adding, Subtracting, Multiplying, Dividing

SQL supports basic arithmetic operators:

• + Addition
• - Subtraction
• * Multiplication
• / Division

Example: Calculate the total price by multiplying quantity and unit price.

SELECT ProductName, Quantity, UnitPrice, Quantity * UnitPrice AS TotalPrice
FROM OrderDetails;

String Functions

Need to manipulate text data? SQL has got you covered with a variety of string functions.

Common String Functions

• CONCAT(string1, string2, ...): Combines multiple strings into one.
• UPPER(string): Converts a string to uppercase.
• LOWER(string): Converts a string to lowercase.
• LENGTH(string): Returns the length of a string.
• SUBSTRING(string, start, length): Extracts a substring from a string.

Example: Combine first and last names into a full name.

SELECT CONCAT(FirstName, ' ', LastName) AS FullName
FROM Employees;

Date and Time Functions

Working with dates and times can be tricky. But SQL provides functions to make it easier.

Common Date and Time Functions

• NOW(): Returns the current date and time.
• CURDATE(): Returns the current date.
• YEAR(date): Extracts the year from a date.
• MONTH(date): Extracts the month from a date.
• DAY(date): Extracts the day from a date.
• DATEDIFF(date1, date2): Calculates the difference between two dates.

Example: Find out how many days have passed since each order was placed.

SELECT OrderID, OrderDate, DATEDIFF(NOW(), OrderDate) AS DaysSinceOrder
FROM Orders;

CASE Statement

Sometimes, you need to perform conditional logic in your queries. That's where the CASE statement comes in handy.

Using CASE

The CASE statement allows you to create if-then-else logic directly in SQL.

SELECT column1,
    CASE
        WHEN condition1 THEN result1
        WHEN condition2 THEN result2
        ELSE result3
    END AS AliasName
FROM table_name;

Example: Categorize products based on their price.

SELECT ProductName, Price,
    CASE
        WHEN Price >= 100 THEN 'Expensive'
        WHEN Price >= 50 THEN 'Moderate'
        ELSE 'Affordable'
    END AS PriceCategory
FROM Products;