The UPPER Function in SQL

The UPPER function is contrary to the LOWER function and returns all characters in uppercase letters as shown in the following statement where each employee’s first name is displayed in uppercase.

Microsoft Access uses UCASE() function.

    Syntax :
UPPER (character)

    SQL Statement :

SELECT    UPPER(first_name)

FROM       employees;

    Output :

Date and Time Functions in SQL

Every DBMS allows you to store date and time values in tables using specific date data types and in specific formats. Each implementation follows its own storage format to save the date and time, for example, Oracle’s default display and input format for any date is DD-MON-YY. Unfortunately, this default storage varies among different implementations and is, therefore, least portable.

Similar to the character functions, date and time functions are also used to manipulate the representation of data. These function are used not only to present date and time data in some desirable format, but are also helpful in comparing date values, and computing intervals between dates.

To start with, here is an example in Oracle which fetches current date from the system, followed by a list of functions that perform the same task for other platforms.

    SQL Statement :

SELECT    SYSDATE

FROM        DUAL;

    Output :

The following table lists respective functions as employed by various DBMSs to get current system date:

DBMS	FUNCTION	SQL STATEMENT	OUTPUT (Format)
MySQL/MariaDB	CURDATE()	SELECT CURDATE();	2014-11-19 (YYYY-MM-DD)
PostgreSQL	CURRENT_DATE	SELECT CURRENT_DATE;	2014-11-19 (YYYY-MM-DD)
Microsoft Access	NOW()	SELECT Now() FROM Products;	11/19/2014 2:28:17 AM
Microsoft SQL Server	GETDATE()	SELECT GETDATE();	2014-11-19 13:10:02.047
SQLite	DATE()	SELECT DATE('now');	2014-11-19 (YYYY-MM-DD)
DB2	CURRENT DATE	SELECT CURRENT DATE FROM sysibm.sysdummy1;	2014-11-19 (YYYY-MM-DD)

The SUBSTR Function in SQL

The SUBSTR function returns specified characters from character value, starting from P, L characters long.  The following statement fetches records of employees who have ‘lex’ (three characters) in their names starting from second position.

NOTE

In Microsoft Access use MID() function. In Microsoft SQL Server, MySQL, and MariaDB you have to use SUBSTRING().

    Syntax :

SUBSTR (character, P,L)

    SQL Statement :

SELECT    first_name, last_name, salary

FROM      employees

WHERE    substr(first_name,2,3)='lex';   

    Output :

The NVL Function in SQL

When you query a table, the null values stored in a column (e.g commission_pct) are shown as (null). Using the NVL function, you can replace these nulls with some meaningful values. According to the following syntax, if expr1 is null, then NVL returns expr2. If expr1 is not null, then NVL returns expr1.  In the following example, you get a list of employees along with their commissions, replacing null values with zero if the employees are not entitled to get commissions.

NOTE

In the presented example we used a number column and replaced all null values with zero. Null values in a character column must be replaced as shown in the following example, where a character value (‘None’) is used to replace all null values in the state province column.

SELECT city,NVL(state_province, 'None') Province FROM locations;

    Syntax :

NVL (expr1, expr2)

    SQL Statement :

SELECT    last_name, NVL(commission_pct, 0) commission

FROM      employees

WHERE    last_name LIKE 'B%'

ORDER    BY last_name;

    Output  :

The LOWER Function in SQL

The LOWER function returns character, with all letters lowercase. The example presented below transforms all data in first name and job id columns to lower case.

  NOTE   Use LCASE() function for the same purpose in Microsoft Access.

NOTE

As mentioned earlier, single row functions can also be used in the WHERE clause. Let’s see an example. Running the first statement below will not yield any result. Change it so that it matches the second statement. Now you’ll get some rows. The LOWER() function in the second statement fetched the result, because all the job ids are stored in uppercase in the employees table, and you forced the query (by using the LOWER function) to first convert all the column values to lower case, match each value with the provided lower case string (pu_clerk), and then return the result.

Select first_name, last_name, job_id from employees where job_id='pu_clerk';

Select first_name, last_name, job_id from employees where lower(job_id)='pu_clerk';

    Syntax :

LOWER (character)

    SQL Statement :

SELECT    LOWER(first_name||' '||last_name) Name, LOWER(job_id) Job

FROM       employees;

    Output  :

The LENGTH Function in SQL

The LENGTH function returns the length of character. Note that it is a character function that returns the answer in numbers, as shown in the following example where it is used to count number of character in the first names of employees.

NOTE

In Microsoft SQL Server and Microsoft Access use DATALENGTH() and LEN() functions, respectively.

    Syntax :

LENGTH (character)

    SQL Statement :

SELECT    first_name, LENGTH(first_name) Length

FROM       employees;

    Output :

The INITCAP Function in SQL

INITCAP returns character, with the first letter of each word in uppercase, all other letters in lowercase. The following example converts the first letter of the job id column to uppercase, and the rest to lower case. Note that the values stored in this table column are all upper case.

    Syntax 

INITCAP (character)

    SQL Statement :

SELECT      INITCAP(job_id)

FROM         employees;

    Output :

Transform and Summarize Data With Functions in SQL

A function can be defined as an operation that is performed with the objective to transform the appearance of data. Like other computer languages, SQL also supports the use of functions to manipulate data. Functions are usually applied to:

·         Convert data types of columns

·         Data calculation

·         Alter display formats

·         Provide collective output for groups of rows

Functions are divided into the following two categories:

    Single Row functions : As the name implies, the functions under this category act only on individual rows, and return a single result for every processed row. You can use these functions in SELECT, WHERE, and ORDER BY clauses. Single row functions are further categorized as follows:

·         Character functions: These functions accept character data as input and can return both character and number values.

·         Number functions: Number functions manipulate numeric data. They receive numeric input and return only numeric values.

·         Date functions: Besides presenting date and time data in some desirable format, these functions are also helpful in comparing date values and computing intervals between dates.

·         Conversion functions: With the help of these functions you can convert data type of some data to another data type. For example, to concatenate a number value to a character string, you convert the numeric value to character data type.

    Aggregate Functions : In contrast to the single row functions, the functions under this category operate on  groups of rows, and return one result per group. These functions are used to retrieve summarized data for analysis and reporting purposes. In this book, you’ll be practicing with the following most common aggregate functions, supported by all platforms.

·         Average function – AVG

·         Count function – COUNT

·         Maximum function – MAX

·         Minimum function – MIN

·         Sum function – SUM

How AND/OR Logical Operators Are Used Together

You can use any number of AND and OR operators together in a single WHERE clause to create complex filtering. But, putting these two together in a WHERE clause may also put you in trouble. Let's see an example of this. You are asked to provide a list of all employees working either in department number 10 or 20 and are earning more than or equal to 6000. In response, you created a query like this:

Select   first_name||' '||last_name Employee, department_id, salary

from      employees

where   department_id=10 or department_id=20 and salary >= 6000

    Output : 

Look at the record of Jennifer Whalen. This should have been filtered out (because of the AND condition which says that the salary must be greater than or equal to 6000). Why this record appeared? The answer is the order of evaluation. As mentioned earlier, AND operator has higher precedence over OR, therefore the WHERE clause in the above statement was executed in the following order:  

1.       department_id = 20 and salary >= 6000 - returned second and third records.

2.       department_id = 10 - returned record number 1 of Jennifer Whalen.

This happened because of the precedence rule that joined wrong operators together. Again, the solution to the problem lies in the use of parentheses to explicitly group related operators. Modify the statement by enclosing the first two filters within parentheses, as shown below.

Select   first_name||' '||last_name Employee, department_id, salary

from     employees

where   (department_id=10 or department_id=20) and salary >= 6000 

    Output 

Since parentheses have a higher order of evaluation than either AND or OR operators, the OR condition (within the parentheses) was evaluated first like this:

1. (department_id=10 or department_id=20) 

2. salary >= 6000

Now, the statement fetches records of employees working under department number 10 or department number 20 earning 6000 or more. Although Jennifer is working in department number 10, she doesn’t meet the second criterion and is thus filtered out.