sql数据查询练习题(聚合函数查询)

1.统计各级职位(不含NULL)员工人数，按照人数降序排列。输出职位，人数。

SELECT job_title, COUNT(*)
FROM employee
WHERE job_title IS NOT NULL
GROUP BY  job_title
ORDER BY  COUNT(*) DESC;

2.统计各个分类的产品数和平均价格，最高价，最低价，按照产品分类号排序。

SELECT category_id, COUNT(*), AVG(price), MAX(price), MIN(price)
FROM product
GROUP BY category_id
ORDER BY category_id;

3.统计部门编号dept_id为1000的部门的职位信息：部门编号，职位（职位以逗号分隔，按递增排序，以逗号分隔）。员工表employee

SELECT dept_id,GROUP_CONCAT(DISTINCT job_title)
FROM employee
WHERE dept_id='1000'
ORDER BY job_title;

GROUP_CONCAT函数返回一个字符串结果，该结果由分组中的值连接组合而成。

4.统计分类1000的产品数和平均价格。产品表：product

SELECT COUNT(*),AVG(price)
FROM product
GROUP BY category_id
HAVING category_id=1000;

5.查询统计2014年订单客户总数。现有订单表：orders

SELECT COUNT(DISTINCT customer_id)
FROM orders
WHERE YEAR(order_date) = 2014;

6.查询编号为[2] 的订单包含的产品ID（product_id）列表，输出订单ID，产品ID列表（以:分隔，并升序排序）。

订单明细表：order_detail

SELECT order_id ,GROUP_CONCAT(distinct product_id  ORDER BY product_id  ASC SEPARATOR ':')
FROM order_detail
WHERE order_id = 2 ;

saparator 分隔器

7.查询部门1000的人数。

员工表：employee

SELECT COUNT(*)
FROM employee
WHERE dept_id = 1000;

8.查询各个部门人数大于10人的，输出部门编号和人数，并按人数升序排序。

SELECT dept_id, COUNT(*)
FROM employee
GROUP BY dept_id
HAVING  COUNT(*) > 10
ORDER BY  COUNT(*) ASC;

9.查询统计产品“17”的订单号，每单平均订购数，和订单总数

SELECT product_id,AVG(buy_number),COUNT(*)
FROM order_detail
WHERE product_id = 17;

10.查询统计2016年订单客户总数

SELECT COUNT(DISTINCT customer_id)
FROM `order`
WHERE YEAR(order_date) = 2016;