-- （1）用子查询查询员工“张小娟”所做的订单信息。
SELECT *
FROM order_master
WHERE saler_no = (
SELECT employee_no
FROM employee
WHERE employee_name = '张小娟'
);
-- （2）查询没有订购商品的且在北京地区的客户编号，客户名称和邮政编码，并按邮政编码降序排序。
SELECT customer_no, customer_name, zip
FROM customer x
WHERE address = '北京市'
AND NOT EXISTS (
SELECT *
FROM order_master y
WHERE x.customer_no = y.customer_no
)
ORDER BY zip DESC;
-- （3）查询订购了“32M DRAM”商品的订单编号，订货数量和订货单价。
SELECT order_no, quantity, price
FROM order_detail
WHERE product_no = (
SELECT product_no
FROM product
WHERE product_name = '32M DRAM'
);
-- （4）查询与员工编号“E2008005”在同一个部门的员工编号，姓名，性别，所属部门。
SELECT employee_no, employee_name, sex, department
FROM employee
WHERE department = (
SELECT department
FROM employee
WHERE employee_no = 'E2008005'
);
--（5）查询既订购了P2005001商品，又订购了P2007002商品的客户编号，订单编号和订单金额
SELECT customer_no, order_no, order_sum
FROM order_master x
WHERE EXISTS (
SELECT *
FROM order_detail y
WHERE x.order_no = y.order_no
AND product_no = 'P2005001'
)
AND EXISTS (
SELECT *
FROM order_detail z
WHERE x.order_no = z.order_no
AND product_no = 'P2007002'
);
-- NOT EXISTS VS. NOT IN 写两个然后比较性能
--（6）查询没有订购“52倍速光驱”或“17寸显示器”的客户编号，客户名称。
SELECT customer_no, customer_name
FROM customer
WHERE customer_no NOT IN (
SELECT customer_no
FROM order_master
WHERE order_no IN (
SELECT order_no
FROM order_detail
WHERE product_no IN (
SELECT product_no
FROM product
WHERE product_name IN ('52倍速光驱', '17寸显示器')
)
)
);
-- ORDER BY VS. ALL VS MIN MAX
--（7）查询订单金额最高的订单编号，客户姓名，销售员名称和相应的订单金额。
SELECT order_no
, (
SELECT customer_name
FROM customer y
WHERE y.customer_no = x.customer_no
) AS customer_name
, (
SELECT employee_name
FROM employee
WHERE employee_no = saler_no
) AS employee_name, order_sum
FROM order_master x
ORDER BY order_sum DESC
LIMIT 1;
-- IN 与 = 以及 OR
--（8）查询订购了“52倍速光驱”商品的订购数量，订购平均价和订购总金额。
SELECT SUM(quantity), AVG(price)
, (
SELECT order_sum
FROM order_master y
WHERE y.order_no = x.order_no
) AS order_sum
FROM order_detail x
WHERE product_no = (
SELECT product_no
FROM product
WHERE product_name = '52倍速光驱'
);
-- BETWEEN 2 AND 4
--（9）查询订购了“52倍速光驱”商品且订货数量界于2~4之间的订单编号，订货数量和订货金额。
SELECT order_no, quantity, price
FROM order_detail
WHERE product_no = (
SELECT product_no
FROM product
WHERE product_name = '52倍速光驱'
)
AND quantity BETWEEN 2 AND 4;
-- GROUP BY
--（10）在订单主表中查询每个业务员的订单数量
SELECT saler_no, COUNT(*) AS order_num
FROM order_master
GROUP BY saler_no;
-- COUNT(*) VS. COUNT(id) 任何性能对比都是基于特定 的存储引擎！
--（11）统计在业务科工作且在1973年或1967年出生的员工人数和平均工资。
SELECT COUNT(employee_no) AS employee_num, AVG(salary)
FROM employee
WHERE department = '业务科'
AND YEAR(birthday) IN (1973, 1967);
-- （12）在订单明细表中统计每种商品的销售数量和金额，并按销售金额的升序排序输出。
SELECT product_no, SUM(quantity) AS quantity
, SUM(quantity * price) AS total
FROM order_detail
GROUP BY product_no
ORDER BY total;
-- （13）统计客户号为“C20050001”的客户的订单数，订货总额和平均订货金额
SELECT COUNT(*), SUM(order_sum)
, AVG(order_sum)
FROM order_master
WHERE customer_no = 'C2005001';
-- （14）统计每个客户的订单数，订货总额和平均订货金额。
SELECT customer_no, COUNT(*), SUM(order_sum)
, AVG(order_sum)
FROM order_master
GROUP BY customer_no;
-- （15）查询订单中至少包含3种（含3种）以上商品的订单编号及订购次数，且订购的商品数量在3件（含3件）以上。
SELECT order_no, COUNT(*)
FROM order_detail
GROUP BY order_no
HAVING COUNT(DISTINCT product_no) >= 3
AND SUM(quantity) >= 3;
-- （16）查找订购了“32M DRAM”的商品的客户编号，客户名称，订货总数量和订货总金额。
SELECT customer_no
, (
SELECT customer_name
FROM customer y
WHERE y.customer_no = x.customer_no
) AS customer_name
, (
SELECT SUM(quantity)
FROM order_detail z
WHERE z.order_no = x.order_no
) AS total_quantity, order_sum
FROM order_master x
WHERE order_no IN (
SELECT order_no
FROM order_detail
WHERE product_no = (
SELECT product_no
FROM product
WHERE product_name = '32M DRAM'
)
);
--（17）查询每个客户订购的商品编号，商品所属类别，商品数量及订货金额，结果显示客户名称，商品所属类别，商品数量及订货金额，并按客户编号升序和按订货金额的降序排序输出。
SELECT (
SELECT customer_name
FROM customer n
WHERE n.customer_no = m.customer_no
) AS customer_name, product_class, quantity, price
FROM (
SELECT (
SELECT customer_no
FROM order_master x
WHERE x.order_no = z.order_no
) AS customer_no
, (
SELECT product_class
FROM product y
WHERE y.product_no = z.product_no
) AS product_class, quantity, price
FROM order_detail z
) m
ORDER BY customer_no, price DESC;
-- （18）按商品类别查询每类商品的订货平均单价在280元（含280元）以上的订货总数量，订货平均单价和订货总金额。
SELECT product_class, SUM(quantity), AVG(price)
, SUM(quantity * price)
FROM order_detail x
LEFT JOIN product y ON x.product_no = y.product_no
GROUP BY product_class
HAVING AVG(price) >= 280;
-- （19）查找至少有2次销售的业务员名称和销售日期。
SELECT (
SELECT employee_name
FROM employee
WHERE employee_no = saler_no
) AS employee_name, order_date
FROM order_master x
WHERE x.saler_no IN (
SELECT y.saler_no
FROM order_master y
GROUP BY saler_no
HAVING COUNT(*) >= 2
)
ORDER BY saler_no;
-- （20）查询销售金额最大的客户名称和总货款额
SELECT (
SELECT customer_name
FROM customer y
WHERE y.customer_no = x.customer_no
) AS customer_name, SUM(order_sum) AS sum
FROM order_master x
GROUP BY customer_no
ORDER BY sum DESC
LIMIT 1;
-- （21）查找销售总额小于5000元的销售员编号，姓名和销售额
SELECT x.employee_no, employee_name, SUM(order_sum)
FROM employee x
LEFT JOIN order_master y ON x.employee_no = y.saler_no
GROUP BY employee_no
HAVING SUM(order_sum) < 5000
OR SUM(order_sum) IS NULL;
-- （22）查找至少订购了3种商品的客户编号，客户名称，商品编号，商品名称，数量和金额。
SELECT e.customer_no, e.customer_name, r.product_no, r.product_name, quantity
, price
FROM order_detail q
LEFT JOIN order_master w ON w.order_no = q.order_no
LEFT JOIN customer e ON e.customer_no = w.customer_no
LEFT JOIN product r ON r.product_no = q.product_no
WHERE q.order_no IN (
SELECT t.order_no
FROM order_detail t
GROUP BY t.order_no
HAVING COUNT(*) >= 3
)
ORDER BY w.customer_no;
-- 待修改，剪掉冗余中间表、无用列，用 EXISTS 重写
--（23）查找同时订购了商品为“P2007002”和商品编号为“P2007001”的商品的客户编号，客户姓名，商品编号，商品名称和销售数量，按客户编号排序输出。
SELECT a.customer_no, a.customer_name, e.product_no, e.product_name, d.quantity
FROM customer a
LEFT JOIN order_master s ON a.customer_no = s.customer_no
LEFT JOIN order_detail d ON s.order_no = d.order_no
LEFT JOIN product e ON e.product_no = d.product_no
WHERE a.customer_no IN (
SELECT customer_no
FROM (
SELECT x.customer_no, x.customer_name, m.product_no, m.product_name, quantity
FROM customer x
LEFT JOIN order_master y ON x.customer_no = y.customer_no
LEFT JOIN order_detail z ON z.order_no = y.order_no
LEFT JOIN product m ON m.product_no = z.product_no
WHERE m.product_no = "P2007001"
GROUP BY x.customer_no
UNION ALL
SELECT x.customer_no, x.customer_name, m.product_no, m.product_name, quantity
FROM customer x
LEFT JOIN order_master y ON x.customer_no = y.customer_no
LEFT JOIN order_detail z ON z.order_no = y.order_no
LEFT JOIN product m ON m.product_no = z.product_no
WHERE m.product_no = "P2007002"
GROUP BY x.customer_no
) p
GROUP BY p.customer_no
HAVING COUNT(*) >= 2
)
AND e.product_no IN ("P2007001", "P2007002")
ORDER BY a.customer_no;
-- （24）计算每一商品每月的销售金额总和，并将结果首先按销售月份然后按订货金额降序排序输出。
SELECT MONTH(order_date) AS month, x.product_no, product_name
, SUM(quantity * price) AS totoal
FROM product x
LEFT JOIN order_detail y ON x.product_no = y.product_no
LEFT JOIN order_master z ON z.order_no = y.order_no
GROUP BY MONTH(order_date), x.product_no
ORDER BY MONTH(order_date), totoal DESC;
-- （25）查询订购了“键盘”商品的客户姓名，订货数量和订货日期
SELECT (
SELECT customer_name
FROM customer z
WHERE z.customer_no = y.customer_no
) AS customer_name, quantity, order_date
FROM order_detail x
LEFT JOIN order_master y ON x.order_no = y.order_no
WHERE product_no = (
SELECT product_no
FROM product
WHERE product_name = "键盘"
);
-- （26）查询每月订购“键盘”商品的客户名称。
SELECT (
SELECT customer_name
FROM customer z
WHERE z.customer_no = y.customer_no
) AS customer_name, MONTH(order_date) AS month
FROM order_detail x
LEFT JOIN order_master y ON x.order_no = y.order_no
WHERE product_no = (
SELECT product_no
FROM product
WHERE product_name = "键盘"
)
ORDER BY order_date;
-- （27）查询至少销售了5种商品的销售员编号，姓名，商品名称，数量及相应的单价，并按销售员编号排序输出。
SELECT employee_no, employee_name, product_name, quantity, price
FROM employee x
LEFT JOIN order_master y ON x.employee_no = y.saler_no
LEFT JOIN order_detail z ON z.order_no = y.order_no
LEFT JOIN product m ON m.product_no = z.product_no
WHERE (
SELECT COUNT(DISTINCT product_no)
FROM order_master w
LEFT JOIN order_detail e ON w.order_no = e.order_no
WHERE w.saler_no = x.employee_no
) >= 5
ORDER BY employee_no;
-- （28）查询没有订购商品的客户编号和客户名称。
SELECT customer_no, customer_name
FROM customer x
WHERE NOT EXISTS (
SELECT *
FROM order_master y
WHERE y.customer_no = x.customer_no
)
