loading..
Русский    English
15:11

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
  1. SELECT *
  2. FROM Laptop
  3. 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
  1. SELECT model, AVG(price) avg_price
  2. FROM Laptop
  3. WHERE model = 1298
  4. 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.

Bookmark and Share
Pages 1 2 3
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-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.
Rambler's Top100