SQL Playground

So far, we've been working with raw data from our database columns. But SQL can do much more than just retrieve stored values! You can perform calculations, transform text, work with dates, and create new values on-the-fly using expressions.

Think of expressions as mini-calculators built right into your SQL queries. Instead of retrieving data and then processing it elsewhere, you can transform it directly in your query.

🧮 What are SQL Expressions?

SQL expressions allow you to perform operations on column data as you query it. You can:

Perform Math - Add, subtract, multiply, divide column values
Transform Text - Change how text appears in results
Work with Dates - Calculate time differences, format dates
Create New Values - Combine multiple columns into new information

This means you can get calculated results directly from your database instead of doing math in spreadsheets later!

🔢 Basic Mathematical Expressions

You can use standard mathematical operators directly on numeric columns:

Mathematical Operators:

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

Basic Expression Syntax:

SELECT column_name * 2, column_name + 100
FROM table_name;

Practical Example:

-- Calculate discounted prices and tax amounts
SELECT product_name, 
       price,
       price * 0.8 AS discounted_price,
       price * 0.1 AS tax_amount
FROM products;

Sample Result:

product_name | price | discounted_price | tax_amount
-------------|-------|------------------|------------
Laptop       | 1000  | 800              | 100
Mouse        | 25    | 20               | 2.5
Keyboard     | 75    | 60               | 7.5

🏷️ Using the AS Keyword for Aliases

When you create expressions, the results often have confusing column names. The AS keyword lets you give them meaningful names:

Column Alias Syntax:

SELECT column_name * 2 AS doubled_value, 
       column_name + 100 AS increased_amount
FROM table_name;

Benefits of Aliases:

Clearer Results - Column headers make sense
Better Reports - Professional-looking output
Easier Reference - Can reference aliased columns elsewhere

Example with meaningful aliases:

-- Calculate order totals with clear column names
SELECT order_id,
       quantity * unit_price AS subtotal,
       quantity * unit_price * 0.1 AS tax,
       quantity * unit_price * 1.1 AS total_amount
FROM order_items;

📝 Table Aliases

You can also give tables shorter names using aliases, especially useful with long table names or when joining multiple tables:

Table Alias Syntax:

SELECT c.column_name, c.another_column
FROM very_long_table_name AS c;

JOIN with Table Aliases:

SELECT c.company_name, o.order_amount  
FROM companies AS c
INNER JOIN orders AS o ON c.company_id = o.company_id;

💡 Pro Tip: You can omit the AS keyword for both column and table aliases. SELECT price * 2 discount_price works the same as SELECT price * 2 AS discount_price.

🛠️ Common Expression Types

📊 Mathematical Calculations

-- Calculate business metrics
SELECT product_name,
       cost,
       selling_price,
       selling_price - cost AS profit,
       (selling_price - cost) / cost * 100 AS profit_percentage
FROM products;

🔤 String Operations

-- Transform text data
SELECT first_name,
       last_name,
       UPPER(first_name) AS first_name_caps,
       LOWER(email) AS email_lowercase,
       LENGTH(company_name) AS name_length
FROM customers;

🔗 Combining Columns

-- Create full names and addresses
SELECT first_name + ' ' + last_name AS full_name,
       city + ', ' + state AS location,
       'Order #' + CAST(order_id AS VARCHAR) AS order_label
FROM customers;

🎯 When to Use Expressions

Expressions are valuable when you need to:

Calculate Values - Discounts, taxes, totals, percentages
Format Data - Make output more readable and professional
Transform Data - Convert units, change formats, normalize text
Create Insights - Derive new information from existing data
Save Time - Avoid post-processing in other tools

Real-world scenarios:

-- E-commerce calculations
SELECT product_name,
       price,
       price * 0.85 AS sale_price,
       CASE 
         WHEN price > 100 THEN 'Premium'
         WHEN price > 50 THEN 'Standard'
         ELSE 'Budget'
       END AS price_category
FROM products;

🔧 Common Functions

Most SQL databases support these basic functions:

Mathematical:

ABS(number)        -- Absolute value
ROUND(number, 2)   -- Round to 2 decimal places
CEILING(number)    -- Round up
FLOOR(number)      -- Round down

String:

UPPER(text)        -- Convert to uppercase
LOWER(text)        -- Convert to lowercase
LENGTH(text)       -- Get text length
SUBSTRING(text, 1, 10)  -- Get first 10 characters

Date:

NOW()              -- Current date/time
DATEDIFF(date1, date2)  -- Difference between dates

📈 Best Practices

Always Use Aliases - Make your results clear and professional
Keep It Simple - Complex expressions can be hard to read and debug
Use Parentheses - Make calculation order explicit: (price * quantity) * tax_rate
Test Your Math - Verify calculations with known values
Be Consistent - Use the same naming conventions for aliases

Good example:

SELECT customer_name,
       order_total,
       order_total * 0.1 AS tax_amount,
       order_total * 1.1 AS final_total
FROM orders;

🚀 Expression vs. Post-Processing

Without Expressions (Multiple Steps):

Get data from database
Export to spreadsheet
Create formulas
Calculate results

With Expressions (One Step):

-- Get calculated results directly from database ✅
SELECT product_name,
       units_sold,
       unit_price,
       units_sold * unit_price AS revenue,
       (units_sold * unit_price) * 0.15 AS commission
FROM sales;

💡 Did You Know? Different databases support different functions, but basic mathematical operations (+, -, *, /) work in all SQL databases. String functions like UPPER(), LOWER(), and LENGTH() are also widely supported, though the exact syntax might vary slightly between database systems.

✅ Key Takeaways

Expressions perform calculations and transformations during queries
Use mathematical operators (+, -, *, /) for numeric calculations
The AS keyword creates meaningful aliases for both columns and tables
Table aliases make complex queries more readable and easier to write
Expressions save time by avoiding post-processing in other tools
Always use aliases to make your results professional and clear
Test your expressions with known values to ensure accuracy
Keep expressions simple for better readability and maintenance
Use parentheses to make calculation order explicit
String and date functions vary between database systems but basics are universal

📝 Exercise Overview

In this lesson's exercises, you'll work with the Companies and Orders tables to practice creating expressions. You'll calculate business metrics, format text output, and use aliases to make your results professional and easy to understand.

These practical scenarios will show you how expressions can transform raw data into meaningful business insights directly in your SQL queries. You'll practice:

Creating mathematical calculations for business metrics
Using string functions to format text output
Combining columns to create new information
Writing professional aliases for clear results

Take your time to experiment with different expressions - this is where SQL becomes a powerful analytical tool!

Ready to practice SQL? Try our coding challenges →

Lesson 7: SQL Expressions and Aliases