查询bus数据库

统计每一家公交公司所属线路的站点总数

SELECT
	 company 公司,SUM(number) 总数,COUNT(*) 路线
FROM
	line
	GROUP BY company
	ORDER BY COUNT(*)

计算经过站点“解放大道古田四路”的公交线路的数量以及营运车辆数量

SELECT
SUM(number) 运营车辆数,COUNT(*) 线路数 
FROM
	line,
	line_station
WHERE
line.lineNo=line_station.lineNo
	AND station='解放大道古田四路'

3. 计算每条公交线路的司机人数，并显示司机人数大于 3 的分组信息，按司机人数逆序显示；

SELECT
	lineNo,
	COUNT(*) 司机数 
FROM
	line l,
	vehicle v,
	driver d 
WHERE
	l.lineID = v.lineID 
	AND d.driverID = v.driverID 
GROUP BY
	lineNo HAVING COUNT(*)>3
ORDER BY
	司机数 desc

查询easyshopping数据库，实现如下需求：
-- 1. 统计每件商品的销售数量和销售金额，要求按照销售量和销售金额升序显示商品名、销售量和销售金额

SELECT
	goodsName 商品名,
	SUM(quantity) 销售量,
	SUM(quantity*unitPrice) 销售金额 
FROM
	goods
	LEFT JOIN ordersdetail od ON goods.goodsID = ordersdetail.goodsID 
GROUP BY
	goodsID 
ORDER BY
	销售量,销售金额

2. 计算每个订单的金额，要求按照订单下单日期升序和订单金额降序显示订单 ID、订单下单日期、订单金额和顾客姓名

SELECT
	o.driverID 订单,
	o.ordersDate 下单日期,
	SUM(od.quantity * g.unitPrice) 订单金额,
	c.cName 客户名 
FROM
	orders o,
	ordersdetail od,
	goods g,
	customer c 
WHERE
	o.ordersID = od.ordersID 
	AND od.goodsID = g.goodsID 
	AND o.customerID = c.customerID 
GROUP BY
	o.ordersID 
ORDER BY
	下单日期,订单金额 desc