T-SQL Beginners’ Challenge #3 – Find the Factorial

This puzzle is part of the TSQL Challenge contest.

Author: Niladri Biswas

Introduction

This challenge though does not have any resemblance with the real time problem directly, but it measures about logical thinking. The problem is all about finding the factorial of numbers. Though it is known to most of us what a factorial is, but to recall the concept here is an example:

Factorial of 3 is 1*2*3 = 6 i.e. the factorial of a non-negative integer n, denoted by n!, is the product of all positive integers less than or equal to n.

Sample Data

Nums
-----------
0
1
3
5
10

Expected Results

Nums        Factorial
----------- -----------
0                    1
1                    1
3                    6
5                  120
10             3628800

Rules

  1. Nums should be sorted in Ascending Order.
  2. The program should run in SQL SERVER 2005 and above.
  3. The output should be in the same way as it has been shown. Column names should be exactly the same and the result must be sorted in Ascending order of Nums.
  4. The program has to be done by a single query and should begin either with a SELECT or WITH statement with no variables, temporary table, table variables permitted.
  5. You cannot use RBAR, cursors, loops etc. in your program.

Sample Script

DECLARE @Fact TABLE(Nums INT)
INSERT INTO @Fact
SELECT 0 UNION ALL
SELECT 1 UNION ALL
SELECT 3 UNION ALL
SELECT 5 UNION ALL
SELECT 10
 
SELECT * FROM @Fact

Restrictions

Notes