SQL Run

Database Programming

5. Operators and Functions

In this chapter

Creating Derived Columns

Performing Arithmetic Operations

Determining the Order of Evaluation

Concatenating Strings with ||

Extracting a Substring with SUBSTRING()

Changing String Case with UPPER() and LOWER()

Trimming Characters with TRIM()

Finding the Length of a String with CHARACTER_LENGTH()

Finding Substrings with POSITION()

Performing Datetime and Interval Arithmetic

Getting the Current Date and Time

Getting User Information

Converting Data Types with CAST()

Evaluating Conditional Values with CASE

Checking for Nulls with COALESCE()

Comparing Expressions with NULLIF()

Operators and functions let you calculate results derived from column values, system-determined values, constants, and other data. You can perform:

An operator is a symbol or keyword indicating an operation that acts on one or more elements. The elements, called operands, are SQL expressions. Recall from “Tips for SQL Syntax” in Chapter 3 that an expression is any legal combination of symbols and tokens that evaluates to a single value (or null). In the expression price * 2, for example, * is the operator, and price and 2 are its operands.

A function is a built-in, named routine that performs a specialized task. Most functions take parenthesized arguments, which are values you pass to the function that the function then uses to perform its task. Arguments can be column names, literals, nested functions, or more-complex expressions. In UPPER(au_lname), for example, UPPER is the function name, and au_lname is the argument.

Creating Derived Columns

You can use operators and functions to create derived columns. A derived column is the result of a calculation and is created with a SELECT-clause expression that is something other than a simple reference to a column. Derived columns don’t become permanent columns in a table; they’re for display and reporting purposes.

The values in a derived column are often computed from values in existing columns, but you can also create a derived column by using a constant expression (such as a string, number, or date) or system value (such as the system time). Listing 5.1 shows a SELECT statement that yields a trivial arithmetic calculation; it needs no FROM clause because it doesn’t retrieve data from a table. Figure 5.1 shows the result.

Listing 5.1A constant expression in a SELECT clause. No FROM clause is needed because I’m not retrieving data from a table. See Figure 5.1 for the result.

SELECT 2 + 3;

Figure 5.1Result of Listing 5.1. This result is a table with one row and one column.

2 + 3
-----
    5

Recall from “Tables, Columns, and Rows” in Chapter 2 that closure guarantees that every result is a table, so even this simple result is a table: a 1 × 1 table that contains the value 5. If I retrieve a column along with a constant, then the constant appears in every row of the result (Listing 5.2 and Figure 5.2).

Listing 5.2Here, I’ve retrieved a column and a constant expression. See Figure 5.2 for the result.

SELECT au_id, 2 + 3
  FROM authors;

Figure 5.2Result of Listing 5.2. The constant is repeated in each row.

au_id 2 + 3
----- -----
A01       5
A02       5
A03       5
A04       5
A05       5
A06       5
A07       5

Your DBMS will assign the derived column a default name, typically the expression itself as a quoted identifier. You should name derived columns explicitly with an AS clause because system-assigned names can be long, unwieldy, and inconvenient for database applications to refer to; see “Creating Column Aliases with AS” in Chapter 4 (Listing 5.3 and Figure 5.3).

Listing 5.3List the book prices discounted by 10 percent. The derived columns would have DBMS-specific default names if the AS clauses were removed. See Figure 5.3 for the result.

SELECT
    title_id,
    price,
    0.10 AS "Discount",
    price * (1 - 0.10) AS "New price"
  FROM titles;

Figure 5.3Result of Listing 5.3.

title_id price Discount New price
-------- ----- -------- ---------
T01      21.99     0.10     19.79
T02      19.95     0.10     17.95
T03      39.95     0.10     35.96
T04      12.99     0.10     11.69
T05       6.95     0.10      6.25
T06      19.95     0.10     17.95
T07      23.95     0.10     21.56
T08      10.00     0.10      9.00
T09      13.95     0.10     12.56
T10       NULL     0.10      NULL
T11       7.99     0.10      7.19
T12      12.99     0.10     11.69
T13      29.99     0.10     26.99

Tips for Derived Columns

Performing Arithmetic Operations

A monadic (or unary) arithmetic operator performs a mathematical operation on a single numeric operand to produce a result. The – (negation) operator changes the sign of its operand, and the not-very-useful + (identity) operator leaves its operand unchanged. A dyadic (or binary) arithmetic operator performs a mathematical operation on two numeric operands to produce a result. These operators include the usual ones: + (addition), – (subtraction), * (multiplication), and / (division). Table 5.1 lists SQL’s arithmetic operators (expr is a numeric expression).

Table 5.1Arithmetic Operators
Operator What It Does
–expr Reverses the sign of expr
+expr Leaves expr unchanged
expr1 + expr2 Sums expr1 and expr2
expr1 – expr2 Subtracts expr2 from expr1
expr1 * expr2 Multiplies expr1 and expr2
expr1 / expr2 Divides expr1 by expr2

To change the sign of a number:

Listing 5.4The negation operator changes the sign of a number. See Figure 5.4 for the result.

SELECT
    title_id,
    -advance AS "Advance"
  FROM royalties;

Figure 5.4Result of Listing 5.4. Note that zero has no sign (is neither positive nor negative).

title_id Advance
-------- -----------
T01        -10000.00
T02         -1000.00
T03        -15000.00
T04        -20000.00
T05       -100000.00
T06        -20000.00
T07      -1000000.00
T08             0.00
T09             0.00
T10             NULL
T11       -100000.00
T12        -50000.00
T13        -20000.00

To add, subtract, multiply, or divide:

Listing 5.5List the biographies by descending revenue (= price × sales). See Figure 5.5 for the result.

SELECT
    title_id,
    price * sales AS "Revenue"
  FROM titles
  WHERE type = 'biography'
  ORDER BY price * sales DESC;

Figure 5.5Result of Listing 5.5.

title_id Revenue
-------- -----------
T07      35929790.00
T12       1299012.99
T06        225834.00
T10             NULL

Tips for Arithmetic Operations

Other Operators and Functions

All DBMSs provide plenty of operators and functions in addition to those defined in the SQL standard (or covered in this book). In fact, the standard is playing catch-up—many of the functions introduced in revised standards have existed for years in DBMSs. The earlier standards were so anemic that they left SQL weaker than a desktop calculator. Search your DBMS documentation for operators and functions to find mathematical, statistical, financial, scientific, trigonometric, conversion, string, datetime, bitwise, system, metadata, security, and other categories.

Determining the Order of Evaluation

Precedence determines the priority of various operators when more than one operator is used in an expression. Operators with higher precedence are evaluated first. Arithmetic operators (+, –, *, and so on) have higher precedence than comparison operators (<, =, >, and so on), which have higher precedence than logical operators (NOT, AND, OR), so the expression

a or b * c >= d

is equivalent to

a or ((b * c) >= d)

Operators with lower precedence are less binding than those with higher precedence. Table 5.2 lists operator precedences from most to least binding. Operators in the same row have equal precedence.

Table 5.2Order of Evaluation (Highest to Lowest)
Operator Description
+, – Monadic identity, monadic negation
*, / Multiplication, division
+, – Addition, subtraction
=, <>, <, <=, >, >= Comparison operators
NOT Logical NOT
AND Logical AND
OR Logical OR

Associativity determines the order of evaluation in an expression when adjacent operators have equal precedence. SQL uses left-to-right associativity.

You don’t need to memorize all this information. You can use parentheses to override precedence and associativity rules (Listing 5.7 and Figure 5.7).

Listing 5.7The first and second columns show how to use parentheses to override precedence rules. The third and fourth columns show how to use parentheses to override associativity rules. See Figure 5.7 for the result.

SELECT
    2 + 3 * 4   AS "2+3*4",
    (2 + 3) * 4 AS "(2+3)*4",
    6 / 2 * 3   AS "6/2*3",
    6 / (2 * 3) AS "6/(2*3)";

Figure 5.7Result of Listing 5.7.

2+3*4 (2+3)*4 6/2*3 6/(2*3)
----- ------- ----- -------
   14      20     9       1

Tips for Order of Evaluation

Concatenating Strings with ||

Use the operator || to combine, or concatenate, strings. The operator’s important characteristics are:

To concatenate strings:

Listing 5.8List the authors’ first and last names, concatenated into a single column and sorted by last name/first name. See Figure 5.8 for the result.

SELECT au_fname || ' ' || au_lname
    AS "Author name"
  FROM authors
  ORDER BY au_lname ASC, au_fname ASC;

Figure 5.8Result of Listing 5.8.

Author name
-----------------
Sarah Buchman
Wendy Heydemark
Hallie Hull
Klee Hull
Christian Kells
 Kellsey
Paddy O'Furniture

Listing 5.9List biography sales by descending sales order. Here, I need to convert sales from an integer to a string. See Figure 5.9 for the result.

SELECT
    CAST(sales AS CHAR(7))
    || ' copies sold of title '
    || title_id
      AS "Biography sales"
  FROM titles
  WHERE type = 'biography'
    AND sales IS NOT NULL
  ORDER BY sales DESC;

Figure 5.9Result of Listing 5.9.

Biography sales
--------------------------------
1500200 copies sold of title T07
100001  copies sold of title T12
11320   copies sold of title T06

Listing 5.10List biographies by descending publication date. Here, I need to convert pubdate from a datetime to a string. See Figure 5.10 for the result.

SELECT
    'Title '
    || title_id
    || ' published on '
    || CAST(pubdate AS CHAR(10))
      AS "Biography publication dates"
  FROM titles
  WHERE type = 'biography'
    AND pubdate IS NOT NULL
  ORDER BY pubdate DESC;

Figure 5.10Result of Listing 5.10.

Biography publication dates
---------------------------------
Title T12 published on 2000-08-31
Title T06 published on 2000-07-31
Title T07 published on 1999-10-01

Listing 5.11List all the authors named Klee Hull. See Figure 5.11 for the result.

SELECT au_id, au_fname, au_lname
  FROM authors
  WHERE au_fname || ' ' || au_lname
        = 'Klee Hull';

Figure 5.11Result of Listing 5.11.

au_id au_fname au_lname
----- -------- --------
A04   Klee     Hull

Tips for Concatenating Strings

Extracting a Substring with SUBSTRING()

Use the function SUBSTRING() to extract part of a string. The function’s important characteristics are:

To extract a substring:

Listing 5.12Split the publisher IDs into alphabetic and numeric parts. The alphabetic part of a publisher ID is the first character, and the remaining characters are the numeric part. See Figure 5.12 for the result.

SELECT
    pub_id,
    SUBSTRING(pub_id FROM 1 FOR 1)
      AS "Alpha part",
    SUBSTRING(pub_id FROM 2)
      AS "Num part"
  FROM publishers;

Figure 5.12Result of Listing 5.12.

pub_id Alpha part Num part
------ ---------- --------
P01    P          01
P02    P          02
P03    P          03
P04    P          04

Listing 5.13List the first initial and last name of the authors from New York State and Colorado. See Figure 5.13 for the result.

SELECT
    SUBSTRING(au_fname FROM 1 FOR 1)
    || '. '
    || au_lname
      AS "Author name",
    state
  FROM authors
  WHERE state IN ('NY', 'CO');

Figure 5.13Result of Listing 5.13.

Author name  state
------------ -----
S. Buchman   NY
W. Heydemark CO
C. Kells     NY

Listing 5.14List the authors whose area code is 415. See Figure 5.14 for the result.

SELECT au_fname, au_lname, phone
  FROM authors
  WHERE SUBSTRING(phone FROM 1 FOR 3) = '415';

Figure 5.14Result of Listing 5.14.

au_fname au_lname phone
-------- -------- ------------
Hallie   Hull     415-549-4278
Klee     Hull     415-549-4278

Tips for Substrings

Changing String Case with UPPER() and LOWER()

Use the function UPPER() to return a string with lowercase letters converted to uppercase, and use the function LOWER() to return a string with uppercase letters converted to lowercase. The functions’ important characteristics are:

To convert a string to uppercase or lowercase:

Listing 5.15List the authors’ first names in lowercase and last names in uppercase. See Figure 5.15 for the result.

SELECT
    LOWER(au_fname) AS "Lower",
    UPPER(au_lname) AS "Upper"
  FROM authors;

Figure 5.15Result of Listing 5.15.

Lower     Upper
--------- -----------
sarah     BUCHMAN
wendy     HEYDEMARK
hallie    HULL
klee      HULL
christian KELLS
          KELLSEY
paddy     O'FURNITURE

Listing 5.16List the titles that contain the characters MO, regardless of case. All the letters in the LIKE pattern must be uppercase for this query to work. See Figure 5.16 for the result.

SELECT title_name
  FROM titles
  WHERE UPPER(title_name) LIKE '%MO%';

Figure 5.16Result of Listing 5.16.

title_name
-------------------------
200 Years of German Humor
I Blame My Mother

Tips for String Case

Case-Insensitive Comparisons

In DBMSs that perform case-sensitive WHERE-clause comparisons by default, UPPER() or LOWER() often is used to make case-insensitive comparisons:

WHERE UPPER(au_fname) = 'JOHN'

If you’re sure that your data are clean, then it’s faster to look for only reasonable letter combinations than to use case functions:

WHERE au_fname = 'JOHN'
   OR au_fname = 'John'

UPPER() and LOWER() affect characters with diacritical marks (such as accents and umlauts): UPPER('ö') is 'Ö', for example. If your data contain such characters and you’re making case-insensitive comparisons such as

WHERE UPPER(au_fname) = 'JOSÉ'

then make sure that your DBMS doesn’t lose the marks on conversion. UPPER('José') should be 'JOSÉ', not 'JOSE'. See also “Filtering Rows with WHERE” in Chapter 4.

Trimming Characters with TRIM()

Use the function TRIM() to remove unwanted characters from the ends of a string. The function’s important characteristics are:

To trim spaces from a string:

Listing 5.17This query strips leading, trailing, and both leading and trailing spaces from the string '  AAA  '. The < and > characters show the extent of the trimmed strings. See Figure 5.17 for the result.

SELECT
  '<' || '  AAA  ' || '>'
    AS "Untrimmed",
  '<' || TRIM(LEADING FROM '  AAA  ') || '>'
    AS "Leading",
  '<' || TRIM(TRAILING FROM '  AAA  ') || '>'
    AS "Trailing",
  '<' || TRIM('  AAA  ') || '>'
     AS "Both";

Figure 5.17Result of Listing 5.17.

Untrimmed Leading   Trailing  Both
--------- --------- --------- -----
<  AAA  > <AAA  >   <  AAA>   <AAA>

To trim characters from a string:

Listing 5.18Strip the leading H from the authors’ last names that begin with H. See Figure 5.18 for the result.

SELECT
    au_lname,
    TRIM(LEADING 'H' FROM au_lname)
      AS "Trimmed name"
  FROM authors;

Figure 5.18Result of Listing 5.18.

au_lname    Trimmed name
----------- ------------
Buchman     Buchman
Heydemark   eydemark
Hull        ull
Hull        ull
Kells       Kells
Kellsey     Kellsey
O'Furniture O'Furniture

Listing 5.19List the three-character title IDs that start with T1, ignoring leading and trailing spaces. See Figure 5.19 for the result.

SELECT title_id
  FROM titles
  WHERE TRIM(title_id) LIKE 'T1_';

Figure 5.19Result of Listing 5.19.

title_id
--------
T10
T11
T12
T13

Tips for Trimming Characters

Finding the Length of a String with CHARACTER_LENGTH()

Use the function CHARACTER_LENGTH() to return the number of characters in a string. The function’s important characteristics are:

To find the length of a string:

Listing 5.20List the lengths of the authors’ first names. See Figure 5.20 for the result.

SELECT
    au_fname,
    CHARACTER_LENGTH(au_fname) AS "Len"
  FROM authors;

Figure 5.20Result of Listing 5.20.

au_fname  Len
--------- ---
Sarah       5
Wendy       5
Hallie      6
Klee        4
Christian   9
            0
Paddy       5

Listing 5.21List the books whose titles contain fewer than 30 characters, sorted by ascending title length. See Figure 5.21 for the result.

SELECT
    title_name,
    CHARACTER_LENGTH(title_name) AS "Len"
  FROM titles
  WHERE CHARACTER_LENGTH(title_name) < 30
  ORDER BY CHARACTER_LENGTH(title_name) ASC;

Figure 5.21Result of Listing 5.21.

title_name                    Len
----------------------------- ---
1977!                           5
Kiss My Boo-Boo                15
How About Never?               16
I Blame My Mother              17
Exchange of Platitudes         22
200 Years of German Humor      25
Spontaneous, Not Annoying      25
But I Did It Unconsciously     26
Not Without My Faberge Egg     26
Just Wait Until After School   28
Ask Your System Administrator  29

Tips for String Lengths

Finding Substrings with POSITION()

Use the function POSITION() to locate a particular substring within a given string. The function’s important characteristics are:

To find a substring:

Listing 5.22List the position of the substring e in the authors’ first names and the position of the substring ma in the authors’ last names. See Figure 5.22 for the result.

SELECT
    au_fname,
    POSITION('e' IN au_fname) AS "Pos e",
    au_lname,
    POSITION('ma' IN au_lname) AS "Pos ma"
  FROM authors;

Figure 5.22Result of Listing 5.22.

au_fname  Pos e au_lname    Pos ma
--------- ----- ----------- ------
Sarah         0 Buchman          5
Wendy         2 Heydemark        6
Hallie        6 Hull             0
Klee          3 Hull             0
Christian     0 Kells            0
              0 Kellsey          0
Paddy         0 O'Furniture      0

Listing 5.23List the books whose titles contain the letter u somewhere within the first 10 characters, sorted by descending position of the u. See Figure 5.23 for the result.

SELECT
    title_name,
    POSITION('u' IN title_name) AS "Pos"
  FROM titles
  WHERE POSITION('u' IN title_name)
        BETWEEN 1 AND 10
  ORDER BY POSITION('u' IN title_name) DESC;

Figure 5.23Result of Listing 5.23.

title_name                    Pos
----------------------------- ---
Not Without My Faberge Egg     10
Spontaneous, Not Annoying      10
How About Never?                8
Ask Your System Administrator   7
But I Did It Unconsciously      2
Just Wait Until After School    2

Tips for Finding Substrings

Performing Datetime and Interval Arithmetic

DBMS compliance with standard SQL datetime and interval operators and functions is spotty because DBMSs usually provide their own extended (nonstandard) operators and functions that perform date and time arithmetic. For information about datetime and interval data types, see “Datetime Types” and “Interval Types” in Chapter 3.

Use the same operators introduced in “Performing Arithmetic Operations” earlier in this chapter to perform datetime and interval arithmetic. The common temporal operations are:

Some operations are undefined; adding two dates makes no sense, for example. Table 5.3 lists the valid SQL operators involving datetimes and intervals. The “Operator Overloading” section explains why you can use the same operator to perform different operations.

Table 5.3Datetime and Interval Operations
Operation Result
Datetime – Datetime Interval
Datetime + Interval Datetime
Datetime – Interval Datetime
Interval + Datetime Datetime
Interval + Interval Interval
Interval – Interval Interval
Interval * Numeric Interval
Interval / Numeric Interval
Numeric * Interval Interval

Operator Overloading

Recall that the +, –, *, and / operators also are used for numeric operations and that Microsoft DBMSs use + for string concatenation as well. Operator overloading is the assignment of more than one function to a particular operator. The operation performed depends on the data types of the operands involved. Here, the +, –, *, and / operators behave differently with numbers than they do with datetimes and intervals (as well as strings, in the case of Microsoft Access and Microsoft SQL Server). Your DBMS might overload other operators and functions as well.

Function overloading is the assignment of more than one behavior to a particular function, depending on the data types of the arguments involved. The MySQL CONCAT() function (see the DBMS tip in “Tips for Concatenating Strings” earlier in this chapter), for example, takes nonstring as well as string arguments. Nonstrings cause CONCAT() to perform additional conversions that it doesn’t need to perform on strings.

The function EXTRACT() isolates a single field of a datetime or interval and returns it as a number. EXTRACT() typically is used in comparison expressions or for formatting results.

To extract part of a datetime or interval:

Listing 5.24List the books published in the first half of the years 2001 and 2002, sorted by descending publication date. See Figure 5.24 for the result.

SELECT
    title_id,
    pubdate
  FROM titles
  WHERE EXTRACT(YEAR FROM pubdate)
        BETWEEN 2001 AND 2002
    AND EXTRACT(MONTH FROM pubdate)
        BETWEEN 1 AND 6
  ORDER BY pubdate DESC;

Figure 5.24Result of Listing 5.24.

title_id pubdate
-------- ----------
T09      2002-05-31
T08      2001-06-01
T05      2001-01-01

Tips for Datetime and Interval Arithmetic

Getting the Current Date and Time

Use the functions CURRENT_DATE, CURRENT_TIME, and CURRENT_TIMESTAMP to get the current date and time from the system clock of the particular computer where the DBMS is running.

To get the current date and time:

Listing 5.25Print the current date, time, and timestamp. See Figure 5.25 for the result.

SELECT
    CURRENT_DATE AS "Date",
    CURRENT_TIME AS "Time",
    CURRENT_TIMESTAMP AS "Timestamp";

Figure 5.25Result of Listing 5.25.

Date       Time     Timestamp
---------- -------- -------------------
2002-03-10 10:09:24 2002-03-10 10:09:24

Listing 5.26List the books whose publication date falls within 90 days of the current date or is unknown, sorted by descending publication date (refer to Figure 5.25 for the “current” date of this query). See Figure 5.26 for the result.

SELECT title_id, pubdate
  FROM titles
  WHERE pubdate
        BETWEEN CURRENT_TIMESTAMP - INTERVAL 90 DAY
            AND CURRENT_TIMESTAMP + INTERVAL 90 DAY
     OR pubdate IS NULL
  ORDER BY pubdate DESC;

Figure 5.26Result of Listing 5.26.

title_id pubdate
-------- ----------
T09      2002-05-31
T10      NULL

Tips for the Current Date and Time

Getting User Information

Use the function CURRENT_USER to identify the active user within the database server.

To get the current user:

Listing 5.27Print the current user. See Figure 5.27 for the result.

SELECT CURRENT_USER AS "User";

Figure 5.27Result of Listing 5.27.

User
------
jsmith

Tips for User Information

Converting Data Types with CAST()

In many situations, your DBMS will convert, or cast, data types automatically. It might allow you to use numbers and dates in character expressions such as concatenation, for example, or it will promote numbers automatically in mixed arithmetic expressions (see “Tips for Arithmetic Operations” earlier in this chapter). Use the function CAST() to convert an expression of one data type to another data type when your DBMS doesn’t perform the conversion automatically. For information about data types, see “Data Types” in Chapter 3. The function’s important characteristics are:

To convert one data type to another:

Listing 5.28Convert the book prices from the DECIMAL data type to INTEGER and CHAR(8) data types. The < and > characters show the extent of the CHAR(8) strings. Your result will be either Figure 5.28a or 5.28b, depending on whether your DBMS truncates or rounds integers.

SELECT
    price
      AS "price(DECIMAL)",
    CAST(price AS INTEGER)
      AS "price(INTEGER)",
    '<' || CAST(price AS CHAR(8)) || '>'
      AS "price(CHAR(8))"
  FROM titles;

Figure 5.28aResult of Listing 5.28. You’ll get this result if your DBMS truncates decimal numbers to convert them to integers.

price(DECIMAL) price(INTEGER) price(CHAR(8))
-------------- -------------- --------------
         21.99             21 <21.99   >
         19.95             19 <19.95   >
         39.95             39 <39.95   >
         12.99             12 <12.99   >
          6.95              6 <6.95    >
         19.95             19 <19.95   >
         23.95             23 <23.95   >
         10.00             10 <10.00   >
         13.95             13 <13.95   >
          NULL           NULL NULL
          7.99              7 <7.99    >
         12.99             12 <12.99   >
         29.99             29 <29.99   >

Figure 5.28bResult of Listing 5.28. You’ll get this result if your DBMS rounds decimal numbers to convert them to integers.

price(DECIMAL) price(INTEGER) price(CHAR(8))
-------------- -------------- --------------
         21.99             22 <21.99   >
         19.95             20 <19.95   >
         39.95             40 <39.95   >
         12.99             13 <12.99   >
          6.95              7 <6.95    >
         19.95             20 <19.95   >
         23.95             24 <23.95   >
         10.00             10 <10.00   >
         13.95             14 <13.95   >
          NULL           NULL NULL
          7.99              8 <7.99    >
         12.99             13 <12.99   >
         29.99             30 <29.99   >

Listing 5.29List history and biography book sales with a portion of the book title, sorted by descending sales. The CHAR(20) conversion shortens the title to make the result more readable. See Figure 5.29 for the result.

SELECT
    CAST(sales AS CHAR(8))
    || ' copies sold of '
    || CAST(title_name AS CHAR(20))
      AS "History and biography sales"
  FROM titles
  WHERE sales IS NOT NULL
    AND type IN ('history', 'biography')
  ORDER BY sales DESC;

Figure 5.29Result of Listing 5.29.

History and biography sales
--------------------------------------------
1500200  copies sold of I Blame My Mother
100001   copies sold of Spontaneous, Not Ann
11320    copies sold of How About Never?
10467    copies sold of What Are The Civilia
9566     copies sold of 200 Years of German
566      copies sold of 1977!

Tips for Converting Data Types

Evaluating Conditional Values with CASE

The CASE expression and its shorthand equivalents, COALESCE() and NULLIF(), let you take actions based on a condition’s truth value (true, false, or unknown). The CASE expression’s important characteristics are:

To use a simple CASE expression:

Listing 5.30Raise the price of history books by 10 percent and psychology books by 20 percent, and leave the prices of other books unchanged. See Figure 5.30 for the result.

SELECT
    title_id,
    type,
    price,
    CASE type
      WHEN 'history'
        THEN price * 1.10
      WHEN 'psychology'
        THEN price * 1.20
      ELSE price
    END
      AS "New price"
  FROM titles
  ORDER BY type ASC, title_id ASC;

Figure 5.30Result of Listing 5.30.

title_id type       price New price
-------- ---------- ----- ---------
T06      biography  19.95     19.95
T07      biography  23.95     23.95
T10      biography   NULL      NULL
T12      biography  12.99     12.99
T08      children   10.00     10.00
T09      children   13.95     13.95
T03      computer   39.95     39.95
T01      history    21.99     24.19
T02      history    19.95     21.95
T13      history    29.99     32.99
T04      psychology 12.99     15.59
T05      psychology  6.95      8.34
T11      psychology  7.99      9.59

To use a searched CASE expression:

Listing 5.31List the books categorized by different sales ranges, sorted by ascending sales. See Figure 5.31 for the result.

SELECT
    title_id,
    CASE
      WHEN sales IS NULL
        THEN 'Unknown'
      WHEN sales <= 1000
        THEN 'Not more than 1,000'
      WHEN sales <= 10000
        THEN 'Between 1,001 and 10,000'
      WHEN sales <= 100000
        THEN 'Between 10,001 and 100,000'
      WHEN sales <= 1000000
        THEN 'Between 100,001 and 1,000,000'
      ELSE 'Over 1,000,000'
    END
      AS "Sales category"
  FROM titles
  ORDER BY sales ASC;

Figure 5.31Result of Listing 5.31.

title_id Sales category
-------- -----------------------------
T10      Unknown
T01      Not more than 1,000
T08      Between 1,001 and 10,000
T09      Between 1,001 and 10,000
T02      Between 1,001 and 10,000
T13      Between 10,001 and 100,000
T06      Between 10,001 and 100,000
T04      Between 10,001 and 100,000
T03      Between 10,001 and 100,000
T11      Between 10,001 and 100,000
T12      Between 100,001 and 1,000,000
T05      Between 100,001 and 1,000,000
T07      Over 1,000,000

Tips for CASE

Checking for Nulls with COALESCE()

The function COALESCE() returns the first non-null expression among its arguments. COALESCE() often is used to display a specific value instead of a null in a result, which is helpful if your users find nulls confusing. COALESCE() is just shorthand for a common form of the searched CASE expression.

COALESCE(expr1, expr2, expr3)

is equivalent to:

CASE
  WHEN expr1 IS NOT NULL THEN expr1
  WHEN expr2 IS NOT NULL THEN expr2
  ELSE expr3
END

To return the first non-null value:

Listing 5.32List the publishers’ locations. If the state is null, then print N/A. See Figure 5.32 for the result.

SELECT
    pub_id,
    city,
    COALESCE(state, 'N/A') AS "state",
    country
  FROM publishers;

Figure 5.32Result of Listing 5.32.

pub_id city          state country
------ ------------- ----- -------
P01    New York      NY    USA
P02    San Francisco CA    USA
P03    Hamburg       N/A   Germany
P04    Berkeley      CA    USA

Tips for COALESCE

Comparing Expressions with NULLIF()

The function NULLIF() compares two expressions and returns null if they are equal or the first expression otherwise. NULLIF() typically is used to convert a user-defined missing, unknown, or inapplicable value to null.

Rather than use a null, some people prefer to represent a missing value with, say, the number −1 or −99, or the string ‘N/A’, ‘Unknown’, or ‘Missing’. DBMSs have clear rules for operations that involve nulls, so it’s sometimes desirable to convert user-defined missing values to nulls. If you want to calculate the average of the values in a column, for example, then you’d get the wrong answer if you had −1 values intermingled with the real, non-missing values. Instead, you can use NULLIF() to convert the −1 values to nulls, which your DBMS will ignore during calculations.

NULLIF() is just shorthand for a common form of the searched CASE expression.

NULLIF(expr1, expr2)

is equivalent to:

CASE
  WHEN expr1 = expr2 THEN NULL
  ELSE expr1
END

To return a null if two expressions are equivalent:

Listing 5.33In the table titles, the column contract contains zero if no book contract exists. This query changes the value zero to null. Nonzero values aren’t affected. See Figure 5.33 for the result.

SELECT
    title_id,
    contract,
    NULLIF(contract, 0) AS "Null contract"
  FROM titles;

Figure 5.33Result of Listing 5.33.

title_id contract Null contract
-------- -------- -------------
T01             1             1
T02             1             1
T03             1             1
T04             1             1
T05             1             1
T06             1             1
T07             1             1
T08             1             1
T09             1             1
T10             0          NULL
T11             1             1
T12             1             1
T13             1             1

Tips for NULLIF

Avoiding Division by Zero

Suppose you want to calculate the male–female ratios for various school clubs, but you discover that the following query fails and issues a divide-by-zero error when it tries to calculate ratio for the Lord of the Rings Club, which has no women:

SELECT
    club_id, males, females,
    males/females AS ratio
  FROM school_clubs;

You can use NULLIF to avoid division by zero. Rewrite the query as:

SELECT
    club_id, males, females,
    males/NULLIF(females,0) AS ratio
  FROM school_clubs;

Any number divided by NULL gives NULL, and no error is generated.