w3resource

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

SQL SQRT() function

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



Become a Patron!

Follow us on Facebook and Twitter for latest update.

It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.

https://www.w3resource.com/sql/arithmetic-functions/sqrt.php