SQL functions and expressions

SQL provides a variety of built-in functions to transform and manipulate data in queries without needing client-side post-processing. Expressions allow computing derived values similarly. Leveraging these can simplify data extraction logic significantly.

SQL Functions

Common categories of functions:

Text Functions

Manipulate string data:

SELECT UPPER(name), LENGTH(comments)
FROM reviews;

UPPER cases to upper, LENGTH gets length.

UPPER(name)	LENGTH(comments)
JOHN DOE	45
JANE SMITH	30
ALEX JONES	60
EMILY BLAKE	50

Date Functions

Date processing:

SELECT DAY(created_date), DATEDIFF(NOW(), created_date)
FROM accounts;

DAY gets day-of-month, DATEDIFF difference between dates.

DAY(created_date)	DATEDIFF(NOW(), created_date)
15	10
3	22
25	5
8	18

Aggregates

We saw aggregates for summarization like COUNT(), AVG(), SUM() earlier.

Conversions

Convert between data types:

SELECT CAST(price AS VARCHAR)
FROM products;

Casts price to text.

Case Expressions

Compare values and return based on conditions:

SELECT name,
  CASE WHEN price < 100 THEN 'Affordable'
       WHEN price < 500 THEN 'Expensive'
       ELSE 'Ultra-Premium' END AS category
FROM wines;

Categories wines based on pricing.

name	category
Chardonnay	Affordable
Pinot Noir	Expensive
Merlot	Affordable
Cabernet	Ultra-Premium

Expressions

SQL allows writing expressions with common math and programming operators like +, - , * , / , % etc. Parentheses control order of evaluation.

For example to compute an order total:

SELECT *, quantity * unit_price AS total_cost
FROM order_lines;

This avoids needing to compute line costs in application code.

Boolean expressions can leverage AND/OR/NOT operators combined with comparison operators like =, !=, > etc:

SELECT *
FROM contests
WHERE end_date > NOW() AND status = 'Open';

Finds currently open contests.

Leveraging functions like LENGTH, DATE_DIFF and expressions for calculations, boolean logic etc. allows doing significantly more data manipulation directly in SQL with less backend programming.

Practice integrating functions and expressions to push data processing into the database engine for faster and simpler analysis.