JavaScriptでSQL

sql_to_javascript.js

//https://www.canit.jp/category/intro/sql

const assert = require("assert");
const student = [
  {
    id: 1,
    name: "佐藤",
    age: 12,
    gender: "男",
    test_score: 89,
    birthday: "2000-05-09",
  },
  {
    id: 2,
    name: "鈴木",
    age: 9,
    gender: "男",
    test_score: 64,
    birthday: "1997-04-09",
  },
  {
    id: 3,
    name: "高橋",
    age: 8,
    gender: "女",
    test_score: 90,
    birthday: "1996-03-11",
  },
  {
    id: 4,
    name: "田中",
    age: 10,
    gender: "男",
    test_score: 45,
    birthday: "1998-04-05",
  },
  {
    id: 5,
    name: "伊藤",
    age: 11,
    gender: "女",
    test_score: 25,
    birthday: "1999-01-13",
  },
  {
    id: 6,
    name: "渡辺",
    age: 8,
    gender: "女",
    test_score: 50,
    birthday: "1996-02-14",
  },
  {
    id: 7,
    name: "山本",
    age: 11,
    gender: "男",
    test_score: 55,
    birthday: "1999-03-30",
  },
  {
    id: 8,
    name: "中村",
    age: 12,
    gender: "男",
    test_score: 100,
    birthday: "2000-06-01",
  },
  {
    id: 9,
    name: "小林",
    age: 10,
    gender: "女",
    test_score: 99,
    birthday: "1998-07-28",
  },
  {
    id: 10,
    name: "加藤",
    age: 10,
    gender: "女",
    test_score: 14,
    birthday: "1998-08-25",
  },
];
//SELECT * FROM Student;
const select = (data) => data.map((i) => i);
console.table(select(student));
assert.equal(select(student).length, 10);

//SELECT id FROM Student;
const select_id = (data) => data.map((i) => i.id);
console.table(select_id(student));
assert.equal(select(student).length, 10);

//SELECT id,test_score FROM Student;
const select_id_test_score = (data) =>
  data.map((i) => ({ id: i.id, test_score: i.test_score }));
console.table(select_id_test_score(student));
assert.equal(select(student).length, 10);

//SELECT * FROM Student WHERE age=12;
const select_age = (data, age) => data.filter((i) => i.age === age);
console.table(select_age(student, 12));
assert.equal(select_age(student, 12).length, 2);

//SELECT * FROM Student WHERE gender="女";
const select_gender = (data, gender) => data.filter((i) => i.gender === gender);
console.table(select_gender(student, "女"));
assert.equal(select_gender(student, "女").length, 5);

//SELECT * FROM Student WHERE age>10;
const select_age_grater = (data, age) => data.filter((i) => i.age > age);
console.table(select_age_grater(student, 10));
assert.equal(select_age_grater(student, 10).length, 4);

//SELECT * FROM Student WHERE test_score<=50;
const select_test_score_grater_less_equal = (data, test_score) =>
  data.filter((i) => i.test_score <= test_score);
console.table(select_test_score_grater_less_equal(student, 50));
assert.equal(select_test_score_grater_less_equal(student, 50).length, 4);

//SELECT * FROM Student WHERE birthday LIKE "%2000-%";
const select_birthday_like = (data, birthday) =>
  data.filter((i) => i.birthday.match("\\s*" + birthday + "\\s*"));
console.table(select_birthday_like(student, "2000-"));
assert.equal(select_birthday_like(student, "2000-").length, 2);

//SELECT * FROM Student WHERE birthday LIKE "1998-%";
console.table(select_birthday_like(student, "1998-"));
assert.equal(select_birthday_like(student, "1998-").length, 3);

//SELECT * FROM Student WHERE name LIKE "%藤"
const select_name_like_before = (data, name) =>
  data.filter((i) => i.name.match(new RegExp(name + ".*", "g")));
console.table(select_name_like_before(student, "藤"));
assert.equal(select_name_like_before(student, "藤").length, 3);

//SELECT * FROM Student WHERE name LIKE "藤%"
const select_name_like_after = (data, name) =>
  data.filter((i) => i.name.match(new RegExp("^" + name + ".*", "g")));
console.table(select_name_like_after(student, "藤"));
assert.equal(select_name_like_after(student, "藤").length, 0);

//SELECT * FROM Student WHERE test_score is NULL;
const select_test_score_is_null = (data) =>
  data.filter((i) => i.test_score === null);
console.table(select_test_score_is_null(student));
assert.equal(select_test_score_is_null(student).length, 0);

//SELECT * FROM Student WHERE test_score is NOT NULL;
const select_test_score_is_not_null = (data) =>
  data.filter((i) => i.test_score !== null);
console.table(select_test_score_is_not_null(student));
assert.equal(select_test_score_is_not_null(student).length, 10);

//SELECT * FROM Student WHERE gender="男" AND age=12;
const select_by_gender_and_age = (data, gender, age) =>
  data.filter((i) => i.gender === gender && i.age === age);
console.table(select_by_gender_and_age(student, "男", 12));
assert.equal(select_by_gender_and_age(student, "男", 12).length, 2);

//SELECT * FROM Student WHERE gender="男" AND age>10;
const select_by_gender_and_age_grater = (data, gender, age) =>
  data.filter((i) => i.gender === gender && i.age > age);
console.table(select_by_gender_and_age_grater(student, "男", 10));
assert.equal(select_by_gender_and_age_grater(student, "男", 10).length, 3);

//SELECT * FROM Student WHERE gener="女" AND birthday LIKE "%1998-%"
const select_by_gender_and_birthday_like = (data, gender, birthday) =>
  data.filter(
    (i) =>
      i.gender === gender &&
      i.birthday.match(new RegExp("^" + birthday + ".*", "g"))
  );
console.table(select_by_gender_and_birthday_like(student, "女", "1998-"));
assert.equal(
  select_by_gender_and_birthday_like(student, "女", "1998-").length,
  2
);

//SELECT * FROM Student WHERE gender="男" AND age=12 AND test_score>50;
const select_by_gender_and_age_and_test_score_grater = (
  data,
  gender,
  age,
  test_score
) =>
  data.filter(
    (i) => i.gender === gender && i.age === age && i.test_score > test_score
  );
console.table(
  select_by_gender_and_age_and_test_score_grater(student, "男", 12, 50)
);
assert.equal(
  select_by_gender_and_age_and_test_score_grater(student, "男", 12, 50).length,
  2
);

//SELECT * FROM Student WHERE age=10 OR age=12;
const select_by_age = (data, agelist) =>
  data.filter((i) => agelist.includes(i.age));
console.table(select_by_age(student, [10, 12]));
assert.equal(select_by_age(student, [10, 12]).length, 5);

//SELECT * FROM Student WHERE age=10 OR age=12 OR name="渡辺";
const select_by_age_or_name = (data, agelist, name) =>
  data.filter((i) => agelist.includes(i.age) || i.name === name);
console.table(select_by_age_or_name(student, [10, 12], "渡辺"));
assert.equal(select_by_age_or_name(student, [10, 12], "渡辺").length, 6);

//SELECT name,age FROM Student ORDER BY age ASC;
const select_name_and_age_order_by_age_asc = (data) =>
  data.sort((a, b) => a.age - b.age).map((i) => ({ name: i.name, age: i.age }));
console.table(select_name_and_age_order_by_age_asc(student));
assert.equal(select_name_and_age_order_by_age_asc(student)[0].age, 8);

//SELECT name,birthday FROM Student ORDER BY birthday ASC;
const select_name_and_birthday_order_by_birthday_asc = (data) =>
  data
    .sort((a, b) => a.birthday.localeCompare(b.birthday))
    .map((i) => ({ name: i.name, birthday: i.birthday }));
console.table(select_name_and_birthday_order_by_birthday_asc(student));
assert.equal(
  select_name_and_birthday_order_by_birthday_asc(student)[0].birthday,
  "1996-02-14"
);

//SELECT name,age FROM Student ORDER BY age DESC;
const select_name_and_age_order_by_age_desc = (data) =>
  data.sort((a, b) => b.age - a.age).map((i) => ({ name: i.name, age: i.age }));
console.table(select_name_and_age_order_by_age_desc(student));
assert.equal(select_name_and_age_order_by_age_desc(student)[0].age, 12);

//SELECT age,id FROM Student ORDER BY age,id ASC;
const select_age_and_id_order_by_age_asc_and_id_asc = (data) =>
  data
    .sort((a, b) => a.age - b.age || a.id - b.id)
    .map((i) => ({ age: i.age, id: i.id }));
console.table(select_age_and_id_order_by_age_asc_and_id_asc(student));
assert.equal(select_age_and_id_order_by_age_asc_and_id_asc(student)[0].age, 8);

//SELECT name,age FROM Student WHERE age>=10 ORDER BY age ASC;
const select_name_and_age_order_by_age_asc_by_age_grater = (data, age) =>
  data
    .filter((i) => i.age >= age)
    .sort((a, b) => a.age - b.age)
    .map((i) => ({ name: i.name, age: i.age }));
console.table(select_name_and_age_order_by_age_asc_by_age_grater(student, 10));
assert.equal(
  select_name_and_age_order_by_age_asc_by_age_grater(student, 10)[0].age,
  10
);

//SELECT name,id,age,gender FROM Student WHERE gender="女" ORDER BY age,id ASC;
const select_name_and_id_and_age_and_gender_order_by_age_id_asc_by_gender = (
  data,
  gender
) =>
  data
    .filter((i) => i.gender === gender)
    .sort((a, b) => a.age - b.age || a.id - b.id)
    .map((i) => ({ name: i.name, age: i.age, gender: i.gender }));
console.table(
  select_name_and_id_and_age_and_gender_order_by_age_id_asc_by_gender(
    student,
    "女"
  )
);
assert.equal(
  select_name_and_id_and_age_and_gender_order_by_age_id_asc_by_gender(
    student,
    "女"
  )[0].age,
  8
);

//SELECT name FROM Student LIMIT 5;
const select_name_limit = (data, limit) =>
  data
    .sort((a, b) => a.id - b.id)
    .map((i) => i.name)
    .slice(0, -1 * limit);
console.table(select_name_limit(student, 5));
assert.equal(select_name_limit(student, 5).length, 5);

//SELECT name FROM Student WHERE age=12 LIMIT 1;
const select_name_by_age_limit = (data, age, limit) => {
  const result = data
    .filter((i) => i.age === age)
    .sort((a, b) => a.id - b.id)
    .map((i) => i.name)
    .slice(0, -1 * limit);
  return result.length ? result[0] : null;
};
console.table(select_name_by_age_limit(student, 12, 1));
assert.equal(select_name_by_age_limit(student, 12, 1), "佐藤");

//SELECT name FROM Student WHERE birthday LIKE "2000-%" LIMIT 1;
const select_name_by_birthday_like_limit = (data, birthday, limit) => {
  const result = data
    .filter((i) => i.birthday.includes(birthday))
    .sort((a, b) => a.id - b.id)
    .map((i) => i.name)
    .slice(0, -1 * limit);
  return result.length ? result[0] : null;
};
console.table(select_name_by_birthday_like_limit(student, "2000-", 1));
assert.equal(select_name_by_birthday_like_limit(student, "2000-", 1), "佐藤");

//SELECT name FROM Student ORDER BY age ASC LIMIT 1;
const select_name_order_by_age_asc_limit = (data, limit) => {
  const result = data
    .sort((a, b) => a.age - b.age)
    .map((i) => i.name)
    .slice(0, -1 * limit);
  return result.length ? result[0] : null;
};
console.table(select_name_order_by_age_asc_limit(student, 1));
assert.equal(select_name_order_by_age_asc_limit(student, 1), "高橋");

//SELECT DISTINCT(age) FROM Student ORDER BY age ASC;
const select_distinct_age_order_by_age_asc = (data) =>
  data
    .sort((a, b) => a.age - b.age)
    .map((i) => i.age)
    .filter((i, index, self) => self.indexOf(i) === index);
console.table(select_distinct_age_order_by_age_asc(student));
assert.equal(select_distinct_age_order_by_age_asc(student).length, 5);

//SELECT SUM(test_score) FROM Student;
const select_sum_test_score = (data) =>
  data.reduce((acc, cur) => acc + cur.test_score, 0);
console.table(select_sum_test_score(student));
assert.equal(select_sum_test_score(student), 631);

//SELECT SUM(test_score) FROM Student WHERE test_score>=50;
const select_sum_test_score_by_test_score_grater = (data, test_score) =>
  data
    .filter((i) => i.test_score >= test_score)
    .reduce((acc, cur) => acc + cur.test_score, 0);
console.table(select_sum_test_score_by_test_score_grater(student, 50));
assert.equal(select_sum_test_score_by_test_score_grater(student, 50), 547);

//SELECT COUNT(test_score) FROM Student;
const select_count_test_score = (data) => data.length;
console.table(select_count_test_score(student));
assert.equal(select_count_test_score(student), 10);

//SELECT COUNT(test_score) FROM Student WHERE test_score>=50;
const select_count_test_score_by_test_score_grater = (data, test_score) =>
  data.filter((i) => i.test_score >= test_score).length;

console.table(select_count_test_score_by_test_score_grater(student, 50));
assert.equal(select_count_test_score_by_test_score_grater(student, 50), 7);

//SELECT AVG(test_score) FROM Student;
const select_avg_test_score = (data) =>
  data.reduce((acc, cur) => acc + cur.test_score, 0) / data.length;
console.table(select_avg_test_score(student));
assert.equal(select_avg_test_score(student), 63.1);

//SELECT AVG(test_score) FROM Student WHERE gender="男";
const select_avg_test_score_by_gender = (data, gender) =>
  data
    .filter((i) => i.gender === gender)
    .reduce((acc, cur) => acc + cur.test_score, 0) /
  data.filter((i) => i.gender === gender).length;
console.table(select_avg_test_score_by_gender(student, "男"));
assert.equal(select_avg_test_score_by_gender(student, "男"), 70.6);

//SELECT MAX(age) FROM Student;
const select_max_age = (data) =>
  data.map((i) => i.age).reduce((a, b) => Math.max(a, b));
console.table(select_max_age(student));
assert.equal(select_max_age(student), 12);

//SELECT MAX(birthday) FROM Student;
const select_max_birthday = (data) =>
  data
    .map((i) => i.birthday)
    .reduce((a, b) => {
      const a_date = new Date(a);
      const b_date = new Date(b);
      return a_date > b_date ? a : b;
    });

console.table(select_max_birthday(student));
assert.equal(select_max_birthday(student), "2000-06-01");

//SELECT MAX(age) FROM Student WHERE test_score>=50;
const select_max_age_by_test_score_grater = (data, test_score) =>
  data
    .filter((i) => i.test_score >= test_score)
    .map((i) => i.age)
    .reduce((a, b) => Math.max(a, b));
console.table(select_max_age_by_test_score_grater(student, 50));
assert.equal(select_max_age_by_test_score_grater(student, 50), 12);

//SELECT MIN(age) FROM Student;
const select_min_age = (data) =>
  data.map((i) => i.age).reduce((a, b) => Math.min(a, b));
console.table(select_min_age(student));
assert.equal(select_min_age(student), 8);

//SELECT MIN(age) FROM Student WHERE test_score>=50;
const select_min_age_by_test_score_grater = (data, test_score) =>
  data
    .filter((i) => i.test_score >= test_score)
    .map((i) => i.age)
    .reduce((a, b) => Math.min(a, b));
console.table(select_min_age_by_test_score_grater(student, 50));
assert.equal(select_min_age_by_test_score_grater(student, 50), 8);

//SELECT gender, SUM(test_score) AS test_score_by_gender FROM Student GROUP BY gender;
const select_gender_sum_test_score_gourp_by_gender = (data) => {
  const gender_group = data
    .sort((a, b) => a.gender - b.gender)
    .map((i) => i.gender)
    .filter((i, index, self) => self.indexOf(i) === index);

  return gender_group.map((gender) => {
    const list = data.filter((i) => i.gender === gender);
    return {
      gender,
      test_score_by_gender: list.reduce((acc, cur) => acc + cur.test_score, 0),
    };
  });
};
console.table(select_gender_sum_test_score_gourp_by_gender(student));
assert.equal(
  select_gender_sum_test_score_gourp_by_gender(student)[0].gender,
  "女"
);
assert.equal(
  select_gender_sum_test_score_gourp_by_gender(student)[0].test_score_by_gender,
  278
);

//SELECT age, SUM(test_score) as test_score_by_age FROM Student GROUP BY age;
const select_age_sum_test_score_gourp_by_age = (data) => {
  const age_group = data
    .sort((a, b) => a.age - b.age)
    .map((i) => i.age)
    .filter((i, index, self) => self.indexOf(i) === index);

  return age_group.map((age) => {
    const list = data.filter((i) => i.age === age);
    return {
      age,
      test_score_by_age: list.reduce((acc, cur) => acc + cur.test_score, 0),
    };
  });
};
console.table(select_age_sum_test_score_gourp_by_age(student));
assert.equal(select_age_sum_test_score_gourp_by_age(student)[0].age, 8);
assert.equal(
  select_age_sum_test_score_gourp_by_age(student)[0].test_score_by_age,
  140
);

//SELECT gender, AVG(test_score) AS test_score_avagrage_by_gender FROM Student GROUP BY gender;
const select_gender_age_test_score_gourp_by_gender = (data) => {
  const gender_group = data
    .sort((a, b) => a.gender - b.gender)
    .map((i) => i.gender)
    .filter((i, index, self) => self.indexOf(i) === index);

  return gender_group.map((gender) => {
    const list = data.filter((i) => i.gender === gender);
    return {
      gender,
      test_score_by_gender:
        list.reduce((acc, cur) => acc + cur.test_score, 0) / list.length,
    };
  });
};
console.table(select_gender_age_test_score_gourp_by_gender(student));
assert.equal(
  select_gender_age_test_score_gourp_by_gender(student)[0].gender,
  "女"
);
assert.equal(
  select_gender_age_test_score_gourp_by_gender(student)[0].test_score_by_gender,
  55.6
);

//SELECT age, AVG(test_score) AS test_score_avarate_by_age FROM Student GROUP BY age ORDER BY AVG(test_score) ASC;
const select_age_test_score_average_by_age_order_by_test_score_avarage = (
  data
) => {
  const age_group = data
    .sort((a, b) => a.age - b.age)
    .map((i) => i.age)
    .filter((i, index, self) => self.indexOf(i) === index);

  return age_group
    .map((age) => {
      const list = data.filter((i) => i.age === age);
      return {
        age,
        test_score_avarage_by_age:
          list.reduce((acc, cur) => acc + cur.test_score, 0) / list.length,
      };
    })
    .sort((a, b) => a.test_score_avarage_by_age - b.test_score_avarage_by_age);
};
console.table(
  select_age_test_score_average_by_age_order_by_test_score_avarage(student)
);
assert.equal(
  select_age_test_score_average_by_age_order_by_test_score_avarage(student)[0]
    .age,
  11
);
assert.equal(
  select_age_test_score_average_by_age_order_by_test_score_avarage(student)[0]
    .test_score_avarage_by_age,
  40
);

//SELECT gender, SUM(test_score) AS test_sucore_by_gender_grater_equal_age FROM Student WHERE age>=10 GROUP BY gender;
const select_gender_test_score_by_gender_and_grater_equal_age = (data, age) => {
  const gender_group = data
    .sort((a, b) => a.gender - b.gender)
    .map((i) => i.gender)
    .filter((i, index, self) => self.indexOf(i) === index);

  return gender_group.map((gender) => {
    const list = data.filter((i) => i.gender === gender && i.age >= age);
    return {
      gender,
      test_score: list.reduce((acc, cur) => acc + cur.test_score, 0),
    };
  });
};
console.table(
  select_gender_test_score_by_gender_and_grater_equal_age(student, 10)
);
assert.equal(
  select_gender_test_score_by_gender_and_grater_equal_age(student, 10)[1]
    .gender,
  "男"
);
assert.equal(
  select_gender_test_score_by_gender_and_grater_equal_age(student, 10)[1]
    .test_score,
  289
);

//SELECT gender, SUM(test_score) as test_score_by_gender FROM Student GROUP BY gender WITH ROLLUP;
const select_gender_sum_test_score_gourp_by_gender_with_rollup = (data) => {
  const gender_group = data
    .sort((a, b) => a.gender - b.gender)
    .map((i) => i.gender)
    .filter((i, index, self) => self.indexOf(i) === index);

  const group_sum = gender_group.map((gender) => {
    const list = data.filter((i) => i.gender === gender);
    return {
      gender,
      test_score: list.reduce((acc, cur) => acc + cur.test_score, 0),
    };
  });

  group_sum.push({
    gender: null,
    test_score: group_sum.reduce((acc, cur) => acc + cur.test_score, 0),
  });

  return group_sum;
};
console.table(
  select_gender_sum_test_score_gourp_by_gender_with_rollup(student)
);
assert.equal(
  select_gender_sum_test_score_gourp_by_gender_with_rollup(student)[2].gender,
  null
);
assert.equal(
  select_gender_sum_test_score_gourp_by_gender_with_rollup(student)[2]
    .test_score,
  631
);

//SELECT name, test_score FROM Student HAVING test_score>=50
const select_name_test_score_by_name_and_grater_equal_test_score = (
  data,
  test_score
) => {
  const name_group = data
    .sort((a, b) => a.name - b.name)
    .map((i) => i.name)
    .filter((i, index, self) => self.indexOf(i) === index);

  return name_group
    .map((name) => {
      const list = data.filter(
        (i) => i.name === name && i.test_score >= test_score
      );
      return list.map((i) => {
        return {
          name: i.name,
          test_score: list.reduce((acc, cur) => acc + cur.test_score, 0),
        };
      })[0];
    })
    .filter((v) => v);
};
console.table(
  select_name_test_score_by_name_and_grater_equal_test_score(student, 50)
);
assert.equal(
  select_name_test_score_by_name_and_grater_equal_test_score(student, 50)[0]
    .test_score,
  90
);

//SELECT age, SUM(test_score) AS test_score_by_age FROM Student GROUP BY age HAVING SUM(test_score)>=100;
//SELECT age, SUM(test_score) AS test_score_by_age FROM Student GROUP BY age HAVING test_score_by_age >=100;
const select_age_sum_test_score_by_age_having_grater_equal_test_score = (
  data,
  test_score
) => {
  const age_group = data
    .sort((a, b) => a.age - b.age)
    .map((i) => i.age)
    .filter((i, index, self) => self.indexOf(i) === index);

  const having = age_group.map((age) => {
    const list = data.filter((i) => i.age === age);
    return list.map((i) => {
      return {
        age: i.age,
        test_score: list.reduce((acc, cur) => acc + cur.test_score, 0),
      };
    })[0];
  });

  return having
    .map((i) => {
      if (i.test_score >= test_score) {
        return i;
      }
    })
    .filter((v) => v);
};
console.table(
  select_age_sum_test_score_by_age_having_grater_equal_test_score(student, 100)
);
assert.equal(
  select_age_sum_test_score_by_age_having_grater_equal_test_score(
    student,
    100
  )[0].age,
  8
);
assert.equal(
  select_age_sum_test_score_by_age_having_grater_equal_test_score(
    student,
    100
  )[0].test_score,
  140
);

//SELECT age, SUM(test_score) AS test_score_by_age FROM Student GROUP BY age HAVING test_score_by_age >=100 ORDER BY test_score_by_age;
const select_age_sum_test_score_by_age_having_grater_equal_test_score_order_by_test_score =
  (data, test_score) => {
    const age_group = data
      .sort((a, b) => a.age - b.age)
      .map((i) => i.age)
      .filter((i, index, self) => self.indexOf(i) === index);

    const having = age_group.map((age) => {
      const list = data.filter((i) => i.age === age);
      return list.map((i) => {
        return {
          age: i.age,
          test_score: list.reduce((acc, cur) => acc + cur.test_score, 0),
        };
      })[0];
    });

    return having
      .map((i) => {
        if (i.test_score >= test_score) {
          return i;
        }
      })
      .filter((v) => v)
      .sort((a, b) => b.test_score + a.test_score);
  };
console.table(
  select_age_sum_test_score_by_age_having_grater_equal_test_score_order_by_test_score(
    student,
    100
  )
);
assert.equal(
  select_age_sum_test_score_by_age_having_grater_equal_test_score_order_by_test_score(
    student,
    100
  )[0].age,
  8
);
assert.equal(
  select_age_sum_test_score_by_age_having_grater_equal_test_score_order_by_test_score(
    student,
    100
  )[0].test_score,
  140
);

//INSERT INTO Student (id, name, age, gender, test_score, birthday) VALUE(11, '石田', 13, '女', 95, '2001/03/15');
const insert_student = (data, item) => {
  const result = data.map((i) => i);
  result.push(item);
  return result;
};

console.table(
  insert_student(student, {
    id: 11,
    name: "石田",
    age: 13,
    gender: "女",
    test_score: 95,
    birthday: "2001/03/15",
  })
);
assert.equal(
  insert_student(student, {
    id: 11,
    name: "石田",
    age: 13,
    gender: "女",
    test_score: 95,
    birthday: "2001/03/15",
  })[10].id,
  11
);
//INSERT INTO Student (id, name, age, gender, test_score, birthday) VALUE(12, '山田', 13, '男', NULL, '2001/03/15');
console.table(
  insert_student(student, {
    id: 12,
    name: "山田",
    age: 13,
    gender: "男",
    test_score: null,
    birthday: "2001/03/15",
  })
);
assert.equal(
  insert_student(student, {
    id: 12,
    name: "山田",
    age: 13,
    gender: "男",
    test_score: null,
    birthday: "2001/03/15",
  })[10].test_score,
  null
);

//INSERT INTO Student (id, name, age, gender, test_score, birthday)VALUES(13,'石井',13,'男',47,'2001/02/14'),(14,'上原',13,'女',50,'2001/01/01');
const insert_students = (data, items) => {
  const result = data.map((i) => i);
  items.map((i) => result.push(i));
  return result;
};
console.table(
  insert_students(student, [
    {
      id: 13,
      name: "石田",
      age: 13,
      gender: "男",
      test_score: 47,
      birthday: "2001/02/14",
    },
    {
      id: 14,
      name: "上原",
      age: 13,
      gender: "女",
      test_score: 50,
      birthday: "2001/01/01",
    },
  ]).sort((a, b) => a.id - b.id)
);
assert.equal(
  insert_students(student, [
    {
      id: 13,
      name: "石田",
      age: 13,
      gender: "男",
      test_score: 47,
      birthday: "2001/02/14",
    },
    {
      id: 14,
      name: "上原",
      age: 13,
      gender: "女",
      test_score: 50,
      birthday: "2001/01/01",
    },
  ]).sort((a, b) => a.id - b.id)[11].id,
  14
);

//INSERT INTO Student (name, age, gender, test_score, birthday)VALUE('石田',13,'女',95,'2001/03/15');
const insert_student_auto_increment = (data, item) => {
  const increment_id =
    data.map((i) => i.id).reduce((a, b) => Math.max(a, b)) + 1;
  const result = data.map((i) => i);
  result.push({ ...item, id: increment_id });
  return result;
};

console.table(
  insert_student_auto_increment(student, {
    name: "石田",
    age: 13,
    gender: "女",
    test_score: 95,
    birthday: "2001/03/15",
  }).sort((a, b) => a.id - b.id)
);
assert.equal(
  insert_student_auto_increment(student, {
    name: "石田",
    age: 13,
    gender: "女",
    test_score: 95,
    birthday: "2001/03/15",
  }).sort((a, b) => a.id - b.id)[10].id,
  11
);

//UPDATE Student set test_score=80 WHERE id=10;
const update_test_score = (data, id, test_score) =>
  data.map((i) => (i.id === id ? { ...i, test_score } : i));
console.table(update_test_score(student, 10, 80).sort((a, b) => a.id - b.id));
assert.equal(
  update_test_score(student, 10, 80).sort((a, b) => a.id - b.id)[9].test_score,
  80
);

//UPDATE Student set name=80 WHERE id=10;
const update_name = (data, id, name) =>
  data.map((i) => (i.id === id ? { ...i, name } : i));
console.table(update_name(student, 10, "山田").sort((a, b) => a.id - b.id));
assert.equal(
  update_name(student, 10, "山田").sort((a, b) => a.id - b.id)[9].name,
  "山田"
);

//UPDATE Student set test_score=NULL WHERE id=10;
const update_test_score_null = (data, id) =>
  data.map((i) => (i.id === id ? { ...i, test_score: null } : i));
console.table(update_test_score_null(student, 10).sort((a, b) => a.id - b.id));
assert.equal(
  update_test_score_null(student, 10).sort((a, b) => a.id - b.id)[9].test_score,
  null
);

//UPDATE Student set name=NULL WHERE id=10;
const update_name_null = (data, id) =>
  data.map((i) => (i.id === id ? { ...i, name: null } : i));
console.table(update_name_null(student, 10).sort((a, b) => a.id - b.id));
assert.equal(
  update_name_null(student, 10).sort((a, b) => a.id - b.id)[9].name,
  null
);

//UPDATE Student set birthday=NULL WHERE id=10;
const update_birthday_null = (data, id) =>
  data.map((i) => (i.id === id ? { ...i, birthday: null } : i));
console.table(update_birthday_null(student, 10).sort((a, b) => a.id - b.id));
assert.equal(
  update_birthday_null(student, 10).sort((a, b) => a.id - b.id)[9].birthday,
  null
);

//UPDATE Student set name="藤井", test_score=65 WHERE id=10;
const update_name_test_score = (data, id, name, test_score) =>
  data.map((i) => (i.id === id ? { ...i, name, test_score } : i));
console.table(
  update_name_test_score(student, 10, "藤井", 65).sort((a, b) => a.id - b.id)
);
assert.equal(
  update_name_test_score(student, 10, "藤井", 65).sort((a, b) => a.id - b.id)[9]
    .name,
  "藤井"
);
assert.equal(
  update_name_test_score(student, 10, "藤井", 65).sort((a, b) => a.id - b.id)[9]
    .test_score,
  65
);

//UPDATE Student set test_score=40 WHERE id=9 OR id=10;
const update_test_score_or = (data, id1, id2) =>
  data.map((i) =>
    i.id === id1 || i.id === id2 ? { ...i, test_score: 40 } : i
  );
console.table(update_test_score_or(student, 9, 10).sort((a, b) => a.id - b.id));
assert.equal(
  update_test_score_or(student, 9, 10).sort((a, b) => a.id - b.id)[9]
    .test_score,
  40
);

//DELETE FROM Student WHERE id=10;
const delete_student = (data, id) => data.filter((i) => i.id !== id);
console.table(delete_student(student, 10).sort((a, b) => a.id - b.id));
assert.equal(delete_student(student, 10).sort((a, b) => a.id - b.id)[8].id, 9);

//DELETE FROM Student WHERE id=9 OR id=10;
const delete_student_or = (data, id1, id2) =>
  data.filter((i) => i.id !== id1 && i.id !== id2);
console.table(delete_student_or(student, 9, 10).sort((a, b) => a.id - b.id));
assert.equal(
  delete_student_or(student, 9, 10).sort((a, b) => a.id - b.id)[7].id,
  8
);

//DELETE FROM Student ORDER BY id DESC LIMIT 1;
const delete_student_order_desc_limit = (data, limit) =>
  data.sort((a, b) => a.id - b.id).slice(0, -1 * limit);
console.table(delete_student_order_desc_limit(student, 1));
assert.equal(delete_student_order_desc_limit(student, 1)[8].id, 9);

//SELECT CONCAT("Hello", "World");
const select_concat = (data, str1, str2) => `${str1}${str2}`;
console.log(select_concat(student, "Hello", "World"));
assert.equal(select_concat(student, "Hello", "World"), "HelloWorld");

//SELECT UPPER("apple");
const select_upper = (data, str) => str.toUpperCase();
console.log(select_upper(student, "apple"));
assert.equal(select_upper(student, "apple"), "APPLE");

//SELECT LOWER("APPLE");
const select_lower = (data, str) => str.toLowerCase();
console.log(select_lower(student, "APPLE"));
assert.equal(select_lower(student, "APPLE"), "apple");

//SELECT 1 + 2;
const select_add = (data, num1, num2) => num1 + num2;
console.log(select_add(student, 1, 2));
assert.equal(select_add(student, 1, 2), 3);

//SELECT 2 * 3;
const select_mul = (data, num1, num2) => num1 * num2;
console.log(select_mul(student, 2, 3));
assert.equal(select_mul(student, 2, 3), 6);

//SELECT MOD(5,3);
const select_mod = (data, num1, num2) => num1 % num2;
console.log(select_mod(student, 5, 3));
assert.equal(select_mod(student, 5, 3), 2);

//SELECT SQRT(9);
const select_sqrt = (data, num) => Math.sqrt(num);
console.log(select_sqrt(student, 9));
assert.equal(select_sqrt(student, 9), 3);

//SELECT POW(2,3);
const select_pow = (data, num1, num2) => Math.pow(num1, num2);
console.log(select_pow(student, 2, 3));
assert.equal(select_pow(student, 2, 3), 8);

//SELECT ABS(-2);
const select_abs = (data, num) => Math.abs(num);
console.log(select_abs(student, -2));
assert.equal(select_abs(student, -2), 2);

//SELECT CURRENT_DATE;
const select_current_date = (data) => new Date();
console.log(select_current_date(student));
assert.equal(select_current_date(student).getDate(), new Date().getDate());

//SELECT CURRENT_TIME;
const select_current_time = (data) => new Date();
console.log(select_current_time(student));
assert.equal(select_current_time(student).getHours(), new Date().getHours());

//SELECT name FROM Student WHERE test_score < (SELECT test_score FROM Student WHERE name = "渡辺");
const select_name_from_test_score_where_name = (data, name) => {
  const test_score = data.find((i) => i.name === name).test_score;
  return data.filter((i) => i.test_score < test_score).map((i) => i.name);
};
console.table(select_name_from_test_score_where_name(student, "渡辺"));
assert.equal(
  select_name_from_test_score_where_name(student, "渡辺")[0],
  "田中"
);

//SELECT gender.AVG(test_score) AS 性別ごと, (SELECT AVG(test_score) FROM Student ) AS 全体 FROM Student GROUP BY gender;
const select_by_gender_from_all_averate_test_score_by_gender = (data) => {
  const gender_group = data
    .sort((a, b) => a.gender - b.gender)
    .map((i) => i.gender)
    .filter((i, index, self) => self.indexOf(i) === index);

  const group_avg = gender_group.map((gender) => {
    const list = data.filter((i) => i.gender === gender);
    return {
      gender,
      test_score:
        list.reduce((acc, cur) => acc + cur.test_score, 0) / list.length,
    };
  });

  group_avg.push({
    gender: "全体",
    test_score:
      Math.round(
        (group_avg.reduce((acc, cur) => acc + cur.test_score, 0) /
          group_avg.length) *
          10
      ) / 10,
  });

  return group_avg;
};
console.table(select_by_gender_from_all_averate_test_score_by_gender(student));
assert.equal(
  select_by_gender_from_all_averate_test_score_by_gender(student)[2].test_score,
  63.1
);

//SELECT age,AVG(test_score) FROM Student GROUP BY age having AVG(test_score) > (SELECT AVG(test_score) FROM Student) ORDER BY age;
const select_age_test_score_average_from_grater_than_all_average = (data) => {
  const age_group = data
    .sort((a, b) => a.age - b.age)
    .map((i) => i.age)
    .filter((i, index, self) => self.indexOf(i) === index);

  const having_avg = age_group.map((age) => {
    const list = data.filter((i) => i.age === age);
    return {
      age,
      test_score:
        list.reduce((acc, cur) => acc + cur.test_score, 0) / list.length,
    };
  });

  const all_avg =
    data.reduce((acc, cur) => acc + cur.test_score, 0) / data.length;
  return having_avg.filter((i) => i.test_score > all_avg).map((i) => i);
};
console.table(
  select_age_test_score_average_from_grater_than_all_average(student)
);
assert.equal(
  select_age_test_score_average_from_grater_than_all_average(student)[0]
    .test_score,
  70
);

//SELECT name FROM Student WHERE name = "佐藤" UNION SELECT name FROM Student WHERE name = "鈴木";
const select_name_from_student_where_name_union_select_name_from_student_where_name =
  (data, name1, name2) => {
    const list1 = data.filter((i) => i.name === name1);
    const list2 = data.filter((i) => i.name === name2);
    return list1.concat(list2).map((i) => i.name);
  };
console.table(
  select_name_from_student_where_name_union_select_name_from_student_where_name(
    student,
    "佐藤",
    "鈴木"
  )
);
assert.equal(
  select_name_from_student_where_name_union_select_name_from_student_where_name(
    student,
    "佐藤",
    "鈴木"
  )[0],
  "佐藤"
);

//SELECT name FROM Student WHERE age = 9 UNION ALL SELECT name FROM Student WHERE name = "鈴木";
const select_name_from_student_where_age_union_all_select_name_from_student_where_name =
  (data, age, name) => {
    const list1 = data.filter((i) => i.age === age);
    const list2 = data.filter((i) => i.name === name);
    return list1.concat(list2).map((i) => i.name);
  };
console.table(
  select_name_from_student_where_age_union_all_select_name_from_student_where_name(
    student,
    9,
    "鈴木"
  )
);
assert.equal(
  select_name_from_student_where_age_union_all_select_name_from_student_where_name(
    student,
    9,
    "鈴木"
  )[0],
  "鈴木"
);

//SELECT name FROM Student WHERE gender = "女" INTERSECT SELECT name FROM Student WHERE id = 3;
const select_name_from_student_where_gender_intersect_select_name_from_student_where_id =
  (data, gender, id) => {
    const list1 = data.filter((i) => i.gender === gender);
    const list2 = data.filter((i) => i.id === id);
    return list1.filter((value) => list2.includes(value));
  };
console.table(
  select_name_from_student_where_gender_intersect_select_name_from_student_where_id(
    student,
    "女",
    3
  )
);
assert.equal(
  select_name_from_student_where_gender_intersect_select_name_from_student_where_id(
    student,
    "女",
    3
  )[0].name,
  "高橋"
);

//SELECT name FROM Student WHERE gender = "女" EXCEPT SELECT name FROM Student WHERE id = 3;
const select_name_from_student_where_gender_except_select_name_from_student_where_id =
  (data, gender, id) => {
    const list1 = data.filter((i) => i.gender === gender);
    const list2 = data.filter((i) => i.id === id);
    return list1.filter((value) => !list2.includes(value));
  };
console.table(
  select_name_from_student_where_gender_except_select_name_from_student_where_id(
    student,
    "女",
    3
  )
);
assert.equal(
  select_name_from_student_where_gender_except_select_name_from_student_where_id(
    student,
    "女",
    3
  )[0].name,
  "渡辺"
);

student2 = [
  { id: 1, name: "佐藤", from_id: 3, class_id: 2 },
  { id: 2, name: "鈴木", from_id: 1, class_id: 3 },
  { id: 3, name: "高橋", from_id: 2, class_id: 4 },
  { id: 4, name: "田中", from_id: 4 },
];

from = [
  { id: 1, syussin: "東京" },
  { id: 2, syussin: "大阪" },
  { id: 3, syussin: "北海道" },
];

//SELECT * FROM Student INNER JOIN From ON from_id = From.id;
const select_from_student_inner_join_from_on_student_id_from_id = (
  data,
  joinData
) => {
  const list = data.map((i) => {
    const f = joinData.filter((j) => j.id === i.from_id);
    return { ...i, ...f[0] };
  });
  return list.map((i) => i).filter((i) => i.syussin !== undefined);
};
console.table(
  select_from_student_inner_join_from_on_student_id_from_id(student2, from)
);
assert.equal(
  select_from_student_inner_join_from_on_student_id_from_id(student2, from)[0]
    .syussin,
  "北海道"
);

//SELECT name,syussin FROM Student INNER JOIN From ON from_id = From.id;
const select_name_syussin_from_student_inner_join_from_on_student_id_from_id = (
  data,
  joinData
) => {
  const list = data.map((i) => {
    const f = joinData.filter((j) => j.id === i.from_id);
    return { ...i, ...f[0] };
  });
  return list
    .map((i) => ({ name: i.name, syussin: i.syussin }))
    .filter((j) => j.syussin !== undefined);
};
console.table(
  select_name_syussin_from_student_inner_join_from_on_student_id_from_id(
    student2,
    from
  )
);
assert.equal(
  select_name_syussin_from_student_inner_join_from_on_student_id_from_id(
    student2,
    from
  )[0].syussin,
  "北海道"
);

//SELECT * FROM Student LEFT JOIN From ON from_id = From.d WHERE syussin="大阪";
const select_from_student_left_join_from_on_student_id_from_id_where_syussin_is_osaka =
  (data, joinData) => {
    const list = data.map((i) => {
      const f = joinData.filter((j) => j.id === i.from_id);
      return { ...i, ...f[0] };
    });
    return list.filter((i) => i.syussin === "大阪");
  };
console.table(
  select_from_student_left_join_from_on_student_id_from_id_where_syussin_is_osaka(
    student2,
    from
  )
);
assert.equal(
  select_from_student_left_join_from_on_student_id_from_id_where_syussin_is_osaka(
    student2,
    from
  )[0].syussin,
  "大阪"
);

//SELECT * FROM Student LEFT JOIN From ON from_id = From.id;
const select_from_student_left_join_from_on_student_id_from_id = (
  data,
  joinData
) => {
  const list = data.map((i) => {
    const f = joinData.filter((j) => j.id === i.from_id);
    return { ...i, ...f[0] };
  });
  return list;
};
console.table(
  select_from_student_left_join_from_on_student_id_from_id(student2, from)
);
assert.equal(
  select_from_student_left_join_from_on_student_id_from_id(student2, from)[0]
    .syussin,
  "北海道"
);

//SELECT * FROM From RIGHT JOIN Student ON From.id = from_id;
const select_from_from_right_join_student_on_from_id_from_id = (
  data,
  joinData
) => {
  const list = data.map((i) => {
    const f = joinData.filter((j) => j.id === i.from_id);
    return { ...i, ...f[0] };
  });
  return list;
};
console.table(
  select_from_from_right_join_student_on_from_id_from_id(from, student2)
);
assert.equal(
  select_from_from_right_join_student_on_from_id_from_id(from, student2)[0]
    .syussin,
  "東京"
);

const classRomm = [
  { id: 1, teacher: "木村" },
  { id: 2, teacher: "近藤" },
  { id: 3, teacher: "桐山" },
  { id: 4, teacher: "高崎" },
];

//SELECT name,syussin,teacher FROM Student JOIN From ON from_id = From.id JOIN ClassRoom ON class_id = ClassRomm.id;
const select_name_syussin_teacher_from_student_join_from_on_student_id_from_id_join_class_room_on_class_id_class_room_id =
  (data, joinData1, joinData2) => {
    const list = data.map((i) => {
      const f = joinData1.filter((j) => j.id === i.from_id);
      const g = joinData2.filter((j) => j.id === i.class_id);
      return { ...i, ...f[0], ...g[0] };
    });
    return list.filter((i) => i.class_id !== undefined);
  };
console.table(
  select_name_syussin_teacher_from_student_join_from_on_student_id_from_id_join_class_room_on_class_id_class_room_id(
    student2,
    from,
    classRomm
  )
);
assert.equal(
  select_name_syussin_teacher_from_student_join_from_on_student_id_from_id_join_class_room_on_class_id_class_room_id(
    student2,
    from,
    classRomm
  )[0].teacher,
  "近藤"
);

//SELECT name, test_score CASE WHEN 50 <= test_score THEN "合格" ELSE "不合格" END FROM Student;
const select_name_test_score_case_when_50_test_score_is_less_than_or_equal_to_then_is_pass_else_is_fail_from_student =
  (data) => {
    const list = data.map((i) => {
      return { ...i, test_result: i.test_score >= 50 ? "合格" : "不合格" };
    });
    return list.map((i) => ({
      name: i.name,
      test_score: i.test_score,
      test_result: i.test_result,
    }));
  };
console.table(
  select_name_test_score_case_when_50_test_score_is_less_than_or_equal_to_then_is_pass_else_is_fail_from_student(
    student
  )
);
assert.equal(
  select_name_test_score_case_when_50_test_score_is_less_than_or_equal_to_then_is_pass_else_is_fail_from_student(
    student
  )[0].test_result,
  "合格"
);

//SELECT name, test_score CASE WHEN 50 <= test_score THEN "合格" WHEN test_score 40 <= test_score THEN "もう少し" ELSE "不合格" END AS 合否 FROM Student;
const select_name_test_score_case_when_50_test_score_is_less_than_or_equal_to_then_is_pass_when_test_score_40_test_score_is_less_than_or_equal_to_then_is_pass_else_is_fail_as_is_pass_from_student =
  (data) => {
    const list = data.map((i) => {
      return {
        ...i,
        test_result:
          i.test_score >= 50
            ? "合格"
            : i.test_score >= 40
            ? "もう少し"
            : "不合格",
      };
    });
    return list.map((i) => ({
      name: i.name,
      test_score: i.test_score,
      test_result: i.test_result,
    }));
  };
console.table(
  select_name_test_score_case_when_50_test_score_is_less_than_or_equal_to_then_is_pass_when_test_score_40_test_score_is_less_than_or_equal_to_then_is_pass_else_is_fail_as_is_pass_from_student(
    student
  )
);
assert.equal(
  select_name_test_score_case_when_50_test_score_is_less_than_or_equal_to_then_is_pass_when_test_score_40_test_score_is_less_than_or_equal_to_then_is_pass_else_is_fail_as_is_pass_from_student(
    student
  )[0].test_result,
  "合格"
);

//SELECT name, test_score CASE WHEN 80 <= test_score THEN "ランクA" WHEN 60 <= test_score THEN "ランクB" WHEN 40 <= test_score THEN "ランクC" WHEN 20 <= test_score THEN "ランクD" ELSE "ランクF" END AS ランク FROM Student;
const select_name_test_score_case_when_80_test_score_is_less_than_or_equal_to_then_is_rank_a_when_60_test_score_is_less_than_or_equal_to_then_is_rank_b_when_40_test_score_is_less_than_or_equal_to_then_is_rank_c_when_20_test_score_is_less_than_or_equal_to_then_is_rank_d_else_is_rank_f_from_student =
  (data) => {
    const list = data.map((i) => {
      return {
        ...i,
        ranke:
          i.test_score >= 80
            ? "ランクA"
            : i.test_score >= 60
            ? "ランクB"
            : i.test_score >= 40
            ? "ランクC"
            : i.test_score >= 20
            ? "ランクD"
            : "ランクF",
      };
    });
    return list.map((i) => ({
      name: i.name,
      test_score: i.test_score,
      ranke: i.ranke,
    }));
  };
console.table(
  select_name_test_score_case_when_80_test_score_is_less_than_or_equal_to_then_is_rank_a_when_60_test_score_is_less_than_or_equal_to_then_is_rank_b_when_40_test_score_is_less_than_or_equal_to_then_is_rank_c_when_20_test_score_is_less_than_or_equal_to_then_is_rank_d_else_is_rank_f_from_student(
    student
  )
);
assert.equal(
  select_name_test_score_case_when_80_test_score_is_less_than_or_equal_to_then_is_rank_a_when_60_test_score_is_less_than_or_equal_to_then_is_rank_b_when_40_test_score_is_less_than_or_equal_to_then_is_rank_c_when_20_test_score_is_less_than_or_equal_to_then_is_rank_d_else_is_rank_f_from_student(
    student
  )[0].ranke,
  "ランクA"
);

//SELECT name, test_score, CASE WHEN name LIKE "%藤" THEN "「藤」あり" ELSE "「藤」なし" END AS 名前の文字列 FROM Student;
const select_name_test_score_case_when_name_like_f_then_is_f_a_else_is_f_n_from_student =
  (data) => {
    const list = data.map((i) => {
      return {
        ...i,
        name_string: i.name.includes("藤") ? "「藤」あり" : "「藤」なし",
      };
    });
    return list.map((i) => ({
      name: i.name,
      test_score: i.test_score,
      name_string: i.name_string,
    }));
  };
console.table(
  select_name_test_score_case_when_name_like_f_then_is_f_a_else_is_f_n_from_student(
    student
  )
);
assert.equal(
  select_name_test_score_case_when_name_like_f_then_is_f_a_else_is_f_n_from_student(
    student
  )[0].name_string,
  "「藤」なし"
);

//SELECT SUBSTR('abcabc',3);
const select_substr_abcabc_3 = (data) => {
  return data.map((i) => {
    return { ...i, substr: i.substr(3) };
  });
};
console.log(select_substr_abcabc_3(["abcabc"]));
assert.equal(select_substr_abcabc_3(["abcabc"])[0].substr, "abc");

//SELECT SUBSTR('476831', 2, 3);
const select_substr_476831_2_3 = (data) => {
  return data.map((i) => {
    return { ...i, substr: i.substr(1, 3) };
  });
};
console.log(select_substr_476831_2_3(["476831"]));
assert.equal(select_substr_476831_2_3(["476831"])[0].substr, "768");

//SELECT REPLACE('東京出身です', '東京', '大阪');
const select_replace_東京出身です_東京_大阪 = (data) => {
  return data.map((i) => {
    return { ...i, replace: i.replace("東京", "大阪") };
  });
}
console.log(select_replace_東京出身です_東京_大阪(["東京出身です"]));
assert.equal(select_replace_東京出身です_東京_大阪(["東京出身です"])[0].replace, "大阪出身です");

//SELECT REVERSE('123456789');
const select_reverse_123456789 = (data) => {
  return data.map((i) => {
    return { reverse: i.split("").reverse().join("") };
  });
}
console.log(select_reverse_123456789(["123456789"]));
assert.equal(select_reverse_123456789(["123456789"])[0].reverse, "987654321");

//SELECT TRIM(' apple ');
const select_trim_apple = (data) => {
  return data.map((i) => {
    return { trim: i.trim() };
  });
}
console.log(select_trim_apple([" apple "]));
assert.equal(select_trim_apple([" apple "])[0].trim, "apple");

//SELECT RTRIM(' apple ');
const select_rtrim_apple = (data) => {
  return data.map((i) => {
    return { rtrim: i.trimRight() };
  });
}
console.log(select_rtrim_apple([" apple "]));
assert.equal(select_rtrim_apple([" apple "])[0].rtrim, " apple");

//SELECT ASCII("A");
const select_ascii_A = (data) => {
  return data.map((i) => {
    return { ascii: i.charCodeAt(0) };
  });
}
console.log(select_ascii_A(["A"]));
assert.equal(select_ascii_A(["A"])[0].ascii, 65);

//SELECT FORMAT(111111, '#,###');
const select_format_111111_format_number_number_number_number_number_number = (
  data
) => {
  return data.map((i) => {
    return { format: new Intl.NumberFormat('ja-JP').format(i) };
  });
}
console.log(select_format_111111_format_number_number_number_number_number_number(["111111"]));
assert.equal(select_format_111111_format_number_number_number_number_number_number(["111111"])[0].format, "111,111");

//SELECT SPACE(3);
const select_space_3 = (data) => {
  return data.map((i) => {
    return { space: i.repeat(3) };
  });
}
console.log(select_space_3([" "]));
assert.equal(select_space_3([" "])[0].space, "   ");