SQLite - Expressions

Hello there, future database wizards! Today, we're diving into the fascinating world of SQLite expressions. As your friendly neighborhood computer teacher, I'm here to guide you through this journey, step by step. Don't worry if you're new to programming – we'll start from the basics and work our way up. So, grab your favorite beverage, get comfortable, and let's embark on this SQLite adventure together!

What are SQLite Expressions?

Before we jump into the nitty-gritty, let's understand what expressions are in SQLite. Think of expressions as the building blocks of your database queries. They're like the ingredients in a recipe – you combine them in different ways to create powerful and meaningful results.

In SQLite, expressions can be as simple as a single value or as complex as a combination of multiple operations. They're used in various parts of SQL statements, such as SELECT, WHERE, and ORDER BY clauses.

Now, let's explore the three main types of expressions in SQLite:

1. Boolean Expressions
2. Numeric Expressions
3. Date Expressions

SQLite - Boolean Expressions

Boolean expressions are like the true/false questions in a quiz. They evaluate to either TRUE or FALSE. These expressions are super helpful when you want to filter data or make decisions in your queries.

Basic Comparison Operators

Let's start with some basic comparison operators:

Operator	Description
=	Equal to
<>	Not equal to
<	Less than
>	Greater than
<=	Less than or equal
>=	Greater than or equal

Here's a simple example:

SELECT name, age
FROM students
WHERE age > 18;

In this query, age > 18 is our boolean expression. It checks if the age is greater than 18 and returns TRUE for all students who are over 18.

Logical Operators

Now, let's spice things up with logical operators:

Operator	Description
AND	Logical AND
OR	Logical OR
NOT	Logical NOT

Here's an example combining multiple conditions:

SELECT name, age, grade
FROM students
WHERE age > 18 AND grade = 'A';

This query finds all students who are over 18 AND have an 'A' grade. It's like saying, "Show me the adult straight-A students!"

BETWEEN Operator

The BETWEEN operator is a shorthand for checking if a value is within a range:

SELECT name, score
FROM exam_results
WHERE score BETWEEN 80 AND 90;

This query finds all students who scored between 80 and 90 on their exam. It's equivalent to writing score >= 80 AND score <= 90.

SQLite - Numeric Expressions

Numeric expressions in SQLite allow you to perform mathematical operations in your queries. It's like having a calculator built into your database!

Arithmetic Operators

Here are the basic arithmetic operators:

Operator	Description
+	Addition
-	Subtraction
*	Multiplication
/	Division
%	Modulo

Let's see them in action:

SELECT name, 
       price, 
       quantity, 
       price * quantity AS total_cost
FROM orders;

In this query, we're calculating the total cost by multiplying price and quantity. The AS keyword allows us to give a name to our calculated column.

Aggregate Functions

SQLite also provides aggregate functions that operate on a set of values:

Function	Description
AVG()	Calculate the average
SUM()	Calculate the sum
COUNT()	Count the number of rows
MAX()	Find the maximum value
MIN()	Find the minimum value

Here's an example using aggregate functions:

SELECT 
    AVG(price) AS average_price,
    SUM(quantity) AS total_items,
    COUNT(*) AS order_count,
    MAX(price) AS highest_price,
    MIN(price) AS lowest_price
FROM orders;

This query gives us a summary of our orders table, including the average price, total items sold, number of orders, and the highest and lowest prices.

SQLite - Date Expressions

Working with dates in databases can be tricky, but SQLite provides some handy functions to make our lives easier.

Date and Time Functions

Here are some commonly used date and time functions:

Function	Description
date()	Returns the current date
time()	Returns the current time
datetime()	Returns the current date and time
julianday()	Returns the Julian day for a date
strftime()	Formats a date according to a format string

Let's see how we can use these functions:

SELECT 
    date('now') AS current_date,
    time('now') AS current_time,
    datetime('now') AS current_datetime,
    julianday('now') AS julian_day,
    strftime('%Y-%m-%d %H:%M', 'now') AS formatted_datetime;

This query shows us various ways to work with the current date and time.

Date Calculations

We can also perform calculations with dates:

SELECT 
    name,
    hire_date,
    date(hire_date, '+1 year') AS first_anniversary,
    julianday('now') - julianday(hire_date) AS days_employed
FROM employees;

In this query, we're calculating each employee's first work anniversary and how many days they've been employed.

And there you have it, folks! We've journeyed through the land of SQLite expressions, from boolean logic to number crunching to time traveling (well, sort of). Remember, practice makes perfect, so don't be afraid to experiment with these expressions in your own queries.

Before we wrap up, here's a little story from my teaching experience: I once had a student who was struggling with boolean expressions. He kept mixing up AND and OR. So, I told him to think of AND as a strict parent (both conditions must be true) and OR as a lenient one (either condition can be true). He never forgot after that!

I hope this tutorial has been helpful and maybe even a bit fun. Keep exploring, keep querying, and most importantly, keep learning. Until next time, happy coding!

Credits: Image by storyset