Calculating New Values

Overview

Questions

• How can I calculate new values on the fly?

Objectives

• Write queries that calculate new values for each selected record.

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:

SQL

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

1.05 * reading
10.311
8.19
8.8305
7.581
4.5675
2.2995
1.533
11.8125

When we run the query, the expression 1.05 * reading is evaluated for each row. 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:

SQL

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

taken	round(5*(reading-32)/9, 2)
734	-29.72
735	-32.22
751	-28.06
752	-26.67

As you can see from this example, though, the string describing our new field (generated from the equation) can become quite unwieldy. SQL allows us to rename our fields, any field for that matter, whether it was calculated or one of the existing fields in our database, for succinctness and clarity. For example, we could write the previous query as:

SQL

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

taken	Celsius
734	-29.72
735	-32.22
751	-28.06
752	-26.67

We can also combine values from different fields, for example by using the string concatenation operator ||:

SQL

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

personal
William Dyer
Frank Pabodie
Anderson Lake
Valentina Roerich
Frank Danforth

Challenge

Fixing Salinity Readings

After further reading, we realize that Valentina Roerich was reporting salinity as percentages. Write a query that returns all of her salinity measurements from the Survey table with the values divided by 100.

Show me the solution

SQL

SELECT taken, reading / 100 FROM Survey WHERE person = 'roe' AND quant = 'sal';

taken	reading / 100
752	0.416
837	0.225

Challenge

Unions

The UNION operator combines the results of two queries:

SQL

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

id	personal	family
dyer	William	Dyer
roe	Valentina	Roerich

The UNION ALL command is equivalent to the UNION operator, except that UNION ALL will select all values. The difference is that UNION ALL will not eliminate duplicate rows. Instead, UNION ALL pulls all rows from the query specifics and combines them into a table. The UNION command does a SELECT DISTINCT on the results set. If all the records to be returned are unique from your union, use UNION ALL instead, it gives faster results since it skips the DISTINCT step. For this section, we shall use UNION.

Use UNION to create a consolidated list of salinity measurements in which Valentina Roerich’s, and only Valentina’s, have been corrected as described in the previous challenge. The output should be something like:

taken	reading
619	0.13
622	0.09
734	0.05
751	0.1
752	0.09
752	0.416
837	0.21
837	0.225

Show me the solution

SQL

SELECT taken, reading FROM Survey WHERE person != 'roe' AND quant = 'sal' UNION SELECT taken, reading / 100 FROM Survey WHERE person = 'roe' AND quant = 'sal' ORDER BY taken ASC;

Challenge

Selecting Major Site Identifiers

The site identifiers in the Visited table have two parts separated by a ‘-’:

SQL

SELECT DISTINCT site FROM Visited;

site
DR-1
DR-3
MSK-4

The sites are identified by a few letters, a dash, and a number. Suppose you want to see what are the major sites where data has been collected. In our data, those would be the sites denoted with the letter codes such as DR or MSK. However, some major site identifiers (i.e. the letter codes) are two letters long and some are three. Therefore, we need to run some kind of operation on the site string to be able to get just the letter codes.

SQLite has functions that would enable us to do just that. The “in string” function instr(X, Y) returns the 1-based index of the first occurrence of string Y in string X, or 0 if Y does not exist in X. For example, the query:

SQL

select INSTR(site, '-') as indexes FROM Visited;

Would return the position of the - character in each site:

indexes
3
3
3

The substring function substr(X, I, [L]) returns the substring of X starting at index I, with an optional length L. For example, the query:

SQL

SELECT  substr(site, 1, 1) as first_char FROM Visited;

Would return the first letter of each site:

first_char
D
D
D

Combine these two functions to produce a list of unique major site identifiers. (For this data, the list should contain only “DR” and “MSK”).

Show me the solution

SQL

SELECT DISTINCT substr(site, 1, instr(site, '-') - 1) AS MajorSite FROM Visited;

Key Points

• Queries can do the usual arithmetic operations on values.
• Use UNION to combine the results of two or more queries.