use mytools;

DROP TABLE IF EXISTS my_stats_data;
CREATE TABLE my_stats_data ( num_val INT(4));

INSERT INTO my_stats_data VALUES (2);
INSERT INTO my_stats_data VALUES (5);
INSERT INTO my_stats_data VALUES (7);
INSERT INTO my_stats_data VALUES (9);
INSERT INTO my_stats_data VALUES (9);
INSERT INTO my_stats_data VALUES (37);

-- Find the mean with this SQL statement:
\! echo '=> Calculate the mean:'; 
SELECT AVG(num_val)
FROM my_stats_data;

-- Find the standard deviation with this SQL statement:
\! echo '=> Calculate the standard deviation:'; 
SELECT STD(num_val)
FROM my_stats_data;

-- Find the z-score with this SQL statement:
\! echo '=> Calculate the z-scores:'; 
WITH simple_stats as
    (SELECT AVG(num_val) as mean,
            STDDEV(num_val) as sd
    FROM my_stats_data)
SELECT num_val, (num_val-simple_stats.mean) / simple_stats.sd as z_score
FROM my_stats_data, simple_stats;

-- Find z-scores greater than 2 with this SQL statement:
\! echo '=> Find the z-scores greater than 2:'; 
WITH simple_stats as
    (SELECT AVG(num_val) as mean,
            STDDEV(num_val) as sd
    FROM my_stats_data)
SELECT num_val, (num_val-simple_stats.mean) / simple_stats.sd as z_score
FROM my_stats_data, simple_stats
HAVING z_score > 2;

