Integer division

Newbies in SQL Server sometimes fall in bewilderment when getting the results of queries like this

select 1/3 as a, 5/3 as b;

Somebody (I suspect, they are users of MySQL or Oracle) expects the results in somewhat as 

To resolve this bewilderment I shall tell, that operation “/” designates just the integer part of the result of dividing two integers  (namely, quotient) if both operands are integers. I.e. the separate designation for this operation is not supplied, and the symbol of “usual division” is used. If you wish to receive decimal number you need convert at least one operand to real data type explicitly (the first column) or implicitly (the second column):

select cast(1 as DEC(12,4))/3 as a, 5./3 as b;

Modulo operation is designated as “%” in SQL Server:

select 1 % 3 as a, 5 % 3 as b;

As for some other DBMS.

PostgreSQL behaves similarly SQL Server.

MySQL has a special operator DIV for obtaining a quotient:

select 1 DIV 3 as a, 5 DIV 3 as b;

The remainder of division can be received also a la Pascal:

select 1 MOD 3 as a, 5 MOD 3 as b;

though “standard” operator will also work:

select 1 % 3 as a, 5 % 3 as b;

Oracle has not operator for getting quotient in general, so the division result

select 1/3 as a, 5/3 as b from dual;

select CEIL(1/3) as a, CEIL(5/3) as b from dual;

select FLOOR(1/3) as a, FLOOR(5/3) as b from dual;

MOD function is used in Oracle for getting  remainder of division:

select MOD(1,3) as a, MOD(5,3) as b from dual;

At last, if the divider equals zero

select
1/0 as a;

Suggested exercises: 137