SQL Expressions

What are SQL Expressions?

SQL expressions are combinations of one or more values, operators, and SQL functions that evaluate to a single value. These expressions are used to retrieve, filter, and manipulate data stored in relational databases.

SQL expressions can be categorized into various types, including:

• Arithmetic Expressions
• String Expressions
• Comparison Expressions
• Logical Expressions
• Aggregate Expressions

1. Arithmetic Expressions

Arithmetic expressions involve mathematical calculations using operators like +, -, *, and /.

Syntax:

SELECT column_name, arithmetic_operation AS alias_name FROM table_name;

Example:

SELECT first_name, salary, salary * 1.10 AS increased_salary FROM employees;

Explanation: This query calculates the total cost by multiplying the price and quantity columns.

2. String Expressions

String expressions involve operations like concatenation, substring extraction, and case conversion.

Syntax:

SELECT CONCAT(string1, string2) FROM table_name;

Example:

SELECT first_name, last_name,  
       CONCAT(first_name, ' ', last_name) AS full_name  
FROM employees;
    

Explanation: This query merges first_name and last_name with a space in between.

3. Date and Time Expressions

Date and time expressions extract or modify date and time values.

Syntax:

SELECT function_name(date_column) FROM table_name;

Example:

SELECT order_id, order_date,  
       DATE_FORMAT(order_date, '%Y-%m-%d') AS formatted_date  
FROM orders;
    

Explanation: This query formats the order_date column to display in the YYYY-MM-DD format.

4. Boolean Expressions

Boolean expressions return TRUE or FALSE based on a condition.

Syntax:

SELECT column_name FROM table_name WHERE condition;

Example:

SELECT product_name, price  
FROM products  
WHERE price > 100;
    

Explanation: This query retrieves products where the price is greater than 100.

5. Aggregate Expressions

Aggregate expressions apply calculations on groups of rows using aggregate functions.

Syntax:

SELECT aggregate_function(column_name) FROM table_name;

Example:

SELECT department, AVG(salary) AS avg_salary  
FROM employees  
GROUP BY department;
    

Explanation: This query computes the average salary for each department.

Conclusion

SQL expressions enhance query flexibility by enabling calculations, transformations, and conditions. Understanding and utilizing them effectively allows for more powerful and dynamic data manipulation.