This content originally appeared on DEV Community and was authored by Angelika Jolly
Sure, let's dive into the basics of SQL operators. SQL (Structured Query Language) is used for managing and manipulating databases. Here are the fundamental types of SQL operators and how they are used:
- Arithmetic Operators
Arithmetic operators perform mathematical operations. Common ones include:
-
+: Addition -
-: Subtraction - `` : Multiplication
-
/: Division -
%: Modulus
Example:
`sql
SELECT 10 + 5; -- Returns 15
SELECT 10 - 5; -- Returns 5
SELECT 10 5; -- Returns 50
SELECT 10 / 5; -- Returns 2
SELECT 10 % 3; -- Returns 1
`
- Comparison Operators
Comparison operators compare two values and return a boolean result (true or false).
-
=: Equal to -
<>or!=: Not equal to -
>: Greater than -
<: Less than -
>=: Greater than or equal to -
<=: Less than or equal to
Example:
`sql
SELECT 10 = 5; -- Returns false
SELECT 10 <> 5; -- Returns true
SELECT 10 > 5; -- Returns true
SELECT 10 < 5; -- Returns false
SELECT 10 >= 5; -- Returns true
SELECT 10 <= 5; -- Returns false
`
- Logical Operators
Logical operators are used to combine multiple conditions.
-
AND: All conditions must be true -
OR: At least one condition must be true -
NOT: Reverses the truth value
Example:
`sql
SELECT 10 > 5 AND 10 < 15; -- Returns true
SELECT 10 > 5 OR 10 < 5; -- Returns true
SELECT NOT 10 > 5; -- Returns false
`
- String Operators
String operators are used to manipulate string values.
-
||or+: Concatenate two strings (varies by SQL dialect) -
LIKE: Pattern matching using wildcards -
%: Zero or more characters (used with LIKE) -
_: A single character (used with LIKE)
Example:
`sql
SELECT 'Hello' || ' World'; -- Returns 'Hello World' (PostgreSQL, SQLite)
SELECT 'Hello' + ' World'; -- Returns 'Hello World' (SQL Server)
SELECT 'Hello' LIKE 'H%'; -- Returns true
SELECT 'Hello' LIKE '_e%'; -- Returns true
`
- Bitwise Operators
Bitwise operators perform operations on bits and are used in some advanced queries.
-
&: Bitwise AND -
|: Bitwise OR -
^: Bitwise XOR -
~: Bitwise NOT -
<<: Bitwise left shift -
>>: Bitwise right shift
Example:
`sql
SELECT 5 & 3; -- Returns 1 (0101 & 0011 = 0001)
SELECT 5 | 3; -- Returns 7 (0101 | 0011 = 0111)
SELECT 5 ^ 3; -- Returns 6 (0101 ^ 0011 = 0110)
SELECT ~5; -- Returns -6 (inverts the bits)
SELECT 5 << 1; -- Returns 10 (0101 << 1 = 1010)
SELECT 5 >> 1; -- Returns 2 (0101 >> 1 = 0010)
`
- Other Operators
-
IN: Checks if a value is within a set of values -
BETWEEN: Checks if a value is within a range -
IS NULL: Checks if a value is null -
IS NOT NULL: Checks if a value is not null
Example:
`sql
SELECT 10 IN (5, 10, 15); -- Returns true
SELECT 10 BETWEEN 5 AND 15; -- Returns true
SELECT NULL IS NULL; -- Returns true
SELECT 10 IS NOT NULL; -- Returns true
`
Practical Example with a Database Table
Let's consider a simple table employees:
| id | name | age | salary |
|---|---|---|---|
| 1 | Alice | 30 | 5000 |
| 2 | Bob | 35 | 6000 |
| 3 | Charlie | 40 | 7000 |
Example Queries:
Select employees with salary greater than 5000:
`sql
SELECT FROM employees WHERE salary > 5000;
`Select employees whose name starts with 'A':
`sql
SELECT FROM employees WHERE name LIKE 'A%';
`Select employees with age between 30 and 40:
`sql
SELECT FROM employees WHERE age BETWEEN 30 AND 40;
`
These are the basics of SQL operators. They form the foundation for writing more complex SQL queries as you progress.
https://www.youtube.com/watch?v=3tCym9ZkEdk
This content originally appeared on DEV Community and was authored by Angelika Jolly
Angelika Jolly | Sciencx (2024-07-06T08:29:43+00:00) First Steps in SQL Operators: A Beginner’s Guide. Retrieved from https://www.scien.cx/2024/07/06/first-steps-in-sql-operators-a-beginners-guide/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.