SQL SQRT() function
SQRT() function
SQL SQRT() returns the square root of a given value in the argument.
Syntax:
SQRT( expression )
Parameters:
| Name | Description |
|---|---|
| expression | An expression which is a numeric value or numeric data type. |
MySQL, PostgreSQL, SQL Server, and Oracle
All of above platforms support the SQL syntax of SQRT().
SQL SQRT() function: Visual presentation
Example :
To get the square root of 36 from the DUAL table, the following SQL statement can be used:
-- This SQL statement calculates the square root of the number 36 and selects the result from the 'dual' table.
SELECT SQRT(36)
-- SELECT statement retrieves data from the database
-- SQRT() function calculates the square root of a specified number
-- In this case, SQRT(36) calculates the square root of 36
FROM dual;
-- Specifies the 'dual' table, a special one-row, one-column table present in Oracle database
-- The 'dual' table is often used for performing calculations or returning single results in SQL queries
Explanation:
- This SQL query is straightforward, as it's only a single statement.
- The purpose of this query is to calculate the square root of the number 36.
- SQRT() is a mathematical function that calculates the square root of a given number.
- In this case, SQRT(36) will return the square root of 36, which is 6.
- The result of the calculation, which is 6 in this case, will be selected as the output of the query.
- The 'dual' table is used here because it's a convenient way to execute single-row queries in Oracle SQL without needing to specify an actual table with data.
Output:
SQRT(36)
----------
6
SQL SQRT() function with distinct
Sample table: customer+-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+ |CUST_CODE | CUST_NAME | CUST_CITY | WORKING_AREA | CUST_COUNTRY | GRADE | OPENING_AMT | RECEIVE_AMT | PAYMENT_AMT |OUTSTANDING_AMT| PHONE_NO | AGENT_CODE | +-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+ | C00013 | Holmes | London | London | UK | 2 | 6000.00 | 5000.00 | 7000.00 | 4000.00 | BBBBBBB | A003 | | C00001 | Micheal | New York | New York | USA | 2 | 3000.00 | 5000.00 | 2000.00 | 6000.00 | CCCCCCC | A008 | | C00020 | Albert | New York | New York | USA | 3 | 5000.00 | 7000.00 | 6000.00 | 6000.00 | BBBBSBB | A008 | | C00025 | Ravindran | Bangalore | Bangalore | India | 2 | 5000.00 | 7000.00 | 4000.00 | 8000.00 | AVAVAVA | A011 | | C00024 | Cook | London | London | UK | 2 | 4000.00 | 9000.00 | 7000.00 | 6000.00 | FSDDSDF | A006 | | C00015 | Stuart | London | London | UK | 1 | 6000.00 | 8000.00 | 3000.00 | 11000.00 | GFSGERS | A003 | | C00002 | Bolt | New York | New York | USA | 3 | 5000.00 | 7000.00 | 9000.00 | 3000.00 | DDNRDRH | A008 | | C00018 | Fleming | Brisban | Brisban | Australia | 2 | 7000.00 | 7000.00 | 9000.00 | 5000.00 | NHBGVFC | A005 | | C00021 | Jacks | Brisban | Brisban | Australia | 1 | 7000.00 | 7000.00 | 7000.00 | 7000.00 | WERTGDF | A005 | | C00019 | Yearannaidu | Chennai | Chennai | India | 1 | 8000.00 | 7000.00 | 7000.00 | 8000.00 | ZZZZBFV | A010 | | C00005 | Sasikant | Mumbai | Mumbai | India | 1 | 7000.00 | 11000.00 | 7000.00 | 11000.00 | 147-25896312 | A002 | | C00007 | Ramanathan | Chennai | Chennai | India | 1 | 7000.00 | 11000.00 | 9000.00 | 9000.00 | GHRDWSD | A010 | | C00022 | Avinash | Mumbai | Mumbai | India | 2 | 7000.00 | 11000.00 | 9000.00 | 9000.00 | 113-12345678 | A002 | | C00004 | Winston | Brisban | Brisban | Australia | 1 | 5000.00 | 8000.00 | 7000.00 | 6000.00 | AAAAAAA | A005 | | C00023 | Karl | London | London | UK | 0 | 4000.00 | 6000.00 | 7000.00 | 3000.00 | AAAABAA | A006 | | C00006 | Shilton | Torento | Torento | Canada | 1 | 10000.00 | 7000.00 | 6000.00 | 11000.00 | DDDDDDD | A004 | | C00010 | Charles | Hampshair | Hampshair | UK | 3 | 6000.00 | 4000.00 | 5000.00 | 5000.00 | MMMMMMM | A009 | | C00017 | Srinivas | Bangalore | Bangalore | India | 2 | 8000.00 | 4000.00 | 3000.00 | 9000.00 | AAAAAAB | A007 | | C00012 | Steven | San Jose | San Jose | USA | 1 | 5000.00 | 7000.00 | 9000.00 | 3000.00 | KRFYGJK | A012 | | C00008 | Karolina | Torento | Torento | Canada | 1 | 7000.00 | 7000.00 | 9000.00 | 5000.00 | HJKORED | A004 | | C00003 | Martin | Torento | Torento | Canada | 2 | 8000.00 | 7000.00 | 7000.00 | 8000.00 | MJYURFD | A004 | | C00009 | Ramesh | Mumbai | Mumbai | India | 3 | 8000.00 | 7000.00 | 3000.00 | 12000.00 | Phone No | A002 | | C00014 | Rangarappa | Bangalore | Bangalore | India | 2 | 8000.00 | 11000.00 | 7000.00 | 12000.00 | AAAATGF | A001 | | C00016 | Venkatpati | Bangalore | Bangalore | India | 2 | 8000.00 | 11000.00 | 7000.00 | 12000.00 | JRTVFDD | A007 | | C00011 | Sundariya | Chennai | Chennai | India | 3 | 7000.00 | 11000.00 | 7000.00 | 11000.00 | PPHGRTS | A010 | +-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+
To get the unique square root of 'opening_amt' from 'customer' table, the following SQL statement can be used :
-- This SQL statement calculates the square root of the 'opening_amt' column for each row in the 'customer' table.
SELECT SQRT(opening_amt)
-- SELECT statement retrieves data from the database
-- SQRT() function calculates the square root of a specified number
-- In this case, SQRT(opening_amt) calculates the square root of the 'opening_amt' column value for each row
FROM customer;
-- Specifies the 'customer' table from which the data is being retrieved
Explanation:
- This SQL query is designed to calculate the square root of the 'opening_amt' column for each row in the 'customer' table.
- The SELECT statement specifies the SQRT(opening_amt) expression, which calculates the square root of the 'opening_amt' column value for each row.
- The query is executed against the 'customer' table to retrieve the square root of the opening amounts.
- Each row in the result will have the square root of the corresponding 'opening_amt' value.
- The 'customer' table is used here as the data source for the calculation.
Output:
SQRT(OPENING_AMT)
-----------------
77.4596669
54.7722558
70.7106781
70.7106781
63.2455532
77.4596669
70.7106781
83.6660027
83.6660027
89.4427191
83.6660027
83.6660027
83.6660027
70.7106781
63.2455532
100
77.4596669
89.4427191
70.7106781
83.6660027
89.4427191
89.4427191
89.4427191
89.4427191
83.6660027
Note: Outputs of the said SQL statement shown here is taken by using Oracle Database 10g Express Edition.
Here is a slide presentation which covers the SQL arithmetic functions.
Check out our 1000+ SQL Exercises with solution and explanation to improve your skills.
Previous: POWER
Next: Character Function