SQL Operators

SQL Operators

SQL operators are used to perform various operations on data stored in a database. They are used to specify conditions in SQL statements and to manipulate data.

Operators are categorized based on their functionality. Below, we provide tables for each category of SQL operators, including the operator symbol, name, and description. Following each table, we present examples demonstrating the use of each operator.

---

1. Arithmetic Operators

Operator	Name	Description
+	Addition	Adds two numbers.
-	Subtraction	Subtracts the right operand from the left operand.
*	Multiplication	Multiplies two numbers.
/	Division	Divides the left operand by the right operand.
%	Modulus	Returns the remainder of a division operation.

Examples:

Assume we have a table named Products with the following columns:

CREATE TABLE Products (
    ProductID INT,
    ProductName VARCHAR(50),
    Price DECIMAL(10, 2),
    Quantity INT
);

Sample data:

INSERT INTO Products (ProductID, ProductName, Price, Quantity) VALUES
(1, 'Laptop', 800.00, 10),
(2, 'Smartphone', 500.00, 20),
(3, 'Tablet', 300.00, 15);

a) Addition (+): Calculate the total value of all products.

SELECT ProductName, Price * Quantity AS TotalValue
FROM Products;

b) Subtraction (-): Reduce the price of all products by $50.

SELECT ProductName, Price - 50 AS DiscountedPrice
FROM Products;

c) Multiplication (*): Calculate the total revenue if all products are sold.

SELECT SUM(Price * Quantity) AS TotalRevenue
FROM Products;

d) Division (/): Calculate the average price per product.

SELECT AVG(Price) AS AveragePrice
FROM Products;

e) Modulus (%): Find products with an odd quantity.

SELECT ProductName
FROM Products
WHERE Quantity % 2 <> 0;

---

2. Comparison Operators

Operator	Name	Description
=	Equal to	Checks if the values of two operands are equal.
>	Greater than	Checks if the value of the left operand is greater than the right operand.
<	Less than	Checks if the value of the left operand is less than the right operand.
>=	Greater than or equal to	Checks if the value of the left operand is greater than or equal to the right operand.
<=	Less than or equal to	Checks if the value of the left operand is less than or equal to the right operand.
<>	Not equal to	Checks if the values of two operands are not equal.

Examples:

Using the same Products table:

a) Equal to (=): Find products priced at $500.

SELECT ProductName
FROM Products
WHERE Price = 500.00;

b) Greater than (>): Find products priced above $500.

SELECT ProductName 
FROM Products 
WHERE Price > 500.00;

c) Less than (<): Find products priced below $500.

SELECT ProductName 
FROM Products 
WHERE Price < 500.00;

d) Greater than or equal to (>=): Find products priced at least $500.

SELECT ProductName 
FROM Products 
WHERE Price >= 500.00;

e) Less than or equal to (<=): Find products priced at most $500.

SELECT ProductName 
FROM Products 
WHERE Price <= 500.00;

f) Not equal to (<>): Find products that are not priced at $500.

SELECT ProductName 
FROM Products 
WHERE Price <> 500.00;

---

3. Logical Operators

Operator	Name	Description
AND	Logical AND	Returns true if both conditions are true.
OR	Logical OR	Returns true if at least one condition is true.
NOT	Logical NOT	Reverses the result of a condition.

Examples:

a) AND Operator: Find products that are priced above $300 and have a quantity greater than 10.

SELECT ProductName 
FROM Products 
WHERE Price > 300 AND Quantity > 10;

b) OR Operator: Find products that are priced above $700 or have a quantity less than 15.

SELECT ProductName 
FROM Products 
WHERE Price > 700 OR Quantity < 15;

c) NOT Operator: Find products that are not priced at $800.

SELECT ProductName 
FROM Products 
WHERE NOT Price = 800.00;

---

4. Other SQL Operators

Operator	Name	Description
IN	Set Membership	Checks if a value is present in a list of values.
BETWEEN	Range Comparison	Checks if a value falls within a specified range.
LIKE	Pattern Matching	Performs a search for a specified pattern in a column.
IS NULL	NULL Check	Checks if a column has a NULL value.

Examples:

a) IN Operator: Find products with prices of either $300 or $800.

SELECT ProductName 
FROM Products 
WHERE Price IN (300.00, 800.00);

b) BETWEEN Operator: Find products priced between $300 and $800.

SELECT ProductName 
FROM Products 
WHERE Price BETWEEN 300.00 AND 800.00;

c) LIKE Operator: Find products that start with the letter ‘S’.

SELECT ProductName 
FROM Products 
WHERE ProductName LIKE 'S%';

d) IS NULL Operator: Find products that have no price specified.

SELECT ProductName 
FROM Products 
WHERE Price IS NULL;

---

Conclusion

SQL operators are used to manipulate and filter data within databases. In this tutorial, we covered:

• Arithmetic Operators: Perform mathematical calculations.
• Comparison Operators: Compare values.
• Logical Operators: Combine multiple conditions.
• Other SQL Operators: Pattern matching, range checking, and NULL value handling.