mirror of
https://github.com/csunny/DB-GPT.git
synced 2025-07-27 13:57:46 +00:00
30 lines
3.1 KiB
SQL
30 lines
3.1 KiB
SQL
CREATE SCHEMA test_case_info;
|
||
COMMENT ON SCHEMA test_case_info is '测试用例信息';
|
||
|
||
SET SEARCH_PATH = test_case_info;
|
||
|
||
CREATE TABLE test_cases (
|
||
case_id SERIAL /*INT AUTO_INCREMENT*/ PRIMARY KEY,
|
||
scenario_name VARCHAR(100),
|
||
scenario_description VARCHAR(6500),
|
||
test_question VARCHAR(500),
|
||
expected_sql VARCHAR(6500),
|
||
correct_output VARCHAR(6500)
|
||
);
|
||
COMMENT ON TABLE test_cases IS '测试用例表';
|
||
COMMENT ON COLUMN test_cases.scenario_name IS '场景名称';
|
||
COMMENT ON COLUMN test_cases.scenario_description IS '场景描述';
|
||
COMMENT ON COLUMN test_cases.test_question IS '测试问题';
|
||
COMMENT ON COLUMN test_cases.expected_sql IS '预期SQL';
|
||
COMMENT ON COLUMN test_cases.correct_output IS '正确输出';
|
||
|
||
INSERT INTO test_cases (scenario_name, scenario_description, test_question, expected_sql, correct_output) VALUES
|
||
('学校管理系统', '测试SQL助手的联合查询，条件查询和排序功能', '查询所有学生的姓名，专业和成绩，按成绩降序排序', 'SELECT students.student_name, students.major, scores.score FROM students JOIN scores ON students.student_id = scores.student_id ORDER BY scores.score DESC;', '返回所有学生的姓名，专业和成绩，按成绩降序排序的结果'),
|
||
('学校管理系统', '测试SQL助手的联合查询，条件查询和排序功能', '查询计算机科学专业的学生的平均成绩', 'SELECT AVG(scores.score) as avg_score FROM students JOIN scores ON students.student_id = scores.student_id WHERE students.major = ''计算机科学'';', '返回计算机科学专业学生的平均成绩'),
|
||
('学校管理系统', '测试SQL助手的联合查询，条件查询和排序功能', '查询哪些学生在2023年秋季学期的课程学分总和超过15', 'SELECT students.student_name FROM students JOIN scores ON students.student_id = scores.student_id JOIN courses ON scores.course_id = courses.course_id WHERE scores.semester = ''2023年秋季'' GROUP BY students.student_id HAVING SUM(courses.credit) > 15;', '返回在2023年秋季学期的课程学分总和超过15的学生的姓名'),
|
||
('电商系统', '测试SQL助手的数据聚合和分组功能', '查询每个用户的总订单数量', 'SELECT users.user_name, COUNT(orders.order_id) as order_count FROM users JOIN orders ON users.user_id = orders.user_id GROUP BY users.user_id;', '返回每个用户的总订单数量'),
|
||
('电商系统', '测试SQL助手的数据聚合和分组功能', '查询每种商品的总销售额', 'SELECT products.product_name, SUM(products.product_price * orders.quantity) as total_sales FROM products JOIN orders ON products.product_id = orders.product_id GROUP BY products.product_id;', '返回每种商品的总销售额'),
|
||
('电商系统', '测试SQL助手的数据聚合和分组功能', '查询2023年最受欢迎的商品（订单数量最多的商品）', 'SELECT products.product_name FROM products JOIN orders ON products.product_id = orders.product_id WHERE YEAR(orders.order_date) = 2023 GROUP BY products.product_id ORDER BY COUNT(orders.order_id) DESC LIMIT 1;', '返回2023年最受欢迎的商品（订单数量最多的商品）的名称');
|
||
|
||
COMMIT;
|