SQL - Operators

Welcome, future database wizards! Today, we're diving into the magical world of SQL operators. Don't worry if you've never written a line of code before – we'll start from the very beginning and work our way up. By the end of this tutorial, you'll be conjuring data with the best of them!

What is SQL Operator?

Let's start with the basics. An SQL operator is like a special tool in your database toolbox. It helps you perform operations on your data, whether you're comparing values, doing math, or combining information. Think of operators as the verbs in your SQL sentences – they make things happen!

For example, when you say "I want all customers whose age is greater than 30," the "greater than" part is an operator. It's telling the database how to filter your data.

Types of Operator in SQL

Now, let's explore the different types of operators in SQL. We'll categorize them and provide examples for each. Buckle up, because we're about to take a rollercoaster ride through the operator playground!

1. Arithmetic Operators

These operators help you do math with your data. Here's a table of arithmetic operators:

Operator	Description	Example
+	Addition	5 + 3 = 8
-	Subtraction	7 - 2 = 5
*	Multiplication	4 * 6 = 24
/	Division	15 / 3 = 5
%	Modulus (remainder)	17 % 5 = 2

Let's see these in action:

SELECT product_name, price, price * 1.1 AS price_with_tax
FROM products;

In this example, we're calculating a new price that includes a 10% tax. The * operator multiplies the original price by 1.1.

2. Comparison Operators

These operators help you compare values. They're super useful for filtering data. Here's a table of comparison operators:

Operator	Description	Example
=	Equal to	age = 30
<>	Not equal to	status <> 'Completed'
>	Greater than	price > 100
<	Less than	quantity < 5
>=	Greater than or equal to	rating >= 4
<=	Less than or equal to	discount <= 0.2

Let's use these in a query:

SELECT customer_name, age
FROM customers
WHERE age > 30 AND status = 'Active';

This query finds all active customers over 30 years old. The > operator checks for ages above 30, and the = operator ensures the status is exactly 'Active'.

3. Logical Operators

Logical operators help you combine multiple conditions. They're like the glue that holds your complex queries together. Here's a table of logical operators:

Operator	Description
AND	True if both conditions are true
OR	True if either condition is true
NOT	Reverses the result of a condition

Let's see how we can use these:

SELECT product_name, price, category
FROM products
WHERE (category = 'Electronics' OR category = 'Computers')
  AND price < 1000
  AND NOT discontinued;

This query finds products in the Electronics or Computers categories, priced under $1000, and not discontinued. It's like telling the database, "Give me all the cool gadgets that won't break the bank and are still available!"

4. LIKE Operator

The LIKE operator is your best friend when you're searching for patterns in text data. It uses wildcards to match parts of strings:

• % : Matches any number of characters
• _ : Matches a single character

Here's an example:

SELECT customer_name, email
FROM customers
WHERE email LIKE '%@gmail.com';

This query finds all customers with Gmail addresses. The % wildcard matches any characters before '@gmail.com'.

5. IN Operator

The IN operator is a shorthand way to combine multiple OR conditions. It's super handy when you're looking for matches in a list of values.

SELECT product_name, category
FROM products
WHERE category IN ('Books', 'Movies', 'Music');

This query retrieves all products in the Books, Movies, or Music categories. It's much cleaner than writing category = 'Books' OR category = 'Movies' OR category = 'Music'.

6. BETWEEN Operator

The BETWEEN operator helps you find values within a range. It's inclusive, meaning it includes the boundary values.

SELECT order_id, order_date, total_amount
FROM orders
WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31';

This query finds all orders placed in the year 2023. It's like saying, "Show me everything from New Year's Day to New Year's Eve!"

SQL Operator Precedence

Now that we've met all these wonderful operators, it's important to know how SQL decides which ones to evaluate first when you use multiple operators in a single query. This is called operator precedence.

Here's a simplified table of SQL operator precedence, from highest to lowest:

1. Parentheses ()
2. Multiplication/Division (*, /)
3. Addition/Subtraction (+, -)
4. Comparison operators (=, <>, <, >, <=, >=)
5. NOT
6. AND
7. OR

Let's see this in action:

SELECT product_name, price, category
FROM products
WHERE category = 'Electronics' AND (price < 500 OR rating > 4);

In this query, SQL will:

1. Evaluate the conditions inside the parentheses first (price < 500 OR rating > 4)
2. Then apply the AND operator with the category condition

This gives us all electronics that are either under $500 or have a rating above 4.

Remember, you can always use parentheses to explicitly control the order of operations if you're unsure or want to override the default precedence.

And there you have it, folks! You've just completed your crash course in SQL operators. With these tools in your belt, you're well on your way to becoming a data manipulation maestro. Remember, practice makes perfect, so don't be afraid to experiment with these operators in your own queries. Happy coding!

Credits: Image by storyset