SQL syntax Language  Index 1 2 [34 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 

Renaming columns and calculations in the result set

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:

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:

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.





Previous | Index | Next

Home SELECT exercises (rating stages) DML exercises Developers Rambler's Top100