The column names listed in the SELECT clause can be changed. This can be done to increase readability because the column names are frequently abbreviated to simplify typing. The keyword AS being used for renaming can be neglected in accordance with the standard, since it implicit.
For example, the following query

SELECT ram AS Mb, hd Gb FROM pc WHERE cd = '24x';

renames the ram column as Mb (megabytes) and the hd column as Gb (gigabytes). This query will return the base memory amount and the hard drive storage on computers with a 24-speed CD-ROM:

Mb	Gb
64	8
32	10

Renaming is especially desired when using value-calculation expressions in SELECT clause. These expressions give the data which may not be directly derived from database tables. An expression including the column names of the table contained in the FROM clause is calculated for each row of the result set. For example, RAM amount in kilobytes can be obtained when writing:

SELECT ram * 1024 AS Kb, hd Gb FROM Pc WHERE cd = '24x';

Now the following result will be obtained:

Kb	Gb
65536	8
32768	10

Sometimes, an explanation needs to be placed along with the corresponding value. To make this, add string expression as complementary column. For example, the query

SELECT ram, 'Mb' AS ram_units, hd, 'Gb' AS hd_units FROM pc WHERE cd = '24x';

gives the following result:

ram	ram_units	hd	hd_units
64	Mb	8	Gb
32	Mb	10	Gb

Should the expression have no specified name, a default naming way will be used, dependable on DBMS used. For example, the names like expression1 and so on will be used in MS Access, while the column returned in MS SQL Server has no title at all.

Predicates (part 2)

LIKE predicate

Syntax
LIKE::=
<expression for calculating the string value>
[NOT] LIKE
<expression for calculating the string value>
[ESCAPE <symbol>]
The LIKE predicate compares the string specified in the first expression for calculating the string value, which is refered to as a value to test, with the pattern that is defined in the second expression for calculating the string value. The pattern may contain any combination of wildcards such as:

• Underline symbol (_), which can be used instead of any single character in the value to test.
• Percent sign (%), which replaces any string of zero or more characters in the value to test.

If the value to test matches the pattern with the wildcards, the preducate equals TRUE. Below are some examples of the patterns.

Pattern	Description
'abc%'	Any rows that begin with the characters "abc".
'abc_'	The rows with the length of strictly 4 characters, the first characters of the string being "abc".
'%z'	Any character sequence that necessarily ends with the character "z".
'%Rostov%'	Any character sequence including the word "Rostov" at any position of the string.

Example. Find all the ships the class names of those ends with the character '�':

SELECT * FROM Ships WHERE class LIKE '%o';

The result of that query is the following table:

name	class	launched
Haruna	Kongo	1916
Hiei	Kongo	1914
Kirishima	Kongo	1915
Kongo	Kongo	1913
Musashi	Yamato	1942
Yamato	Yamato	1941

Example. Find all the ships the class names ending with the character '�' but not with the characters 'go':

SELECT * FROM Ships WHERE class NOT LIKE '%go' AND class LIKE '%o';

name	class	launched
Musashi	Yamato	1942
Yamato	Yamato	1941

If the string to search includes a wildcard as a character, then specify a control symbol the ESCAPE clause. This control symbol is to be used in the pattern prior to the wildcard to warn that the wildcard here should be considered as an ordinary character. For example, if the value to search includes the character "_", the pattern '%_%' results in returning all the record from the table. Hence, the pattern should be written as

'%#_%' ESCAPE '#'

To search the string "25%", the following predicate may be used:

LIKE '25|%' ESCAPE '|'

The truth value of LIKE predicate is assigned in accordance with the following rules:

• If either value to test or the pattern, or control symbol is NULL, the truth value is UNKNOWN.
• Otherwise, if the value to test and the pattern have zero-length, the truth value is TRUE.
• Otherwise, if the value to test matches the pattern, LIKE predicate evaluates to TRUE.
• If none of the above conditions is met, LIKE predicate evaluates to FALSE.

Using NULL value in the search conditions

Predicate
IS [NOT] NULL

allows for checking the absence (availability) of value in the table columns. In such cases, using ordinary comparison predicates can lead to incorrect results, because comparing with NULL value evaluates to UNKNOWN.

So, searching for no-price records in the PC table (typist errors) may be performed with the following operator:

SELECT * FROM Pc WHERE price IS NULL;

Suggested exercises: 44.