-- （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

)