Problem 9

A Pythagorean triplet is a set of three natural numbers, a b c, for which,

a² + b² = c²

For example, 3² + 4² = 9 + 16 = 25 = 5².

There exists exactly one Pythagorean triplet for which a + b + c = 1000.
Find the product abc.

Solution

select a*b*c from PythagoreanTriple

where a + b + c = 1000

Discussion

Clearly this problem is easy if you have a table full of Pythagorean triples.  It turns out I do and it is a lot bigger than 1000.

Please, leave a comment, find me on Twitter (@azzlsoft) or email (rich@azzlsoft.com).

Problem 8

Find the greatest product of five consecutive digits in the 1000-digit number.

73167176531330624919225119674426574742355349194934
96983520312774506326239578318016984801869478851843
85861560789112949495459501737958331952853208805511
12540698747158523863050715693290963295227443043557
66896648950445244523161731856403098711121722383113
62229893423380308135336276614282806444486645238749
30358907296290491560440772390713810515859307960866
70172427121883998797908792274921901699720888093776
65727333001053367881220235421809751254540594752243
52584907711670556013604839586446706324415722155397
53697817977846174064955149290862569321978468622482
83972241375657056057490261407972968652414535100474
82166370484403199890008895243450658541227588666881
16427171479924442928230863465674813919123162824586
17866458359124566529476545682848912883142607690042
24219022671055626321111109370544217506941658960408
07198403850962455444362981230987879927244284909188
84580156166097919133875499200524063689912560717606
05886116467109405077541002256983155200055935729725
71636269561882670428252483600823257530420752963450

Solution

declare @digits varchar(MAX)

set @digits = ‘

73167176531330624919225119674426574742355349194934

96983520312774506326239578318016984801869478851843

85861560789112949495459501737958331952853208805511

12540698747158523863050715693290963295227443043557

66896648950445244523161731856403098711121722383113

62229893423380308135336276614282806444486645238749

30358907296290491560440772390713810515859307960866

70172427121883998797908792274921901699720888093776

65727333001053367881220235421809751254540594752243

52584907711670556013604839586446706324415722155397

53697817977846174064955149290862569321978468622482

83972241375657056057490261407972968652414535100474

82166370484403199890008895243450658541227588666881

16427171479924442928230863465674813919123162824586

17866458359124566529476545682848912883142607690042

24219022671055626321111109370544217506941658960408

07198403850962455444362981230987879927244284909188

84580156166097919133875499200524063689912560717606

05886116467109405077541002256983155200055935729725

71636269561882670428252483600823257530420752963450′

declare @returnpattern varchar(2);

set @returnpattern = char(13) + char(10)

set @digits = replace(@digits, @returnpattern, ”)

declare @currentDigit int

set @currentDigit = 1

declare @sql varchar(max)

while(@currentDigit <= len(@digits))

begin

       set @sql = ‘insert into Problem_008 (ordinal, digit) values (‘ + str(@currentdigit) + ‘, ‘ + substring(@digits, @currentDigit, 1) + ‘)’

       exec(@sql)

       set @currentDigit = @currentDigit + 1

end

GO

select max(product) from

(select

       digit

       * (select digit from Problem_008 a where a.ordinal = Problem_008.ordinal + 1)

       * (select digit from Problem_008 a where a.ordinal = Problem_008.ordinal + 2)

       * (select digit from Problem_008 a where a.ordinal = Problem_008.ordinal + 3)

       * (select digit from Problem_008 a where a.ordinal = Problem_008.ordinal + 4) as product

from Problem_008

where ordinal < (select count(*) from Problem_008) – 3) Products

Discussion

This one is going to take a bit of explanation.  I parse the thousand digit number for it’s component digits and insert them into a database table with their ordinals.  After the digits are in a table it’s a simple query.

Clearly, I didn’t need to insert the digits into a table to get the result, but as with all of the TSQL solutions I want to solve it as if the information exists in the database if possible.

If you have any questions, leave a comment, find me on Twitter (@azzlsoft) or email (rich@azzlsoft.com).

Problem 7

By listing the first six prime numbers: 2, 3, 5, 7, 11, and 13, we can see that the 6th prime is 13.

What is the 10001st prime number?

Solution

— By listing the first six prime numbers:

— 2, 3, 5, 7, 11, and 13, we can see that

— the 6th prime is 13.

— What is the 10001st prime number?

SELECT number FROM

       (SELECT row_number() OVER (ORDER BY number) as primeindex, number

              FROM Number

              WHERE isprime = 1) Primes

WHERE Primes.primeindex = 10001

Discussion

On the surface this solution might seem pretty ‘meh’, but I’m a big fan.  It’s obviously going to be my fastest solution because it’s a simple look up – very common in real life scenarios.  I also got to use row_number() and a subquery as a data source which always delight me.

If you have any questions, leave a comment, find me on Twitter (@azzlsoft) or email (rich@azzlsoft.com).

Problem 6

The sum of the squares of the first ten natural numbers is,

1² + 2² + … + 10² = 385

The square of the sum of the first ten natural numbers is,

(1 + 2 + … + 10)² = 55² = 3025

Hence the difference between the sum of the squares of the first ten natural numbers and the square of the sum is 3025 385 = 2640.

Find the difference between the sum of the squares of the first one hundred natural numbers and the square of the sum.

Solution

— The sum of the squares of

— the first ten natural numbers is,

— 1^2 + 2^2 + … + 10^2 = 385

— The square of the sum of the

— first ten natural numbers is,

— (1 + 2 + … + 10)^2 = 55^2 = 3025

— Hence the difference between the sum

— of the squares of the first ten natural

— numbers and the square of the sum is

— 3025 – 385 = 2640.

— Find the difference between the sum of

— the squares of the first one hundred natural

— numbers and the square of the sum.

select power(sum(number),2) – sum([square])

from Number

where number <= 100

Discussion

Just like the other solutions, this one is quite simple.  It is aided by the fact that when I populated my database I included all of the squares in the number table as well.

If you have questions, leave a comment or please find me on Twitter (@azzlsoft) or email (rich@azzlsoft.com).

Problem 5

2520 is the smallest number that can be divided by each of the numbers from 1 to 10 without any remainder.

What is the smallest positive number that is evenly divisible by all of the numbers from 1 to 20?

Solution

— 2520 is the smallest number that can be

— divided by each of the numbers from 1 to 10

— without any remainder.

— What is the smallest positive number

— that is evenly divisible by all of the

— numbers from 1 to 20?

SELECT EXP(SUM(LOG(POWER(number, FLOOR(LOG(20) / log(number))))))

FROM Number WHERE isprime = 1

AND number < 20

Discussion

I’ll admit it.  I am pretty proud of this solution.  Assuming that we have a list of primes already (which we do) there are two problems that we need to solve.  The first is, what is the proper exponent for each prime?

If you remember from Monday’s solution (F#), I mentioned that 2⁵ was too big (32 > 20) so 2⁴ was the number we needed to use?  So how do you figure that out?  Well, we want to find the maximum k such that

2^k ≤ 20

Right?  Let’s solve for k.  Here’s the “trick”:

log(2^k) ≤ log(20)

k*log(2) ≤ log(20)

k ≤ log(20)/log(2)

k ≤ 4.32

Since k is an integer, we can set it to 4.  That’s pretty easy.

The next problem I ran into was that SQL doesn’t natively support a “product” aggregate and doesn’t appear to have a convenient fold mechanism.  I was able to solve it with COALESCE, but that required a separate variable to accumulate the results.  I really wanted to solve this with a simple query.  Then I found another trick…

log(a*b) = log(a)+log(b)

So I could use the built in sum aggregator to find the product.  That is

a*b*c… = exp(log(a) + log(b) + log(c)…)

When you couple this trick with the original you find your answer.

If you have questions, leave a comment or please find me on Twitter (@azzlsoft) or email (rich@azzlsoft.com).

Problem 4

A palindromic number reads the same both ways. The largest palindrome made from the product of two 2-digit numbers is 9009 = 91 99.

Find the largest palindrome made from the product of two 3-digit numbers.

Solution

–A palindromic number reads the same both ways.

–The largest palindrome made from the product of

–two 2-digit numbers is 9009 = 91  99.

–Find the largest palindrome made from the

–product of two 3-digit numbers.

— this is a fun query

— we join the number table to

— itself ensuring that the right

— number is greater than or equal

— to the left number

SELECT MAX(LeftNumber.number * RightNumber.number)

FROM Number LeftNumber

INNER JOIN Number RightNumber

ON LeftNumber.number <= RightNumber.number

— then we limit the numbers to 3 digits

— an obvious (and better) method for this is

— LeftNumber.number >= 100

— and LeftNumber.number <= 999

— but this technique is more interesting

WHERE LEN(CAST(LeftNumber.number AS VARCHAR)) = 3

and LEN(CAST(RightNumber.number AS VARCHAR)) = 3

— finally, the clever part is determining

— the palindromicity of the product

and(CAST(LeftNumber.number * RightNumber.number AS VARCHAR)

  = REVERSE(CAST(LeftNumber.number * RightNumber.number AS VARCHAR)))

Discussion

I am quite happy with this solution.

If you have questions, leave a comment or please find me on Twitter (@azzlsoft) or email (rich@azzlsoft.com).

The prime factors of 13195 are 5, 7, 13 and 29.

What is the largest prime factor of the number 600851475143 ?

Solution

–The prime factors of 13195 are 5, 7, 13 and 29.

–What is the largest prime factor of the number 600851475143?

— this number is bigger than my database

— so we will add it

CREATE PROCEDURE [dbo].[FactorNumber]

       @number AS BIGINT,

       @originalnumber AS BIGINT

AS

DECLARE @isPrime BIT

SET @isPrime = 1

DECLARE @factor BIGINT

SET @factor = FLOOR(SQRT(@number))

DECLARE @divisor BIGINT

WHILE @factor > 1

BEGIN

       IF @number % @factor = 0

       BEGIN

              EXEC FactorNumber @factor, @originalnumber

              — can’t do the math in the function call

              — so I used another variable

              SET @divisor = @number / @factor

              EXEC FactorNumber @divisor, @originalnumber

              SET @isPrime = 0

       END

       SET @factor = @factor – 1

END

— here we store the information for later

IF @isPrime = 1

BEGIN

       INSERT INTO NumberFactor  (number, factor) VALUES (@originalnumber, @number)

END

–here are the remaining steps to get the answer

–exec FactorNumber 600851475143, 600851475143

–select MAX(factor) from NumberFactor where number = 600851475143

Discussion

I’ve included the procedure and the select statement here because this number exceeded my number list.  It follows the same philosophy as some of the previous examples except that it inserts its values into a table instead of returning them to the calling function.  This is why we need to pass the original number in as well.

If you have questions, leave a comment or please find me on Twitter (@azzlsoft) or email (rich@azzlsoft.com).

Problem 2

Each new term in the Fibonacci sequence is generated by adding the previous two terms. By starting with 1 and 2, the first 10 terms will be:

1, 2, 3, 5, 8, 13, 21, 34, 55, 89, …

By considering the terms in the Fibonacci sequence whose values do not exceed four million, find the sum of the even-valued terms.

Solution

–By considering the terms in the Fibonacci sequence\

–whose values do not exceed four million, find the

–sum of the even valued terms.

–this is a trivial query to write

select SUM(number) from Number

where number < 4000000

and isfibonacci = 1

and number % 2 = 0

Discussion

Once again, I have a large database of numbers with properties associated with them.  That makes this solution fairly uninteresting. 

If you have questions, leave a comment or please find me on Twitter (@azzlsoft) or email (rich@azzlsoft.com).

Problem 1

If we list all the natural numbers below 10 that are multiples of 3 or 5, we get 3, 5, 6 and 9. The sum of these multiples is 23.

Find the sum of all the multiples of 3 or 5 below 1000.

Solution

— I have a table called Number

— that has about 3 million

— integers starting at 1

— this problem is pretty

— simple from a query perspective

SELECT SUM(number) FROM Number

WHERE number < 1000

AND ((number % 3 = 0) OR (number % 5 = 0))

Discussion

When it’s possible to fill a table in a reasonable amount of time using brute force techniques, I will.  If the technique for filling the table is interesting I will demonstrate that.  I realize that in many cases this won’t be possible so I will have to use whatever “trick” is required to solve the problem before the sun burns out.

If you have questions, leave a comment or find me on Twitter (@azzlsoft) or email (rich@azzlsoft.com).