课外天地 李树青学习天地数据库系统原理课件 → 查询练习——学生信息


  共有20168人关注过本帖平板打印复制链接

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

帅哥哟,离线,有人找我吗?
admin
  1楼 博客 | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信 管理员
等级:管理员 帖子:1945 积分:26736 威望:0 精华:34 注册:2003/12/30 16:34:32
查询练习——学生信息  发帖心情 Post By:2006/4/4 21:58:51 [只看该作者]

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='黎明')

[此贴子已经被作者于2006-4-4 22:02:37编辑过]

 回到顶部