■問題2-3
# 解答①
SELECT name, grade, id FROM student;

# 解答②
SELECT name, grade, id FROM school.student;



■問題3-1
SELECT
  name AS '名前',
  grade AS '学年',
  id AS '学生番号'
FROM student;


■問題3-2
（1）
SELECT 5 - 2 * 3;

（2）
SELECT (5 - 2) * 3;

（3）
SELECT 15 DIV 4;

（4）
SELECT 15 / 4;

（5）
SELECT 15 MOD 4;


■問題3-3
（1）
SELECT * FROM student WHERE grade = 3;

（2）
SELECT name FROM student WHERE name LIKE '%郎';


■問題3-4
（1）
SELECT * FROM resource WHERE price >= 2000 AND price <= 5000;

（2）
SELECT * FROM resource WHERE price < 2000 OR price > 5000;

（3）
SELECT * FROM student WHERE grade = 1 OR grade = 2;

（4）
SELECT * FROM student WHERE grade <> 1 AND grade <> 2;



■問題4-1
# 解答①
SELECT
  id AS '学生番号',
  name AS '名前',
  grade AS '学年',
  english AS '英語',
  math AS '数学',
  science AS '理科'
FROM student
INNER JOIN score USING (id);

# 解答②
SELECT
  student.id AS '学生番号',
  name AS '名前',
  grade AS '学年',
  english AS '英語',
  math AS '数学',
  science AS '理科'
FROM student
INNER JOIN score
ON student.id = score.id;


■問題4-2
# 解答①
SELECT
  english AS '英語',
  name AS '名前',
  id AS '学生番号',
  grade AS '学年'
FROM student INNER JOIN score USING (id)
ORDER BY english DESC

# 解答②
SELECT
  english AS '英語',
  name AS '名前',
  student.id AS '学生番号',
  grade AS '学年'
FROM student INNER JOIN score
ON student.id = score.id
ORDER BY english DESC;


■問題4-3
# 解答①
SELECT
  grade AS '学年',
  AVG(english) AS '英語の平均点',
  AVG(math) AS '数学の平均点',
  AVG(science) AS '数学の平均点'
FROM student INNER JOIN score USING (id)
GROUP BY grade
ORDER BY grade;

# 解答②
SELECT
  grade AS '学年',
  AVG(english) AS '英語の平均点',
  AVG(math) AS '数学の平均点',
  AVG(science) AS '数学の平均点'
FROM student INNER JOIN score
ON student.id = score.id
GROUP BY grade
ORDER BY grade;


■問題4-4
# 解答①
SELECT
  class_name.name AS 'カテゴリ名',
  COUNT(class_name.name) AS '商品数'
FROM resource
INNER JOIN class_name USING (class)
GROUP BY class_name.name
HAVING COUNT(class_name.name) >= 2
ORDER BY '商品数' DESC;

# 解答②
SELECT
  class_name.name AS 'カテゴリ名',
  COUNT(class_name.name) AS '商品数'
FROM resource INNER JOIN class_name
ON resource.class = class_name.class
GROUP BY class_name.name
HAVING COUNT(class_name.name) >= 2
ORDER BY '商品数' DESC;



■問題5-1
SELECT * FROM resource RIGHT OUTER JOIN class_name USING (class);


■問題5-2
SELECT
  IFNULL(code,'--') AS '商品コード',
  IFNULL(resource.name,'該当なし') AS '商品名',
  class_name.name AS 'カテゴリ',
  IFNULL(price,'--') AS '値段'
FROM resource
RIGHT OUTER JOIN class_name USING (class);


■問題5-3
SELECT
  name AS 'カテゴリ'
FROM class_name
WHERE class = ANY(
  SELECT class FROM resource
  WHERE price >=4000
  GROUP BY class
);



■問題7-1
（1）
UPDATE foods SET number = 200 WHERE food_name = 'カレーライス';

（2）
UPDATE foods SET food_name = '焼肉定食', price=700 WHERE number = 50;


■問題7-2
（1）
ALTER TABLE foods RENAME TO food_list;

（2）
ALTER TABLE food_list CHANGE food_name name VARCHAR(20);
