From:http://jinl007.spaces.live.com/blog/cns!ea97e38ff8737716!174.entry
列出各系的学生数(人数多的排在前面)
1) Sql语句:select sdept,count(*) as dept_student_number from student
group by sdept order by dept_student_number desc
2) 执行结果:
2) 找出各科成绩均在85分以上(含)的学生的学号、姓名
1) Sql语句:select sno,sname from student where sno in(select sno from sc group by sno having min(grade)>=85)
2) 执行结果:
3) 找出有三门课程的成绩在75分以下的学生的学号、姓名
1) Sql语句:select sno,sname from student where sno in(select sno from sc where grade<75 group by sno having (count(grade) =3 ))
2) 执行结果:
4) 列出数据库成绩的前五名学生的学号、姓名、成绩
1) Sql语句:select top 5 student.sno,sname,grade from student,sc where student.sno=sc.sno and cno=(select cno from course where cname='数据库') order by grade desc
另解
From:http://www.programfan.com/club/showtxt.asp?id=207466
student(sno,sname,sage,ssex,sdept)
course(cno,cname,credit)
sc(sno,cno,grade)
注明:credit是学分
student里的sno和course里cno分别sc里的sno和cno的外键
1)找出各科成绩均在85分以上(含)的学生的学号、姓名
2)找出有三门课程的成绩在75分以下的学生的学号、姓名
3)列出数据库成绩的前五名学生的学号、姓名、成绩
1)select distinct b.sno,b.sname
|