WikiGalaxy
SQL Arithmetic Operators
Addition (+):
The addition operator adds two numbers. It is used in SQL to calculate the sum of two numeric columns or expressions.
Subtraction (-):
The subtraction operator subtracts one number from another. It is used to find the difference between two numeric columns or expressions.
Multiplication (*):
The multiplication operator multiplies two numbers. It is used to calculate the product of two numeric columns or expressions.
Division (/):
The division operator divides one number by another. It is used to calculate the quotient of two numeric columns or expressions.
SELECT salary + bonus AS TotalIncome FROM employees;
SELECT price - discount AS SalePrice FROM products;
SELECT quantity * price AS TotalCost FROM sales;
SELECT total / count AS Average FROM statistics;
Practical Use Cases:
Arithmetic operations are crucial for financial calculations, statistical analysis, and inventory management in SQL databases.
Console Output:
TotalIncome: 75000
SalePrice: 299.99
TotalCost: 1500
Average: 500
SQL Comparison Operators
Equal (=):
The equal operator is used to compare two values and returns true if they are equal.
Not Equal (<>):
The not equal operator is used to compare two values and returns true if they are not equal.
Greater Than (>):
The greater than operator is used to compare two values and returns true if the left operand is greater than the right operand.
Less Than (<):
The less than operator is used to compare two values and returns true if the left operand is less than the right operand.
SELECT * FROM employees WHERE salary = 50000;
SELECT * FROM products WHERE price <> 100;
SELECT * FROM orders WHERE quantity > 10;
SELECT * FROM customers WHERE age < 30;
Practical Use Cases:
Comparison operators are essential for filtering data, validating conditions, and performing queries based on specific criteria.
Console Output:
Employee: John Doe
Product: Widget A
Order: #12345
Customer: Jane Smith
SQL Logical Operators
AND:
The AND operator is used to combine multiple conditions in a SQL query and returns true if all conditions are true.
OR:
The OR operator is used to combine multiple conditions in a SQL query and returns true if at least one condition is true.
NOT:
The NOT operator is used to negate a condition in a SQL query and returns true if the condition is false.
SELECT * FROM employees WHERE department = 'Sales' AND salary > 50000;
SELECT * FROM products WHERE category = 'Electronics' OR category = 'Appliances';
SELECT * FROM orders WHERE NOT status = 'Shipped';
Practical Use Cases:
Logical operators are vital for constructing complex queries that require multiple conditions to be checked simultaneously.
Console Output:
Employee: Alice Brown
Product: Laptop
Order: #67890
SQL IN Operator
IN Operator:
The IN operator allows you to specify multiple values in a WHERE clause. It is a shorthand for multiple OR conditions.
SELECT * FROM employees WHERE department IN ('HR', 'Finance', 'IT');
SELECT * FROM products WHERE category IN ('Books', 'Toys', 'Games');
Practical Use Cases:
The IN operator is useful for filtering data based on a list of potential matches, making queries cleaner and more efficient.
Console Output:
Department: HR
Category: Books
SQL BETWEEN Operator
BETWEEN Operator:
The BETWEEN operator selects values within a given range. It is inclusive, meaning the start and end values are included in the result set.
SELECT * FROM employees WHERE salary BETWEEN 40000 AND 60000;
SELECT * FROM products WHERE price BETWEEN 100 AND 500;
Practical Use Cases:
The BETWEEN operator is excellent for filtering data based on ranges, such as dates, prices, or quantities.
Console Output:
Salary: 55000
Price: 250
SQL LIKE Operator
LIKE Operator:
The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.
SELECT * FROM employees WHERE name LIKE 'J%';
SELECT * FROM products WHERE description LIKE '%laptop%';
Practical Use Cases:
The LIKE operator is useful for pattern matching, allowing searches for partial matches in text fields.
Console Output:
Name: John
Description: High-performance laptop
SQL IS NULL Operator
IS NULL Operator:
The IS NULL operator is used to test for empty values (NULL).
SELECT * FROM employees WHERE manager_id IS NULL;
SELECT * FROM products WHERE discontinued IS NULL;
Practical Use Cases:
The IS NULL operator is critical for identifying records with missing information, ensuring data completeness and integrity.
Console Output:
Manager ID: NULL
Discontinued: NULL
SQL EXISTS Operator
EXISTS Operator:
The EXISTS operator is used to test for the existence of any record in a subquery. It returns true if the subquery returns one or more records.
SELECT * FROM employees WHERE EXISTS (SELECT 1 FROM departments WHERE departments.manager_id = employees.id);
Practical Use Cases:
The EXISTS operator is useful for checking the presence of related records, ensuring referential integrity in complex queries.
Console Output:
Employee: Exists in Department
SQL ALL & ANY Operators
ALL Operator:
The ALL operator is used to compare a value to all values in another value set or result from a subquery.
ANY Operator:
The ANY operator is used to compare a value to any value in a list or subquery. It returns true if any of the subquery values meet the condition.
SELECT * FROM products WHERE price > ALL (SELECT price FROM competitors);
SELECT * FROM orders WHERE quantity > ANY (SELECT quantity FROM sales);
Practical Use Cases:
The ALL and ANY operators are powerful tools for comparative analysis, allowing detailed evaluations against multiple criteria.
Console Output:
Product: Best Price
Order: High Quantity
