Introduction
SQL (Structured Query Language) is essential for managing and manipulating relational databases. Among its core functionalities are the various operators that perform arithmetic calculations, comparisons, and logical operations. This article elaborates on these operators, providing examples with expected outputs.
1. Arithmetic Operators
Arithmetic operators perform mathematical operations on numeric data.
SUM
The SUM function returns the total sum of a numeric column.
SELECT SUM(salary) AS total_salary FROM employees;
-- Expected output: total_salary
-- -------------
-- 5000000
AVG
The AVG function returns the average value of a numeric column.
SELECT AVG(salary) AS average_salary FROM employees;
-- Expected output: average_salary
-- ---------------
-- 75000
MIN
The MIN function returns the smallest value in a column.
SELECT MIN(salary) AS min_salary FROM employees;
-- Expected output: min_salary
-- -----------
-- 30000
MAX
The MAX function returns the largest value in a column.
SELECT MAX(salary) AS max_salary FROM employees;
-- Expected output: max_salary
-- -----------
-- 120000
COUNT
The COUNT function returns the number of rows that match a specified criterion.
SELECT COUNT(*) AS sales_count FROM employees WHERE department = 'Sales';
-- Expected output: sales_count
-- ------------
-- 15
Basic Arithmetic Operators
-
+(Addition): Adds two numbers. -
-(Subtraction): Subtracts the second number from the first. -
*(Multiplication): Multiplies two numbers. -
/(Division): Divides the first number by the second. -
%(Modulus): Returns the remainder of a division operation.
SELECT salary + 500 AS increased_salary FROM employees;
-- Expected output: increased_salary
-- -----------------
-- 30500
-- 50500
-- ...
SELECT salary - 500 AS decreased_salary FROM employees;
-- Expected output: decreased_salary
-- -----------------
-- 29500
-- 49500
-- ...
SELECT salary * 1.1 AS new_salary FROM employees;
-- Expected output: new_salary
-- -----------
-- 33000
-- 55000
-- ...
SELECT salary / 2 AS half_salary FROM employees;
-- Expected output: half_salary
-- ------------
-- 15000
-- 25000
-- ...
SELECT salary % 100 AS remainder FROM employees;
-- Expected output: remainder
-- ---------
-- 0
-- 0
-- ...
2. Comparison Operators
Comparison operators compare two values, returning true or false.
=
Equals. Checks if the values of two operands are equal.
SELECT * FROM employees WHERE salary = 50000;
-- Expected output: id | name | department | salary
-- --------------------------------
-- 2 | Alice | HR | 50000
>
Greater than. Checks if the value of the left operand is greater than the value of the right operand.
SELECT * FROM employees WHERE salary > 50000;
-- Expected output: id | name | department | salary
-- ---------------------------------
-- 3 | Bob | IT | 60000
-- 4 | Carol | Marketing | 70000
-- ...
<
Less than. Checks if the value of the left operand is less than the value of the right operand.
SELECT * FROM employees WHERE salary < 50000;
-- Expected output: id | name | department | salary
-- --------------------------------
-- 1 | John | Sales | 30000
>=
Greater than or equal to. Checks if the value of the left operand is greater than or equal to the value of the right operand.
SELECT * FROM employees WHERE salary >= 50000;
-- Expected output: id | name | department | salary
-- ---------------------------------
-- 2 | Alice | HR | 50000
-- 3 | Bob | IT | 60000
-- ...
<=
Less than or equal to. Checks if the value of the left operand is less than or equal to the value of the right operand.
SELECT * FROM employees WHERE salary <= 50000;
-- Expected output: id | name | department | salary
-- --------------------------------
-- 1 | John | Sales | 30000
-- 2 | Alice | HR | 50000
<> or !=
Not equal to. Checks if the values of two operands are not equal.
SELECT * FROM employees WHERE salary <> 50000;
-- Expected output: id | name | department | salary
-- ---------------------------------
-- 1 | John | Sales | 30000
-- 3 | Bob | IT | 60000
-- ...
SELECT * FROM employees WHERE salary != 50000;
-- Expected output: id | name | department | salary
-- ---------------------------------
-- 1 | John | Sales | 30000
-- 3 | Bob | IT | 60000
-- ...
3. Logical Operators
Logical operators combine two or more conditions in a SQL statement.
AND
Combines two conditions and returns true if both conditions are true.
SELECT * FROM employees WHERE department = 'Sales' AND salary > 50000;
-- Expected output: id | name | department | salary
-- --------------------------------
-- 5 | Dave | Sales | 60000
OR
Combines two conditions and returns true if either condition is true.
SELECT * FROM employees WHERE department = 'Sales' OR department = 'Marketing';
-- Expected output: id | name | department | salary
-- ---------------------------------
-- 1 | John | Sales | 30000
-- 4 | Carol | Marketing | 70000
NOT
Negates a condition, returning true if the condition is false.
SELECT * FROM employees WHERE NOT department = 'Sales';
-- Expected output: id | name | department | salary
-- ---------------------------------
-- 2 | Alice | HR | 50000
-- 3 | Bob | IT | 60000
-- 4 | Carol | Marketing | 70000
BETWEEN ... AND
Selects values within a given range. The range includes the end values.
SELECT * FROM employees WHERE salary BETWEEN 30000 AND 50000;
-- Expected output: id | name | department | salary
-- --------------------------------
-- 1 | John | Sales | 30000
-- 2 | Alice | HR | 50000
IN
Checks if a value is within a set of values.
SELECT * FROM employees WHERE country IN ('Nigeria', 'Ghana', 'Togo', 'Cameroun');
-- Expected output: id | name | department | country
-- -----------------------------------
-- 1 | John | Sales | Nigeria
-- 6 | Emma | HR | Ghana
LIKE
Searches for a specified pattern in a column. Commonly used with wildcards:
-
%: Represents zero or more characters. -
_: Represents a single character.
SELECT * FROM employees WHERE name LIKE '%jo%';
-- Expected output: id | name | department | salary
-- ---------------------------------
-- 1 | John | Sales | 30000
-- 7 | Johnson| IT | 80000
SELECT * FROM employees WHERE name LIKE 'A%';
-- Expected output: id | name | department | salary
-- ---------------------------------
-- 2 | Alice | HR | 50000
-- 8 | Andrew | Marketing | 55000
SELECT * FROM employees WHERE name LIKE '%Keyword';
-- Expected output: id | name | department | salary
-- -------------------------------------
-- 9 | JaneKeyword| Marketing | 60000
Check out this special post I wrote on the LIKE operator
IS NULL / IS NOT NULL
Checks if a column is null or not null.
SELECT * FROM employees WHERE manager_id IS NULL;
-- Expected output: id | name | department | salary
-- --------------------------------
-- 1 | John | Sales | 30000
-- 5 | Dave | Sales | 60000
SELECT * FROM employees WHERE manager_id IS NOT NULL;
-- Expected output: id | name | department | salary
-- ---------------------------------
-- 2 | Alice | HR | 50000
-- 3 | Bob | IT | 60000
4. WHERE Clause
The WHERE clause is used to filter records based on specified conditions.
SELECT * FROM employees WHERE department = 'Sales';
-- Expected output: id | name | department | salary
-- --------------------------------
-- 1
| John | Sales | 30000
-- 5 | Dave | Sales | 60000
SELECT * FROM employees WHERE salary > 50000 AND department = 'Sales';
-- Expected output: id | name | department | salary
-- --------------------------------
-- 5 | Dave | Sales | 60000
Conclusion
SQL operators enable powerful manipulation and retrieval of data. Mastering these operators—arithmetic, comparison, and logical—allows for sophisticated database queries and efficient data management. Each example provided includes expected output to illustrate the practical application of these operators in SQL.
PS:
I love coffee, and writing these articles takes a lot of it! If you enjoy my content and would like to support my work, you can buy me a cup of coffee. Your support helps me to keep writing great content and stay energized. Thank you for your kindness!
Buy Me A Coffee.
Top comments (0)