yilun8711/mcut_text2sql
02
1[
2 {
3 "sql_prompt": "請列出所有客戶的名稱和電子郵件。",
4 "sql_context": "CREATE TABLE customer (\n id VARCHAR(50) NOT NULL, \n name TEXT, \n email TEXT, \n PRIMARY KEY (id)\n) COLLATE utf8mb4_0900_ai_ci DEFAULT CHARSET=utf8mb4 ENGINE=InnoDB;\n\n/* 3 rows from customer table:\nid name email\nc1 annie annie123@gmail.com\nc2 jack jacklee@gmail.com\nc3 bob bob456@gmail.com */\n\nCREATE TABLE orders (\n id INTEGER, \n customer_id VARCHAR(50), \n product TEXT, \n amount INTEGER, \n order_date TEXT, \n CONSTRAINT fk_customer_order FOREIGN KEY(customer_id) REFERENCES customer (id) ON DELETE CASCADE\n) COLLATE utf8mb4_0900_ai_ci DEFAULT CHARSET=utf8mb4 ENGINE=InnoDB;\n\n/* 3 rows from orders table:\nid customer_id product amount order_date\n1 c1 apple 300 2025/2/3\n2 c1 pen 50 2025/2/4\n3 c2 apple 200 2025/2/5 */",
5 "sql": "SELECT name, email FROM customer;"
6 },
7 {
8 "sql_prompt": "請找出所有已訂購過商品的客戶名字。",
9 "sql_context": "CREATE TABLE customer (\n id VARCHAR(50) NOT NULL, \n name TEXT, \n email TEXT, \n PRIMARY KEY (id)\n) COLLATE utf8mb4_0900_ai_ci DEFAULT CHARSET=utf8mb4 ENGINE=InnoDB;\n\n/* 3 rows from customer table:\nid name email\nc1 annie annie123@gmail.com\nc2 jack jacklee@gmail.com\nc3 bob bob456@gmail.com */\n\nCREATE TABLE orders (\n id INTEGER, \n customer_id VARCHAR(50), \n product TEXT, \n amount INTEGER, \n order_date TEXT, \n CONSTRAINT fk_customer_order FOREIGN KEY(customer_id) REFERENCES customer (id) ON DELETE CASCADE\n) COLLATE utf8mb4_0900_ai_ci DEFAULT CHARSET=utf8mb4 ENGINE=InnoDB;\n\n/* 3 rows from orders table:\nid customer_id product amount order_date\n1 c1 apple 300 2025/2/3\n2 c1 pen 50 2025/2/4\n3 c2 apple 200 2025/2/5 */",
10 "sql": "SELECT DISTINCT c.name FROM customer c LEFT JOIN orders o ON c.id = o.customer_id WHERE o.id IS NOT NULL;"
11 },
12 {
13 "sql_prompt": "請列出所有客戶，包括沒有購買過商品的客戶。",
14 "sql_context": "CREATE TABLE customer (\n id VARCHAR(50) NOT NULL, \n name TEXT, \n email TEXT, \n PRIMARY KEY (id)\n) COLLATE utf8mb4_0900_ai_ci DEFAULT CHARSET=utf8mb4 ENGINE=InnoDB;\n\n/* 3 rows from customer table:\nid name email\nc1 annie annie123@gmail.com\nc2 jack jacklee@gmail.com\nc3 bob bob456@gmail.com */\n\nCREATE TABLE orders (\n id INTEGER, \n customer_id VARCHAR(50), \n product TEXT, \n amount INTEGER, \n order_date TEXT, \n CONSTRAINT fk_customer_order FOREIGN KEY(customer_id) REFERENCES customer (id) ON DELETE CASCADE\n) COLLATE utf8mb4_0900_ai_ci DEFAULT CHARSET=utf8mb4 ENGINE=InnoDB;\n\n/* 3 rows from orders table:\nid customer_id product amount order_date\n1 c1 apple 300 2025/2/3\n2 c1 pen 50 2025/2/4\n3 c2 apple 200 2025/2/5 */",
15 "sql": "SELECT c.name, c.email FROM customer c LEFT JOIN orders o ON c.id = o.customer_id;"
16 },
17 {
18 "sql_prompt": "請列出每位客戶購買過哪些商品及其總金額。",
19 "sql_context": "CREATE TABLE customer (\n id VARCHAR(50) NOT NULL, \n name TEXT, \n email TEXT, \n PRIMARY KEY (id)\n) COLLATE utf8mb4_0900_ai_ci DEFAULT CHARSET=utf8mb4 ENGINE=InnoDB;\n\n/* 3 rows from customer table:\nid name email\nc1 annie annie123@gmail.com\nc2 jack jacklee@gmail.com\nc3 bob bob456@gmail.com */\n\nCREATE TABLE orders (\n id INTEGER, \n customer_id VARCHAR(50), \n product TEXT, \n amount INTEGER, \n order_date TEXT, \n CONSTRAINT fk_customer_order FOREIGN KEY(customer_id) REFERENCES customer (id) ON DELETE CASCADE\n) COLLATE utf8mb4_0900_ai_ci DEFAULT CHARSET=utf8mb4 ENGINE=InnoDB;\n\n/* 3 rows from orders table:\nid customer_id product amount order_date\n1 c1 apple 300 2025/2/3\n2 c1 pen 50 2025/2/4\n3 c2 apple 200 2025/2/5 */",
20 "sql": "SELECT c.name, o.product, SUM(o.amount) AS total_amount FROM customer c LEFT JOIN orders o ON c.id = o.customer_id GROUP BY c.name, o.product;"
21 },
22 {
23 "sql_prompt": "請列出所有訂單及其相對應客戶的名稱。",
24 "sql_context": "CREATE TABLE customer (\n id VARCHAR(50) NOT NULL, \n name TEXT, \n email TEXT, \n PRIMARY KEY (id)\n) COLLATE utf8mb4_0900_ai_ci DEFAULT CHARSET=utf8mb4 ENGINE=InnoDB;\n\n/* 3 rows from customer table:\nid name email\nc1 annie annie123@gmail.com\nc2 jack jacklee@gmail.com\nc3 bob bob456@gmail.com */\n\nCREATE TABLE orders (\n id INTEGER, \n customer_id VARCHAR(50), \n product TEXT, \n amount INTEGER, \n order_date TEXT, \n CONSTRAINT fk_customer_order FOREIGN KEY(customer_id) REFERENCES customer (id) ON DELETE CASCADE\n) COLLATE utf8mb4_0900_ai_ci DEFAULT CHARSET=utf8mb4 ENGINE=InnoDB;\n\n/* 3 rows from orders table:\nid customer_id product amount order_date\n1 c1 apple 300 2025/2/3\n2 c1 pen 50 2025/2/4\n3 c2 apple 200 2025/2/5 */",
25 "sql": "SELECT o.id, c.name FROM orders o LEFT JOIN customer c ON o.customer_id = c.id;"
26 },
27 {
28 "sql_prompt": "請列出所有沒有訂購過任何商品的客戶。",
29 "sql_context": "CREATE TABLE customer (\n id VARCHAR(50) NOT NULL, \n name TEXT, \n email TEXT, \n PRIMARY KEY (id)\n) COLLATE utf8mb4_0900_ai_ci DEFAULT CHARSET=utf8mb4 ENGINE=InnoDB;\n\n/* 3 rows from customer table:\nid name email\nc1 annie annie123@gmail.com\nc2 jack jacklee@gmail.com\nc3 bob bob456@gmail.com */\n\nCREATE TABLE orders (\n id INTEGER, \n customer_id VARCHAR(50), \n product TEXT, \n amount INTEGER, \n order_date TEXT, \n CONSTRAINT fk_customer_order FOREIGN KEY(customer_id) REFERENCES customer (id) ON DELETE CASCADE\n) COLLATE utf8mb4_0900_ai_ci DEFAULT CHARSET=utf8mb4 ENGINE=InnoDB;\n\n/* 3 rows from orders table:\nid customer_id product amount order_date\n1 c1 apple 300 2025/2/3\n2 c1 pen 50 2025/2/4\n3 c2 apple 200 2025/2/5 */",
30 "sql": "SELECT c.name, c.email FROM customer c LEFT JOIN orders o ON c.id = o.customer_id WHERE o.id IS NULL;"
31 },
32 {
33 "sql_prompt": "請列出所有已購買過 'apple' 的客戶名字。",
34 "sql_context": "CREATE TABLE customer (\n id VARCHAR(50) NOT NULL, \n name TEXT, \n email TEXT, \n PRIMARY KEY (id)\n) COLLATE utf8mb4_0900_ai_ci DEFAULT CHARSET=utf8mb4 ENGINE=InnoDB;\n\n/* 3 rows from customer table:\nid name email\nc1 annie annie123@gmail.com\nc2 jack jacklee@gmail.com\nc3 bob bob456@gmail.com */\n\nCREATE TABLE orders (\n id INTEGER, \n customer_id VARCHAR(50), \n product TEXT, \n amount INTEGER, \n order_date TEXT, \n CONSTRAINT fk_customer_order FOREIGN KEY(customer_id) REFERENCES customer (id) ON DELETE CASCADE\n) COLLATE utf8mb4_0900_ai_ci DEFAULT CHARSET=utf8mb4 ENGINE=InnoDB;\n\n/* 3 rows from orders table:\nid customer_id product amount order_date\n1 c1 apple 300 2025/2/3\n2 c1 pen 50 2025/2/4\n3 c2 apple 200 2025/2/5 */",
35 "sql": "SELECT DISTINCT c.name FROM customer c LEFT JOIN orders o ON c.id = o.customer_id WHERE o.product = 'apple';"
36 },
37 {
38 "sql_prompt": "請列出所有客戶的名稱及其訂購總金額。",
39 "sql_context": "CREATE TABLE customer (\n id VARCHAR(50) NOT NULL, \n name TEXT, \n email TEXT, \n PRIMARY KEY (id)\n) COLLATE utf8mb4_0900_ai_ci DEFAULT CHARSET=utf8mb4 ENGINE=InnoDB;\n\n/* 3 rows from customer table:\nid name email\nc1 annie annie123@gmail.com\nc2 jack jacklee@gmail.com\nc3 bob bob456@gmail.com */\n\nCREATE TABLE orders (\n id INTEGER, \n customer_id VARCHAR(50), \n product TEXT, \n amount INTEGER, \n order_date TEXT, \n CONSTRAINT fk_customer_order FOREIGN KEY(customer_id) REFERENCES customer (id) ON DELETE CASCADE\n) COLLATE utf8mb4_0900_ai_ci DEFAULT CHARSET=utf8mb4 ENGINE=InnoDB;\n\n/* 3 rows from orders table:\nid customer_id product amount order_date\n1 c1 apple 300 2025/2/3\n2 c1 pen 50 2025/2/4\n3 c2 apple 200 2025/2/5 */",
40 "sql": "SELECT c.name, COALESCE(SUM(o.amount), 0) AS total_spent FROM customer c LEFT JOIN orders o ON c.id = o.customer_id GROUP BY c.name;"
41 },
42 {
43 "sql_prompt": "請找出在 '2025/2/4' 訂購的商品及其客戶名字。",
44 "sql_context": "CREATE TABLE customer (\n id VARCHAR(50) NOT NULL, \n name TEXT, \n email TEXT, \n PRIMARY KEY (id)\n) COLLATE utf8mb4_0900_ai_ci DEFAULT CHARSET=utf8mb4 ENGINE=InnoDB;\n\n/* 3 rows from customer table:\nid name email\nc1 annie annie123@gmail.com\nc2 jack jacklee@gmail.com\nc3 bob bob456@gmail.com */\n\nCREATE TABLE orders (\n id INTEGER, \n customer_id VARCHAR(50), \n product TEXT, \n amount INTEGER, \n order_date TEXT, \n CONSTRAINT fk_customer_order FOREIGN KEY(customer_id) REFERENCES customer (id) ON DELETE CASCADE\n) COLLATE utf8mb4_0900_ai_ci DEFAULT CHARSET=utf8mb4 ENGINE=InnoDB;\n\n/* 3 rows from orders table:\nid customer_id product amount order_date\n1 c1 apple 300 2025/2/3\n2 c1 pen 50 2025/2/4\n3 c2 apple 200 2025/2/5 */",
45 "sql": "SELECT c.name, o.product FROM orders o LEFT JOIN customer c ON o.customer_id = c.id WHERE o.order_date = '2025/2/4';"
46 },
47 {
48 "sql_prompt": "請列出購買過的所有商品及其總數量。",
49 "sql_context": "CREATE TABLE customer (\n id VARCHAR(50) NOT NULL, \n name TEXT, \n email TEXT, \n PRIMARY KEY (id)\n) COLLATE utf8mb4_0900_ai_ci DEFAULT CHARSET=utf8mb4 ENGINE=InnoDB;\n\n/* 3 rows from customer table:\nid name email\nc1 annie annie123@gmail.com\nc2 jack jacklee@gmail.com\nc3 bob bob456@gmail.com */\n\nCREATE TABLE orders (\n id INTEGER, \n customer_id VARCHAR(50), \n product TEXT, \n amount INTEGER, \n order_date TEXT, \n CONSTRAINT fk_customer_order FOREIGN KEY(customer_id) REFERENCES customer (id) ON DELETE CASCADE\n) COLLATE utf8mb4_0900_ai_ci DEFAULT CHARSET=utf8mb4 ENGINE=InnoDB;\n\n/* 3 rows from orders table:\nid customer_id product amount order_date\n1 c1 apple 300 2025/2/3\n2 c1 pen 50 2025/2/4\n3 c2 apple 200 2025/2/5 */",
50 "sql": "SELECT product, SUM(amount) AS total_amount FROM orders GROUP BY product;"
51 },
52 {
53 "sql_prompt": "請列出每位客戶在特定日期（如 '2025/2/3'）的總購買金額。",
54 "sql_context": "CREATE TABLE customer (\n id VARCHAR(50) NOT NULL, \n name TEXT, \n email TEXT, \n PRIMARY KEY (id)\n) COLLATE utf8mb4_0900_ai_ci DEFAULT CHARSET=utf8mb4 ENGINE=InnoDB;\n\n/* 3 rows from customer table:\nid name email\nc1 annie annie123@gmail.com\nc2 jack jacklee@gmail.com\nc3 bob bob456@gmail.com */\n\nCREATE TABLE orders (\n id INTEGER, \n customer_id VARCHAR(50), \n product TEXT, \n amount INTEGER, \n order_date TEXT, \n CONSTRAINT fk_customer_order FOREIGN KEY(customer_id) REFERENCES customer (id) ON DELETE CASCADE\n) COLLATE utf8mb4_0900_ai_ci DEFAULT CHARSET=utf8mb4 ENGINE=InnoDB;\n\n/* 3 rows from orders table:\nid customer_id product amount order_date\n1 c1 apple 300 2025/2/3\n2 c1 pen 50 2025/2/4\n3 c2 apple 200 2025/2/5 */",
55 "sql": "SELECT c.name, SUM(o.amount) AS total_spent FROM customer c LEFT JOIN orders o ON c.id = o.customer_id WHERE o.order_date = '2025/2/3' GROUP BY c.name;"
56 },
57 {
58 "sql_prompt": "請列出所有客戶及其購買的商品數量，如果沒有購買則顯示為 0。",
59 "sql_context": "CREATE TABLE customer (\n id VARCHAR(50) NOT NULL, \n name TEXT, \n email TEXT, \n PRIMARY KEY (id)\n) COLLATE utf8mb4_0900_ai_ci DEFAULT CHARSET=utf8mb4 ENGINE=InnoDB;\n\n/* 3 rows from customer table:\nid name email\nc1 annie annie123@gmail.com\nc2 jack jacklee@gmail.com\nc3 bob bob456@gmail.com */\n\nCREATE TABLE orders (\n id INTEGER, \n customer_id VARCHAR(50), \n product TEXT, \n amount INTEGER, \n order_date TEXT, \n CONSTRAINT fk_customer_order FOREIGN KEY(customer_id) REFERENCES customer (id) ON DELETE CASCADE\n) COLLATE utf8mb4_0900_ai_ci DEFAULT CHARSET=utf8mb4 ENGINE=InnoDB;\n\n/* 3 rows from orders table:\nid customer_id product amount order_date\n1 c1 apple 300 2025/2/3\n2 c1 pen 50 2025/2/4\n3 c2 apple 200 2025/2/5 */",
60 "sql": "SELECT c.name, COUNT(o.product) AS product_count FROM customer c LEFT JOIN orders o ON c.id = o.customer_id GROUP BY c.name;"
61 },
62 {
63 "sql_prompt": "請列出所有商品及其購買次數，只包含至少被購買一次的商品。",
64 "sql_context": "CREATE TABLE customer (\n id VARCHAR(50) NOT NULL, \n name TEXT, \n email TEXT, \n PRIMARY KEY (id)\n) COLLATE utf8mb4_0900_ai_ci DEFAULT CHARSET=utf8mb4 ENGINE=InnoDB;\n\n/* 3 rows from customer table:\nid name email\nc1 annie annie123@gmail.com\nc2 jack jacklee@gmail.com\nc3 bob bob456@gmail.com */\n\nCREATE TABLE orders (\n id INTEGER, \n customer_id VARCHAR(50), \n product TEXT, \n amount INTEGER, \n order_date TEXT, \n CONSTRAINT fk_customer_order FOREIGN KEY(customer_id) REFERENCES customer (id) ON DELETE CASCADE\n) COLLATE utf8mb4_0900_ai_ci DEFAULT CHARSET=utf8mb4 ENGINE=InnoDB;\n\n/* 3 rows from orders table:\nid customer_id product amount order_date\n1 c1 apple 300 2025/2/3\n2 c1 pen 50 2025/2/4\n3 c2 apple 200 2025/2/5 */",
65 "sql": "SELECT product, COUNT(*) AS purchase_count FROM orders GROUP BY product HAVING COUNT(*) > 0;"
66 },
67 {
68 "sql_prompt": "請列出所有客戶及其訂購金額超過 200 的記錄。",
69 "sql_context": "CREATE TABLE customer (\n id VARCHAR(50) NOT NULL, \n name TEXT, \n email TEXT, \n PRIMARY KEY (id)\n) COLLATE utf8mb4_0900_ai_ci DEFAULT CHARSET=utf8mb4 ENGINE=InnoDB;\n\n/* 3 rows from customer table:\nid name email\nc1 annie annie123@gmail.com\nc2 jack jacklee@gmail.com\nc3 bob bob456@gmail.com */\n\nCREATE TABLE orders (\n id INTEGER, \n customer_id VARCHAR(50), \n product TEXT, \n amount INTEGER, \n order_date TEXT, \n CONSTRAINT fk_customer_order FOREIGN KEY(customer_id) REFERENCES customer (id) ON DELETE CASCADE\n) COLLATE utf8mb4_0900_ai_ci DEFAULT CHARSET=utf8mb4 ENGINE=InnoDB;\n\n/* 3 rows from orders table:\nid customer_id product amount order_date\n1 c1 apple 300 2025/2/3\n2 c1 pen 50 2025/2/4\n3 c2 apple 200 2025/2/5 */",
70 "sql": "SELECT c.name, o.product, o.amount FROM customer c LEFT JOIN orders o ON c.id = o.customer_id WHERE o.amount > 200;"
71 },
72 {
73 "sql_prompt": "請找出所有購買過 'pen' 的客戶名字。",
74 "sql_context": "CREATE TABLE customer (\n id VARCHAR(50) NOT NULL, \n name TEXT, \n email TEXT, \n PRIMARY KEY (id)\n) COLLATE utf8mb4_0900_ai_ci DEFAULT CHARSET=utf8mb4 ENGINE=InnoDB;\n\n/* 3 rows from customer table:\nid name email\nc1 annie annie123@gmail.com\nc2 jack jacklee@gmail.com\nc3 bob bob456@gmail.com */\n\nCREATE TABLE orders (\n id INTEGER, \n customer_id VARCHAR(50), \n product TEXT, \n amount INTEGER, \n order_date TEXT, \n CONSTRAINT fk_customer_order FOREIGN KEY(customer_id) REFERENCES customer (id) ON DELETE CASCADE\n) COLLATE utf8mb4_0900_ai_ci DEFAULT CHARSET=utf8mb4 ENGINE=InnoDB;\n\n/* 3 rows from orders table:\nid customer_id product amount order_date\n1 c1 apple 300 2025/2/3\n2 c1 pen 50 2025/2/4\n3 c2 apple 200 2025/2/5 */",
75 "sql": "SELECT DISTINCT c.name FROM customer c LEFT JOIN orders o ON c.id = o.customer_id WHERE o.product = 'pen';"
76 },
77 {
78 "sql_prompt": "請列出所有訂單及其客戶的電子郵件。",
79 "sql_context": "CREATE TABLE customer (\n id VARCHAR(50) NOT NULL, \n name TEXT, \n email TEXT, \n PRIMARY KEY (id)\n) COLLATE utf8mb4_0900_ai_ci DEFAULT CHARSET=utf8mb4 ENGINE=InnoDB;\n\n/* 3 rows from customer table:\nid name email\nc1 annie annie123@gmail.com\nc2 jack jacklee@gmail.com\nc3 bob bob456@gmail.com */\n\nCREATE TABLE orders (\n id INTEGER, \n customer_id VARCHAR(50), \n product TEXT, \n amount INTEGER, \n order_date TEXT, \n CONSTRAINT fk_customer_order FOREIGN KEY(customer_id) REFERENCES customer (id) ON DELETE CASCADE\n) COLLATE utf8mb4_0900_ai_ci DEFAULT CHARSET=utf8mb4 ENGINE=InnoDB;\n\n/* 3 rows from orders table:\nid customer_id product amount order_date\n1 c1 apple 300 2025/2/3\n2 c1 pen 50 2025/2/4\n3 c2 apple 200 2025/2/5 */",
80 "sql": "SELECT o.id, c.email FROM orders o LEFT JOIN customer c ON o.customer_id = c.id;"
81 },
82 {
83 "sql_prompt": "請列出所有訂購金額超過 250 的客戶名字及其訂單詳情。",
84 "sql_context": "CREATE TABLE customer (\n id VARCHAR(50) NOT NULL, \n name TEXT, \n email TEXT, \n PRIMARY KEY (id)\n) COLLATE utf8mb4_0900_ai_ci DEFAULT CHARSET=utf8mb4 ENGINE=InnoDB;\n\n/* 3 rows from customer table:\nid name email\nc1 annie annie123@gmail.com\nc2 jack jacklee@gmail.com\nc3 bob bob456@gmail.com */\n\nCREATE TABLE orders (\n id INTEGER, \n customer_id VARCHAR(50), \n product TEXT, \n amount INTEGER, \n order_date TEXT, \n CONSTRAINT fk_customer_order FOREIGN KEY(customer_id) REFERENCES customer (id) ON DELETE CASCADE\n) COLLATE utf8mb4_0900_ai_ci DEFAULT CHARSET=utf8mb4 ENGINE=InnoDB;\n\n/* 3 rows from orders table:\nid customer_id product amount order_date\n1 c1 apple 300 2025/2/3\n2 c1 pen 50 2025/2/4\n3 c2 apple 200 2025/2/5 */",
85 "sql": "SELECT DISTINCT c.name, o.product FROM customer c LEFT JOIN orders o ON c.id = o.customer_id WHERE o.amount > 250;"
86 },
87 {
88 "sql_prompt": "請找出沒有購買 'apple' 的客戶名字。",
89 "sql_context": "CREATE TABLE customer (\n id VARCHAR(50) NOT NULL, \n name TEXT, \n email TEXT, \n PRIMARY KEY (id)\n) COLLATE utf8mb4_0900_ai_ci DEFAULT CHARSET=utf8mb4 ENGINE=InnoDB;\n\n/* 3 rows from customer table:\nid name email\nc1 annie annie123@gmail.com\nc2 jack jacklee@gmail.com\nc3 bob bob456@gmail.com */\n\nCREATE TABLE orders (\n id INTEGER, \n customer_id VARCHAR(50), \n product TEXT, \n amount INTEGER, \n order_date TEXT, \n CONSTRAINT fk_customer_order FOREIGN KEY(customer_id) REFERENCES customer (id) ON DELETE CASCADE\n) COLLATE utf8mb4_0900_ai_ci DEFAULT CHARSET=utf8mb4 ENGINE=InnoDB;\n\n/* 3 rows from orders table:\nid customer_id product amount order_date\n1 c1 apple 300 2025/2/3\n2 c1 pen 50 2025/2/4\n3 c2 apple 200 2025/2/5 */",
90 "sql": "SELECT DISTINCT c.name FROM customer c LEFT JOIN orders o ON c.id = o.customer_id WHERE o.product != 'apple' OR o.product IS NULL;"
91 },
92 {
93 "sql_prompt": "請列出所有客戶及其購買的商品名稱，以及每個商品的總金額。",
94 "sql_context": "CREATE TABLE customer (\n id VARCHAR(50) NOT NULL, \n name TEXT, \n email TEXT, \n PRIMARY KEY (id)\n) COLLATE utf8mb4_0900_ai_ci DEFAULT CHARSET=utf8mb4 ENGINE=InnoDB;\n\n/* 3 rows from customer table:\nid name email\nc1 annie annie123@gmail.com\nc2 jack jacklee@gmail.com\nc3 bob bob456@gmail.com */\n\nCREATE TABLE orders (\n id INTEGER, \n customer_id VARCHAR(50), \n product TEXT, \n amount INTEGER, \n order_date TEXT, \n CONSTRAINT fk_customer_order FOREIGN KEY(customer_id) REFERENCES customer (id) ON DELETE CASCADE\n) COLLATE utf8mb4_0900_ai_ci DEFAULT CHARSET=utf8mb4 ENGINE=InnoDB;\n\n/* 3 rows from orders table:\nid customer_id product amount order_date\n1 c1 apple 300 2025/2/3\n2 c1 pen 50 2025/2/4\n3 c2 apple 200 2025/2/5 */",
95 "sql": "SELECT c.name, o.product, COALESCE(SUM(o.amount), 0) AS total_amount FROM customer c LEFT JOIN orders o ON c.id = o.customer_id GROUP BY c.name, o.product;"
96 },
97 {
98 "sql_prompt": "請找出所有客戶的名字，並按照他們的總購買金額進行排序。",
99 "sql_context": "CREATE TABLE customer (\n id VARCHAR(50) NOT NULL, \n name TEXT, \n email TEXT, \n PRIMARY KEY (id)\n) COLLATE utf8mb4_0900_ai_ci DEFAULT CHARSET=utf8mb4 ENGINE=InnoDB;\n\n/* 3 rows from customer table:\nid name email\nc1 annie annie123@gmail.com\nc2 jack jacklee@gmail.com\nc3 bob bob456@gmail.com */\n\nCREATE TABLE orders (\n id INTEGER, \n customer_id VARCHAR(50), \n product TEXT, \n amount INTEGER, \n order_date TEXT, \n CONSTRAINT fk_customer_order FOREIGN KEY(customer_id) REFERENCES customer (id) ON DELETE CASCADE\n) COLLATE utf8mb4_0900_ai_ci DEFAULT CHARSET=utf8mb4 ENGINE=InnoDB;\n\n/* 3 rows from orders table:\nid customer_id product amount order_date\n1 c1 apple 300 2025/2/3\n2 c1 pen 50 2025/2/4\n3 c2 apple 200 2025/2/5 */",
100 "sql": "SELECT c.name, COALESCE(SUM(o.amount), 0) AS total_spent FROM customer c LEFT JOIN orders o ON c.id = o.customer_id GROUP BY c.name ORDER BY total_spent DESC;"
101 },
102 {
103 "sql_prompt": "請列出所有客戶及其購買 'apple' 的次數。",
104 "sql_context": "CREATE TABLE customer (\n id VARCHAR(50) NOT NULL, \n name TEXT, \n email TEXT, \n PRIMARY KEY (id)\n) COLLATE utf8mb4_0900_ai_ci DEFAULT CHARSET=utf8mb4 ENGINE=InnoDB;\n\n/* 3 rows from customer table:\nid name email\nc1 annie annie123@gmail.com\nc2 jack jacklee@gmail.com\nc3 bob bob456@gmail.com */\n\nCREATE TABLE orders (\n id INTEGER, \n customer_id VARCHAR(50), \n product TEXT, \n amount INTEGER, \n order_date TEXT, \n CONSTRAINT fk_customer_order FOREIGN KEY(customer_id) REFERENCES customer (id) ON DELETE CASCADE\n) COLLATE utf8mb4_0900_ai_ci DEFAULT CHARSET=utf8mb4 ENGINE=InnoDB;\n\n/* 3 rows from orders table:\nid customer_id product amount order_date\n1 c1 apple 300 2025/2/3\n2 c1 pen 50 2025/2/4\n3 c2 apple 200 2025/2/5 */",
105 "sql": "SELECT c.name, COUNT(o.product) AS apple_count FROM customer c LEFT JOIN orders o ON c.id = o.customer_id WHERE o.product = 'apple' GROUP BY c.name;"
106 },
107 {
108 "sql_prompt": "請列出所有客戶的名字及其購買商品的總數。",
109 "sql_context": "CREATE TABLE customer (\n id VARCHAR(50) NOT NULL, \n name TEXT, \n email TEXT, \n PRIMARY KEY (id)\n) COLLATE utf8mb4_0900_ai_ci DEFAULT CHARSET=utf8mb4 ENGINE=InnoDB;\n\n/* 3 rows from customer table:\nid name email\nc1 annie annie123@gmail.com\nc2 jack jacklee@gmail.com\nc3 bob bob456@gmail.com */\n\nCREATE TABLE orders (\n id INTEGER, \n customer_id VARCHAR(50), \n product TEXT, \n amount INTEGER, \n order_date TEXT, \n CONSTRAINT fk_customer_order FOREIGN KEY(customer_id) REFERENCES customer (id) ON DELETE CASCADE\n) COLLATE utf8mb4_0900_ai_ci DEFAULT CHARSET=utf8mb4 ENGINE=InnoDB;\n\n/* 3 rows from orders table:\nid customer_id product amount order_date\n1 c1 apple 300 2025/2/3\n2 c1 pen 50 2025/2/4\n3 c2 apple 200 2025/2/5 */",
110 "sql": "SELECT c.name, COUNT(o.id) AS total_orders FROM customer c LEFT JOIN orders o ON c.id = o.customer_id GROUP BY c.name;"
111 },
112 {
113 "sql_prompt": "請找出所有訂單的商品及其購買客戶名字。",
114 "sql_context": "CREATE TABLE customer (\n id VARCHAR(50) NOT NULL, \n name TEXT, \n email TEXT, \n PRIMARY KEY (id)\n) COLLATE utf8mb4_0900_ai_ci DEFAULT CHARSET=utf8mb4 ENGINE=InnoDB;\n\n/* 3 rows from customer table:\nid name email\nc1 annie annie123@gmail.com\nc2 jack jacklee@gmail.com\nc3 bob bob456@gmail.com */\n\nCREATE TABLE orders (\n id INTEGER, \n customer_id VARCHAR(50), \n product TEXT, \n amount INTEGER, \n order_date TEXT, \n CONSTRAINT fk_customer_order FOREIGN KEY(customer_id) REFERENCES customer (id) ON DELETE CASCADE\n) COLLATE utf8mb4_0900_ai_ci DEFAULT CHARSET=utf8mb4 ENGINE=InnoDB;\n\n/* 3 rows from orders table:\nid customer_id product amount order_date\n1 c1 apple 300 2025/2/3\n2 c1 pen 50 2025/2/4\n3 c2 apple 200 2025/2/5 */",
115 "sql": "SELECT DISTINCT o.product, c.name FROM orders o LEFT JOIN customer c ON o.customer_id = c.id;"
116 }
117]