AskTable MCP User Guide: Query and SQL Tools

Configuration is just the first step. How to use it efficiently is the key. This article will dive deep into the usage tips of AskTable MCP Server to help you become a data querying expert.

---

1. Two Core Tools

AskTable MCP Server provides two core tools, each with its own characteristics and applicable scenarios.

1.1 Tool Comparison

1.2 How to Choose?

Use "Query Data" when you:

• •✅ Need to get data results directly
• •✅ Want AI to automatically analyze and summarize
• •✅ Don't care about the specific SQL implementation
• •✅ Need to quickly answer business questions

Use "Generate SQL" when you:

• •✅ Need to view SQL statements
• •✅ Want to learn or understand SQL写法
• •✅ Need to modify or optimize SQL
• •✅ Need to use SQL in other systems

---

2. Query Data Tool Details

2.1 Basic Usage

Simple Query:

User: "Query how many students there are in total"

AI calls: query("Query how many students there are in total")

Returns: {"status": "success", "data": "There are 200 students in total"}

Aggregation Query:

User: "How many students are in each class?"

AI calls: query("How many students are in each class?")

Returns:
{
  "status": "success",
  "data": "Class 1: 50 students, Class 2: 48 students, Class 3: 52 students, Class 4: 50 students"
}

2.2 Complex Query Examples

Multi-table Join:

Question: "Query the list of students enrolled in 'Database Principles' course"

AI will automatically:
1. Identify that students, courses, and enrollments three tables need to be joined
2. Generate the correct JOIN statement
3. Execute query and return results

Condition Filtering:

Question: "Query students who are older than 20 and have excellent grades"

AI will automatically:
1. Understand the conditions "age > 20" and "excellent grades"
2. Generate WHERE clause
3. Return list of students meeting the conditions

Time Range:

Question: "Query the total order amount in the last 7 days"

AI will automatically:
1. Calculate the time range (today - 7 days)
2. Generate date filtering conditions
3. Calculate and return the total amount

2.3 Prompt Tips

Be Clear About Query Goals:

• •❌ "Look at the data"
• •✅ "Query all student names and ages"

Specify Time Range:

• •❌ "Recent orders"
• •✅ "Orders in the last 7 days"

Be Clear About Aggregation:

• •❌ "Count orders"
• •✅ "Count orders by day"

Specify Sorting:

• •❌ "Query sales"
• •✅ "Query top 10 products by sales"

2.4 Common Questions

Q: What if there are too many query results?

A: Specify limiting conditions in the question:

"Query the top 10 students"
"Query orders with sales greater than 10000"

Q: How to query specific fields?

A: Clearly specify the needed fields:

"Query student names, ages, and classes"

Q: How to handle null values?

A: Explain how to handle in the question:

"Query students who have email"
"Query students with non-null email"

---

3. Generate SQL Tool Details

3.1 Basic Usage

Simple Query:

User: "Generate SQL to query all students"

AI calls: gen_sql("Generate SQL to query all students")

Returns:
{
  "status": "success",
  "data": "SELECT * FROM students"
}

Complex Query:

User: "Write SQL to count average scores for each class"

AI calls: gen_sql("Write SQL to count average scores for each class")

Returns:
{
  "status": "success",
  "data": "SELECT class_name, AVG(score) as avg_score FROM students GROUP BY class_name"
}

3.2 Advanced Usage

Subquery:

Question: "Generate SQL to query students with scores higher than the average"

Generated SQL:
SELECT * FROM students
WHERE score > (SELECT AVG(score) FROM students)

Window Function:

Question: "Generate SQL to query top 3 students in each class by score"

Generated SQL:
SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY class_id ORDER BY score DESC) as rank
  FROM students
) t WHERE rank <= 3

CTE (Common Table Expression):

Question: "Generate SQL to query users who have orders for 3 consecutive days"

Generated SQL:
WITH daily_orders AS (
  SELECT user_id, DATE(created_at) as order_date
  FROM orders
  GROUP BY user_id, DATE(created_at)
)
SELECT user_id FROM daily_orders
GROUP BY user_id
HAVING COUNT(DISTINCT order_date) >= 3

3.3 SQL Optimization Suggestions

View Generated SQL:

User: "Generate SQL to query all orders"
AI: Return SQL
User: "Can this SQL be optimized?"
AI: Analyze and provide optimization suggestions

Add Index Suggestions:

User: "This query is slow, what indexes should be added?"
AI: Analyze SQL and suggest indexes

---

4. Permission Control

4.1 Why is Permission Control Needed?

In enterprise environments, users of different roles should only access data within their permission scope:

• •Sales can only see orders in their region
• •Finance can only see approved reports
• •Management can see global data

4.2 Using role_id

Scenario: Restrict sales to only view data in their region

Step 1: Create Role in AskTable

Log in to AskTable and create role "East China Sales":

• •Permission rule: orders.region = 'East China'
• •Get role_id: role_abc123

Step 2: Specify role_id When Querying

User: "Query order total"

AI calls: query(
  question="Query order total",
  role_id="role_abc123"
)

Actually executed SQL:
SELECT SUM(amount) FROM orders WHERE region = 'East China'

4.3 Using role_variables

Scenario: Restrict employees to only view their own data

Step 1: Create Role in AskTable

Create role "Employee":

• •Permission rule: orders.employee_id = {{employee_id}}
• •Get role_id: role_def456

Step 2: Pass Variables When Querying

User: "Query my orders"

AI calls: query(
  question="Query my orders",
  role_id="role_def456",
  role_variables={"employee_id": 12345}
)

Actually executed SQL:
SELECT * FROM orders WHERE employee_id = 12345

4.4 Permission Control Best Practices

Principle of Least Privilege:

• •Only grant necessary permissions
• •Use role_id instead of directly querying all data

Dynamic Variables:

• •Use role_variables to pass user information
• •Avoid hardcoding user IDs in SQL

Multi-layer Permissions:

• •Can use multiple permission rules simultaneously
• •Example: Region restriction + Time restriction

---

5. Practical Cases

Case 1: Sales Data Analysis

Background: Sales manager needs to analyze this month's sales situation

Conversation Example:

User: "Query this month's total sales"
AI: This month's total sales is ¥1,234,567

User: "How much did it grow compared to last month?"
AI: Last month's total sales was ¥987,654, this month grew by 25%

User: "Which product sold the best?"
AI: The highest sales product is "Product A" with ¥345,678 in sales

User: "Generate SQL to query sales for each product"
AI: SELECT product_name, SUM(amount) as total_sales
    FROM orders
    WHERE MONTH(created_at) = MONTH(CURRENT_DATE)
    GROUP BY product_name
    ORDER BY total_sales DESC

Case 2: User Behavior Analysis

Background: Product manager needs to analyze user activity

Conversation Example:

User: "Query daily active users in the last 7 days"
AI: Daily active users in the last 7 days:
    3/1: 1,234 users
    3/2: 1,456 users
    ...

User: "Which users are the most active?"
AI: The most active users are:
    User A: 15 logins
    User B: 12 logins
    ...

User: "Generate SQL to query user retention rate"
AI: WITH first_login AS (
      SELECT user_id, MIN(DATE(login_time)) as first_date
      FROM user_logs
      GROUP BY user_id
    ),
    retention AS (
      SELECT f.first_date,
             COUNT(DISTINCT f.user_id) as new_users,
             COUNT(DISTINCT CASE WHEN DATE(l.login_time) = DATE_ADD(f.first_date, INTERVAL 1 DAY) THEN l.user_id END) as day1_retained
      FROM first_login f
      LEFT JOIN user_logs l ON f.user_id = l.user_id
      GROUP BY f.first_date
    )
    SELECT first_date,
           new_users,
           day1_retained,
           ROUND(day1_retained * 100.0 / new_users, 2) as retention_rate
    FROM retention

Case 3: Financial Report

Background: Finance personnel need to generate monthly reports

Conversation Example:

User: "Query this month's income and expenses"
AI: This month's income: ¥2,345,678
    This month's expenses: ¥1,234,567
    Net profit: ¥1,111,111

User: "Statistics expenses by department"
AI: R&D department: ¥567,890
    Sales department: ¥345,678
    Admin department: ¥123,456
    ...

User: "Generate SQL to query monthly profit trend"
AI: SELECT DATE_FORMAT(date, '%Y-%m') as month,
           SUM(CASE WHEN type = 'income' THEN amount ELSE 0 END) as income,
           SUM(CASE WHEN type = 'expense' THEN amount ELSE 0 END) as expense,
           SUM(CASE WHEN type = 'income' THEN amount ELSE -amount END) as profit
    FROM transactions
    GROUP BY DATE_FORMAT(date, '%Y-%m')
    ORDER BY month

---

6. Prompt Best Practices

6.1 Be Clear and Specific

Bad prompts:

• •"Look at the data"
• •"Make some statistics"
• •"Analyze it"

Good prompts:

• •"Query all student names and ages"
• •"Count the number of students in each class"
• •"Analyze sales trends in the last 30 days"

6.2 Step-by-step Queries

Decompose complex problems:

Question: "Analyze sales situation and give suggestions"

Decompose into:
1. "Query this month's total sales"
2. "Compare growth with last month"
3. "Query products with declining sales"
4. "Analyze reasons and give suggestions"

6.3 Use Business Terminology

Terms AI can understand:

• •"Active users", "Retention rate", "Conversion rate"
• •"Year-over-year", "Month-over-month", "Growth rate"
• •"TOP 10", "Ranking", "Proportion"

Example:

"Query this month's user retention rate"
"Compare sales with the same period last year"
"Query the channel with the highest conversion rate"

6.4 Specify Format

Specify output format:

"Display sales for each product in table format"
"Generate a bar chart showing sales trends"
"Export to CSV format"

---

7. Common Issues

7.1 Query Timeout

Problem: Query keeps returning no result

Solution:

• •Narrow query scope (add time limit, quantity limit)
• •Optimize query conditions (add indexes)
• •Increase timeout (modify configuration)

7.2 Inaccurate Results

Problem: Query results don't match expectations

Solution:

• •Use "Generate SQL" to see the actually executed SQL
• •Check if data in the database is correct
• •Describe query requirements more clearly

7.3 Permission Error

Problem: Prompt that no permission to access some data

Solution:

• •Check if role_id is correct
• •Confirm permission rules are configured correctly
• •Contact administrator to adjust permissions

7.4 Cannot Understand Question

Problem: AI can't understand your query

Solution:

• •Use simpler, more direct expressions
• •Decompose complex problems into multiple simple ones
• •Use standard business terminology

---

8. Summary

Mastering AskTable MCP Server usage tips can greatly improve data query efficiency:

Core Points: ✅ Understand the differences between the two tools and applicable scenarios ✅ Use clear and specific prompts ✅ Use permission control to protect data security ✅ Use step-by-step queries for complex problems

Advanced Tips: ✅ Learn common SQL patterns ✅ Understand business terminology and metrics ✅ Optimize query performance

Next Steps:

• •MCP Architecture and Principles - Deep dive into technical details
• •Practice more query scenarios
• •Explore advanced features

---

Related Reading:

• •MCP Introduction Guide
• •MCP Quick Start Guide
• •MCP Deployment Guide

Technical Exchange:

• •Website: asktable.com