総合演習：オンラインショップのデータ分析

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE,
    city TEXT,
    age INTEGER
);

CREATE TABLE items (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    category TEXT,
    price INTEGER NOT NULL,
    stock INTEGER DEFAULT 0
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER,
    item_id INTEGER,
    quantity INTEGER NOT NULL,
    order_date TEXT NOT NULL
);

INSERT INTO customers (id, name, email, city, age) VALUES
    (1, '山田太郎', 'yamada@example.com', '東京', 28),
    (2, '佐藤花子', 'sato@example.com', '大阪', 34),
    (3, '鈴木一郎', 'suzuki@example.com', '東京', 22),
    (4, '田中美咲', 'tanaka@example.com', '名古屋', 29),
    (5, '高橋健太', 'takahashi@example.com', '福岡', 45),
    (6, '伊藤直樹', 'ito@example.com', '大阪', 31),
    (7, '渡辺真理', 'watanabe@example.com', '東京', 26),
    (8, '小林修平', 'kobayashi@example.com', '名古屋', 38);

INSERT INTO items (id, name, category, price, stock) VALUES
    (1, 'SQL入門書', '書籍', 2800, 15),
    (2, 'データベース実践ガイド', '書籍', 3500, 8),
    (3, 'ワイヤレスマウス', '電子機器', 4500, 20),
    (4, 'USBハブ', '電子機器', 2200, 30),
    (5, 'ノートPC用スタンド', '電子機器', 6800, 5),
    (6, 'コーヒー豆 200g', '食品', 1200, 50),
    (7, '紅茶セット', '食品', 1800, 25),
    (8, 'プログラマーTシャツ', '衣類', 3200, 12),
    (9, 'パーカー', '衣類', 5500, 7),
    (10, 'エナジーバー 10本入', '食品', 980, 40);

INSERT INTO orders (id, customer_id, item_id, quantity, order_date) VALUES
    (1, 1, 1, 1, '2026-01-10'),
    (2, 1, 6, 2, '2026-01-10'),
    (3, 2, 3, 1, '2026-01-12'),
    (4, 3, 1, 1, '2026-01-15'),
    (5, 3, 10, 3, '2026-01-15'),
    (6, 4, 8, 2, '2026-01-18'),
    (7, 5, 5, 1, '2026-01-20'),
    (8, 2, 7, 1, '2026-01-22'),
    (9, 6, 2, 1, '2026-01-25'),
    (10, 7, 4, 2, '2026-01-28'),
    (11, 8, 9, 1, '2026-01-30'),
    (12, 1, 3, 1, '2026-02-01');

SELECT * FROM customers;

SELECT name AS '商品名', price AS '価格(円)' FROM items;

SELECT DISTINCT city FROM customers;

SELECT * FROM customers WHERE city = '東京';

SELECT * FROM items WHERE price BETWEEN 1000 AND 5000;

SELECT * FROM items WHERE category IN ('書籍', '食品');

SELECT * FROM customers WHERE name LIKE '%田%';

INSERT INTO customers (id, name, email, city, age)
VALUES (9, '中村さくら', 'nakamura@example.com', '札幌', 27);

SELECT * FROM customers WHERE id = 9;

UPDATE items SET price = 3000 WHERE id = 1;

SELECT * FROM items WHERE id = 1;

UPDATE items SET stock = 0 WHERE id = 10;

SELECT * FROM items WHERE stock = 0;

DELETE FROM items WHERE stock = 0;

SELECT * FROM items;

SELECT name, price FROM items ORDER BY price DESC;

SELECT name, price FROM items ORDER BY price ASC LIMIT 3;

SELECT
    COUNT(*) AS '商品総数',
    AVG(price) AS '平均価格',
    MAX(price) AS '最高価格',
    MIN(price) AS '最低価格'
FROM items;

SELECT
    category AS 'カテゴリ',
    COUNT(*) AS '商品数',
    AVG(price) AS '平均価格'
FROM items
GROUP BY category;

SELECT AVG(age) AS '平均年齢' FROM customers;

DROP TABLE orders;
DROP TABLE items;
DROP TABLE customers;