SQL Operations in a nutshell :

Congratulations, we now have worked on retrieving data. Next step is now how to work on that data to produce ready to use output. In this article we will basically cover how to transform, calculate and reshape data directly inside a querry rather than pulling those raw values and working with them on a separate platform.
We have different SQL Operations: Arithmetic, string, date, conditional and Aggregate Operations.

Arithmetic Operations

SQL supports standard mathematical operations in a select statement
syntax:

SELECT product_name, price, quantity, price * quantity AS line_total
FROM order_items;

line_total is a column we have made in our select query to find the total price of each item ordered.
Arithmetic operations are also used for :

1. Applying discounts or tax - price * 0.16

2. computing differences - Revenue - cost_price

syntax:

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

String Operations

String operations clean up, combine, or reformat text data.
common string functions:

• UPPER() / LOWER() — change cases
• TRIM() — remove leading/trailing whitespace
• LENGTH() — get the number of characters
• SUBSTRING() — extract part of a string
• REPLACE() — swap out text
• initcap() - Changes the cases of the begining of every word to upper case. In another language is putting words into proper cases.

syntax:

SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM customers;

syntax:

update tembo.tembo_staging
set guest_name = initcap(trim(guest_name))
where guest_name != initcap(trim(guest_name));

What the above code does:
It is updating the table tembo_staging specifically the column guest_name by telling SQL to look through the values of that column, find all those values that are not trimmed and properly cased and trim them first before properly casing them hence the trim() inside the inticap() function.

Aggregate Operations

Aggregate functions collapse many rows into a single summary value, and are almost always paired with GROUP BY.
Core aggregate functions:

• COUNT() — number of rows
• SUM() — total of a numeric column
• AVG() — average value
• MIN() / MAX() — smallest/largest value

syntax:

SELECT product_category, AVG(price) AS avg_price, COUNT(*) AS total_products
FROM products
GROUP BY product_category;

Conditional Operations with CASE WHEN

CASE lets you build if/else logic directly into a query, turning raw values into categories, labels, or flags without needing a separate processing step.
syntax:

SELECT order_id, total,
  CASE
    WHEN total > 500 THEN 'Large'
    WHEN total > 100 THEN 'Medium'
    ELSE 'Small'
  END AS order_size
FROM orders;

order_size will be a column on it's own, that categorises the orders by the total price.

We can also use a case when statement inside aggregates for conditional counting or summing eg,
syntax:

SELECT
  COUNT(CASE WHEN status = 'shipped' THEN 1 END) AS shipped_orders,
  COUNT(CASE WHEN status = 'cancelled' THEN 1 END) AS cancelled_orders
FROM orders;

cancelled_orders and shipped_orders are columns of their own derived from the orders table.The values inside are the number of orders that were cancelled and shipped respectively.

Date Operations

Dates come with their own set of operations, since they need to be compared, calculated on, and formatted differently from plain numbers or text.
syntax:

SELECT order_id, order_date, CURRENT_DATE - order_date AS days_since_order
FROM orders;

The values i the column days_since_order will be in days

syntax:

SELECT *
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '30 days';

INTERVAL on it's own is a date function. Here we are filtering by recent activity.

syntax:

SELECT order_id, EXTRACT(MONTH FROM order_date) AS order_month
FROM orders;

EXTRACT is also a date function used to extracting parts of a date eg months, days or years.

syntax:

SELECT TO_CHAR(order_date, 'YYYY-MM') AS order_month
FROM orders;

TO_CHAR converts the date value to a readable text string/ text , however you like to call it.

NOTE: Some date functions differe for different databases. The above examples are for Postgres Database. You will need to check more on the same once we move past the basics.

Combining them all

In a real data set you will mostly find yourself in a postion where you will have to combine all these functions into one querry to answer a certain business question.
syntax:

SELECT
  TO_CHAR(order_date, 'YYYY-MM') AS month,
  COUNT(*) AS total_orders,
  SUM(total) AS total_revenue,
  ROUND(AVG(total), 2) AS avg_order_value
FROM orders
GROUP BY TO_CHAR(order_date, 'YYYY-MM')
ORDER BY month;

In layman's language, im trying to find the total number of orders and total revenue for each month, but I want my month column to be in the format 2024 March for instance, and I want the table to be ordered from the earliest date to the latest.

To summarise...

You move from just retrieving data to generating insights from that data, that is the whole point of SQL operations.

Once comfortable with that, you can move to the next step which are subquerries and CTE's then to window functions.