MySQL PI() – Calculating Pi Value in MySQL [Simple Examples]

By /
PI Function

In this tutorial, we will study the MySQL PI() function. While studying maths in high school, you must have come across the value of πœ‹ known as Pi. The value of Pi is 22/7 or 3.142 up to three decimal places. Pi is used in a variety of mathematical calculations –Β  geometry, calculus, and physics.

One of the first instances where you would have encountered Pi is while finding the area of a circle. Since Pi is so widely used in different operations across different domains, MySQL provides us with the PI() function which returns the value of Pi.

Syntax of MySQL PI()

PI();Code language: SQL (Structured Query Language) (sql)

Examples of MySQL PI()

Let us look at a basic example of printing the value of Pi. We will alias in our SELECT statement to make our output readable.

SELECT PI() AS Value_of_PI;Code language: SQL (Structured Query Language) (sql)

And the output we get is the value of πœ‹ up to 6 decimal places.

Pi Basic Example

Calculating Circumference and Area of a Circle

Let us dive into some complex and real-world examples now. Consider the below β€˜Circle’ table.

Circle Table
Circle Table

Let us display figure ID, radius and the circumference of these circles. The formula for the circumference of a circle is 2πœ‹r, where r is the radius of the circle. Our query is,Β 

SELECT FigureId, Radius, 2*PI()*Radius AS Circumference FROM Circle;Code language: SQL (Structured Query Language) (sql)

And we get the output as,

Circle Table Circumference Example1

Now, how about using the above query but displaying only those circle records whose circumference is greater than 50? We will use the WHERE clause.

SELECT FigureId, Radius, 2*PI()*Radius AS Circumference FROM Circle WHERE 2*PI()*Radius>50;Code language: SQL (Structured Query Language) (sql)

And we get our output as,

Circle Table Circumference Example2

Let us display the figure id, radius, and the area of these circles. The formula for the area of a circle is πœ‹r2, where r is the radius of the circle. We will use the POW() function in this query. Our query is,

SELECT FigureId, Radius, PI()*POW(Radius, 2) AS Area FROM Circle;Code language: SQL (Structured Query Language) (sql)

And we get our output as,

Circle Table Area Example

Calculating Surface Area and Volume of a Sphere

Let us look at another example. Consider the below Sphere table.

Sphere Table MySQL Pi
Sphere Table

Let us create a column named SurfaceArea which contains the surface area value of the corresponding spheres. We will use the ALTER and UPDATE statements for this. The formula for the surface area of a sphere is 4πœ‹r2.

ALTER TABLE Sphere ADD SurfaceArea float; 
UPDATE Sphere SET SurfaceArea=4*PI()*POW(Radius, 2); 
SELECT * FROM Sphere;Code language: SQL (Structured Query Language) (sql)

The ALTER statement will add the SurfaceArea column with data type float to the Sphere table. The column is pre-populated with NULL values. The UPDATE statement will update all the NULL values in the column to the value of the surface area of the sphere. Finally, we use the SELECT statement to display our newly updated table.Β 

The output is shown as follows,

Sphere Surface Area Example MySQL PI

Building on the above, let us create a column named Volume which contains the volume value of the corresponding spheres. The formula for the volume of a sphere is 4/3 πœ‹r3.

ALTER TABLE Sphere ADD Volume float; 
UPDATE Sphere SET Volume=4/3*PI()*POW(Radius, 3); 
SELECT * FROM Sphere;Code language: SQL (Structured Query Language) (sql)

Just like the previous example, the ALTER statement will add the Volume column with data type float to the Sphere table. The column is pre-populated with NULL values. The UPDATE statement will update all the NULL values in the column to the value of the volume of the sphere.

The 4/3, MySQL PI() and POW() expressions get evaluated first and then all their results get multiplied. Finally, we use the SELECT statement to display our newly updated table. Notice we use the division operator and not the DIV function.

And the output is,

Sphere Volume Example

Conclusion

MySQL PI() is very useful for operations across various domains and more so while performing operations on a table. I would encourage you to try different use cases or formulas which include Pi and apply them using PI() in MySQL.

References