sql测试题和答案
在进行SQL数据库开发时，进行一些测试是非常重要的。通过测试，我们可以验证数据库的正确性，保证数据的一致性，并且提前发现和解决潜在的问题。本文将提供一些常见的SQL测试题目，并且给出相应的答案，帮助读者更好地理解SQL语言的使用和测试方法。
一、题目1：查询订单表中购买数量最多的商品名称和购买数量。
答案：
```sql
SELECT product_name, MAX(quantity) AS max_quantity
FROM orders
GROUP BY product_name;
```
二、题目2：查询每个部门的平均工资和人数，结果按平均工资降序排列。
答案：
```sql
SELECT department, AVG(salary) AS avg_salary, COUNT(*) AS num_employees
FROM employees
GROUP BY department
ORDER BY avg_salary DESC;
```
三、题目3：查询没有订单的客户名称和。
答案：
```sql
SELECT customer_name, contact_number
FROM customers
WHERE customer_id NOT IN (
  SELECT DISTINCT customer_id
  FROM orders
);
```
四、题目4：查询每个国家的订单数和总销售额，并按总销售额降序排列。
答案：
```sql
SELECT country, COUNT(*) AS num_orders, SUM(total_amount) AS total_sales
FROM orders
GROUP BY country
ORDER BY total_sales DESC;
```
五、题目5：查询购买了所有商品的客户名称和。
答案：
```sql
SELECT customer_name, contact_number
FROM customers
WHERE customer_id IN (
  SELECT customer_id
  FROM orders
  GROUP BY customer_id
  HAVING COUNT(DISTINCT product_name) = (
    SELECT COUNT(DISTINCT product_name)
    FROM products
  )
);
```
六、题目6：查询每个部门的最高工资和最低工资。
答案：
```sql
SELECT department, MAX(salary) AS max_salary, MIN(salary) AS min_salary
FROM employees
GROUP BY department;
```
七、题目7：查询所有没有下单的商品名称和单价。
答案：
```sqlselect中distinct
SELECT product_name, unit_price
FROM products
WHERE product_id NOT IN (
  SELECT DISTINCT product_id
  FROM orders
);
```
八、题目8：查询每个订单的商品数量和该商品所在分类的平均销售单价。
答案：
```sql
der_id, o.product_name, o.quantity, AVG(p.unit_price) AS avg_unit_price
FROM orders o
JOIN products p ON o.product_id = p.product_id
GROUP der_id, o.product_name, o.quantity;
```
九、题目9：查询每个客户的订单数量和最后一次下单时间。
答案：
```sql
SELECT c.customer_name, der_id) AS num_orders, der_date) AS last_order_date