17:37

	Content	Login

SQL Problems and solutions S. I. Moiseenko

Content

SQL console

back go forward

Prepare for the second stage of testing

Typical problems

Once more about NULL values page 2

For example, let's consider the information about the models 1298 in the Laptop table. Let's carry out the query to learn the data:

Console

Execute

SELECT * 
FROM Laptop 
WHERE model = 1298;

Here are the results of carrying out of this query:

code	model	speed	ram	hd	price	screen
1	1298	350	32	4	700.0	11
4	1298	600	64	10	1050.0	15
6	1298	450	64	10	950.0	12

Let's consider the task of getting the average price of the model 1298. While all prices are known the solution of this task doesn't cause any doubts:

Console

Execute

SELECT model, AVG(price) avg_price 
FROM Laptop 
WHERE model = 1298
GROUP BY model;

model	avg_price
1298	900.0

Let now the price of the model with code 4 is unknown. If, as it was decided earlier, we substitute an unknown value, say, with zero (UPDATE laptop SET price=0 WHERE code=4), then we will get knowingly the wrong average price value – 550.0.

If we use the NULL value, the result will be quite right – 825.0, because null values will be ignored under grouping, in the result of which the average value will be calculated only by the models with the known price (that is the average value by two models).

So, as I tried to show, the NULL value is the inherent peculiarity of the relational model, and instead of critics I offer to learn correctly working with such values.

Notes:

For the sake of objectivity we recommend you to read the reasonable criticism by C.J. Date concerning use of NULL values [1]. E.F. Codd [6] has offered two different types of the NULL values corresponding to just those two aspects of their applying about which there was a speech above.

Pages

C.J.Date NULL E.F.Codd

Previous [ORDER BY clause]

[Three-valued logic and WHERE clause] Next

Last added:

Tags

aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONSTRAINT CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema DATEADD DATEDIFF DATENAME DATEPART DATETIME date_time functions DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates edge equi-join EXCEPT exercise (-2) More tags

The book was updated
month ago

SQL Problems and solutions

S. I. Moiseenko

Prepare for the second stage of testing

Typical problems

Once more about NULL values page 2