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
We will be given an integer input and we have find out whether the given number is a Perfect Number or not.
Any of the below numbers
In case of 496 you should get below output
|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.
RaAz SøRên said:
DECLARE @UserInput INT=496
DECLARE @i INT=1
DECLARE @TempTable table (divisors INT)
DECLARE @SumOfDivisors INT
INSERT INTO @TempTable (divisors) VALUES (
(SELECT CASE WHEN @UserInput%@i=0 THEN @i ELSE 0 END AS divisor))
SET @SumOfDivisors=(SELECT sum(divisors) FROM @TempTable where divisors !=0)
SELECT cast(@UserInput AS VARCHAR(10))+' is a perfect number' as Nums
SELECT cast(@UserInput AS VARCHAR(19))+' is not perfect number' as Nums