实验四、SQL语言——SELECT查询操作

二、实验内容和主要知识点（预习实验示例，事先上机验证实验示例，同时按上课进度，完成布置的实验内容）

编写查询SQL语句，上机验证，并在CG平台评测通过

(1)学生表Student，由学号(Sno)、姓名(Sname)、性别(Ssex)、年龄(Sage)、所在系(Sdept)五个属性组成，记作：Student(Sno,Sname,Ssex,Sage,Sdept)，其中主码为Sno。

(2)课程表Course，由课程号(Cno)、课程名(Cname)、先修课号(Cpno)、学分(Ccredit)四个属性组成，记作：Course(Cno,Cname,Cpno,Ccredit)，其中主码为Cno。

(3)学生选课SC，由学号(Sno)、课程号(Cno)、成绩(Grade)三个属性组成，记作：SC(Sno,Cno,Grade)，其中主码为(SNO,CNO)。

基于“教学管理”数据库JXGL，试用SQL的查询语句表达下列查询：

1.检索至少选修一门课程的女学生姓名。

SELECT Sname

FROM SC,student

WHERE SC.SNO=STUDENT.SNO AND SSEX='女'

group by sname

having count (*)>=1

2.检索王同学不学的课程的课程号。

select cno

from course

where cno not in

(select cno

 from sc,student

where sc.sno=student.sno and student.sname like '王%'

3.检索全部学生都选修的课程的课程号与课程名。

select cno ,cname

from course

where not exists

(select *

 from student

 where not exists

 (select *

  from sc

  where sc.sno=student.sno and sc.cno=course.cno

  )

 )

4.检索选修了所有3学分课程的学生学号。

select distinct sno

from sc x

where not exists

(select *

 from course

 where ccredit=3 and not exists

 (select *

  from sc y

  where x.sno=y.sno and course.cno=y.cno))

5.统计所有（每个）学生选修的课程门数，要求显示学号和课程门数，包括没有选课的学生。

select student.sno,count(cno)

from student left outer join sc on student.sno=sc.sno

group by student.sno

6.求选修课程号为4课程的学生的平均年龄。

select avg(sage)

from student

where sno in

(select distinct sno

 from sc

 where cno='4')

7.求学分为3的每门课程的学生平均成绩。

select sc.cno,avg(grade)

from course inner join sc on course.cno =sc.cno

where ccredit =3

group by sc.cno

8.检索学号比王非同学大，而年龄比他小的学生姓名。 

select sname

from student

where sno>(select sno from student where sname='王非') and sage <(select sage from student where sname='王非')

9.求年龄大于女同学平均年龄的男学生姓名和年龄。

select sname,sage

from student

where sage>(select avg(sage) from student where ssex='女') and ssex='男'

10.求年龄大于所有女同学年龄的男学生姓名和年龄。

select sname,sage

from student

where sage>(select max(sage) from student where ssex='女') and ssex='男'

11.检索所有比“王华”年龄大的学生姓名、年龄和性别。

select sname,sage,ssex

from student

where sage>(select sage from student where sname='王华')

12.检索选修4门以上课程的学生总成绩(不统计不及格的课程)，并要求按总成绩的降序排列出来。

select sno,sum(grade)

from sc

where sno in

(select sno

 from sc

 group by sno

 having count(cno)>4 )and grade>=60

 group by sno

设有如下4个基本表：

STUDENT(学生表)，由SNO，SNAME，SEX，AGE，CLASS 组成

TEACHER(教师表)由TNO,TNAME,SEX,AGE,PROF,DEPT组成

COURSE(课程表) 由CNO,CNAME,TNO组成

SC (成绩表)由SNO,CNO,GRADE组成

1.查询选修'8105'课程的成绩高于'980302'号同学成绩的所有同学的记录（学号和成绩）。

select sno,grade

from sc

where cno='8105' and grade>(select grade from sc where sno='980302'and cno='8105');

2.查询与学号为980103的同学同岁的所有学生的SNO、SNAME和AGE。

select SNO,SNAME,AGE

from student

where age=(select age from student where sno='980103')

3.查询“钱军”教师任课的课程号，选修其课程学生的学号和成绩。

select distinct course.cno , sno,grade

from course ,sc,teacher

where sc.cno=course.cno and  course.cno in

(

select course.cno

from course,teacher

where teacher.tno=course.tno and tname='钱军'

);

4.查询选修某课程的学生人数多于20人的教师姓名。

select distinct tname

from teacher,sc,course

where teacher.tno=course.tno and sc.cno=course.cno

group by sc.cno,tname

having count(sno)>20

5.查询同学选修编号为“8105”课程且成绩至少高于其选修编号为“8245”课程的同学的SNO及“8105”课程成绩（CNO、SNO、GRADE）,并按成绩从高到低次序排列。

select x.cno,x.sno,x.grade as '8105课程成绩'

from sc x

where x.cno='8105' and x.grade>

(select y.grade

 from sc y

 where y.cno='8245' and y.sno=x.sno)

order by 3 desc

6.查询选修编号为“8105”课程且成绩高于所有选修编号为“8245”课程成绩的同学的CNO、SNO、GRADE。

select cno,sno,grade

from sc

where cno='8105' and grade >all

(select grade

 from sc

 where cno='8245')

7.查询成绩比该课程平均成绩高的学生的成绩表。

select x.sno,x.cno,x.grade

from sc x

where x.grade>

(select avg(y.grade)

 from sc y

 where x.cno=y.cno

 group by y.cno)

8.列出所有任课教师的TNAME和DEPT。

select distinct Teacher.TNAME,Teacher.DEPT

from Teacher

inner join Course on Course.TNO = Teacher.TNO

where Course.TNO in (

select distinct Course.TNO

from Course)

9.列出所有未讲课教师的 TNAME 和 DEPT所有老师都讲课，没有结果

select Teacher.TNAME,Teacher.DEPT

from Teacher

where Teacher.TNO not in (

select distinct Course.TNO

from Course)

10.查询每门课最高分的学生的SNO、CNO、GRADE。

select s1.SNO,s1.CNO,s1.GRADE

from SC as s1

where s1.GRADE IN (

select max(s2.GRADE)

from SC as s2

where s1.CNO = s2.CNO)

11.查询与“李华”同性别并同班的同学SNAME。

select SNAME

from Student

where SEX = (

select SEX

from Student

where SNAME = '李华')

and CLASS = (

select CLASS

from Student

where SNAME = '李华')

and sname <>'李华'

12.查询“女”教师及其所上的课程。（女教师所有信息，以及所授课程的课号和课程名）

select teacher.tno,tname,sex,age,prof,dept,cno,cname

from teacher

inner join course on course.tno = teacher.tno

where teacher.sex = '女'

13.查询选修“数据库系统”课程的“男”同学的成绩表(学号、姓名、成绩)

select sc.sno,sname,grade

from sc,student,course

where sc.sno=student.sno and sc.cno=course.cno and sex = '男' and cname = '数据库系统'

14.查询所有比刘涛年龄大的教师姓名、年龄和刘涛的年龄。

select x.tname,x.age,y.age

from teacher x,teacher y

where x.age>y.age and y.tname='刘涛'

15.查询不讲授“8104”号课程的教师姓名。

select tname

from teacher

except

select tname

from teacher inner join course on teacher.tno=course.tno

where cno='8104'

三、实验体会

对SQL命令有了更进一步的了解，对各种查询语句有了更深刻的领会。

In查询：用于过滤你所需要查询的内容： 

select 字段名 from 表名 where 字段名 in（具体内容）

分组查询：查询的每个分组中首次出现的一条记录

select 字段名 from 表名 group by 待分组的字段名