SQL - Expressions

Hello there, future SQL wizards! Today, we're going to embark on an exciting journey into the world of SQL expressions. Don't worry if you're new to programming - I'll be your friendly guide, and we'll take this step by step. By the end of this tutorial, you'll be crafting SQL expressions like a pro!

What is SQL Expression?

Let's start with the basics. An SQL expression is like a formula or a calculation that you can use in your SQL queries. It's a combination of one or more values, operators, and SQL functions that evaluate to a value. Think of it as the secret sauce that adds flavor to your database queries!

For example, let's say you have a table of products, and you want to apply a 10% discount to all prices. You could use an expression like this:

SELECT product_name, price, price * 0.9 AS discounted_price
FROM products;

In this query, price * 0.9 is our SQL expression. It's taking the price and multiplying it by 0.9 (which is the same as subtracting 10%).

SQL Boolean Expressions

Now, let's dive into Boolean expressions. These are like the true/false questions of the SQL world. They always result in either TRUE, FALSE, or NULL (which means "unknown").

Boolean expressions are super useful when you want to filter your data or make decisions in your queries. Here are some common operators used in Boolean expressions:

Operator	Description
=	Equal to
<>	Not equal to
>	Greater than
<	Less than
>=	Greater than or equal to
<=	Less than or equal to
AND	Logical AND
OR	Logical OR
NOT	Logical NOT

Let's see these in action with some examples:

-- Find all products priced over $100
SELECT * FROM products WHERE price > 100;

-- Find all orders from the last 30 days
SELECT * FROM orders WHERE order_date >= CURRENT_DATE - INTERVAL '30 days';

-- Find all active customers in New York or California
SELECT * FROM customers 
WHERE (state = 'NY' OR state = 'CA') AND is_active = TRUE;

In each of these examples, the part after the WHERE clause is a Boolean expression. It's like asking the database a yes/no question for each row of data.

SQL Numeric Expressions

Next up, we have numeric expressions. These are like the math problems of SQL - they involve numbers and arithmetic operations.

Here are the basic arithmetic operators you can use:

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

Let's look at some examples:

-- Calculate the total value of inventory
SELECT product_name, quantity, price, quantity * price AS total_value
FROM inventory;

-- Calculate the age of employees
SELECT first_name, last_name, EXTRACT(YEAR FROM CURRENT_DATE) - EXTRACT(YEAR FROM birth_date) AS age
FROM employees;

-- Calculate the percentage of budget spent
SELECT project_name, budget, spent, (spent / budget) * 100 AS percent_spent
FROM projects;

In these examples, we're using numeric expressions to perform calculations on our data. This is incredibly useful for deriving new insights from your existing data.

SQL Date Expressions

Last but not least, let's talk about date expressions. Dealing with dates in SQL can be tricky, but it's also incredibly powerful. Date expressions allow you to manipulate and compare dates in your queries.

Here are some common date functions:

Function	Description
CURRENT_DATE	Returns the current date
CURRENT_TIMESTAMP	Returns the current date and time
DATE_ADD	Adds a specified time interval to a date
DATE_SUB	Subtracts a specified time interval from a date
DATEDIFF	Calculates the difference between two dates

Let's see these in action:

-- Find orders placed today
SELECT * FROM orders WHERE order_date = CURRENT_DATE;

-- Find customers who haven't placed an order in the last 90 days
SELECT * FROM customers 
WHERE customer_id NOT IN (
    SELECT DISTINCT customer_id 
    FROM orders 
    WHERE order_date >= CURRENT_DATE - INTERVAL '90 days'
);

-- Calculate how many days ago each order was placed
SELECT order_id, order_date, 
       DATEDIFF(CURRENT_DATE, order_date) AS days_ago
FROM orders;

These date expressions allow you to perform complex operations with dates, which is crucial for analyzing time-based data.

And there you have it, folks! We've journeyed through the land of SQL expressions, from Boolean logic to number crunching to time travel (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 SQL joke for you: Why did the database administrator leave his wife? She had one-to-many relationships! (Ba dum tss!)

Happy querying, and may your joins always be swift and your queries never time out!

Credits: Image by storyset