--テーブル作成
CREATE TABLE item_category (
    category_id INT PRIMARY KEY AUTO_INCREMENT COMMENT 'カテゴリID',
    category_name VARCHAR(256) NOT NULL COMMENT 'カテゴリ名'
);

--データ登録
    INSERT INTO item_category VALUES(NULL, '家具');
    INSERT INTO item_category VALUES(NULL, '飲料');
    INSERT INTO item_category VALUES(NULL, '食品');
    
    
--テーブル作成2
CREATE TABLE items (
    item_id INT PRIMARY KEY AUTO_INCREMENT COMMENT 'アイテムID',
    name VARCHAR(256) NOT NULL COMMENT 'アイテム名',
    price INT NOT NULL COMMENT '価格',
    category_id INT COMMENT 'カテゴリID',
    FOREIGN KEY (category_id)
        REFERENCES item_category(category_id)
);

--登録
    INSERT INTO items VALUES(NULL, 'ローマのテーブル', 150000, 1);
    INSERT INTO items VALUES(NULL, 'げーたるーど2000 500ml', 370, 2);
    INSERT INTO items VALUES(NULL, '原点飴', 2500, 3);
    INSERT INTO items VALUES(NULL, '魅惑の椅子', 100000, 1);
    
    
--問3,4,5
    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 item_category.category_name = '家具'
    ORDER BY items.price ASC;
    
---問6
    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 BETWEEN 1000 AND 50000;
    
 --問7   
    SELECT COUNT (*) AS data_count FROM items;
    
--問8
    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 = (SELECT MAX(price) FROM items);
    
--問9
    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 '%の%';