**Tags**

Complex SQL Challenges, Complex TSQL Challenge, Interview Qs.SQL SERVER Questions, Interview questions on Joins, Interview Questions on SQL, InterviewQuestions, InterviewQuestions for SQL, Joins, Joins Interview questions, Joins Puzzle, Learn complex SQL, Learn SQLQueries for SQL Interview, Learn T-SQL, Objective Puzzle, Perfect Numbers, PL/SQL Challenges, Puzzles, SELECT Puzzle, SQL, SQL 2012, SQL 2014, SQL 2014 Interview Questions, SQL Challenge, SQL Challenges, SQL Joins, SQL pl/sql puzzles, SQL Puzzles, SQL Queries, SQL SERVER Interview questions, SQL Skills, SQL Sudoku, SQLSERVER, T SQL Puzzles, T-SQL Challenge, Tough SQL Challenges, Tough SQL Puzzles, TSQL, TSQL Challenge, TSQL Challenges, TSQL Interview questions, TSQL Queries

**T-SQL Query | [ The Perfect Number Puzzle ]**

**What is a Perfect Number ?**

In number theory, a perfect number is a positive integer that is equal to the sum of its proper positive divisors, that is, the sum of its positive divisors excluding the number itself (also known as its aliquot sum).

What is the logic to find a given number is a perfect number ? For example 6 is perfect Number which is divisible by 3,2,1 and their sum is 6, so 6 is a perfect number.

List of Perfect Numbers – http://en.wikipedia.org/wiki/List_of_perfect_numbers

**Puzzle Statement**

We will be given an integer input and we have find out whether the given number is a Perfect Number or not.

**Sample Input**

Any of the below numbers

6

28

496

14

25..etc

**Expected output**

In case of 496 you should get below output

Nums |

496 is a Perfect Number |

**UPDATE – 24-Apr-2015 – Solution 1 **

-- DECLARE @t AS INT = 496 DECLARE @Cnt AS INT = 1 CREATE TABLE #Prime ( Val INT ) WHILE ( @Cnt <= @t/2 ) BEGIN IF @t% @cnt = 0 BEGIN INSERT INTO #Prime VALUES (@Cnt) END SET @Cnt = @Cnt + 1 END SELECT CASE WHEN SUM(Val) = @t THEN CONCAT (@t,' is a Perfect Number') ELSE CONCAT (@t,' is NOT a Perfect Number') END Nums FROM #Prime DROP TABLE #Prime -- |

Add a comment if you have any other solution in mind. We all need to learn.

Keep Learning

RaAz SøRên

said:DECLARE @UserInput INT=496

DECLARE @i INT=1

DECLARE @TempTable table (divisors INT)

DECLARE @SumOfDivisors INT

WHILE(@i<=(@UserInput/2))

BEGIN

INSERT INTO @TempTable (divisors) VALUES (

(SELECT CASE WHEN @UserInput%@i=0 THEN @i ELSE 0 END AS divisor))

SET @i=@i+1

END

SET @SumOfDivisors=(SELECT sum(divisors) FROM @TempTable where divisors !=0)

IF(@UserInput=@SumOfDivisors)

BEGIN

SELECT cast(@UserInput AS VARCHAR(10))+' is a perfect number' as Nums

END

ELSE

BEGIN

SELECT cast(@UserInput AS VARCHAR(19))+' is not perfect number' as Nums

END

LikeLike