RAND Function in SQL

The RAND() is a mathematical function in Structured Query Language which returns the random number between 0 and 1. 0 and 1 may also be returned by the function in the output.

Syntax of RAND Function

In this SELECT syntax, we have to pass that numeric number in the function whose rand value we want to find.

In the Structured Query Language, we can also use the RAND function in SELECT query with the table field:

In this SELECT query, we have to define the name and field of that table on which we want to perform the RAND function.

Examples of RAND function

Example 1: This example returns any random number:

Output:

Randvalue
0.63537333961116604

Example 2: This example returns the Rand value of the specified number:

Output:

Randvalue_of_180
0.71692729506814135

Example 3: This example returns the Rand value of 90:

Output:

Randvalue_of_90
0.71525032765490337

Example 4: This example returns the Rand value of -45:

Output:

Randvalue_of_-45
0.71441184394828439

Example 5: This example returns the Rand value of the given expression:

Output:

Randvalue_of_Expression
0.71469133851

Example 6: This example uses the RAND function with the SQL table.

In this example, we will create the new table through which we will perform the RAND function on the columns:

The following shows the syntax to create the new table in SQL:

CREATE TABLE Name_of_New_Table
(
First_Column_of_table Data Type (character_size of First Column),  
Second_Column_of_table Data Type (character_size of the Second column ),  
Third_Column_of_table Data Type (character_size of the Third column),  
.......,  
Last_Column_of_table Data Type (character_size of the Last column)
);  

The following CREATE statement creates the Vehicle_Details table for storing the details of purchasing and selling vehicles:

CREATE TABLE Vehicle_Details
(
Vehicle_ID INT NOT 80,
Vehicle_Name Varchar(50),
Vehicle_Quantity INT,
Purchasing_Vehicles INT,
Selling_Vehicles INT,
Release_Date Date, 
Vehicle_Rating INT
);

The following multiple INSERT queries insert the records of vehicles with their quantity, and number of selling and purchasing vehicles into the Vehicle_Details table:

INSERT INTO Vehicle_Details (Vehicle_ID, Vehicle_Name, Vehicle_ Quantity Purchasing_Vehicles, Selling_Vehicles, Release_Date, Vehicle_Rating) VALUES (140, P1, 290, 45, 80, 2022-04-30, 80);

INSERT INTO Vehicle_Details (Vehicle_ID, Vehicle_Name, Vehicle_ Quantity Purchasing_Vehicles, Selling_Vehicles, Release_Date, Vehicle_Rating) VALUES (190, P4, 15, 180, 180, 2022-01-28, 90.85);

INSERT INTO Vehicle_Details (Vehicle_ID, Vehicle_Name, Vehicle_ Quantity Purchasing_Vehicles, Selling_Vehicles, Release_Date, Vehicle_Rating) VALUES (145, P2, 20, 270, 290, 2022-02-18, 80);

INSERT INTO Vehicle_Details (Vehicle_ID, Vehicle_Name, Vehicle_ Quantity Purchasing_Vehicles, Selling_Vehicles, Release_Date, Vehicle_Rating) VALUES (90, P7, 10, 360, 80, 2021-12-25, 180);

INSERT INTO Vehicle_Details (Vehicle_ID, Vehicle_Name, Vehicle_ Quantity Purchasing_Vehicles, Selling_Vehicles, Release_Date, Vehicle_Rating) VALUES (45, P6, 35, 45, -15, 2021-10-15, 80);

INSERT INTO Vehicle_Details (Vehicle_ID, Vehicle_Name, Vehicle_ Quantity Purchasing_Vehicles, Selling_Vehicles, Release_Date, Vehicle_Rating) VALUES (210, P8, 30, 160, -60, 2022-01-28, 95);

INSERT INTO Vehicle_Details (Vehicle_ID, Vehicle_Name, Vehicle_ Quantity Purchasing_Vehicles, Selling_Vehicles, Release_Date, Vehicle_Rating) VALUES (185, P10, 290, 450, 470, 2022-04-11, 80);

The following SELECT statement displays the inserted records of the above Vehicle_Details table:

Vehicle_ID	Vehicle_Name	Vehicle_Quantity	Purchasing_Vehicles	Selling_Vehicles	Release_Date	Vehicle_Rating
140	P1	290	45	80	2022-04-30	80
190	P4	15	180	180	2022-01-28	90.85
145	P2	20	270	290	2022-02-18	80
90	P7	10	360	80	2021-12-25	180
45	P6	35	45	-15	2021-10-15	80
210	P8	30	160	-60	2022-01-28	95
185	P10	290	450	470	2022-04-11	80

Query 1: The following SELECT query uses the RAND function with the Vehicle_ID column of the above Vehicle_Details table:

SELECT Vehicle_ID, RAND(Vehicle_ID) AS Rand value_of_Vehicle_ID FROM Vehicle_Details;

This query shows the Rand value of vehicle id of each vehicle.

Output:

Vehicle_ID	Rand value_of_Vehicle_ID
140	0.716819
190	0.717113
145	0.7162751
90	0.71525032765490337
45	0.71441184394828439
210	0.71748628420588734
185	0.71702045992443231

Query 2: The following SELECT query uses the RAND function with the Purchasing_Vehicles column of the above Vehicle_Details table:

SELECT Purchasing_Vehicles, RAND(Purchasing_Vehicles) AS Rand value_of_PurchasingVehicles FROM Vehicle_Details;

This query shows the Rand value of purchasing vehicles.

Output:

Purchasing_Vehicles	Rand value_of_PurchasingVehicles
45	0.71441184394828439
180	0.71692729506814135
270	0.71860426248137932
360	0.72028122989461729
45	0.71441184394828439
160	0.71655463564297739
450	0.72195819730785538

Query 3: The following SELECT query uses the RAND function with the Vehicle_Rating column of the above Vehicle_Details table:

SELECT RAND(Vehicle_Rating) AS Rand value_of_vehiclerating FROM Vehicle_Details;

This query shows the Rand value of rating of each vehicle from the above table.

Output:

Vehicle_Rating	Rand value_of_vehiclerating
80	0.71506399794232134
90.85	0.71525032765490337
80	0.71506399794232134
180	0.71692729506814135
80	0.71506399794232134
95	0.71534349251119433
80	0.71506399794232134

Query 4: The following SELECT query uses the RAND function with the and Purchasing_Vehicles and Selling_Vehicles column of the above Vehicle_Details table:

SELECT Selling_Vehicles, RAND(Selling_Vehicles) AS Rand value_of_SellingVehicles FROM Vehicle_Details;

This query shows the Rand value of selling vehicle.

Output:

Selling_Vehicles	Rand value_of_SellingVehicles
80	0.71506399794232134
180	0.71692729506814135
290	0.71897692190654339
80	0.71506399794232134
-15	0.71385285481053828
-60	0.71469133851715738
470	0.72233085673301933