At the interview, they asked to explain the results of three queries:

SELECT 2/3; SELECT CONVERT (DECIMAL (18,6))2/3; SELECT CONVERT (DECIMAL (18,6),2)/3;

The first query rounds the value to zero, okay. Why is the second here unsolvable? Why in the third gives the value of eight sixes after zero?

  • In SQL questions, always specify a DBMS. It is also desirable in the tag (so that the specialists on a specific DBMS are more likely to see your question), and in the question, and with the version, because, in different versions, the behavior may be different. - German Borisov

2 answers 2

The question (or rather, the answer to it) absolutely depends on the specific DBMS.

For example, the following values ​​will be obtained in MySQL (after eliminating syntactic errors, of course):

 mysql> SELECT 2/3; +--------+ | 2/3 | +--------+ | 0.6667 | +--------+ 1 row in set (0.03 sec) mysql> SELECT CONVERT (2/3, DECIMAL (18,6)); +-------------------------------+ | CONVERT (2/3, DECIMAL (18,6)) | +-------------------------------+ | 0.666667 | +-------------------------------+ 1 row in set (0.02 sec) mysql> SELECT CONVERT (2, DECIMAL (18,6))/3; +-------------------------------+ | CONVERT (2, DECIMAL (18,6))/3 | +-------------------------------+ | 0.6666666667 | +-------------------------------+ 1 row in set (0.00 sec)

    Why is the second here unsolvable?

    Because CONVERT is a function with two parameters.

    Why in the third gives the value of eight sixes after zero?

    Eight digits after the decimal point is the representation of DECIMAL(20,8) , resulting from the division of DECIMAL(18,6) and the whole.

    Compare

     SELECT CONVERT(DECIMAL(18,6), 2.0/3); -> 0.666666

    and

     SELECT CONVERT(DECIMAL(18,6),2)/3 as Value, SQL_VARIANT_PROPERTY(CONVERT(DECIMAL(18,6),2)/3, 'BaseType') as Type, SQL_VARIANT_PROPERTY(CONVERT(DECIMAL(18,6),2)/3, 'Precision') as Precision, SQL_VARIANT_PROPERTY(CONVERT(DECIMAL(18,6),2)/3, 'Scale') as Scale; -> 0.66666666 decimal 20 8
    • Thanks, so in the second query I was wrong with the SELECT CONVERT brackets (DECIMAL (18,6) 2/3); and the solution is .000000. - vienya
    • @vienya yes. still need a comma between the parameters - type and value - Igor