SQL Challenge for Beginners - Find Factorial

The Challenge for Beginners

This challenge is not a 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 Input Data

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

Expected Output

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

Script

Here is the script to generate the sample data

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

Solution :

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

DECLARE @no INT,@f BIGINT,@nums INT

DECLARE C1 CURSOR FOR SELECT nums FROM @fact
OPEN C1
FETCH NEXT FROM C1 INTO @no
WHILE @@FETCH_STATUS=0
BEGIN
        SELECT @f=1,@nums=@no
        WHILE @no>0
        BEGIN
             SET @f=@f*@no
             SET @no=@no-1 
        END
        DECLARE @t TABLE(nums INT,Fact BIGINT)
        INSERT INTO @T SELECT @Nums,@F
        FETCH NEXT FROM C1 INTO @no
END
CLOSE C1
DEALLOCATE C1 

SELECT * FROM @T ORDER BY 1

Just Paste Above solution in your SQL Server You will get a

Output that really you need.

SQL Challenge

Here is a challenge that takes you away from those repetitive boring type of queries that you write over and over again, several times a day. All of us, the database people, are familiar with thinking in set based manner as well as row by row style. Here is something that is very interesting where you might need to process records in a 'three-line-at-a-time' fashion.

For the purpose of this challenge, imagine that you are working for a bank which just decided to scan all the banking documents. Assume that they have an old fashioned scanner which scans the documents and produces a text file with the customer number. So far so good. Well, not really! Unfortunately the scanner produces a graphical representation of the customer number using three lines of symbols: space, unerscores and pipe characters.

Here is an example of the output produced by the scanner.

Here are the rules to keep in mind while reading and recognizing the output generated by the scanner.

• Each digit is represented using 9 cells (3x3)
• Only spaces, underscores and pipe characters are used
• The number of digits in each account number may vary.
• The Scanner is not 100% reliable and it might produce some digits that are invalid

The Challenge

Your job is to read the output produced by the scanner and identify the the customer number represented by each image. Remember that the scanner is not very reliable and it might produce invalid digit representations. For each digit that is not valid, set the value to 'X'

Sample Data

Here is the sample data for this challenge. Please take care with spaces, tabs and carriage returns as each digit is represented by three lines of text and if a space, tab or carriage return is misplaced, the whole image will be distorted.

Id          ScanNumber

----------- ---------------------------

1            _  _  _  _  _  _  _  _  _  

            | || || || || |  || ||_ |_|  

            |_||_||_||_||_|  ||_| _| _| 

2               _  _  _  _  _  _     _ 

            |_||_|| || ||_   |  |  ||_ 

              | _||_||_||_|  |  |  | _|

3            _  _  _     _  _  _  _  _  

            |_ |_|| || ||_ |_| _|  ||_| 

            |_||_||_||_||_||_||_   | _| 

4               _  _  _  _  _  _     _ 

            |_||_|| ||_||_   |  |  ||_ 

              | _||_||_||_|  |  |  ||_|

5               _  _  _  _  _  _     _ 

            | ||_|| ||_||_   |  |  ||_ 

              | _||_||_||_|  |  |  ||_|

6            _     _  _     _  _  _  _ 

            | |  | _| _||_||_ |_   ||_|

            |_|  ||_  _|  | _||_|  ||_|

Expected Results

Based on the sample input and the rules discussed earlier, here is the expected output.

Id          Value

----------- ---------

1           000007059

2           490067715

3           680X68279

4           490867716

5           X90867716

6           012345678

Sample Scripts

Use the following script to generate the sample data for this challenge.

DECLARE @t TABLE (Id int, ScanNumber NVARCHAR(116))

INSERT INTO @t

SELECT  1,--> 000 007 059

'_  _  _  _  _  _  _  _  _ 

| || || || || |  || ||_ |_|

|_||_||_||_||_|  ||_| _| _|

' UNION 

SELECT 2,-->  490 067 715

'   _  _  _  _  _  _     _ 

|_||_|| || ||_   |  |  ||_ 

  | _||_||_||_|  |  |  | _|

' UNION

SELECT  3, --> 680 X68 279

'_  _  _     _  _  _  _  _ 

|_ |_|| || ||_ |_| _|  ||_|

|_||_||_||_||_||_||_   | _|

' UNION

SELECT  4,--> 490 867 716

'   _  _  _  _  _  _     _ 

|_||_|| ||_||_   |  |  ||_ 

  | _||_||_||_|  |  |  ||_|

'  UNION

SELECT  5,--> X90 867 716

'   _  _  _  _  _  _     _ 

| ||_|| ||_||_   |  |  ||_ 

  | _||_||_||_|  |  |  ||_|

' 

UNION 

SELECT 6,--> 012 345 678

'_     _  _     _  _  _  _ 

| |  | _| _||_||_ |_   ||_|

|_|  ||_  _|  | _||_|  ||_|

Notes

1. Each record may have more than three lines of data (each line is separated by a CR and LF). Your code should consider only the first three lines.
2. The length of the first three lines of each recrd will always be the same and will be divisible by three.
3. There may be 3x3 blocks of spaces in the string. In such a case, you should generate an empty space in the output. If a 3x3 block does not create a valid digit (except for the case of a 3x3 block of spaces), you should generate an "X".
4. The number of 3x3 blocks in each record may vary