复杂的sql语句

以下练习数据库表自己设计，完成功能。
1）有一个商品表，里面有商品id，商品名称，进货价，出货价。
（1）统计每个商品的利润。
（2）求利润最高的商品的名称以及它的利润。

2)、查出学生表中每个班级学生的人数？
正确 select class,count(class) from student group by class having count(class)>10;
正确 select class,count(class) from student group by class having count(id)>10;
错误 select class,count(class) from student group by class having count(studentno)>10;

3)、查出学生表中哪个月份入学的学生最多？ 数量是多少？
以下两个方法都没有考虑月份之间相同的学生数：
select
MONTH(recruit) as ‘月份’,
count(MONTH(recruit)) as ‘人数’
from student
GROUP BY MONTH(recruit)
order by count(MONTH(recruit))
desc LIMIT 0,1; – 推荐用别名

select list.`月份`,max(list.`人数`) from (
select 
MONTH(recruit) as '月份',
count(MONTH(recruit)) as '人数' 
from student 
GROUP BY MONTH(recruit) 
) as list; -- 不取别名不会报错，但是JDBC取值不方便

4)、查出全院学生的平均年龄？
select avg(age) from student where colleageid=1;

5)、查出年龄低于平均年龄的学生信息?
select id,name from student where age<(select avg(age) from student where colleageid=1)

6)、查找来自山西，姓王的学生，名字只有2个汉字，根据id从大到小排序
select * from student where address like ‘%山西%’ and substring(name,1,1)=‘王’ and charactor_length(name)=2 order by id desc;
select * from student where name like ‘王_’ and address like ‘%山西%’ ;

7)、查找名字是3个汉字并且中间字为“胜”的学生信息
select * from student where charactor_length(name)=3 and substring(name,2,1)=‘胜’;

8)、自己设计程序，比较io的写入文本的速度和写入数据库的速度。例如：循环写入1万条学生信息。

9)、订单表：ID,订单日期,订单金额,订货人姓名,备注4个字段：
（A）创建该表
（B）写SQL，获得每个订货人最近一次的订货日期、订货金额
select f.merchantname, f.date, orders.price
from (select merchantname, max(date) date from orders group by merchantname) f, orders where f.merchantname = orders.merchantname and f.date = orders.date;
（C）写SQL，获得每个订货人总的订货总次数、总的订货金额
select merchantname,count(merchantname),sum(price) from orders group by merchantname;
（D）写SQL，获得每个订货人总的订货总次数、最近一次订货日期
select merchantname,count(merchantname),max(date) from orders group by merchantname;