主题：查询练习——学生信息

1）查找和黎明身高（年龄）一样的其他学生信息 1.1 select number,name,sex from stu where height=(select height from stu where name='黎明') and name<>'黎明'

1.2 select number,name,sex from stu where year(birthday)=(select year(birthday) from stu where name='黎明') and name<>'黎明'

2）列出男女生各自不同身高的人数分布 2.1 select height,count(*) from student where sex=1 group by height select height,count(*) from student where sex=0 group by height

2.2 select height,count(*) from stu where sex=1 group by height union select height,count(*) from stu where sex=0 group by height

2.3 select sex,CEILING(height*100),count(*) as 'count' from stu group by sex,height

3）查找黎明选修的课程和成绩 3.1 select course,grade from stu,grade where stu.number=grade.number and name='黎明'

3.2 select course,grade from stu inner join grade on stu.number=grade.number and name='黎明'

4）查找身高最矮的学生姓名 4.1 select name,height from stu where height=(select min(height) from stu)

5）查找每个学生的选修课程数和平均成绩 5.1 select course,count(*),avg(grade) from grade group by course

6）查找没有被学生选修的课程 6.1 select course from course where course not in(select course from grade)

7）查找选修了至少一门和黎明选修课程一样的学生 7.1 select number from grade where course in (select course from grade where number in (select number from stu where name='黎明')) and number<>(select number from stu where name='黎明')

7.2 select number from grade where course in ( select course from grade inner join stu on stu.number=grade.number and name='黎明' ) and number<>(select number from stu where name='黎明')