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.

Using the AND/OR Logical Operators in SQL

Always remember the following rules for the AND and OR logical operators:

·         AND will return rows only when both conditions are TRUE.

·         OR requires either condition to be TRUE.

·         AND has a higher precedence over OR.

Note that both these operators can be used together in the WHERE clause of a SQL statement to construct compound logical expression.

The WHERE clause in the following example is made up of two conditions, and the keyword AND is used to join them. AND instructs the database management system software to return only rows that meet all the conditions specified. If a record has department number 20, but the job id is not MK_MAN (Marketing Manager), it is not retrieved. Similarly, records having job id MK_MAN in other departments will not to be retrieved as well.

In the first example below, the AND operator is used to search employees working in department number 20 as MK_MAN. As you can see, the sole fetched record fulfills both conditions - mentioned before and after the AND operator. The example contains a single AND clause and is thus made up of two filter conditions. To narrow the result, you are allowed to add more filter conditions, each separated by an AND keyword like this: WHERE department_id=20 AND job_id=’MK_MAN’ AND first_name=’Michael’

    SQL Statement :

SELECT       first_name, department_id, job_id

FROM          employees

WHERE       department_id=20 AND job_id=’MK_MAN’;

The second logical operator that you can use in you SQL statement is the OR operator which is less restrictive and thus returns more rows. Most database management systems do not even evaluate the second condition in an OR WHERE clause if the first condition has already been met i.e. the rows are returned without considering the second condition if the first condition evaluates to true.

The query mentioned below, fetches all employees who are either in department number 20 OR who work as MK_MAN. The second record returned by the query doesn’t fulfills the job id part of the condition, but since it satisfies the first condition (department_id =20), it is picked by the query for display. 

    SQL Statement :

SELECT     first_name, department_id, job_id

FROM        employees

WHERE     department_id=20 OR job_id=’MK_MAN’;

    Output : 

Using the ISNULL Operator in SQL

A null value, as mentioned earlier, is a value that is unavailable or inapplicable. It is neither said to be a zero nor can it be represented as a space. Also, you cannot use an equal to operator (=) in the WHERE clause to match null values. The valid procedure to find a null match is to use the IS NULL operator. In the first two examples below you won’t get any record. In the first statement the equal to (=) comparison operator is used, and in the second one, the statement is tried with an empty space (‘ ‘). In fact, the second query will also throw an “invalid number” error, because you are comparing a numeric column with an empty string. The third query is correct which uses the IS NULL operator to display records of sales representatives. To get negating records (other than sales personnel), use the NOT logical operator in the WHERE clause like this: commission_pct IS NOT NULL.

    SQL Statements :

SELECT       first_name, last_name, commission_pct

FROM          employees

WHERE      commission_pct=NULL;

SELECT       first_name, last_name, commission_pct

FROM          employees

WHERE      commission_pct=’ ‘;

SELECT       first_name, last_name, commission_pct

FROM          employees

WHERE      commission_pct  IS  NULL;

    Output :

Date Manipulation Functions

The following examples present date manipulation functions for different implementations, starting with Oracle. The Employees table contains a date column named Hire_Date. All the examples in this section retrieve a list of all employees who were hired in 2003.

The first one below is for Oracle. In this example we used nested functions to first convert the year portion in the hire date column to character – to_char(hire_date,’YYYY’).  The character value is then converted  to a number with the help of the to_number function, and is matched with the specified year.

    SQL Statement :

SELECT    first_name, hire_date

FROM       employees

WHERE    to_number(to_char(hire_date, 'YYYY')) = 2003;

    Output :

The table that follows describes how you would issue the above statement in other DBMSs to get the same output using platform specific date functions.

DBMS	SQL STATEMENT WITH SPECIFIC DATE FUNCTIONS
Alternate in Oracle using the BETWEEN operator	SELECT first_name, hire_date FROM employees WHERE hire_date BETWEEN to_date('01-JAN-2003') and to_date('31-DEC-2003');
Microsoft SQL Server	SELECT first_name, hire_date FROM employees WHERE DATEPART(yy, hire_date) = 2003;
Microsoft Access	SELECT first_name, hire_date FROM employees WHERE DATEPART('yyyy', hire_date) = 2003;
MariaDB and MySQL	SELECT first_name, hire_date FROM employees WHERE YEAR(hire_date) = 2003;
SQLite	SELECT first_name, hire_date FROM employees WHERE strftime('%Y', hire_date) = 2003;
PostgreSQL	SELECT first_name, hire_date FROM employees WHERE DATE_PART('year', hire_date) = 2003;

There is a long list of such functions for each platform. Refer to your DBMS documentation for the list of the date-time manipulation functions it supports. Since we’re connected to an Oracle database session, we’ll explore some date functions specific to Oracle in the next few sections.