The Compound Scalar Expression






The Compound Scalar Expression

The scalar expressions that were shown so far all consist of one component, such as a literal, column specification or system variable. They are all singular scalar expressions. In addition, SQL supports compound scalar expressions; see also Section 5.3. These are expressions that consist of more than one component. The features of a compound expression depend on its data type.

<compound scalar expression> ::=
   <compound numeric expression>       |
   <compound alphanumeric expression>  |
   <compound date expression>          |
   <compound time expression>          |
   <compound timestamp expression>     |
   <compound hexadecimal expression>


The Compound Numeric Expression

A compound numeric expression is a scalar expression that consists of, minimally, a singular scalar numeric expression extended with operators, brackets, and other scalar expressions. The result is a scalar value with a numeric data type.

<compound numeric expression> ::=
   [ + | - ] <scalar numeric expression> |
   ( <scalar numeric expression> )       |
   <scalar numeric expression>
      <mathematical operator> <scalar numeric 
expression>

<mathematical operator> ::= * | / | + | -


Here are some examples:

Compound numeric expression  Value
---------------------------  -----
14 * 8                         112
(-16 + 43) / 3                   9
5 * 4 + 2 * 10                  40
18E3 + 10E4                  118E3
12.6 / 6.3                     2.0

Figure lists the mathematical operators that can be used in a compound numeric expression.

The Mathematical Operators and Their Meaning

Mathematical operator

Meaning

*

multiply

/

divide

+

add

-

subtract


Before we give examples, we make the following comments:

  • Non-numeric expressions can occur in a compound numeric expression. The only requirement is that the final result of the entire expression returns a numeric value.

  • If required, brackets can be used in numeric compound expressions to indicate the order of execution.

  • If any component of a numeric compound expression has the value NULL, the value of the entire expression is, by definition, NULL.

  • The calculation of the value of a numeric compound expression is performed in keeping with the following priority rules: (1) left to right, (2) brackets, (3) multiplication and division, and (4) addition and subtraction.

Some examples are (we assume that the AMOUNT column has the value 25):

Compound numeric expression           Value
---------------------------           ---------
6 + 4 * 25                                  106
6 + 4 * AMOUNT                              106
0.6E1 + 4 * AMOUNT                          106
(6 + 4) * 25                                250
(50 / 10) * 5                                25
50 / (10 * 5)                                 1
NULL * 30                                  NULL

Incorrect compound numeric expressions are:

86 + 'Jim'
((80 + 4)
4/2 (* 3)

28. Get the match number and the sets won and lost for each match in which the number of sets won is greater than or equal to the number of sets lost multiplied by 2.

SELECT   MATCHNO, WON, LOST
FROM     MATCHES
WHERE    WON >= LOST * 2

The result is:

MATCHNO  WON  LOST
-------  ---  ----
      1    3     1
      3    3     0
      7    3     0

Explanation: To answer this query, we need the compound numeric expression LOST * 2.

What are the precision and the scale of the result of a calculation that involves two decimal values? For example, if we multiply a decimal(4,3) by a decimal(8,2), what will be the precision and the scale of that result? Here we show the rules that SQL uses to determine them. We assume that P1 and S1, respectively, are the precision and the scale of the first decimal value, and that P2 and S2 are those of the second value. In addition, we assume that there exists a function called MAX that enables us to determine the largest of two values.

Multiplication If we multiply two decimals, the scale of the result is equal to S1 + S2, and its precision is equal to P1+ P2. For example, multiplying a decimal(4,3) with a decimal (5,4) returns a decimal(9,7).

Addition If we add two decimals, the scale of the result is equal to S1 + S2 and its precision is equal to MAX(P1-S1,P2-S2) + MAX(S1,S2) + 1. Adding, for example, a decimal(4,2) to a decimal(7,4) returns a decimal(8,4).

Subtraction If we subtract a decimal from another, the scale of the result is equal to S1 + S2, and its precision is equal to MAX(P1-S1,P2-S2) + MAX(S1,S2) + 1. In other words, for subtraction and addition, the same rules apply.

SQL products do differ in the rules for division. To illustrate this, we show the rules for two products, namely, for MySQL and DB2.

Division according to MySQL The scale of the result of a division is equal to S1 + 4, and the precision is equal to P1 + 4. For example, if we divide a decimal(4,3) by a decimal(5,4) the result is a decimal(8,6).

Division according to DB2 The precision of the answer is 31, and the scale is equal to 31 P1 + S1 S2. This means that dividing a decimal(4,3) by a decimal(5,4) leads to a decimal (31,24), and that is different from MySQL.

Exercise 5.21:

Determine the values of the following numeric compound expressions:

400 - (20 * 10)
(400 - 20) * 10
400 - 20 * 10
400 / 20 * 10
111.11 * 3
222.22 / 2
50.00 * 3.00

The Compound Alphanumeric Expression

The value of a compound alphanumeric expression has an alphanumeric data type. With a compound expression, the values of alphanumeric expressions are concatenated using the || operator.

If the MySQL database server is started in a standard way, the || operator does not lead to the concatenation of alphanumeric values, but it is regarded as an OR operator to combine predicates. You can change this by changing the value of the system parameter SQL_MODE. Use the following SET statement:

SET SQL_MODE= 'PIPES_AS_CONCAT'

This specification is needed for the following statements. It applies only to the current session. By specifying the term GLOBAL in front of the system parameter SQL_MODE, it becomes a global specification.

<compound alphanumeric expression> ::=
   <scalar alphanumeric expression> "||"
      <scalar alphanumeric expression>


Two important rules apply to compound alphanumeric expressions:

  • Nonalphanumeric expressions can be used in a compound alphanumeric expression as long as they are first converted into alphanumeric values with, for example, a cast expression.

  • If somewhere in a compound alphanumeric expression the value NULL occurs, the value of the whole expression evaluates to NULL.

Examples:

Compound alphanumeric expression  Value
--------------------------------  --------
'Jim'                             Jim
'data'||'base'                    database
'da'||'ta'||'ba'||'se'            database
CAST(1234 AS CHAR(4))             1234
'Jim'||CAST(NULL AS CHAR)         NULL

29. Get the player number and the address of each player who lives in Stratford.

SELECT   PLAYERNO, TOWN || ' ' || STREET || ' ' || HOUSENO
FROM     PLAYERS
WHERE    TOWN = 'Stratford'

The result is:

PLAYERNO  TOWN || ' ' || STREET ...
--------  ---------------------------
       2  Stratford Stoney Road 43
       6  Stratford Haseltine Lane 80
       7  Stratford Edgecombe Way 39
      39  Stratford Eaton Square 78
      57  Stratford Edgecombe Way 16
      83  Stratford Magdalene Road 16a
     100  Stratford Haseltine Lane 80

Portability

In some SQL database servers, the + operator is used instead of the || operator.


Exercise 5.22:

For each player, get the player number followed by a concatenation of the data elements: the first initial, a full stop, a space, and the full last name.

Exercise 5.23:

For each team, get the number and the division of the team followed by the word division.

The Compound Date Expression

It is possible to calculate dates in SQL. For example, a few days, months, or years can be added to a date. The result of such a calculation is always a new date that is later (for addition) or earlier (for subtraction) than the original date expression.

When calculating the new date, the different number of days in the months and the leap years are taken into account. The calculation is done in a proleptic way, which means that no adjustment is made because in the Gregorian calendar, the days October 5 to 14 in the year 1582 are missing completely. This also means that we can use a date such as January 1, 1000, even though this date is earlier than the point in time when the Gregorian calendar was introduced. That means that what we call January 1, 1200, according to the Gregorian calendar now, probably was called differently then.

A calculation with dates is specified with a compound date expression.

<compound date expression> ::=
   <scalar date expression> [ + | - ] <date interval>

<date interval> ::=
      INTERVAL <interval length> <date interval unit>

<interval length> ::= <scalar expression>

<date interval unit> ::=
   DAY | WEEK | MONTH | QUARTER | YEAR


A compound date expression starts with a scalar expression (such as a date literal or a column with a date data type) followed by an interval that is added to or subtracted from the scalar expression.

An interval does not represent a certain moment in time, but a certain period or length of time. This period is expressed in a number of days, weeks, months, quarters, or years, or a combination of these five. Interval literals can be used to indicate how long, for example, a certain project lasted or how long a match took. Here are a few examples of interval literals:

Interval           Value
-----------------  -------------------
INTERVAL 10 DAY    period of 10 days
INTERVAL 100 WEEK  period of 100 weeks
INTERVAL 1 MONTH   period of 1 month
INTERVAL 3 YEAR    period of 3 years

Portability

In some SQL products, the keyword INTERVAL does not have to be specified or cannot be specified. In MySQL, it is mandatory. And in some products, the plural forms of the interval units can be used. For example, you can also specify DAYS or MONTHS.


An interval is not a complete expression. It must always occur within a compound date expression, and within such an expression, it cannot be specified first.

30. Get, for each penalty with a number higher than 5, the payment number, the day on which the penalty was paid and the date 7 days after the payment date.

SELECT  PAYMENTNO, PAYMENT_DATE, PAYMENT_DATE + INTERVAL 7 DAY
FROM    PENALTIES
WHERE   PAYMENTNO > 5

The result is:

PAYMENTNO  PAYMENT_DATE  PAYMENT_DATE + INTERVAL 7 DAY
---------  ------------  -----------------------------
        6  1980-12-08    1980-12-15
        7  1982-12-30    1983-01-06
        8  1984-11-12    1984-11-19

Explanation: The SELECT clause contains the expression DATE + INTERVAL 7 DAY. The second part after the plus is the interval. Each interval is preceded by the word INTERVAL. The word DAY is the interval unit, and 7 is the interval length. In this case, it is an interval of 7 days.

As stated, an interval should always follow an expression with a date data type. The following INSERT statement is, therefore, not allowed:

INSERT INTO TABLEX VALUES (INTERVAL 7 DAY)

31. Get the penalties that were paid between Christmas 1982 (December 25) and New Year's Eve.

SELECT  PAYMENTNO, PAYMENT_DATE
FROM    PENALTIES
WHERE   PAYMENT_DATE >= '1982-12-25'
AND     PAYMENT_DATE <= '1982-12-25' + INTERVAL 6 DAY

The result is:

PAYMENTNO  PAYMENT_DATE
---------  ------------
        7  1982-12-30

Explanation In the second condition of the WHERE clause after the less than or equal to operator, an expression is specified that holds a calculation in which 6 days are added to the date of Christmas 1982.

When a compound date expression contains more than one interval, it is essential that no calculations be made with interval literals only. Interval literals can be added to dates only. The expression DATE + (INTERVAL 1 YEAR + INTERVAL 20 DAY) will be rejected, for example. The reason is that brackets are used, and they force SQL to add the two interval literals to each other first, which is not allowed. The next two formulations cause no problems:

DATECOL + INTERVAL 1 YEAR + INTERVAL 20 DAY
(DATECOL + INTERVAL 1 YEAR) + INTERVAL 20 DAY

Instead of a literal, complex expressions can be used to specify an interval. In most cases, brackets are required. Here are a few more correct examples:

DATECOL + INTERVAL PLAYERNO YEAR + INTERVAL 20*16 DAY
DATECOL + INTERVAL (PLAYERNO*100) YEAR + INTERVAL
LENGTH('SQL') DAY

The scalar expression that is used to indicate the interval does not have to be a value with an integer data type; decimals and floats are allowed as well. However, SQL rounds the value first. The part after the decimal point simply is removed, and the value is rounded up or down. So, the following two expressions have the same value.

DATECOL + INTERVAL 1.8 YEAR
DATECOL + INTERVAL 2 YEAR

Portability

As indicated, how calculations with dates are specified depends on the SQL product. The examples here show the differences.


32. Add 30 days to a date.

DB2           : DATECOL + 30 DAYS
MySQL         : DATECOL + INTERVAL 30 DAY
Oracle        : DATECOL + 30
MS SQL Server : DATEADD(day, 30, DATECOL)
SQL2          : DATECOL + INTERVAL '30' DAY

33. Move a date 1 month further.

DB2           : DATECOL + 1 MONTH
MySQL         : DATECOL + INTERVAL 1 MONTH
Oracle        : ADD_MONTHS(DATECOL, 1)
MS SQL Server : DATEADD(month, 1, DATECOL)
SQL2          : DATECOL + INTERVAL '1' MONTH

Exercise 5.24:

Determine the result of the following compound date expressions. We assume that the column DATECOL has the value 29 February 2000.

 DATECOL + INTERVAL 7 DAY
 DATECOL  INTERVAL 1 MONTH
(DATECOL  INTERVAL 2 MONTH) + INTERVAL 2 MONTH
 CAST('2001-02-28' AS DATE) + INTERVAL 1 DAY
 CAST('2001-02-28' AS DATE) + INTERVAL 2 MONTH  INTERVAL 2
   MONTH

Exercise 5.25:

For each row in the COMMITTEE_MEMBERS table, get the player number, the begin date, and the begin date plus 2 months and 3 days.

The Compound Time Expression

As with dates, it is possible to calculate with times. For example, a number of hours, minutes, or seconds can be added to or subtracted from a specified time. The result after the calculation is always a new time.

Calculations with times are always specified as compound time expressions. This type of expression identifies a certain moment of a day to a millionth of a second precisely.

Portability

MySQL does not support actual compound time expressions yet. However, the scalar function ADDTIME can be used instead. In this book, we use this function as a substitute for the compound time expression.


<compound time expression> ::=
   ADDTIME( <scalar time expression> , <time 
interval> )

<time interval> ::= <scalar time expression>


A compound time expression starts with a scalar expression (such as a time literal or a column with the time data type) followed by an interval that is added to or subtracted from that scalar expression.

An interval does not represent a certain moment in time, but a certain period or length of time. This period is expressed in a number of hours, minutes, and seconds or a combination of these three. Time interval literals can be used to indicate how long, for example, a match took. An interval is specified the same way as a time expression:

Interval    Value
----------  ------------------
'10:00:00'  period of 10 hours
'00:01:00'  period of 1 minute
'00:00:03'  period of 3 seconds

Because times do not occur in the sample database, we create an additional table to show some examples.

Figure. Create a special variant of the MATCHES table that includes the date the match was played, the time it started, and the time it ended.

CREATE   TABLE MATCHES_SPECIAL
        (MATCHNO        INTEGER NOT NULL,
         TEAMNO          INTEGER NOT NULL,
         PLAYERNO        INTEGER NOT NULL,
         WON             SMALLINT NOT NULL,
         LOST            SMALLINT NOT NULL,
         START_DATE      DATE NOT NULL,
         START_TIME      TIME NOT NULL,
         END_TIME        TIME NOT NULL,
         PRIMARY KEY     (MATCHNO))

INSERT INTO MATCHES_SPECIAL VALUES
   (1, 1, 6, 3, 1, '2004-10-25', '14:10:12', '16:50:09')

INSERT INTO MATCHES_SPECIAL VALUES
   (2, 1, 44, 3, 2, '2004-10-25', '17:00:00', '17:55:48')

35. For each match, get the time it starts, plus 8 hours.

SELECT   MATCHNO, START_TIME,
         ADDTIME(START_TIME, '08:00:00')
FROM     MATCHES_SPECIAL

The result is:

MATCHNO  START_TIME  ADDTIME(START_TIME, '08:00:00')
-------  ----------  -------------------------------
      1  14:10:12    22:10:12
      2  17:00:00    25:00:00

36. Find the matches that ended 6 1/2 hours before midnight.

SELECT   MATCHNO , END_TIME
FROM     MATCHES_SPECIAL
WHERE    ADDTIME(END_TIME, '06:30:00') < '24:00:00'

The result is:

MATCHNO  END_TIME
-------  --------
      2  16:50:09

Calculations with times follow predictable rules. When a few seconds are added to a certain time, the sum of the number of seconds in the seconds component of the time and the number of seconds in the interval is calculated. For each 60 seconds that can be removed from the sum without the sum becoming less than 0, 1 is added to the minutes component. A comparable rule applies to the minutes component: For each 60 minutes that can be removed from the sum, 1 is added to the hours component. The hours component, however, can become greater than 24. The expression ADDTIME('10:00:00', '100:00:00') is allowed and returns the value 110:00:00.

Exercise 5.26:

Show the expression for adding 10 hours to the point in time 11:34:34.

Exercise 5.27:

What is the result of the expression ADDTIME('11:34:34', '24:00:00')?

The Compound Timestamp Expression

The value of a compound timestamp expression identifies a certain moment on a day in the Gregorian calendar, such as 4:00 in the afternoon on January 12, 1991.

<compound timestamp expression> ::=
   <scalar timestamp expression> [ + | - ]
      <timestamp interval>

<timestamp interval> ::=
   INTERVAL <interval length> <timestamp interval 
unit>

<interval length> ::= <scalar expression>

<timestamp interval unit> ::=
    MICROSECOND | SECOND | MINUTE | HOUR |
    DAY | WEEK | MONTH | QUARTER | YEAR


Just as it is possible to calculate with dates and times, it is possible to calculate with timestamps. For example, a couple months, days, hours, or seconds can be added to or subtracted from a timestamp. The rules for processing are according to those for calculating with dates and times.

If too many hours are added to a time, the surplus is simply thrown away. For a timestamp expression, this means that the days component increases. So, if 24 hours are added to something, the result would be the same as adding 1 day.

Portability

In some SQL products, the keyword INTERVAL does not have to be specified or cannot be specified. In MySQL, it is mandatory. And in some products, the plural forms of the interval units can be used. For example, you can also specify HOURS or MINUTES.


What holds for the timestamp literal also holds for the compound timestamp expression. When the result is stored in a table, SQL cuts off the microseconds part; see the following example.

37. Create a table in which timestamps can be stored.

CREATE TABLE TSTAMP (COL1 TIMESTAMP)

SET @TIME = TIMESTAMP('1980-12-08 23:59:59.59')

INSERT INTO TSTAMP VALUES (@TIME + INTERVAL 3 MICROSECOND)

SELECT COL1, COL1 + INTERVAL 3 MICROSECOND FROM TSTAMP

The result is:

COL1                 COL1 + INTERVAL 3 MICROSECOND
-------------------  -----------------------------
1980-12-08 23:59:59  1980-12-08 23:59:59.000003

Explanation: It is obvious that the microseconds are missing in the result of the SELECT statement, although they have been entered with an INSERT statement.

Exercise 5.28:

Show the expression for adding 1,000 minutes to the timestamp 1995-12-12 11:34:34.

Exercise 5.29:

Find for each penalty the payment number and the payment date followed by that same date, plus 3 hours, 50 seconds, and 99 microseconds.



 Python   SQL   Java   php   Perl 
 game development   web development   internet   *nix   graphics   hardware 
 telecommunications   C++ 
 Flash   Active Directory   Windows