Unit 3 :- Single-Row Functions

Functions are very powerful feature of SQL and can be used to do the following:

• Perform calculations on data.
• Modify individual data items.
• Manipulate output for groups of rows.
• Format dates and numbers for display.
• Convert column data types.

SQL functions sometimes take arguments and always return a value.

There are Two distinct types of functions:

• Single-row functions.
• Multiple-row functions.

Single-Row Functions

These functions operate on single rows only and return one result per row. There are different types single-row functions. This section covers the following ones:

• Character
• Case-manipulation functions
• LOWER
• UPPER
• INITCAP
• Character-manipulation functions
• CONCAT
• SUBSTR
• LENGTH
• INSTR
• LPAD   |   RPAD
• TRIM
• REPLACE
• Number
• ROUND
• TRUNC
• MOD
• Date
• MONTHS_BETWEEN
• ADD_MONTHS
• NEXT_DAY
• LAST_DAY
• ROUND
• TRUNC
• Conversion
• Implicit data type conversion
• VARCHAR2 OR CHAR -----   TO -----   NUMBER
• VARCHAR2 OR CHAR -----   TO -----   DATE
• NUMBER                       -----    TO -----  VARCHAR2
• DATE                              -----    TO ----    VARCHAR2
• Explicit data type conversion
• TO_CHAR
• TO_DATE
• TO_NUMBER
• General
• NVL
• NVL2
• NULLIF
• COALESCE
• CASE
• DECODE

Multiple-Row Functions

Functions can manipulate groups of rows to give one result per group of rows. These functions are known as group functions. This is covered in next Unit.

SORTING in SQL

The default sort order is ascending:

• Numeric values are displayed with the lowest value first e.g. 1-10
• Date values are displayed with the earliest value first e.g. 01-JAN-12 before 12-FEB-13
• Character values are displayed in alphabetical order e.g. A first Z last
• Null values are displayed last for ascending sequence and first for descending sequence.

Sorting in DESCENDING Order

Sorting by Column Alias

We can use a column alias in the ORDER BY clause. The below example sorts the data by Annual Salary.

Example:

Sorting By Multiple Columns:

We can sort query by more than one column. The sort limit is the number of columns in the given table.

Example:

ORDER BY Clause in SQL

• Sort rows with the ORDER BY clause
• ASC:  ascending order (Default)
• DESC:  descending order
• The ORDER BY clause comes last in the SELECT statement.
• We can Sort the rows by the name of the column which may not be present in the sorted list. (see second example)

Example:

Example: Here names of employees are sorted according to salary but salary column is not present in the sorting list. 

Operator Precedence in SQL

• The rules of precedence determines the order in which expressions are evaluated and calculated.
• The table below lists the default order of precedence.
• You can override the default order by using parentheses around the expressions you want to calculate first.

   ORDER EVALUATED                     OPERATOR

                   1                                        Arithmetic Operator

                   2                                        Concatenation Operator

                   3                                        Comparison Conditions

                   4                                        IS [NOT] NULL, LIKE, [NOT] IN

                   5                                        [NOT] BETWEEN

                   6                                        NOT logical condition

                   7                                        AND logical condition

                   8                                        OR logical condition

NOTE:

Override rules of precedence by using parentheses.

Example:  (Carefully study both the scenario in the example)

1) In the first scenario first AND operator is calculated and then the result is operated with OR.

2) In the second scenario first OR operator is calculated because of Parentheses and then result is operated with AND.