Geeks around the world will be celebrating Pi day, a shameless excuse to put the month before the day like the Americans do so we can talk Mathematics on “3.14” day

So what better way to show how cool SQL can be with some expressions to approach Pi with continuous series

All we really need to know by way of background is the CONNECT BY LEVEL trick to generate arbirtary sequences of row, eg

```
SQL> select level from dual connect by level <= 5;
LEVEL
----------
1
2
3
4
5
```

Now we can get some approximations to Pi using the some of the standard SQL functions SQRT and POWER

```
SQL> select sqrt(8*sum(1/power((level*2-1),2))) pi
2 from dual
3 connect by level <= 100000
4 order by 1 desc;
PI
----------
3.14158947
1 row selected.
SQL>
SQL>
SQL> select sqrt(6*sum(1/(level*level))) pi
2 from dual
3 connect by level <= 100000
4 order by 1 desc;
PI
----------
3.1415831
1 row selected.
SQL>
SQL> select 8*sum(1/((4*(level-1)+1)*(4*(level-1)+3))) pi
2 from dual
3 connect by level <= 100000;
PI
----------
3.14158765
1 row selected.
SQL>
SQL>
SQL> select
2 4*sum(
3 power(-1,level-1)/(level*2-1)*
4 ( 12*power(1/18,level*2-1)+
5 8*power(1/57,level*2-1)-
6 5*power(1/239,level*2-1))) pi
7 from dual
8 connect by level <= 100;
PI
----------
3.14159265
1 row selected.
SQL>
```

Some methods to calculate Pi need factorials, but there isn’t a native SQL function for that. But in 12c, that’s ** no problem**, we can define SQL functions on the fly directly inside our SQL statement !

```
SQL>
SQL> with
2 function factorial(n int) return int is
3 f int := 1;
4 begin
5 for i in 1 .. n loop
6 f := f * i;
7 end loop;
8 return f;
9 end;
10 select 2*sum(
11 power(2,level-1)*power(factorial(level-1),2)/factorial(2*level-1)
12 ) pi
13 from dual
14 connect by level <= 17;
15 /
PI
----------
3.1415864
1 row selected.
```

But what if you’re not on 12c yet ? Well, you should be! You can check out why it’s time to upgrade from Maria and myself chatting about it over coffee

But whilst you’re on 11g, there is still plenty of cool SQL options to play with Pi. Here we can use recursive SQL to generate Pi, based on the formula:

```
SQL> with term(numerator,product,seq) as
2 ( select sqrt(2) numerator, sqrt(2)/2 product , 1 seq from dual
3 union all
4 select sqrt(2+numerator), sqrt(2+numerator)*product/2 , seq+1
5 from term, dual
6 where term.seq <= 16
7 )
8 select 2/product pi
9 from term
10 where seq = 16;
PI
----------
3.14159265
1 row selected.
```

SQL…still the most awesome language out there!

- November 2017 (25)
- October 2017 (57)
- September 2017 (46)
- August 2017 (59)
- July 2017 (49)
- June 2017 (39)
- May 2017 (50)
- April 2017 (49)
- March 2017 (68)
- February 2017 (37)
- January 2017 (65)
- December 2016 (52)
- November 2016 (78)
- October 2016 (70)
- September 2016 (54)
- August 2016 (70)
- July 2016 (39)
- June 2016 (57)
- May 2016 (65)
- April 2016 (73)
- March 2016 (89)
- February 2016 (81)
- January 2016 (91)
- December 2015 (73)
- November 2015 (95)
- October 2015 (93)
- September 2015 (78)
- August 2015 (84)
- July 2015 (81)
- June 2015 (59)

## Recent comments

2 weeks 5 days ago

7 weeks 10 hours ago

7 weeks 5 days ago

12 weeks 3 days ago

33 weeks 4 days ago

1 year 1 week ago

1 year 31 weeks ago

2 years 15 weeks ago

2 years 16 weeks ago

2 years 33 weeks ago