SELECT文

ORDER BY句

GROUP BY句

AS句

LEFT (OUTER) JOIN

CREATE TABLE products (
  product_id INT PRIMARY KEY,
  name VARCHAR(255),
  price DECIMAL(10, 2),
  category VARCHAR(255),
  stock INT
);

CREATE TABLE order_details (
  order_id INT,
  product_id INT,
  quantity INT,
  unit_price DECIMAL(10, 2),
  FOREIGN KEY (order_id) REFERENCES orders(order_id),
  FOREIGN KEY (product_id) REFERENCES products(product_id)
);

INSERT INTO
  products (product_id, name, price, category, stock)
VALUES
  (1, 'スキーセット', 1000.00, '電化製品', 50),
  (2, 'オフィスチェア', 2000.00, '家具', 30),
  (3, 'ワイヤレスイヤホン', 1500.00, '電化製品', 20),
  (4, 'ランプ', 1800.00, 'インテリア', 100),
  (5, 'コーヒーテーブル', 2200.00, '家具', 15),
  (6, '暖房器具', 1200.00, '電化製品', 5),
  (7, '折り畳み式デスク', 2500.00, 'インテリア', 72),
  (8, '花瓶', 1700.00, '家具', 50);

INSERT INTO
  order_details (order_id, product_id, quantity, unit_price)
VALUES
  (1, 7, 3, 2055.68),
  (1, 6, 3, 2851.67),
  (2, 7, 4, 2142.06),
  (3, 8, 1, 1895.57),
  (3, 2, 4, 2260.63),
  (3, 1, 1, 2021.08),
  (3, 6, 3, 2902.36),
  (4, 6, 2, 1386.03),
  (5, 1, 1, 1954.72),
  (6, 1, 4, 2940.92),
  (7, 3, 1, 2285.61),
  (7, 6, 3, 1178.19),
  (8, 1, 3, 2779.75),
  (9, 4, 1, 1400.12),
  (9, 6, 5, 2144.2),
  (9, 4, 4, 1036.16),
  (10, 7, 1, 1635.4),
  (10, 8, 3, 2250.08),
  (11, 8, 5, 1096.84),
  (11, 2, 1, 1955.74),
  (11, 6, 2, 2779.54),
  (11, 4, 3, 1495.2),
  (12, 2, 2, 1215.76),
  (13, 6, 5, 1248.65),
  (13, 3, 3, 2131.34),
  (14, 4, 5, 1959.35),
  (14, 1, 2, 2167.44),
  (14, 2, 3, 1993.87),
  (14, 2, 5, 2339.67),
  (15, 6, 1, 2869.89),
  (15, 1, 3, 2306.48),
  (16, 2, 4, 1372.08),
  (16, 5, 4, 1841.16),
  (17, 4, 4, 1654.03),
  (17, 2, 2, 1914.16),
  (18, 8, 5, 2555.04),
  (18, 7, 5, 1308.6),
  (19, 1, 2, 1666.68),
  (19, 5, 3, 2079.11),
  (19, 2, 4, 1674.52),
  (20, 6, 5, 2848.61),
  (20, 7, 5, 2037.97),
  (20, 3, 1, 1906.95),
  (21, 2, 4, 2003.84),
  (22, 8, 2, 2213.75),
  (22, 3, 1, 2559.99),
  (22, 5, 2, 2455.2),
  (22, 4, 5, 2638.27),
  (23, 6, 2, 2140.55),
  (23, 6, 2, 1472.94),
  (24, 1, 1, 1867.58),
  (25, 7, 1, 1139.4),
  (25, 1, 1, 1059.78),
  (25, 1, 5, 2901.68),
  (26, 2, 2, 2861.59),
  (27, 7, 1, 2658.56),
  (27, 6, 3, 1265.51),
  (28, 8, 4, 1667.44),
  (28, 4, 4, 1477.07),
  (28, 1, 5, 1287.53),
  (29, 6, 1, 2946.33),
  (29, 3, 5, 2292.16),
  (29, 3, 2, 2836.95),
  (29, 4, 3, 2623.72),
  (30, 1, 1, 2402.29),
  (30, 3, 3, 2606.56),
  (30, 2, 4, 1681.5),
  (31, 3, 2, 1998.35),
  (31, 4, 5, 2853.46),
  (32, 1, 3, 2065.53),
  (32, 2, 3, 2610.23),
  (33, 6, 2, 2094.47),
  (33, 8, 5, 1353.43),
  (34, 6, 2, 1954.26),
  (35, 7, 1, 1754.61),
  (35, 8, 1, 1034.69),
  (35, 7, 3, 1745.66),
  (36, 5, 4, 2741.04),
  (36, 3, 3, 1003.17),
  (37, 6, 3, 1540.53),
  (37, 2, 2, 2693.31),
  (37, 2, 5, 1089.46),
  (38, 7, 1, 1026.17),
  (38, 7, 2, 2616.15),
  (38, 2, 1, 1267.21),
  (39, 1, 5, 2538.24),
  (39, 7, 4, 2604.55),
  (39, 3, 2, 1007.58),
  (40, 8, 1, 1325.92),
  (40, 7, 1, 2176.67),
  (40, 7, 1, 2603.89),
  (41, 3, 5, 1742.28),
  (41, 7, 1, 2767.15),
  (42, 7, 2, 1618.8),
  (43, 3, 2, 1484.89),
  (44, 8, 2, 1715.23),
  (45, 7, 1, 1956.34),
  (45, 5, 1, 1021.3),
  (45, 5, 3, 1031.14),
  (46, 6, 2, 2457.8),
  (47, 2, 1, 1854.87),
  (47, 5, 2, 2992.03),
  (47, 4, 4, 1578.58),
  (47, 1, 1, 2224.6),
  (48, 3, 1, 1675.21),
  (49, 3, 3, 1930.82),
  (50, 2, 4, 2213.35),
  (50, 2, 5, 1788.05),
  (50, 6, 5, 1631.92),
  (50, 7, 2, 1921.11),
  (51, 8, 4, 2677.06),
  (52, 7, 4, 1893.38),
  (53, 3, 3, 2698.11),
  (53, 8, 1, 2924.49),
  (53, 2, 5, 2915.54),
  (53, 4, 4, 1983.13),
  (54, 1, 3, 2109.9),
  (54, 3, 4, 1272.69),
  (55, 6, 2, 1586.32),
  (55, 5, 1, 2327.0),
  (56, 8, 3, 1662.72),
  (56, 7, 1, 2162.51),
  (56, 4, 2, 1971.11),
  (57, 8, 5, 2692.52),
  (57, 1, 2, 2928.16),
  (58, 5, 2, 1801.97),
  (58, 4, 2, 2030.0),
  (58, 2, 1, 2530.99),
  (59, 6, 3, 1028.55),
  (59, 8, 2, 1461.37),
  (60, 6, 2, 1787.03),
  (60, 8, 3, 2722.7),
  (60, 1, 3, 2077.94),
  (60, 8, 2, 2688.06),
  (61, 5, 5, 2611.66),
  (61, 3, 4, 1995.25),
  (61, 4, 4, 1415.44),
  (62, 7, 3, 1022.94),
  (62, 2, 5, 2053.81),
  (62, 7, 2, 2424.29),
  (63, 7, 1, 2763.62),
  (64, 3, 1, 2321.67),
  (65, 5, 4, 2403.21),
  (65, 6, 2, 1834.93),
  (65, 3, 5, 2487.28),
  (66, 8, 1, 2157.62),
  (67, 6, 4, 2941.44),
  (67, 5, 4, 1819.23),
  (68, 8, 4, 1473.09),
  (68, 3, 2, 2606.67),
  (68, 5, 1, 2546.68),
  (68, 4, 1, 1956.6),
  (69, 6, 5, 1475.88),
  (70, 7, 3, 2014.65),
  (70, 2, 2, 2965.9),
  (71, 1, 3, 1549.58),
  (71, 5, 5, 2902.37),
  (72, 2, 2, 1452.32),
  (73, 8, 5, 1490.23),
  (73, 8, 5, 2161.99),
  (73, 5, 2, 1744.84),
  (74, 7, 3, 1518.73),
  (74, 5, 5, 2260.73),
  (74, 1, 4, 2840.62),
  (74, 5, 5, 1501.77),
  (75, 6, 4, 2239.99),
  (75, 2, 2, 2129.66),
  (75, 6, 2, 2534.72),
  (76, 3, 5, 2859.61),
  (76, 7, 5, 2807.3),
  (76, 6, 3, 2762.55),
  (76, 8, 3, 1781.14),
  (77, 7, 4, 1796.66),
  (77, 6, 3, 2473.02),
  (78, 3, 2, 1156.31),
  (78, 2, 2, 1803.08),
  (78, 3, 1, 2177.73);

-- 問題1. 各商品の商品名と在庫数、売上総量を売上総量の多い順に取得してください
-- この下に解答してください
SELECT
  products.name,
  products.stock,
  SUM(order_details.quantity) AS total_ordered
FROM
  products
  LEFT JOIN order_details ON products.product_id = order_details.product_id
GROUP BY
  products.name,
  products.stock
ORDER BY
  total_ordered DESC;

-- 問題1. 各商品の商品名と在庫数、売上総量を売上総量の多い順に取得してください
-- この下に解答してください

商品の在庫状況と売れ行きの関連を探ろう

売上の推移を確認しよう

LIMIT句

商品の売れ行きを分析しよう

顧客のクレームデータとレビューを分析しよう

年月別で売上の季節変動を分析しよう

このレッスンでは、商品、注文、顧客、クレーム、レビュー情報が格納されたデータベースを用いて、ショッピングサイトの売上に影響を与える要因をSQLで分析し、改善策を見つける方法を学びます。
実際のショッピングサイトの運営者として、データを詳細に調査し、売上向上のための戦略を立案しましょう。
