query-writing

分类: 数据库与数据存储 | 上传者: tclatos | 下载: 0 | 版本: v1.0（最新）

For writing and executing SQL queries - from simple single-table queries to complex multi-table JOINs and aggregations

更新日志: Source: GitHub https://github.com/tclatos/genai-tk

目录结构

当前层级: skills/langchain_examples/query-writing/

📄 SKILL.md 2.2 KB

SKILL.md

---
name: query-writing
description: For writing and executing SQL queries - from simple single-table queries to complex multi-table JOINs and aggregations
---

# Query Writing Skill

## When to Use This Skill

Use this skill when you need to answer a question by writing and executing a SQL query.

## Workflow for Simple Queries

For straightforward questions about a single table:

1. **Identify the table** - Which table has the data?
2. **Get the schema** - Use `sql_db_schema` to see columns
3. **Write the query** - SELECT relevant columns with WHERE/LIMIT/ORDER BY
4. **Validate** - Use `sql_db_query_checker` to check syntax
5. **Execute** - Run with `sql_db_query`
6. **Format answer** - Present results clearly

## Workflow for Complex Queries

For questions requiring multiple tables:

### 1. Plan Your Approach
**Use `write_todos` to break down the task:**
- Identify all tables needed
- Map relationships (foreign keys)
- Plan JOIN structure
- Determine aggregations

### 2. Examine Schemas
Use `sql_db_schema` for EACH table to find join columns and needed fields.

### 3. Construct Query
- SELECT - Columns and aggregates
- FROM/JOIN - Connect tables on FK = PK
- WHERE - Filters before aggregation
- GROUP BY - All non-aggregate columns
- ORDER BY - Sort meaningfully
- LIMIT - Default 5 rows

### 4. Validate and Execute
Use `sql_db_query_checker` to verify the query, then run it with `sql_db_query`.

## Example: Best-Selling Artists

```sql
SELECT
    ar.Name AS Artist,
    ROUND(SUM(il.UnitPrice * il.Quantity), 2) AS TotalRevenue
FROM Artist ar
INNER JOIN Album al ON ar.ArtistId = al.ArtistId
INNER JOIN Track t  ON al.AlbumId  = t.AlbumId
INNER JOIN InvoiceLine il ON t.TrackId = il.TrackId
GROUP BY ar.ArtistId, ar.Name
ORDER BY TotalRevenue DESC
LIMIT 5;
```

## Example: Revenue by Country

```sql
SELECT
    c.Country,
    ROUND(SUM(i.Total), 2) AS TotalRevenue
FROM Invoice i
INNER JOIN Customer c ON i.CustomerId = c.CustomerId
GROUP BY c.Country
ORDER BY TotalRevenue DESC
LIMIT 5;
```

## Quality Guidelines

- Query only relevant columns (not SELECT *)
- Always apply LIMIT (5 default)
- Use table aliases for clarity
- For complex queries: use write_todos to plan
- Never use DML statements (INSERT, UPDATE, DELETE, DROP)

登录后下载/点赞/收藏 ❤ 4 | ★ 0

query-writing

目录结构

SKILL.md

举报内容

提示