SQL Operators and Functions: A Comprehensive Guide

Structured Query Language (SQL) is essential for interacting with relational databases. Two critical aspects that enhance SQL’s power are operators and functions. These allow developers to manipulate data, perform calculations, and execute complex queries efficiently. In this article, we’ll dive into the different types of SQL operators and functions, providing detailed explanations, examples, and use cases.

Introduction to SQL Operators
Types of SQL Operators
Introduction to SQL Functions
Types of SQL Functions
Practical Examples
Conclusion

Introduction to SQL Operators

SQL Operators are special symbols or keywords used to perform operations on one or more operands in SQL queries. They are used to manipulate data values and control the flow of query logic. Operators are categorized based on their functionality, such as arithmetic calculations, comparisons, and logical conditions.

Types of SQL Operators

1. Arithmetic Operators

Arithmetic operators are used for basic mathematical operations.

Operator	Description	Example	Result
`+`	Addition	`SELECT 5 + 3;`	`8`
`-`	Subtraction	`SELECT 10 - 4;`	`6`
`*`	Multiplication	`SELECT 6 * 2;`	`12`
`/`	Division	`SELECT 9 / 3;`	`3`
`%`	Modulus (Remainder)	`SELECT 10 % 3;`	`1`

2. Comparison Operators

These operators are used to compare values in SQL queries.

Operator	Description	Example	Result
`=`	Equal to	`SELECT 5 = 5;`	`TRUE`
`!=`	Not equal to	`SELECT 5 != 3;`	`TRUE`
`>`	Greater than	`SELECT 10 > 5;`	`TRUE`
`<`	Less than	`SELECT 2 < 5;`	`TRUE`
`>=`	Greater than or equal to	`SELECT 5 >= 5;`	`TRUE`
`<=`	Less than or equal to	`SELECT 3 <= 7;`	`TRUE`

3. Logical Operators

Logical operators are used to combine multiple conditions in SQL queries.

Operator	Description	Example	Result
`AND`	Returns TRUE if all conditions are TRUE	`SELECT TRUE AND FALSE;`	`FALSE`
`OR`	Returns TRUE if any condition is TRUE	`SELECT TRUE OR FALSE;`	`TRUE`
`NOT`	Reverses the result of a condition	`SELECT NOT TRUE;`	`FALSE`

4. Bitwise Operators

Bitwise operators perform bit manipulation.

Operator	Description	Example	Result
`&`	AND	`SELECT 5 & 3;`	`1`
`	`	OR	`SELECT 5
`^`	XOR	`SELECT 5 ^ 3;`	`6`

Introduction to SQL Functions

SQL Functions are built-in methods that perform operations on data values and return a single result. They are categorized into several types based on their usage.

Types of SQL Functions

1. Aggregate Functions

Aggregate functions are used to perform calculations on multiple rows of data.

Function	Description	Example	Result
`SUM()`	Calculates total	`SELECT SUM(salary) FROM employees;`	Total salary
`AVG()`	Calculates average	`SELECT AVG(salary) FROM employees;`	Average salary
`COUNT()`	Counts rows	`SELECT COUNT(*) FROM employees;`	Number of employees
`MAX()`	Finds max value	`SELECT MAX(salary) FROM employees;`	Highest salary
`MIN()`	Finds min value	`SELECT MIN(salary) FROM employees;`	Lowest salary

2. String Functions

String functions manipulate text data.

Function	Description	Example	Result
`CONCAT()`	Concatenates strings	`SELECT CONCAT('SQL', ' ', 'Guide');`	`SQL Guide`
`LENGTH()`	Returns length of string	`SELECT LENGTH('SQL');`	`3`
`UPPER()`	Converts to uppercase	`SELECT UPPER('sql');`	`SQL`
`LOWER()`	Converts to lowercase	`SELECT LOWER('SQL');`	`sql`

3. Date and Time Functions

Date functions handle date values.

Function	Description	Example	Result
`NOW()`	Returns current date and time	`SELECT NOW();`	Current date/time
`CURDATE()`	Returns current date	`SELECT CURDATE();`	Current date
`DATEDIFF()`	Returns difference in days	`SELECT DATEDIFF('2024-12-31', '2024-01-01');`	`364`

4. Numeric Functions

Numeric functions perform operations on numeric data.

Function	Description	Example	Result
`ROUND()`	Rounds a number	`SELECT ROUND(123.456, 2);`	`123.46`
`ABS()`	Returns absolute value	`SELECT ABS(-10);`	`10`

Practical Examples


-- Using Comparison and Logical Operators  
SELECT * FROM employees WHERE salary > 50000 AND department = 'IT';  

-- Using Aggregate Function  
SELECT department, COUNT(*) FROM employees GROUP BY department;  

-- Using String and Date Functions  
SELECT CONCAT(first_name, ' ', last_name), NOW() FROM employees;

Conclusion

SQL operators and functions are indispensable for writing efficient queries and managing data. By mastering these tools, you can enhance your SQL skills and streamline database operations. For more detailed programming articles, visit AJ Tech Blog.

Related Articles: