SQL – Functions

Functions

When using operations, you can also use calculation functions to, for example, improve the display of the result.

select wine_id, round(cost/100 * 105.2) as 'increment'
from inventory
where cost < 5.1

Here the calculation is rounded to 2 digits after the decimal point.
You can specify the title of the edit with an "alias". You type the word "as" and the name in single quotes (') after the calculation.
You can also use text functions if the content consists of a sequence of characters.
This query shows the customer's last name (surname) and first name (firstname).
In addition, the number of characters of the family name (surname) is calculated in a third column.
In the condition, the first name (firstname) is converted to uppercase and then compared to 'MARTIN'.

select surname, firstname, length(surname)
from customer
where upper(firstname) = 'MARTIN'

If you want to know the number of records that meet a condition, you can use the statistical function "COUNT". If you want to get the highest or lowest value in a field, you can use the functions "MAX" or "MIN". And you can make totals with the function "SUM".
The lowest cost price, the highest cost price and the sales value is calculated in this example.

select min(cost) as 'lowest', max(cost) as 'highest', sum(on_hand*cost) as 'sales value'
from inventory

The "on_hand" field contains the number of bottles in stock for each wine. The value of the wines in the warehouse is therefore greater than 9 million!
A representative wants to know how many customers live in "Alexandra" (Australia). You can quickly look up that with the query below.

select count(*)
from customer
where city = 'Alexandra'

You can count the number of records on any field from the table.
An overview of the most frequently used functions: