SQLite - Operators

Hello, future database wizards! Today, we're diving into the magical world of SQLite operators. As your friendly neighborhood computer teacher, I'm here to guide you through this journey, even if you've never written a line of code before. So, grab your virtual wands (keyboards), and let's cast some SQLite spells!

What is an Operator in SQLite?

Before we jump into the deep end, let's start with the basics. In SQLite (and programming in general), an operator is like a special symbol or keyword that tells the computer to perform specific operations or comparisons. Think of operators as the verbs in the language of databases - they make things happen!

For example, when you see a "+" sign in SQLite, it's not just a cute little cross. It's telling the database, "Hey, add these things together!" Similarly, when you see "=", it's not just two lines hanging out - it's asking, "Are these two things equal?"

Now that we've got that sorted, let's explore the different types of operators in SQLite. Trust me, it's more fun than it sounds!

SQLite Arithmetic Operators

Arithmetic operators in SQLite are like the basic math operations you learned in school, but now they're working their magic on your data. Let's look at them in action:

Operator	Description	Example
+	Addition	SELECT 5 + 3; -- Returns 8
-	Subtraction	SELECT 10 - 4; -- Returns 6
*	Multiplication	SELECT 6 * 2; -- Returns 12
/	Division	SELECT 15 / 3; -- Returns 5
%	Modulus (remainder)	SELECT 17 % 5; -- Returns 2

Let's break these down with some real-world examples:

-- Let's say we have a table called 'products' with columns 'price' and 'quantity'
SELECT name, price * quantity AS total_value
FROM products;

In this example, we're using the multiplication operator (*) to calculate the total value of each product. SQLite will multiply the price by the quantity for each row, giving us the total value.

-- Calculating the average price
SELECT AVG(price) AS average_price
FROM products;

-- Now let's round it to 2 decimal places
SELECT ROUND(AVG(price), 2) AS rounded_average_price
FROM products;

Here, we're using the division operator behind the scenes (AVG function divides the sum by the count), and then we're rounding the result. It's like asking SQLite to do your math homework!

SQLite Comparison Operators

Comparison operators are like the judgmental friends of the SQLite world - they're always comparing things. Here's a table of these picky operators:

Operator	Description	Example
=	Equal to	SELECT * FROM products WHERE price = 10;
!= or <>	Not equal to	SELECT * FROM products WHERE category != 'Electronics';
<	Less than	SELECT * FROM products WHERE price < 50;
>	Greater than	SELECT * FROM employees WHERE salary > 50000;
<=	Less than or equal to	SELECT * FROM inventory WHERE quantity <= 5;
>=	Greater than or equal to	SELECT * FROM orders WHERE order_date >= '2023-01-01';

Let's see these in action:

-- Find all products that are out of stock
SELECT name, quantity
FROM products
WHERE quantity = 0;

-- Find all employees who are not in the IT department
SELECT name, department
FROM employees
WHERE department <> 'IT';

-- Find all orders placed in the last week
SELECT order_id, order_date
FROM orders
WHERE order_date >= DATE('now', '-7 days');

In these examples, we're using comparison operators to filter our data. It's like telling SQLite, "I only want to see this specific stuff, thank you very much!"

SQLite Logical Operators

Logical operators are the decision-makers of SQLite. They help us combine conditions and make more complex queries. Here are our logical friends:

Operator	Description	Example
AND	True if all conditions are true	SELECT * FROM products WHERE price > 100 AND category = 'Electronics';
OR	True if any condition is true	SELECT * FROM customers WHERE country = 'USA' OR country = 'Canada';
NOT	Negates a condition	SELECT * FROM employees WHERE NOT department = 'Sales';

Let's put these to work:

-- Find all expensive electronics
SELECT name, price, category
FROM products
WHERE price > 500 AND category = 'Electronics';

-- Find all customers from North America
SELECT name, country
FROM customers
WHERE country = 'USA' OR country = 'Canada' OR country = 'Mexico';

-- Find all employees not in Sales or Marketing
SELECT name, department
FROM employees
WHERE NOT (department = 'Sales' OR department = 'Marketing');

In these examples, we're combining conditions to get exactly the data we want. It's like being a detective, piecing together clues to solve a mystery!

SQLite Bitwise Operators

Now, we're entering the realm of binary magic with bitwise operators. These operators work on the binary representations of numbers. They're not used as often in everyday SQLite queries, but they're powerful tools for certain operations.

Operator	Description	Example
&	Bitwise AND	SELECT 5 & 3; -- Returns 1
|	Bitwise OR	SELECT 5 | 3; -- Returns 7
~	Bitwise NOT	SELECT ~5; -- Returns -6
<<	Left shift	SELECT 5 << 1; -- Returns 10
>>	Right shift	SELECT 5 >> 1; -- Returns 2

Let's break down a bitwise operation:

-- Using bitwise AND to check if a number is odd or even
SELECT 
    number,
    CASE 
        WHEN number & 1 = 1 THEN 'Odd'
        ELSE 'Even'
    END AS parity
FROM (
    SELECT 1 AS number
    UNION ALL SELECT 2
    UNION ALL SELECT 3
    UNION ALL SELECT 4
    UNION ALL SELECT 5
);

In this example, we're using the bitwise AND operator to check if a number is odd or even. If a number AND 1 equals 1, it's odd; otherwise, it's even. It's like teaching SQLite to count on its fingers!

And there you have it, folks! We've journeyed through the land of SQLite operators, from the familiar territory of arithmetic to the exotic realm of bitwise operations. Remember, practice makes perfect, so don't be afraid to experiment with these operators in your own queries. Before you know it, you'll be wielding these SQLite powers like a true database sorcerer!

Credits: Image by storyset