SQL Reporting  «Prev 

SQL Query Functions Review

This module covered the functions that you can use in your queries to create new columns of information based on the results of computations. These functions break down into three broad categories:
  1. String- (or character-) based functions that operate on string values. These contain functionalities like pulling the leftmost three characters, adding two strings together to create a new string, and so on.
  2. Numeric functions that work with numbers and are used to summarize, average, and generally calculate values based on values in the table or values hard-coded into the SELECT statement.
  3. Date functions that allow you to manipulate date information, including adding time increments to a known date, calculating differences between two dates, and so on.

Using these date functions against table information will give you natural break points to be used with the GROUP BY capabilities.

Listing 5.3 List the book prices discounted by 10 percent. The derived columns would have DBMSspecific default names if the
AS clauses were removed. See Figure 5.3 for the result. 
SELECT title_id, price,
0.10 AS "Discount",
price * (1 - 0.10) AS "New price"
FROM titles;
Listing 5.3. shown above 
title_id price Discount New price
-------- ----- -------- ---------
T01 21.99 0.10 19.79
T02 19.95 0.10 17.95
T03 39.95 0.10 35.96
T04 12.99 0.10 11.69
T05 6.95 0.10 6.25
T06 19.95 0.10 17.95
T07 23.95 0.10 21.56
T08 10.00 0.10 9.00
T09 13.95 0.10 12.56
T10 NULL 0.10 NULL
T11 7.99 0.10 7.19
T12 12.99 0.10 11.69
T13 29.99 0.10 26.99

Figure 5.3 shown above

Oracle

Oracle requires a FROM clause in a SELECT statement and so creates the dummy table DUAL automatically to be used for SELECTing a constant expression; search Oracle documentation for DUAL table. To run Listing 5.1, add a FROM clause that selects the constant value from DUAL: 
SELECT 2 + 3
FROM DUAL;

DB2

DB2 requires a FROM clause in a SELECT statement and so creates the dummy table SYSIBM.SYSDUMMY1 automatically to be used for SELECTing a constant expression; search DB2 documentation for SYSIBM.SYSDUMMY1. To run Listing 5.1, add a FROM clause that selects the constant value from SYSIBM.SYSDUMMY1: 

SELECT 2 + 3
FROM SYSIBM.SYSDUMMY1;

PostgreSQL

In older PostgreSQL versions, convert the floating- point number in Listing 5.3 to DECIMAL;
To run Listing 5.3, change the New price calculation in the SELECT clause to: 
price * CAST((1 - 0.10) AS DECIMAL)