10-381 查询选修了2门以上课程的学生学号和平均成绩。

-- select sno as 学号, cast(avg(grade) as decimal(10,4)) as 平均分 select sno as 学号, round(avg(grade),4) as 平均分 from score group by sno having count(*) >= 2 

10-382 查询商品相关信息（多表查询）

讯享网select a.id as gid,a.name as gname,category_id as cid,b.name as cname from sh_goods as a left join sh_goods_category as b on a.category_id = b.id

10-383 查询五星商品对应的商品分类信息（多表查询）

select a.id as gid,a.category_id as cid,b.name as cname,score from sh_goods as a left join sh_goods_category as b on a.category_id = b.id where score = 5

10-384 查询商品分类id为10或11的商品对应的商品信息（多表查询）

讯享网select a.id as gid,a.name as gname,b.id as cid,b.name as cname from sh_goods as a right join sh_goods_category as b on a.category_id = b.id where b.id in (10,11)

10-385 获取指定商品的商品分类名称（多表查询）

select name from sh_goods_category where id = (select category_id from sh_goods where id = 5)

10-386 查询商品价格小于500的商品分类名称（多表查询）

讯享网select name from sh_goods_category as a where id in ( select category_id from sh_goods where price < 500 )

10-387 查询指定分类下符合条件的商品信息

select id,name,price from sh_goods where price < (select min(price) from sh_goods where category_id = 8) and category_id = 3

10-388 创建视图，包含商品打折前后的价格信息

讯享网create view view_goods -- (id,name,old_price,new_price) as select id,name,price as old_price,price*0.5 as new_price from sh_goods;

10-389 创建视图，包含拥有属性值个数大于1的商品信息

create view view_goods_2 as select id,name from sh_goods where id in( select goods_id from sh_goods_attr_value group by goods_id having count(*) > 1 )

10-390 检索出生日期早于伍容华出生日期（不包含此日期）的所有学生信息。

讯享网

讯享网select * from student where birth < (select birth from student where sname = '伍容华')

10-391 检索被学生选修的课程编号和课程名称。

select distinct cno,cname from course where cno in (select cno from score)

10-392 检索没被学生选修的课程编号和课程名称。

讯享网select distinct cno,cname from course where cno not in (select cno from score)

10-393 检索选修了三门课以上的学生学号、姓名、院部

select sno,sname,dept from student where sno in ( select sno from score group by sno having count(*)>=3 )

10-394 查询每门必修课的课程编号，课程名称和选修人数。

讯享网select a.cno,cname,count(sno) as total from course as a left join score as b on a.cno=b.cno where attribute='必修' group by a.cno;

10-395 检索蒙族学生选修的课程编号、课程名称。

select cno,cname from course where cno in ( select cno from score where sno in ( select sno from student where nation = '蒙' ) )

10-396 查找所有“大学语文”分数在80以上的学生的姓名，所在院系

讯享网select sname,dept from student where sno in ( select sno from score where cno = (select cno from course where cname = '大学语文' and grade > 80) )

10-397 在教师表中查询出所有教师所在部门编号，并消除重复记录。

select distinct DepartmentID from Teacher

10-398 查询所有教师信息,按教师编号升序排列

讯享网select * from Teacher order by TeacherID 

10-399 查询出出生日期最大（即年龄最小）的学生姓名及出生日期

select StudentName,Birth from Student where Birth = (select max(Birth) from Student)

10-400 查询学生人数大于5人的班级编号

讯享网select ClassID from Class where studentnum > 5