SELECT文

ORDER BY句

LIMIT句

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT,
  order_date DATE,
  status VARCHAR(50),
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

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)
);

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

INSERT INTO
  orders (order_id, customer_id, order_date, status)
VALUES
  (1, 6, '2023-01-01', 'Completed'),
  (2, 6, '2023-01-05', 'Completed'),
  (3, 1, '2023-01-10', 'Completed'),
  (4, 5, '2023-01-14', 'Completed'),
  (5, 6, '2023-01-18', 'Completed'),
  (6, 6, '2023-01-21', 'Completed'),
  (7, 5, '2023-01-25', 'Completed'),
  (8, 6, '2023-01-30', 'Completed'),
  (9, 1, '2023-02-02', 'Completed'),
  (10, 1, '2023-02-07', 'Completed'),
  (11, 3, '2023-02-11', 'Completed'),
  (12, 2, '2023-02-14', 'Completed'),
  (13, 3, '2023-02-18', 'Completed'),
  (14, 6, '2023-02-23', 'Completed'),
  (15, 2, '2023-02-28', 'Completed'),
  (16, 5, '2023-03-04', 'Completed'),
  (17, 4, '2023-03-07', 'Completed'),
  (18, 6, '2023-03-11', 'Completed'),
  (19, 6, '2023-03-15', 'Completed'),
  (20, 1, '2023-03-19', 'Completed'),
  (21, 2, '2023-03-24', 'Completed'),
  (22, 5, '2023-03-28', 'Completed'),
  (23, 2, '2023-03-31', 'Completed'),
  (24, 1, '2023-04-03', 'Completed'),
  (25, 3, '2023-04-07', 'Completed'),
  (26, 5, '2023-04-11', 'Completed'),
  (27, 4, '2023-04-15', 'Completed'),
  (28, 3, '2023-04-20', 'Completed'),
  (29, 6, '2023-04-25', 'Completed'),
  (30, 1, '2023-04-29', 'Completed'),
  (31, 1, '2023-05-03', 'Completed'),
  (32, 2, '2023-05-08', 'Completed'),
  (33, 6, '2023-05-12', 'Completed'),
  (34, 1, '2023-05-16', 'Completed'),
  (35, 3, '2023-05-20', 'Completed'),
  (36, 2, '2023-05-24', 'Completed'),
  (37, 4, '2023-05-28', 'Completed'),
  (38, 1, '2023-06-02', 'Completed'),
  (39, 4, '2023-06-09', 'Completed'),
  (40, 4, '2023-06-18', 'Completed'),
  (41, 5, '2023-06-27', 'Completed'),
  (42, 5, '2023-07-06', 'Completed'),
  (43, 6, '2023-07-12', 'Completed'),
  (44, 6, '2023-07-20', 'Completed'),
  (45, 1, '2023-07-28', 'Completed'),
  (46, 1, '2023-08-06', 'Completed'),
  (47, 5, '2023-08-15', 'Completed'),
  (48, 5, '2023-08-21', 'Completed'),
  (49, 3, '2023-08-30', 'Completed'),
  (50, 2, '2023-09-08', 'Completed'),
  (51, 2, '2023-09-12', 'Completed'),
  (52, 6, '2023-09-16', 'Completed'),
  (53, 6, '2023-09-21', 'Completed'),
  (54, 5, '2023-09-24', 'Completed'),
  (55, 5, '2023-09-29', 'Completed'),
  (56, 6, '2023-10-02', 'Completed'),
  (57, 2, '2023-10-05', 'Completed'),
  (58, 6, '2023-10-09', 'Completed'),
  (59, 5, '2023-10-12', 'Completed'),
  (60, 1, '2023-10-16', 'Completed'),
  (61, 1, '2023-10-20', 'Completed'),
  (62, 4, '2023-10-23', 'Completed'),
  (63, 1, '2023-10-28', 'Completed'),
  (64, 5, '2023-11-01', 'Completed'),
  (65, 6, '2023-11-05', 'Completed'),
  (66, 2, '2023-11-10', 'Completed'),
  (67, 3, '2023-11-14', 'Completed'),
  (68, 3, '2023-11-19', 'Completed'),
  (69, 6, '2023-11-24', 'Completed'),
  (70, 3, '2023-11-29', 'Completed'),
  (71, 3, '2023-12-03', 'Completed'),
  (72, 1, '2023-12-07', 'Completed'),
  (73, 4, '2023-12-11', 'Completed'),
  (74, 4, '2023-12-16', 'Completed'),
  (75, 6, '2023-12-19', 'Completed'),
  (76, 4, '2023-12-22', 'Completed'),
  (77, 4, '2023-12-26', 'Completed'),
  (78, 6, '2023-12-31', 'Completed');

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);

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);

CREATE TABLE winter_sales_summary (
  product_id INT PRIMARY KEY,
  name VARCHAR(255),
  winter_sales DECIMAL(10, 2)
);

INSERT INTO
  winter_sales_summary (product_id, name, winter_sales)
VALUES
  (1, 'スキーセット', 51344.27),
  (2, 'オフィスチェア', 41879.86),
  (3, 'ワイヤレスイヤホン', 27468.03),
  (4, 'ランプ', 19827.11),
  (5, 'コーヒーテーブル', 36814.03),
  (6, '暖房器具', 78698.05),
  (7, '折り畳み式デスク', 42150.01),
  (8, '花瓶', 37734.53);

-- 問題1. 冬季（12月〜2月）の期間に売上が増加する商品を上位5件表示してください
-- この下に解答してください
SELECT
  name,
  winter_sales
FROM
  winter_sales_summary
ORDER BY
  winter_sales DESC
LIMIT
  5;

-- 問題1. 冬季（12月〜2月）の期間に売上が増加する商品を上位5件表示してください
-- この下に解答してください

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

売上の推移を確認しよう

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

GROUP BY句

AS句

LEFT (OUTER) JOIN

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

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

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