Execute the following SQL Server T-SQL scripts in SSMS Query Editor to carry out statistical analysis of a randomly generated set of numbers. Calculate: Average, Median, Minimum, Maximum and Standard Deviation.

-- SQL statistical analysis - SQL median - SQL average

-- SQL standard deviation - SQL calculate median

USE AdventureWorks;

-- SQL local temporary table

CREATE TABLE #NumberList (

  NumberListID INT    IDENTITY ( 1 , 1 ),

  X            BIGINT    NOT NULL)

GO

-- Populate temporary table with 20 rows of random numbers between 1 and 99

SET nocount  ON

DECLARE  @i INT

SET @i = 1

-- T-SQL while loop

WHILE (@i <= 21)

  BEGIN

    INSERT INTO #NumberList

               (X)

    VALUES     (Cast(Rand() * 100 AS BIGINT))

    SET @i = @i + 1

  END

GO

SELECT * FROM #NumberList;

/*

NumberListID      X

1                 68

2                 82

3                 19

4                 70

5                 39

6                 66

7                 51

8                 91

9                 95

10                7

11                88

12                51

13                63

14                36

15                92

16                43

17                48

18                27

19                66

20                94

21                1

*/

-- SQL common table expression - CTE

-- SQL cross join

-- SQL row_number over order by

WITH cteAvg

     AS (SELECT [avg] = Avg(X),

                [min] = Min(X),

                [max] = Max(X),

                [stddev] = Stdev(X)

         FROM   #NumberList),

     cteMedian

     AS (SELECT X,

                Row_number()

                  OVER(ORDER BY X ASC) AS high,

                Row_number()

                  OVER(ORDER BY X DESC) AS low

         FROM   #NumberList)

SELECT [avg]    AS average,

       X        AS median,

       [min]    AS minimum,

       [max]    AS maximum,

       [stddev] AS 'standard deviation'

FROM   cteMedian

       CROSS JOIN cteAvg

WHERE  high IN (low,low + 1,low - 1)

GO

/* Results

average     median      minimum     maximum     standard deviation

57          63          1           95          28.6024474477273

*/

-- Cleanup

drop table #NumberList