[Database]Write Queries (SQL) with AI

Summary: Use AI to write SQL queries that run the moment you paste them — and leave hand-written syntax errors behind.

1. The Pain Point

Have you ever been there — you've got a business table sitting in Access or Excel, and your boss casually asks, "How much did each salesperson sell last year, and which department crushed it?" Your stomach drops. Answering that takes SQL (Structured Query Language — in plain terms, a standard "language for talking to a database": you tell it what you want, and it hands back the results). But writing it yourself always trips you up: a missing comma, a misspelled table name, or a join that explodes into tens of thousands of rows...

The truth is, it's not that you can't think it through — you just don't want to wrestle with the syntax. Now with AI (this time we'll use the web Copilot: just open copilot.microsoft.com in your browser, no install needed; ChatGPT / Claude work the same way — paste the prompt in as-is), all you have to do is describe what you want in plain language, and it spits out a query you can run directly (a query is simply one "question" to the database — e.g., "list everyone in 销售部"). This article walks you through it, hands-on.

2. What You'll Achieve

By the end of this article, you'll be able to:

• Use Copilot to write the four most common, most useful SQL queries: SELECT (pick columns), WHERE (add conditions), JOIN (combine two tables), and GROUP BY (group and aggregate);
• Understand which part of the AI-generated SQL maps to which part of your request — so you're not blindly trusting whatever it gives you and left clueless when it breaks;
• Take an AI-written SQL statement, validate it on a small sample first, then run it against the real database, dodging the most common pitfalls.

Throughout, we'll use one realistic sample dataset. Every SQL statement is copy-paste-runnable (standard SQL version; I'll cover Access's differences separately in the "Pitfall Guide").

3. Hands-On Case Study

Preparation: Two Sample Tables

Let's imagine a small company with two tables:

• The 员工 table records each employee's ID, name, department, and base salary;
• The 销售记录 table records every order — who sold it (by ID), what was sold, the sale amount, and the date.

First, use the standard SQL below to create the tables and insert the data (MySQL / SQL Server / PostgreSQL can all run it directly; SQL Server note: if your database uses a non-Chinese collation (the common default), switch VARCHAR to NVARCHAR and prefix Chinese literals with N (e.g., N'销售部'), or Chinese text will be silently stored as ?):

sql
-- Create tables
CREATE TABLE 员工 (
    工号     VARCHAR(10) PRIMARY KEY,
    姓名     VARCHAR(20),
    部门     VARCHAR(20),
    基本工资 INT
);

CREATE TABLE 销售记录 (
    订单号   VARCHAR(10) PRIMARY KEY,
    工号     VARCHAR(10),
    产品     VARCHAR(20),
    销售额   INT,
    日期     DATE,
    FOREIGN KEY (工号) REFERENCES 员工(工号)
);

-- Insert sample data
INSERT INTO 员工 (工号, 姓名, 部门, 基本工资) VALUES
('E001','张三','销售部',8000),
('E002','李四','销售部',8500),
('E003','王五','技术部',10000),
('E004','赵六','技术部',9500),
('E005','孙七','行政部',7000);

INSERT INTO 销售记录 (订单号, 工号, 产品, 销售额, 日期) VALUES
('S001','E001','笔记本',5000,'2024-01-15'),
('S002','E001','台式机',3000,'2024-02-10'),
('S003','E002','显示器',2000,'2024-01-20'),
('S004','E003','服务',8000,'2024-02-05'),
('S005','E002','笔记本',4500,'2024-03-01'),
('S006','E001','显示器',1800,'2024-03-12');

A field is simply a "column" in a table — such as 姓名 or 部门; each unit of data is a "row," also called a "record." We'll mix these terms later, but they mean the same thing.

Step 1: Write the Simplest Query with AI (SELECT + WHERE)

Requirement: List the names and departments of colleagues in 销售部.

Prompt for Copilot (copy-paste ready):

我有一张叫"员工"的表，字段有：工号、姓名、部门、基本工资。请用 SQL 查出部门等于"销售部"的员工，只显示"姓名"和"部门"两列。

Copilot will produce this SQL:

sql
SELECT 姓名, 部门
FROM 员工
WHERE 部门 = '销售部';

Results:

姓名	部门
张三	销售部
李四	销售部

Remember these three keywords: SELECT (which columns to pick), FROM (which table to read from), and WHERE (to add a filter). In WHERE, strings must use single quotes '销售部' — that's the SQL rule, and omitting them throws an error.

Step 2: Add More Flexible Conditions (WHERE)

Requirement: Find people whose base salary is above 9000.

Prompt:

在"员工"表里，查出基本工资大于 9000 的员工，显示姓名、部门、基本工资。

SQL:

sql
SELECT 姓名, 部门, 基本工资
FROM 员工
WHERE 基本工资 > 9000;

Results: 王五 (技术部, 10000), 赵六 (技术部, 9500).

Quick tip: In WHERE, numbers are compared without quotes, while text needs single quotes. This is the #1 rookie mistake.

Step 3: Join Two Tables (JOIN)

Looking at just the employee table or just the sales table isn't enough — the boss wants "who sold each order, and for how much." That means linking the two tables by 工号.

JOIN = combining two tables by a shared field. Here the shared field is 工号: it appears in both 销售记录 and 员工.

Prompt:

把"销售记录"表和"员工"表用"工号"关联起来，列出每笔订单的订单号、销售员姓名、销售额。

SQL:

sql
SELECT 销售记录.订单号, 员工.姓名, 销售记录.销售额
FROM 销售记录
INNER JOIN 员工 ON 销售记录.工号 = 员工.工号;

Results (all 6 orders have a matching employee):

订单号	姓名	销售额
S001	张三	5000
S002	张三	3000
S003	李四	2000
S004	王五	8000
S005	李四	4500
S006	张三	1800

INNER JOIN ... ON means: keep only the rows where 工号 matches in both tables. Note the 表名. prefix before field names — because names like 工号 and 销售额 exist in both tables, and without disambiguation SQL gets confused.

Step 4: Group and Aggregate (GROUP BY + Aggregate Functions)

The boss rephrases: "How many people are in each department, what's the total salary, and what's the average?"

Here we use GROUP BY = grouping by a field, then computing one summary value per group; paired with aggregate functions (functions that crunch a bunch of numbers into one — like SUM for summing, COUNT for counting, AVG for averaging).

Prompt:

在"员工"表里，按"部门"分组，统计每个部门的人数、工资总额、平均工资。

SQL:

sql
SELECT 部门,
       COUNT(*)      AS 人数,
       SUM(基本工资) AS 工资总额,
       AVG(基本工资) AS 平均工资
FROM 员工
GROUP BY 部门;

Results:

部门	人数	工资总额	平均工资
销售部	2	16500	8250
技术部	2	19500	9750
行政部	1	7000	7000

COUNT(*) counts how many rows are in the group, SUM adds them up, AVG averages them, and AS gives the computed column an alias for display. Key point: any non-aggregated column in SELECT (here it's 部门) must also appear in GROUP BY, or SQL throws an error.

Step 5: Put It All Together (JOIN + WHERE + GROUP BY)

Finally, the big hands-on combo: calculate the total sales per department for orders dated after February 1, 2024.

This step uses everything from before. Let's break it into two moves:

1. First JOIN the two tables, keeping only the recent-enough orders (WHERE);
2. Then GROUP BY department and sum.

Prompt:

关联"销售记录"和"员工"两张表（按工号），只保留日期在 2024-02-01 及之后的订单，按部门分组，算出每个部门的总销售额。

SQL:

sql
SELECT 员工.部门,
       SUM(销售记录.销售额) AS 总销售额
FROM 销售记录
INNER JOIN 员工 ON 销售记录.工号 = 员工.工号
WHERE 销售记录.日期 >= '2024-02-01'
GROUP BY 员工.部门;

Manual verification with the data above: orders after Feb 1 are S002 (3000, 张三/销售部), S004 (8000, 王五/技术部), S005 (4500, 李四/销售部), and S006 (1800, 张三/销售部).

Results:

部门	总销售额
销售部	9300
技术部	8000

At this point, you've truly got the SELECT / WHERE / JOIN / GROUP BY / aggregate-function combo under your belt.

4. How It Works (Principles)

SQL is a "declarative" language — you don't tell it "open this table first, then loop like that"; you just declare "what I want," and the database engine works out the execution order itself. The reading order of a query is: FROM (which table to pull data from) → JOIN (whether to combine another table) → WHERE (filter each row through a sieve) → GROUP BY (group) → SELECT (finally pick the columns to display). What AI (like Copilot) does, at its core, is translate your plain-language request into the corresponding "part" of that fixed pipeline above; once you understand the pipeline, you can read — and even edit — the code AI gives you, instead of copy-pasting blindly.

5. Pitfall Guide

1. Strings use single quotes; numbers don't. '销售部' is right, 销售部 is wrong; 基本工资 > 9000 is right, while 基本工资 > '9000' may quietly coerce types in some databases and yield weird results.
2. Always write the ON join condition in a JOIN. Forget ON 销售记录.工号 = 员工.工号, and the two tables get "multiplied out" (a Cartesian product — simply put, every row of one table is naively paired with every row of the other). 5 employees × 6 orders instantly becomes 30 rows; once the data grows, your machine freezes.
3. The columns in GROUP BY must correspond one-to-one with the non-aggregated columns in SELECT. You wrote 部门 in SELECT, so 部门 must be in GROUP BY; if you want to show an extra ungrouped field, either add it to GROUP BY or wrap it in an aggregate function.
4. When table/field names contain spaces or Chinese characters, wrap the identifier in quotes per your database. Standard SQL and PostgreSQL use double quotes "员工"; MySQL uses backticks `员工`; Access and SQL Server conventionally use square brackets [员工] (SQL Server also accepts double quotes by default). This article's examples use Chinese names and run fine in most databases, but before going to production, confirm which quoting your database actually accepts.
5. Validate AI-generated SQL on a small sample before hitting the real database. Especially for data-mutating statements like DELETE and UPDATE, always run them once in a test database, check that the affected row count is right, and only then touch the real data.

Supplement (for Access users): Access differs from standard SQL mainly in two places — date literals and batch inserts. Dates use the hash sign #2024-02-01# instead of single quotes; and Access does NOT support the multi-row insert form INSERT ... VALUES (...),(...), so you must split it into multiple INSERT INTO ... VALUES (...). Also, Access's LIKE wildcard is * rather than % (e.g., WHERE 姓名 LIKE '张*'). The SELECT / WHERE / JOIN / GROUP BY syntax is largely the same across both.

6. Going Further

By now you can "use AI to write queries — and actually understand them." If you want to solidify your SQL fundamentals, revisit B13 (the series' database-basics article, which covers how to design data tables), and get a firm grip on concepts like table structure, primary and foreign keys (a primary key uniquely identifies a row; a foreign key points to the primary key of another table). Once those click, you'll edit the SQL AI gives you more smoothly.

Going further (the L3 level), we can turn "the queried results" directly into dynamic reports: for example, use Power Query to pull and clean data from a database or Excel, then drag-and-drop a PivotTable to summarize, or use DAX (measure formulas built for doing complex calculations on pivot tables) for year-over-year and month-over-month comparisons. By then, AI won't just write SQL — it'll help you build the whole macro / automation flow, upgrading you from "querying data" to "data reporting itself."