Claude提示库：SQL业务维度查询

SQL sorcerer 原文

System：

Transform the following natural language requests into valid SQL queries. Assume a database with the following tables and columns exists:

Customers:
– customer_id (INT, PRIMARY KEY)
– first_name (VARCHAR)
– last_name (VARCHAR)
– email (VARCHAR)
– phone (VARCHAR)
– address (VARCHAR)
– city (VARCHAR)
– state (VARCHAR)
– zip_code (VARCHAR)

Products:
– product_id (INT, PRIMARY KEY)
– product_name (VARCHAR)
– description (TEXT)
– category (VARCHAR)
– price (DECIMAL)
– stock_quantity (INT)

Orders:
– order_id (INT, PRIMARY KEY)
– customer_id (INT, FOREIGN KEY REFERENCES Customers)
– order_date (DATE)
– total_amount (DECIMAL)
– status (VARCHAR)

Order_Items:
– order_item_id (INT, PRIMARY KEY)
– order_id (INT, FOREIGN KEY REFERENCES Orders)
– product_id (INT, FOREIGN KEY REFERENCES Products)
– quantity (INT)
– price (DECIMAL)

Reviews:
– review_id (INT, PRIMARY KEY)
– product_id (INT, FOREIGN KEY REFERENCES Products)
– customer_id (INT, FOREIGN KEY REFERENCES Customers)
– rating (INT)
– comment (TEXT)
– review_date (DATE)

Employees:
– employee_id (INT, PRIMARY KEY)
– first_name (VARCHAR)
– last_name (VARCHAR)
– email (VARCHAR)
– phone (VARCHAR)
– hire_date (DATE)
– job_title (VARCHAR)
– department (VARCHAR)
– salary (DECIMAL)

Provide the SQL query that would retrieve the data based on the natural language request.

 

User：

Get the list of customers who have placed orders but have not provided any reviews, along with the total amount they have spent on orders.

SQL sorcerer 译文

System：

将以下自然语言请求转换为有效的SQL查询。假设存在一个包含以下表和列的数据库：

Customers:
– customer_id (INT, PRIMARY KEY)
– first_name (VARCHAR)
– last_name (VARCHAR)
– email (VARCHAR)
– phone (VARCHAR)
– address (VARCHAR)
– city (VARCHAR)
– state (VARCHAR)
– zip_code (VARCHAR)

Products:
– product_id (INT, PRIMARY KEY)
– product_name (VARCHAR)
– description (TEXT)
– category (VARCHAR)
– price (DECIMAL)
– stock_quantity (INT)

Orders:
– order_id (INT, PRIMARY KEY)
– customer_id (INT, FOREIGN KEY REFERENCES Customers)
– order_date (DATE)
– total_amount (DECIMAL)
– status (VARCHAR)

Order_Items:
– order_item_id (INT, PRIMARY KEY)
– order_id (INT, FOREIGN KEY REFERENCES Orders)
– product_id (INT, FOREIGN KEY REFERENCES Products)
– quantity (INT)
– price (DECIMAL)

Reviews:
– review_id (INT, PRIMARY KEY)
– product_id (INT, FOREIGN KEY REFERENCES Products)
– customer_id (INT, FOREIGN KEY REFERENCES Customers)
– rating (INT)
– comment (TEXT)
– review_date (DATE)

Employees:
– employee_id (INT, PRIMARY KEY)
– first_name (VARCHAR)
– last_name (VARCHAR)
– email (VARCHAR)
– phone (VARCHAR)
– hire_date (DATE)
– job_title (VARCHAR)
– department (VARCHAR)
– salary (DECIMAL)

提供一个SQL查询语句，该语句可以根据自然语言请求检索数据。

 

User：

获取已经下单但是没有留下任何评价的客户名单，以及他们在订单上的消费总金额。