Databases and SQL

Calculating New Values

After carefully re-reading the expedition logs, we realize that the radiation measurements they report may need to be corrected upward by 5%. Rather than modifying the stored data, we can do this calculation on the fly as part of our query:

SELECT 1.05 * reading FROM Survey WHERE quant='rad';

When we run the query, the expression 1.05 * reading is evaluated for each row and displayed, although the entries in the database remain unchanged. Expressions can use any of the fields, all of usual arithmetic operators, and a variety of common functions (Exactly which ones depends on which database manager is being used). For example, we can convert temperature readings from Fahrenheit to Celsius and round to two decimal places:

SELECT taken, round(5*(reading-32)/9, 2) FROM Survey WHERE quant='temp';

We can also combine values from different fields to create new strings by using the string concatenation operator ||:

SELECT personal || ' ' || family FROM Person;

Beware! These operators will work on any field regardless of type, and the answers they give might be meaningless (Remember than in SQLite dates are strings).

SELECT 2 * dated FROM Visited;

Unions

The UNION operator combines the results of two queries:

SELECT * FROM Person WHERE ident='dyer' UNION SELECT * FROM Person WHERE ident='roe';