Single row functions

SQL has many built-in functions. Single row (scalar) functions work on one value at a time, and return one value for each row.

Math functions:

• POWER(x, y) (or POW): x raised to the power y. POWER(2, 3) gives 8.
• ROUND(n, d): n rounded to d decimal places. ROUND(25.678, 1) gives 25.7; a negative d rounds to the left of the decimal point: ROUND(1256, -2) gives 1300.
• MOD(a, b): the remainder when a is divided by b. MOD(17, 5) gives 2.

String functions:

• UCASE/UPPER and LCASE/LOWER: change the case.
• MID/SUBSTR(s, pos, n): n characters of s, starting at position pos (counting from 1). MID('Delhi', 2, 3) gives 'elh'.
• LENGTH(s): the number of characters.
• LEFT(s, n) and RIGHT(s, n): n characters from the left or right end. RIGHT('Python', 3) gives 'hon'.
• INSTR(s, sub): the position where sub first appears in s (0 if not found).
• LTRIM, RTRIM, TRIM: remove spaces from the left, the right, or both ends.

Date functions: NOW() (current date and time), DATE() (the date part), MONTH(), MONTHNAME(), YEAR(), DAY(), DAYNAME(). For example, DAYNAME('2024-01-26') gives 'Friday'.

Functions can be combined in calculations. In the book's car showroom, the EMI is worked out using MOD: the remainder after dividing the price by 10,000 is paid first, and the rest is split into instalments. One row in, one value out.