|
|
SQL provides a rich set of functionality for performing calculations with the data stored in a database. Some of this is already apparent from the WHERE clauses you've been using in earlier chapters because you've seen how you can perform basic comparisons to filter data. You've also looked at calculated columns as a means of reshaping database data. As yet, though, you haven't explored the full range of options available or all the places that you can use calculations.
Consider the following SQL query:
SELECT ColumnA, Expression1 AS ColumnB FROM Table WHERE Expression2;
Although Expression1 can simply be a column name and Expression2 can be just a comparison involving a column, you have more options at your disposal. Expression1 evaluates to a value to be stored in ColumnB, but that evaluation can be a complex as you like, involving as many columns and literal values as you like (as well as functions, as you'll see later in the chapter). Expression2 has to evaluate to Boolean true or false values for each row. If it's true, a row is filtered and included in the results, but this can involve much more than a simple comparison.
To build up the various expressions that can be placed into SQL statements (not just queries—the same applies to other SQL statements such as UPDATE), you combine column names with operators and/or literal values. There's even nothing stopping you from doing something as simple as using just a literal value for a calculated column; however, because the result will be just a set of rows with a set value for a certain column, this isn't particularly useful. In general, you will be performing calculations based on the data stored in a row while avoiding simply returning column values.
SQL includes many operators for use in expressions, some of which you've seen already, such as + and *. Several of the keywords you've seen earlier in the book are also technically operators—including AND, LIKE, and BETWEEN—although in practice some of these work in a more complex way than the simpler arithmetic or comparison ones.
The full list of arithmetic operators is +, -, *, /, and %. Of these, the first four are for addition (doubling as string concatenation in SQL Server and MySQL), subtraction, multiplication, and division, and the fifth is the slightly more complex modulus operator. This operator returns the integer remainder of a division operation. For example:
19 % 7 = 5
This is because seven fits into 19 twice, leaving a remainder of five.
You saw all the comparison operators in Chapter 2, "Retrieving Data with SQL," but to recap the full list is =, !=, <>, <, <=, !<, > , >=, and !>.
There are also several bitwise operators that perform bitwise operations on numeric values, where the binary equivalent of numbers are operated on. Table 5-1 shows these operators.
|
Bitwise Operator |
Operation |
|---|---|
|
& |
Bitwise AND |
|
| |
Bitwise OR |
|
^ |
Bitwise XOR (exclusive OR) |
|
~ |
Bitwise NOT |
Finally, you can use parentheses to specify the order that operators are executed. This is necessary in situations such as the following:
5 + 3 * 2
In this example, the * operator takes precedence over the + operator and executes first, so the result will be 11. However, using parentheses you could write the following:
(5 + 3) * 2
Here the calculation in parentheses executes first, making the result 16.
| Note |
For a full list of operators, precedence, and so on, see the documentation that accompanies the Relational Database Management System (RDBMS) you're using. |
Now you've seen all the building blocks of expressions (apart from functions), so it's time to put them together and see the rules behind expression building.
In general, you'll be building either standard expressions that evaluate to a calculated value or Boolean expressions that evaluate to true or false.
For calculated values, you'll typically work with column values, literal values, and arithmetic operators to build values. For example:
ColumnA / ColumnB
Or, for example:
(ColumnA + 2) * ColumnB
For Boolean expressions, you build up Boolean calculations with comparison and logical operators:
(ColumnA < 2) OR NOT (ColumnB >= 5)
In all cases, the column values used are for whatever row is currently being processed.
As you can see, although there isn't a huge amount to cover here because there aren't that many operators, the possibilities are limitless, allowing complex expressions to be built with ease.
 |
Connect to the InstantUniversity database and execute this query:
SELECT StudentID, Mark,
(Mark * 100) / 80.0 AS ActualPercentage, IfPassed,
Comments
FROM StudentExam
WHERE ExamID = 3;
In this example you're assuming that the Mark column in the StudentExam table gives an absolute mark rather than a percentage result and that the total marks available in the exam with an ExamID of 3 is 80. You use an expression to convert the value in the Mark column into a percentage based on this information as follows:
(Mark * 100) / 80.0
Note that the Mark column is of type int. For this reason, the total number of marks, 80, is expressed as a floating-point value (by appending .0), which forces the integer result of Mark * 100 to be converted into a floating-point value. The reason for this is that integers can always be expressed as floating-point values, but not vice versa. To guarantee a successful result, the RDBMS performs this conversion automatically for you. If you used the following, there would be a possibility that no conversion would be performed, and you would lose accuracy because the result would be expressed as an integer:
(Mark * 100) / 80
| Note |
This is dependent on the RDBMS in use—SQL Server and DB2 treat the result as an integer, but Oracle, MySQL, and Access allow floating-point values. |
The output from this query is as follows:
StudentID Mark ActualPercentage IfPassed Comments ------------ ----- -------------------- -------- -------------- 2 44 55.000000 1 Scraped through 6 78 97.500000 1 Excellent work 8 46 57.500000 1 Poor result
|
|
|