Database Reference
In-Depth Information
The result in Microsoft SQL Server 2012 is:
This is the specific Microsoft SQL Server 2012 error message. However, you will receive an
equivalent message from Microsoft Access, Oracle Database, DB2, or MySQL.
The second problem with the SQL built-in functions that you should understand is that you
cannot use them in an SQL WHERE clause. Thus, you cannot use the following SQL statement:
x
/* *** SQL-Query-CH02-34 *** */
SELECT *
FROM RETAIL_ORDER
WHERE OrderTotal > AVG(OrderTotal);
An attempt to use such a statement will also result in an error statement from the DBMS:
Again, this is the specific Microsoft SQL Server 2012 error message, but other DBMS prod-
ucts will give you an equivalent error message. In Chapter 7, you will learn how to obtain the
desired result of the above query using a sequence of SQL views.
SQL expressions in SQL SeLeCT Statements
It is possible to do basic arithmetic in SQL statements. For example, suppose we want to com-
pute the values of extended price, perhaps because we want to verify the accuracy of the data
in the ORDER_ITEM table. To compute the extended price, we can use the SQL expression
Quantity * Price in the SQL query:
/* *** SQL-Query-CH02-35 *** */
SELECT
Quantity * Price AS EP
FROM
ORDER_ITEM;
The result is:
An SQL expression is basically a formula or set of values that determines the exact re-
sults of an SQL query. We can think of an SQL expression as anything that follows an actual
or implied equal to (=) character (or any other relational operator, such as greater than [>],
less than [<], and so on) or that follows certain SQL keywords, such as LIKE and BETWEEN.
Thus, the SELECT clause in the preceding query includes the implied equal to (=) sign as EP =
Quantity * Price. For another example, in the WHERE clause:
WHERE Buyer IN ('Nancy Meyers', 'Cindy Lo', 'Jerry Martin');
the SQL expression consists of the enclosed set of three text values following the IN keyword.
 
 
Search WWH ::




Custom Search