--Q1
CREATE TABLE item_category (
    category_id INT AUTO_INCREMENT COMMENT 'カテゴリID',
    category_name VARCHAR(256) NOT NULL COMMENT 'カテゴリ名',
    PRIMARY KEY (category_id)
)
INSERT INTO item_category (category_name) VALUES ('家具');
INSERT INTO item_category (category_name) VALUES ('飲料');
INSERT INTO item_category (category_name) VALUES ('食品');

--Q2
CREATE TABLE items (
    item_id INT AUTO_INCREMENT COMMENT 'アイテムID',
    name VARCHAR(256) NOT NULL COMMENT 'アイテム名',
    price INT NOT NULL COMMENT '価格',
    category_id INT COMMENT 'カテゴリID',
    PRIMARY KEY (item_id),
    FOREIGN KEY (category_id) REFERENCES item_category(category_id)
)
INSERT INTO items (name, price, category_id) VALUES ('ローマのテーブル', 150000, 1);
INSERT INTO items (name, price, category_id) VALUES ('げーたるーど2000 500ml', 370, 2);
INSERT INTO items (name, price, category_id) VALUES ('原始肉', 2500, 3);
INSERT INTO items (name, price, category_id) VALUES ('魅惑の椅子', 100000, 1);

--Q3
SELECT items.category_id, items.item_id, items.name, items.price, item_category.category_name
FROM items INNER JOIN item_category ON items.category_id = item_category.category_id; 

--Q4
SELECT items.category_id, items.item_id, items.name, items.price, item_category.category_name
FROM items INNER JOIN item_category ON items.category_id = item_category.category_id
ORDER BY price ASC;

--Q5
SELECT items.category_id, items.item_id, items.name, items.price, item_category.category_name
FROM items INNER JOIN item_category ON items.category_id = item_category.category_id
WHERE category_name = '家具';

--Q6
SELECT items.category_id, items.item_id, items.name, items.price, item_category.category_name
FROM items INNER JOIN item_category ON items.category_id = item_category.category_id
WHERE price BETWEEN 1000 AND 50000;

--Q7
SELECT COUNT(item_id) AS data_count FROM items;

--Q8
SELECT items.category_id, items.item_id, items.name, items.price, item_category.category_name
FROM items INNER JOIN item_category ON items.category_id = item_category.category_id
WHERE items.price >= ALL (SELECT items.price FROM items);

--Q9
SELECT items.category_id, items.item_id, items.name, items.price, item_category.category_name
FROM items INNER JOIN item_category ON items.category_id = item_category.category_id
WHERE items.name LIKE '%の%';