Prepare for the second stage of testing

Numeric functions in SQL Server

The LOG(x) function returns the natural logarithm of the expression x. The returned result is of type   Тип данных с плавающей точкой. float.

Starting with  A database management system (DBMS) by Microsoft Corporation. SQL(Structured Query Language) is a database computer language designed for the retrieval and management of data in relational database management systems (RDBMS), database schema creation and modification, and database object access control management.SQL Server 2012, this function received an optional argument for specifying the base of the logarithm.

Console

Execute

SELECT LOG(2) ln, LOG(2, 10) log10;

SELECT LOG(2) ln, LOG(2, 10) log10;

    Scheme

No scheme

This query returns the natural and the base-10 logarithms of 2.

Besides, there is the function LOG10(x) inherited from previous versions that returns the base-10 logarithm of the expression x. It has been redundant from the very beginning, since instead of it, the well-known change-of-base formula for logarithms can be used:

LOGba = LOGca/LOGcb

Thus, the following three expressions will return the same result (0.301029995663981):

Console

Execute

SELECT LOG(2,10) log_1, LOG10(2) log_2, LOG(2)/LOG(10) log_3;

SELECT LOG(2,10) log_1, LOG10(2) log_2, LOG(2)/LOG(10) log_3;

    Scheme

No scheme

The function EXP(x), or the exponential function, returns the number e raised to the power of x. The returned result is of type FLOAT.

This function is inversely related to the LOG function:

Console

Execute

SELECT EXP(LOG(2)) a, LOG(EXP(2)) b;

SELECT EXP(LOG(2)) a, LOG(EXP(2)) b;

    Scheme

No scheme

Another useful feature of logarithms - namely, the fact that the logarithm of a product is the sum of the logarithms of the factors - allows us to calculate the product of values in a column, i.e.

log(a*b*c) = log(a) + log(b) + log(c).

We can easily prove the correctness of this equality by the following example:

Console

Execute

SELECT LOG(2*5*7) log_prod,  LOG(2) + LOG(5) + LOG(7) sum_log;

SELECT LOG(2*5*7) log_prod,  LOG(2) + LOG(5) + LOG(7) sum_log;

    Scheme

No scheme

Among the SQL aggregate functions, there isn't any for multiplying values. However, using the aforementioned feature of logarithms and some elementary transformations, we can reduce this task to calculating a sum of values. Indeed,

a*b*c = exp(log(a*b*c)) = exp(log(a) + log(b) + log(c)).

Calculate the factorial of the number equal to the quantity of rows in the Laptop table.

Solution

Console

Execute

SELECT EXP(SUM(LOG(rn))) FROM(
SELECT ROW_NUMBER() OVER(ORDER BY code) rn FROM laptop
) X;

SELECT EXP(SUM(LOG(rn))) FROM( SELECT ROW_NUMBER() OVER(ORDER BY code) rn FROM laptop ) X;

    Scheme

No scheme

Suggested exercises: 106