June 22, 2011, 1:45 a.m.
posted by novinick
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.
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.
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:
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.
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.
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.
Two important rules apply to compound alphanumeric expressions:
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.
In some SQL database servers, the + operator is used instead of the || operator.
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.
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
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.
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.
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
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 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.
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.
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.
35. For each match, get the time it starts, plus 8 hours.
36. Find the matches that ended 6 1/2 hours before midnight.
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.
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.
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.