-- STEP1（表の作成）
CREATE TABLE novels (
  id     INTEGER PRIMARY KEY,
  title  TEXT NOT NULL,
  author TEXT NOT NULL,
  read   INTEGER NOT NULL CHECK (read   BETWEEN 1 AND 5),
  heavy  INTEGER NOT NULL CHECK (heavy  BETWEEN 1 AND 5),
  laugh  INTEGER NOT NULL CHECK (laugh  BETWEEN 1 AND 5),
  hot    INTEGER NOT NULL CHECK (hot    BETWEEN 1 AND 5),
  unreal INTEGER NOT NULL CHECK (unreal BETWEEN 1 AND 5)
);
--=====================================================================================
-- STEP2（データの格納）
INSERT INTO novels (id, title, author, read, heavy, laugh, hot, unreal) VALUES
  (1, '大泉エッセイ',         '大泉洋',     1, 1, 5, 1, 1),
  (2, '陸王',                 '池井戸潤',   3, 2, 1, 5, 1),
  (3, '独立記念日',           '原田マハ',   1, 2, 1, 2, 1),
  (4, '同志少女よ、敵を撃て', '逢坂冬馬',   4, 5, 1, 4, 4),
  (5, '恋文の技術',           '森見登美彦', 3, 1, 4, 2, 2);
--=====================================================================================
--STEP3（データ全体の確認）
--SELECT * FROM novels;
--=====================================================================================
--STEP4（条件に当てはまるデータの確認）
--SELECT title, read
--FROM novels
--WHERE read <= 2;
--=====================================================================================
--STEP5（並び順の変更）
--SELECT title, hot
--FROM novels
--ORDER BY hot DESC;
--=====================================================================================
--STEP6（具体例: 距離の計算n=1）
--SELECT title,
       --CASE WHEN read > 1 THEN (read - 1) * (read - 1) ELSE 0 END
     + (heavy  - 1) * (heavy  - 1)
     + (laugh  - 5) * (laugh  - 5)
     + (hot    - 2) * (hot    - 2)
     + (unreal - 1) * (unreal - 1) --AS distance
--FROM novels
--ORDER BY distance
--LIMIT 2;
--=====================================================================================
--STEP7（具体例: 距離の計算n=複数）
DROP TABLE IF EXISTS test_users;

CREATE TABLE test_users (
  name   TEXT PRIMARY KEY,
  read   INTEGER,
  heavy  INTEGER,
  laugh  INTEGER,
  hot    INTEGER,
  unreal INTEGER
);

INSERT INTO test_users (name, read, heavy, laugh, hot, unreal) VALUES
  ('本は読まない・気楽に笑いたい', 1, 1, 5, 2, 1),
  ('読書家・重い話や歴史が好き',   5, 5, 2, 5, 5),
  ('熱い展開が好き',               3, 2, 3, 5, 1);

SELECT u.name AS user_name,
       n.title,
       CASE WHEN n.read > u.read THEN (n.read - u.read) * (n.read - u.read) ELSE 0 END
     + (n.heavy  - u.heavy)  * (n.heavy  - u.heavy)
     + (n.laugh  - u.laugh)  * (n.laugh  - u.laugh)
     + (n.hot    - u.hot)    * (n.hot    - u.hot)
     + (n.unreal - u.unreal) * (n.unreal - u.unreal) AS distance
FROM test_users AS u
CROSS JOIN novels AS n
ORDER BY u.name, distance;