Single Row Functions

Single row functions are also known as scalar functions. They are applied on a single value and return a single value — one output for every row they process. Figure 1.2 groups the single row functions in SQL under three categories: Numeric (Math) functions, String functions, and Date and Time functions.

Each category is defined by its input and output types: the Math group takes numbers and gives back numbers; the String group works on character data and may hand back either text (e.g. a substring) or a number (e.g. a length or position); the Date-and-Time group operates on dates and times and can produce a number, a piece of text, or another date/time value. …

Numeric (math) functions take numbers in and give numbers out. The textbook works with three commonly used ones — POWER(), ROUND() and MOD() — whose syntax and worked outputs are listed in Table 1.5:

• POWER(X, Y) — also written POW(X, Y) — calculates X raised to the power Y. POWER(2,3) gives 8.
• ROUND(N, D) rounds off N to D decimal places; if D = 0 (or omitted), N is rounded to the nearest integer. ROUND(2912.564, 1) gives 2912.6, ROUND(283.2) gives 283.
• MOD(A, B) returns the remainder after dividing A by B. MOD(21, 2) gives 1. …

String functions perform operations on alphanumeric data — changing case, extracting a substring, measuring length, locating one string inside another, and stripping unwanted spaces. Table 1.6 lists each string function with its syntax and a worked example:

• UCASE(string)/UPPER(string) converts to uppercase; LOWER(string)/LCASE(string) converts to lowercase.
• MID(string, pos, n) — also SUBSTRING()/SUBSTR() — returns n characters starting at position pos (or everything from pos to the end, if n is omitted). MID("Informatics", 3, 4) gives form.
• LENGTH(string) returns the character count — LENGTH("Informatics") is 11.
• LEFT(string, N)/RIGHT(string, N) return N characters from the left/right of the string.
• INSTR(string, substring) returns the position of the first occurrence of substring, or 0 if absent.
• LTRIM(), RTRIM() and TRIM() remove leading, trailing, or both leading and trailing white space. …

Date and time functions operate on date and time data — displaying the current date, extracting a date's day/month/year, or naming the day of the week. Table 1.7 lists the date functions with syntax and worked outputs:

• NOW() returns the current system date and time.
• DATE() returns just the date part of a date/time expression.
• MONTH(date)/MONTHNAME(date) return the month in numeric form / by name.
• YEAR(date) returns the year.
• DAY(date)/DAYNAME(date) return the day of the month / the day's name.

Example 1.4 applies these to the EMPLOYEE table's DOJ (date of joining) column — first extracting day/month/year separately, then formatting a full "Weekday, Day, Month, Year" string for every employee who didn't join on a Sunday. Activity 1.4 then asks you to apply the same functions to the EMPLOYEE table on your own. …