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
Improving my SQL BI Skills 
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