/*
ある模擬試験の結果を格納した試験結果テーブル(TEST_RESULTS)がある。受験者(USER_ID)の点数(POINT)より標準偏差を求め、
受験者毎の偏差値を算出しなさい。また、偏差値は小数点第2位を四捨五入すること。
ただし、テストID(TEST_ID) = '100'のデータのみ対象とし、標準偏差がゼロの場合は、偏差値は50とする。

※拡張ライブラリの「STDEV関数」は利用できないので注意すること。

偏差値は次の手順で求められる。

点数の平均値を求める
個々の点数と平均値の差（個々の点数 － 平均値）を求める
分散（個々の点数と平均値の差の二乗の平均）を求める
標準偏差（分散の正の平方根）を求める
個々の点数と平均値との差に10を掛ける
個々の点数と平均値との差に10を掛けた値を標準偏差で割る
標準偏差で割った値に50を足して偏差値を求める
また、標準偏差を式で表すと以下になります。

表示項目は以下とする。(エイリアスを使用し→の項目名とする)

USER_ID → USER
POINT → PT
算出した偏差値 → DEV_VAL
表示順

算出した偏差値の降順
USER_IDの昇順
*/
with avg1 as(
    select
        avg(POINT) as avgp
    from 
        TEST_RESULTS
    where
        TEST_ID = '100'
), avg2 as(
    select
        avg(pow(POINT - avgp,2)) as var
    from TEST_RESULTS
    cross join avg1
    where
        test_ID = '100'
)
select
    USER_ID as USER,
    POINT as PT,
    round(((cast((POINT - avgp) * 10 as real)) / sqrt(var)),1) + 50 as DEV_VAL
from TEST_RESULTS
cross join avg1
cross join avg2
where
    TEST_ID = '100'
order by 
    DEV_VAL desc,
    USER asc;