By default, a column can hold NULL. If you not want to allow NULL value in a column, you will want to place a constraint on this column specifying that NULL is now not an allowable value.

For example, in the following statement,

CREATE TABLE Customer
(SID integer NOT NULL,
Last_Name varchar (30) NOT NULL,
First_Name varchar(30));

Columns “SID” and “Last_Name” cannot include NULL, while “First_Name” can include NULL.

An attempt to execute the following SQL statement,

INSERT INTO Customer (Last_Name, First_Name) values (’Wong’,'Ken’);

will result in an error because this will lead to column “SID” being NULL, which violates the NOT NULL constraint on that column.

The TRIM function in SQL is used to remove specified prefix or suffix from a string. The most common pattern being removed is white spaces. This function is called differently in different databases:

• MySQL: TRIM(), RTRIM(), LTRIM()
• Oracle: RTRIM(), LTRIM()
• SQL Server: RTRIM(), LTRIM()

The syntax for these trim functions are:

TRIM([[LOCATION] [remstr] FROM ] str): [LOCATION] can be either LEADING, TRAILING, or BOTH. This function gets rid of the [remstr] pattern from either the beginning of the string or the end of the string, or both. If no [remstr] is specified, white spaces are removed.

LTRIM(str): Removes all white spaces from the beginning of the string.

RTRIM(str): Removes all white spaces at the end of the string.

Example 1:

SELECT TRIM(’   Sample   ’);

Result:

‘Sample’

Example 2:

SELECT LTRIM(’   Sample   ’);

Result:

‘Sample   ’

Example 3:

SELECT RTRIM(’   Sample   ’);

Result:

‘   Sample’

Sometimes it is necessary to combine together (concatenate) the results from several different fields. Each database provides a way to do this:

• MySQL: CONCAT()
• Oracle: CONCAT(), ||
• SQL Server: +

The syntax for CONCAT() is as follows:

CONCAT(str1, str2, str3, …): Concatenate str1, str2, str3, and any other strings together. Please note the Oracle CONCAT() function only allows two arguments — only two strings can be put together at a time using this function. However, it is possible to concatenate more than two strings at a time in Oracle using ‘||’.

Let’s look at some examples. Assume we have the following table:

Table Geography

Example 1:

MySQL/Oracle:
SELECT CONCAT(region_name,store_name) FROM Geography
WHERE store_name = ‘Boston’;

Result:

‘EastBoston’

Example 2:

Oracle:
SELECT region_name || ‘ ‘ || store_name FROM Geography
WHERE store_name = ‘Boston’;

Result:

‘East Boston’

Example 3:

SQL Server:
SELECT region_name + ‘ ‘ + store_name FROM Geography
WHERE store_name = ‘Boston’;

Result:

‘East Boston

The Substring function in SQL is used to grab a portion of the stored data. This function is called differently for the different databases:

• MySQL: SUBSTR(), SUBSTRING()
• Oracle: SUBSTR()
• SQL Server: SUBSTRING()

The most frequent uses are as follows (we will use SUBSTR() here):

SUBSTR(str,pos): Select all characters from <str> starting with position <pos>. Note that this syntax is not supported in SQL Server.

SUBSTR(str,pos,len): Starting with the <pos>th character in string <str> and select the next <len> characters.

Assume we have the following table:

Table Geography

Example 1:

SELECT SUBSTR(store_name, 3)
FROM Geography
WHERE store_name = ‘Los Angeles’;

Result:

’s Angeles’

Example 2:

SELECT SUBSTR(store_name,2,4)
FROM Geography
WHERE store_name = ‘San Diego’;

Result:

‘an D’

The Replace function in SQL is used to update the content of a string. The function call is REPLACE() for MySQL, Oracle, and SQL Server. The syntax of the Replace function is:

Replace(str1, str2, str3): In str1, find where str2 occurs, and replace it with str3.

Assume we have the following table:

Table Geography

If we apply the following Replace function:

SELECT REPLACE(region_name, ‘east’, ‘astern’)
FROM Geography;

Result:

The Length function in SQL is used to get the length of a string. This function is called differently for the different databases:

• MySQL: LENGTH()
• Oracle: LENGTH()
• SQL Server: LEN()

The syntax for the Length function is as follows:

Length(str): Find the length of the string str.

Let’s take a look at some examples. Assume we have the following table:

Table Geography

Example 1:

SELECT Length(store_name)
FROM Geography
WHERE store_name = ‘Los Angeles’;

Result:

11

Example 2:

SELECT region_name, Length(region_name)
FROM Geography;

Result:

In the SQL Alias section, we saw that the syntax for using table and column aliases is as follows:

SELECT “table_alias”.”column_name1″ “column_alias”
FROM “table_name” “table_alias”

The keyword AS is used to assign an alias to the column or a table. It is insert between the column name and the column alias or between the table name and the table alias. The syntax for using AS is as follows:

SELECT “table_alias”.”column_name1″ AS “column_alias”
FROM “table_name” AS “table_alias”

Let’s take a look at the same example as we used in SQL Alias. Assume we have the following table, Store_Information,

Table Store_Information

To find total sales by store using AS as part of the table and column alias, we type in:

SELECT A1.store_name Store, SUM(A1.Sales) AS “Total Sales”
FROM Store_Information AS A1
GROUP BY A1.store_name

Result:

We next focus on the use of aliases. There are two types of aliases that are used most frequently: column alias and table alias.

In short, column aliases exist to help organizing output. In the previous example, whenever we see total sales, it is listed as SUM(sales). While this is comprehensible, we can envision cases where the column heading can be complicated (especially if it involves several arithmetic operations). Using a column alias would greatly make the output much more readable.

The second type of alias is the table alias. This is accomplished by putting an alias directly after the table name in the FROM clause. This is convenient when you want to obtain information from two separate tables (the technical term is ‘perform joins’). The advantage of using a table alias when doing joins is readily apparent when we talk about joins.

Before we get into joins, though, let’s look at the syntax for both the column and table aliases:

SELECT “table_alias”.”column_name1″ “column_alias”
FROM “table_name” “table_alias”

Briefly, both types of aliases are placed directly after the item they alias for, separate by a white space. We again use our table, Store_Information,

Table Store_Information

We use the same example as that in the SQL GROUP BY section, except that we have put in both the column alias and the table alias:

SELECT A1.store_name Store, SUM(A1.Sales) “Total Sales”
FROM Store_Information A1
GROUP BY A1.store_name

Result:

The SUM function is used to calculate the total for a column. The syntax is,

SELECT SUM(”column_name”)
FROM “table_name”

For example, if we want to get the sum of all sales from the following table,

Table Store_Information

we would type in

SELECT SUM(Sales) FROM Store_Information

Result:

$2750 represents the sum of all Sales entries: $1500 + $250 + $300 + $700.