June 22, 2011, 1:45 a.m.

posted by novinick

## The Compound Scalar ExpressionThe 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.
## The Compound Numeric ExpressionA 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. 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.
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 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.
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 P
## The Compound Alphanumeric ExpressionThe 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 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
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.
Portability In some SQL database servers, the ## The Compound Date ExpressionIt 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.
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 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.
As stated, an interval should always follow an expression with a date data type. The following INSERT INTO TABLEX VALUES (INTERVAL 7 DAY) ## 31. Get the penalties that were paid between Christmas 1982 (December 25) and New Year's Eve.
Explanation In the second condition of the 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 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.
## 33. Move a date 1 month further.
## The Compound Time ExpressionAs 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
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 |

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 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 |

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 |

#### 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.

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.

- Comment