sql数据查询练习题(连接与嵌套查询)

1.查询销售数量总数超过600的产品信息，输出产品号，产品名称。

SELECT product.product_id, product_name 
FROM product,order_detail
WHERE  product.product_id = order_detail.product_id
GROUP BY product.product_id
HAVING SUM(buy_number)>600;

2.查询一次订购了4,7,8号产品的订单号。

SELECT order_id 
FROM order_detail
WHERE product_id IN(4,7,8)
GROUP BY  order_id
HAVING COUNT(DISTINCT product_id)>=3;

3.查询一次订购了4,7,8号产品的客户名及电话。

SELECT customer_name,phone
FROM customer
WHERE customer.customer_id IN(
    SELECT customer_id
    FROM `order`
    WHERE `order`.order_id IN (
        SELECT order_id 
        FROM order_detail
        WHERE product_id IN(4,7,8)
        GROUP BY  order_id
        HAVING COUNT(DISTINCT product_id)>=3));

4.查询上海客户2014年的订单情况，输出客户名称，订单号，订单日期，订单金额，输出结果按客户名称升序订单号升序排列。

SELECT DISTINCT customer_name, o.order_id, order_date, o.total_money
FROM customer c, `order` o , order_detail od
WHERE (c.city = '上海' ) AND YEAR(o.order_date) = 2014 AND c.customer_id = o.customer_id
 AND o.order_id = od.order_id
ORDER BY customer_name ASC, o.order_id ASC;

5.统计查询订单笔数超过15笔的上海客户的订单情况，输出客户名称，订单次数，订单总额（订单金额之和），结果按客户名升序排列。

SELECT  customer_name, COUNT(order_id), SUM(total_money)
FROM customer c, `order` o 
WHERE (c.city = '上海' ) AND c.customer_id = o.customer_id
GROUP BY customer_name
HAVING COUNT(order_id) >= 15
ORDER BY customer_name  ASC;

6.统计查询上海客户订单数排名前5的订单情况 ，输出客户号、客户名称及订单数。

SELECT  c.customer_id, customer_name, COUNT(order_id)
FROM customer c, `order` o 
WHERE (c.city = '上海' ) AND c.customer_id = o.customer_id
GROUP BY c.customer_id
ORDER BY COUNT(order_id)  DESC 
LIMIT 5;

7.查询1002部门的员工及上司信息，输出员工号、员工姓名、上司工号、上司姓名(包括上司不确定的员工信息)，结果按员工号升序排列。

SELECT emp.employee_id, emp.employee_name, emp.report_to, su.employee_name as superior_name
FROM employee emp
LEFT JOIN employee su ON emp.report_to = su.employee_id 
WHERE emp.dept_id = '1002'
ORDER BY emp.employee_id ASC;

8.查询产品类别名称为“打印机”价格超过1000的产品信息，输出类别号、类别名称、产品号、产品名称、单价，结果按产品号升序排列。

SELECT c.category_id, name, product_id, product_name, price
FROM category c, product  p
WHERE c.category_id = p.category_id  AND name = '打印机' AND price > 1000
ORDER BY product_id;

9.统计查询各类产品数及产品号列表，输出类别号、类别名称、该类别包含产品数、该类别产品号列表（按产品号降序排列，产品号之间用下划线分隔）。

SELECT c.category_id, name, COUNT(product_id), GROUP_CONCAT(DISTINCT product_id ORDER BY product_id DESC SEPARATOR '_')
FROM category c, product p
WHERE c.category_id = p.category_id  
GROUP BY c.category_id ;

10.查询产品类别名称为“一体机”且一次销售数量在45及以上的产品销售信息，输出订单详情id、产品号、销售数量和销售额，结果按订单详情id升序排列。

SELECT o.id, p.product_id, o.buy_number, o.total_money
FROM category c, product p, order_detail o
WHERE o.product_id = p.product_id AND p.category_id = c.category_id AND c.name = '一体机' AND o.buy_number >= 45
ORDER BY o.id;

11.统计查询2016年销售量前10的产品销售信息，输出产品号、总销售量。

SELECT product_id, SUM(buy_number)
FROM  `order` o,order_detail d
WHERE  o.order_id = d.order_id
AND YEAR(order_date) = 2016
GROUP BY product_id
ORDER BY SUM(buy_number) DESC LIMIT 10;

12.查询被1001、1002、1003、1004、1005五位顾客都购买过的产品信息，输出产品号，价格。

SELECT product_id,price
FROM `order` o,order_detail d
WHERE  o.order_id = d.order_id 
AND customer_id IN (1001,1002,1003,1004,1005)
GROUP BY product_id
HAVING COUNT(DISTINCT customer_id) >= 5;

13.统计查询订单金额超过该客户平均订单额（该客户订单金额平均值）的上海客户的订单情况，输出客户名、订单号、订单金额、该客户平均订单额（该客户订单金额平均值），结果按客户名升序订单号升序排列。

SELECT customer_name, order_id, total_money, avgsum
FROM customer, `order`, (SELECT customer_id, AVG(total_money) AS avgsum FROM `order` GROUP BY customer_id) AS a1 
WHERE city = '上海' AND customer.customer_id = `order`.customer_id AND a1.customer_id = `order`.customer_id
GROUP BY order_id
HAVING total_money > avgsum
ORDER BY customer_name, order_id;

以下是对 SQL 查询语句中每句的作用进行详细解释：

SELECT customer_name, order_id,  
total_money, avgsum

这一句表示要查询出顾客名、订单号、订单金额和该顾客订单金额平均值（该顾客的所有订单金额的平均数）四个字段。

FROM customer, `order`,   
 (SELECT customer_id, AVG(total_money)   
 AS avgsum FROM `order`  
 GROUP BY customer_id) AS a1 

这一句通过 FROM 子句指定了查询的数据表格。其中 FROM 后面跟着三个数据表的名称：customer、order 和一个子查询 a1。子查询 a1 的作用是统计每个客户的订单金额平均值，因为查询中需要使用该客户订单金额平均值进行筛选，而该值在 customer 和 order 两个数据表中都不是直接可用的字段。通过这样的方式，就避免了使用多层嵌套的 SQL 查询。

WHERE city = '上海' AND customer.customer_id = `order`.customer_id AND a1.customer_id = `order`.customer_id

这一句使用 WHERE 子句进行条件筛选。其中，city = ‘上海’ 表示只查询城市为上海的客户；customer.customer_id = order.customer_id 表示连接查询 customer 表和 order 表，并在其共同的 customer_id 列上进行匹配；a1.customer_id = order.customer_id 表示将子查询 a1 的结果也与 order 表进行连接查询，同时在它们共同的 customer_id 列上进行匹配。

GROUP BY order_id

这一句表示按照订单号进行分组。因为每个客户有多个订单，所以需要将同一个客户的所有订单归为一组。分组运算可以使用 GROUP BY 子句来实现。

HAVING total_money > avgsum

这一句使用 HAVING 子句对分组之后的数据进行筛选。其中，total_money > avgsum 表示只保留订单金额大于该客户订单金额平均值的记录。这样就过滤掉了这些客户的订单金额都低于该客户平均订单额的记录。

ORDER BY customer_name, order_id;

这一句使用 ORDER BY 子句对结果进行排序。其中，customer_name 和 order_id 为排序的字段名，代表先按照顾客名升序排列，再按照订单号升序排列。这样就按照客户名字的字母顺序排列，相同客户的订单按照订单号升序排列。

14.查询商品类别名称为“一体机”、“台式机”两类商品的信息，输出产品号、价格，结果按商品号升序排列。

SELECT p.product_id, p.price
FROM category c, product p
WHERE c.category_id = p.category_id AND (c.name = '台式机' OR c.name = '一体机') 
ORDER BY p.product_id;

15.查询商品类别名称为“一体机”、“台式机”两类商品的销售情况，输出订单详情id、产品号、销售数量，结果按订单详情id升序排列。

SELECT  o.id,p.product_id, buy_number
FROM category c, product p,order_detail o
WHERE c.category_id = p.category_id AND p.product_id = o.product_id AND(c.name = '台式机' OR c.name = '一体机') 
ORDER BY o.id;

16.统计查询商品类别名称为“一体机”、“台式机”两类商品的销售情况，输出产品号、销售总数、销售总额，结果按商品号升序排列。

SELECT  p.product_id, SUM(buy_number),SUM(total_money)
FROM category c, product p,order_detail o
WHERE c.category_id = p.category_id AND p.product_id = o.product_id AND(c.name = '台式机' OR c.name = '一体机') 
GROUP BY p.product_id
ORDER BY p.product_id;

17.查询 2016 年没有下订单的客户，输出客户编号和客户名称，结果按客户名称升序排列。

SELECT customer_id,customer_name
FROM customer 
GROUP BY customer_id
HAVING
 customer_id NOT IN (
    SELECT customer_id
    FROM `order` 
    WHERE YEAR(order_date) = 2016
)
ORDER BY customer_name;

18.查询每门被选修的课程信息，输出课程号，课程名，结果按课程号降序排列。

SELECT DISTINCT c.cno,cname
FROM course c, sc
WHERE c.cno = sc.cno 
ORDER BY c.cno DESC;

19.查询被借阅次数最多的图书信息，列出书号、书名、作者。

SELECT  b.bno,bname,author
FROM book b,borrow r
WHERE b.bno = r.bno
GROUP BY b.bno
HAVING COUNT(*) = (
  SELECT COUNT(*) FROM borrow GROUP BY bno ORDER BY COUNT(*) DESC LIMIT 1
)

20.查询销售总数最多的产品信息，输出产品id、产品名称、产品编码

SELECT p.product_id,product_name,product_code
FROM product p, order_detail o
WHERE p.product_id = o.product_id
GROUP BY p.product_id
HAVING SUM(buy_number) = (
    SELECT SUM(buy_number) FROM order_detail GROUP BY product_id ORDER BY SUM(buy_number) DESC LIMIT 1
   
);