This SQLite Tutorial tutorial is very big. Some time its not loading properly. So I divided tutorial in two parts.

Click here to read the SQLite Tutorial Part one

Click here to download the complete SQLite Tutorial

### SQLite Tutorial Part Two Continue –

## The Power of the Sign Function — A Mathematical Explanation

It may come as a shock, but the problems in the last section, and much more, can be solved using the sign function. This is just the simple function in which sign(-200)=-1,.. sign(-1)=-1, sign(0)=0, sign(1)=1,.. sign(300)=1. So if the number is > 0 a 1 is returned. Zero is the only number that returns zero. All negative numbers return -1. Again, this simple function does not exist in sqlite, but you can easily create it, permanently. The next section will focus on the creation of this function, but here, the mathematical properties are explained.

The sign function can define the absolute value function abs() as the value of a number times its sign, or sign(x)*(x), abbreviated sign(x)(x). Here is a more detailed look at this function:

sign(x)(x) = abs(x) Example, assume x=3 sign(3)(3) = abs(3) 1*3 = 3 Example, assume x=-3 sign(-3)(-3) = abs(-3) -1*-3 = 3 Example, assume x=0 sign(0)(0) = abs(0) 0*0 = 0

Comparisons can be made with the sign function between two variables x and y. For instance, if sign(x-y) is 1, then, x is greater than y.

sign(x-y) is equal to 1 if x > y sign(x-y) is equal to 0 if x = y sign(x-y) is equal to -1 if x < y

Now look closely at the three statements below. The sign function starts to resemble an IF statement; a 1 is returned if and only if x = y. Thoroughly understanding the statements below is important, as the rest of the discussion quickly builds from these examples.

IF ( X==Y ) return 1; ELSE return 0; can be expressed as follows: 1 - abs(sign(x-y)) is equal to 0 if x > y 1 - abs(sign(x-y)) is equal to 1 if x = y 1 - abs(sign(x-y)) is equal to 0 if x < y

It is possible to return a 1 if and only if x < y, otherwise return a zero.

IF ( X < Y ) return 1; ELSE return 0; can be expressed as follows: 1-sign(1+sign(x-y)) is equal to 0 if x > y 1-sign(1+sign(x-y)) is equal to 0 if x = y 1-sign(1+sign(x-y)) is equal to 1 if x < y

The last example is known as the delta for x<y, or Delta[x<y]. This Delta notation will be used instead of writing it out in long form or using the IF statement. Therefore, the following is a summarized table of all the Delta functions or comparison operators.

Delta[x=y] = 1 - abs(sign(x-y)) Delta[x!=y] = abs(sign(x-y)) Delta[x<y] = 1-sign(1+sign(x-y)) Delta[x<=y] = sign(1-sign(x-y)) Delta[x>y] = 1-sign(1-sign(x-y)) Delta[x>=y] = sign(1+sign(x-y)) Delta[z=x AND z=y] = sign( Delta[z=x]*Delta[z=y] ) Delta[z=x OR z=y] = sign( Delta[z=x]+Delta[z=y] ) Delta[z>x AND z<y] = sign( Delta[z>x]*Delta[z<y] ) ... more can be defined ... but you get the idea

To summarize the following if statement, note the introduction of a third variable, z:

if( x==y ) return z; else return 0;

The above expression, in Delta notation, is the following:

z*Delta[x=y]

Here is an interesting example:

create table logic (value int); insert into logic (value) values (1); insert into logic (value) values (0); insert into logic (value) values (-1);

First, take the Cartesian product to show all possible combinations of x and y.

sqlite> .header on sqlite> .mode column sqlite> select x.value,y.value from logic x, logic y; x.value y.value ---------- ---------- 1 1 1 0 1 -1 0 1 0 0 0 -1 -1 1 -1 0 -1 -1

After the sign function is created (which we will do in the next section), using the above table, we could examine Delta[x!=y] as follows;

sqlite> .header on sqlite> .mode column sqlite> select x.value,y.value,abs(sign(x.value-y.value)) from logic x, logic y; x.value y.value abs(sign(x.value-y.value)) ---------- ---------- -------------------------- 1 1 0 1 0 1 1 -1 1 0 1 1 0 0 0 0 -1 1 -1 1 1 -1 0 1 -1 -1 0

Note that every time x is not equal to y, abs(sign(x.value-y.value)) returns a 1. After the sign function is created, these example will run. This is extremely powerful. To show that we have created a condition statement without using the where or group by statements, consider the following example. z.value will only be displayed in the right hand column when x.value != y.value.

sqlite> select x.value,y.value,z.value, z.value*abs(sign(x.value-y.value)) from logic x, logic y, logic z; x.value y.value z.value z.value*abs(sign(x.value-y.value)) ---------- ---------- ---------- ---------------------------------- 1 1 1 0 1 1 0 0 1 1 -1 0 1 0 1 1 1 0 0 0 1 0 -1 -1 1 -1 1 1 1 -1 0 0 1 -1 -1 -1 0 1 1 1 0 1 0 0 0 1 -1 -1 0 0 1 0 0 0 0 0 0 0 -1 0 0 -1 1 1 0 -1 0 0 0 -1 -1 -1 -1 1 1 1 -1 1 0 0 -1 1 -1 -1 -1 0 1 1 -1 0 0 0 -1 0 -1 -1 -1 -1 1 0 -1 -1 0 0 -1 -1 -1 0

## References

- Solving Complex SQL Problems
- This is a list of examples using the sign function.
- www.sqlite.org
- The homepage for the SQLite project.
- Lemon Parser Generator Tutorial
- A tutorial on the parser used with sqlite.