select sname,score from student,s_c,course where student.sno=s_c.sno and s_c.cno=course.cno and cname=毛概 and score isnotnull orderby score desc
查询所有科目分数总和大于1100的学生姓名与分数总和
select sname,sex,SUM(score) from student,s_c where student.sno=s_c.sno groupby sname,sex havingSUM(score)>1100
查询年龄在20~23的学生
select sname,age from student where age between20and23
查询年龄不在20~23的学生
select sname,age from student where age notbetween20and23
查询第2,3,4组的学生姓名与性别
select sname,sex from student where gno in(2,3,4)
查询不在2,3,4组的学生
select sname,sex from student where gno notin(2,3,4)
查询不在2,3,4组的姓刘的同学
select sname,sex from student where gno notin(2,3,4) and sname like刘%
查询不在2,3,4组且名字第二个字为‘金’的同学
select sname,sex from student where gno notin(2,3,4) and sname like\_金%
查询所有不姓刘但是名字第二个字为’金’的同学所有信息
select * from student where sname notlike刘%and sname like\_金%
查询成绩为空的学生姓名
selectdistinct sname from student,s_c where s_c.sno=student.sno and s_c.score isnull
查询选修毛概的学生的姓名成绩,并且升序输出
select sname,score from student,s_c,course where student.sno=s_c.sno and s_c.cno=course.cno and cname=毛概 and score isnotnull orderby score desc
查询所有科目分数总和大于1100的学生姓名与分数总和
select sname,sex,SUM(score) from student,s_c where student.sno=s_c.sno groupby sname,sex havingSUM(score)>1100
用嵌套查询,查询所有选修了计网实验的学生姓名
select sname from student where sno in( select sno from s_c where cno in( select cno from course where cname=计网实验 ) )
查询与王艺璇所在同一组的其他同学姓名性别
select sname,sex from student where gno in ( select gno from student where sname=王艺璇 ) and sname!=王艺璇
找出每个学生超过自己课程平均分的课程名字
select sname,cname from student,course,s_c x where score>( selectAVG(score) from s_c y where y.sno=x.sno ) and student.sno=x.sno and x.cno=course.cno
实验八
求学生的总人数
selectCOUNT(*) from student
求选修了课程的学生人数
selectCOUNT(student.sno) from student whereexists( select * from student_course where student.sno=student_course.sno )
求课程的课程号和选修该课程的人数。
select tcid,COUNT(student_course.sno) from student_course groupby tcid
求选修课超过3 门课的学生学号
select sno from student_course groupby sno havingCOUNT(tcid)>3
11-05更新
求选修了高等数学的学生学号和姓名(生成的课程数据中没有高等数学,改为数据库)
selectdistinct student.sno,sname from student,course,student_course,teacher_course where course.cname=数据库and course.cno=teacher_course.cno and student.sno=student_course.sno
求数据库课程的成绩高于张三的学生学号和成绩
select sno,score from student_course sc_a where sc_a.tcid in( select tcid from teacher_course where cno=( select cno from course where cname=数据库 ) ) and score>( selectdistinct score from student_course sc_b where sno=( select sno from student where student.sname=张三22 ) and sc_b.tcid in ( select tcid from teacher_course where cno=( select cno from course where cname=数据库 ) ) )
求其他系中比计算机系某一学生年龄小的学生信息(即求其它系中年龄小于计算机系年龄最大者的学生)
select * from student where student.dno!=计算机and student.birthday > ( selectmin(birthday) from student b where b.dno=计算机 )
求其他系中比计算机系学生年龄都小的学生信息
select * from student where student.dno!=计算机and student.birthday > ( selectMAX(birthday) from student b where b.dno=计算机 )
求选修了数据库课程的学生姓名
select sname from student where sno in ( selectdistinct sno from student_course where tcid in( select tcid from teacher_course where cno=( select cno from course where cname=数据库 ) ) )
求没有选修数据库课程的学生姓名
select sname from student where sno notin ( selectdistinct sno from student_course where tcid in( select tcid from teacher_course where cno in ( select cno from course where cname = 数据库 ) ) )
查询选修了全部课程的学生的姓名
select sname from student wherenotexists( select * from course wherenotexists( select * from teacher_course,student_course where student.sno=student_course.sno and student_course.tcid=teacher_course.tcid and course.cno=teacher_course.cno ) )
求至少选修了学号为101的学生所选修的全部课程的学生学号和姓名。
select sno,sname
from student
where sno in(
select sno
from student_course sc1
where not exists(
select *
from student_course sc2
where sc2.sno=101 and not exists(
select *
from student_course sc3
where sc3.sno=sc1.sno and
sc3.tcid=sc2.tcid
)
)
)