补充作业一
设有三个关系:
S(SNO, SNAME, AGE, SEX,Sdept)
SC(SNO, CNO, GRADE)
C(CNO, CNAME, TEACHER)
试用关系代数表达式表示下列查询:
1、查询学号为S3学生所学课程的课程名与任课教师名。
2、查询至少选修LIU老师所教课程中一门课的女生姓名。
3、查询WANG同学不学的课程的课程号。
4、查询至少选修两门课程的学生学号。
5、查询选修课程中包含LIU老师所教全部课程的学生学号。
补充作业二
三个关系同上,试用SQL语言表示下列查询:
1、 查询门门课程都及格的学生的学号
方法1:
提示:根据学号分组,就得到每个学生所有的课程成绩,在某个学生这一组成绩里,如果他所有的课程成绩都大于60分则输出该组学生的学号
Select sno frome sc group by sno having(min(grade)>=60)
2、查询既有课程大于90分又有课程不及格的学生的学号
自身连接:
Select sno from sc where grade >90 and sno in (select sno from sc where grade<60)
3、查询平均分不及格的课程号和平均成绩
Select cno , avg(GRADE) from sc group by cno having avg(grade)<60
查询平均分及格的课程号和课程名
Select C.cno , Cname from SC,C where C.cno=SC.cno group by C.cno having avg(grade)>=60
4、找出至少选修了2号学生选修过的全部课程的学生
提示:不存在这样的课程y,学生2选修了y,而学生x没有选。
SELECT DISTINCT Sno
FROM SC as SCX
WHERE NOT EXISTS
(SELECT *
FROM SC as SCY
WHERE SCY.Sno =‘2’AND NOT EXISTS
(SELECT *
FROM SC SCZ
WHERE SCZ.Sno=SCX.Sno AND SCZ.Cno=SCY.Cno))
5、求各门课程去掉一个最高分和最低分后的平均分
第一步,求所有成绩的平均分(去掉一个最高分和最低分)
select avg(GRADE) from SC
where GRADE not in (select top 1 GRADE from SC order by GRADE)
and GRADE not in (select top 1 GRADE from SC order by GRADE desc)
第二步,将所有成绩按各门课程的课程号CNO分组
SELECT CNO avg(GRADE) from SC
where GRADE not in (select top 1 GRADE from SC order by GRADE)
and GRADE not in (select top 1 GRADE from SC order by GRADE desc) group by CNO
1、查询7号课程没有考试成绩的学生学号。
Select sno fromsc where cno='7' and grade is null
2、查询7号课程成绩在90分以上或60分以下的学生学号。
Select sno from sc where cno='7' and grade not between 60and 9
3、查询课程名以“数据”两个字开头的所有课程的课程号和课程名。
Select cno,cname from c where cname like '数据%'
4、查询每个学生所有课程的平均成绩,输出学生学号和平均成绩。
Select sno,avg(grade)from sc group by sno
5、查询每门课程的选修人数,输出课程号和选修人数。
Selectcno,count(*) from sc group by cno
6、查询选修7号课程的学生的学号、姓名、性别。
Selects.sno,sname,ssex from s,sc where s.sno=sc.sno and cno='7'
或:
Select sno,sname,ssex from s where sno in
( Select sno from sc where cno='7' )
7、查询选修7号课程的学生的平均年龄。
Selectavg(sage) from s,sc where s.sno=sc.sno and cno='7'
或:
Select avg(sage) from s where sno in
(Select sno from sc where cno='7' )
8、查询有30名以上学生选修的课程号。
Select cno fromsc group by cno having count(*)>30
9、查询至今没有考试不及格的学生学号。
Select distinctsno from sc where sno not in
( Select sno from sc where grade<60 )
或:
Select sno from sc group by sno havingmin(grade)>=60
补充三
1、找出选修课程号为C2的学生学号与成绩。
Select sno,grade from sc where cno='C2'
2、找出选修课程号为C4的学生学号与姓名。
Selects.sno,sname from s,sc where s.sno=sc.sno and cno='C4'
注意本题也可以用嵌套做
思考本题改为“找出选修课程号为C4的学生学号、姓名与成绩”后还能用嵌套做吗?
3、找出选修课程名为 Maths 的学生学号与姓名。
Selects.sno,sname from s,sc,c
where s.sno=sc.sno and c.cno=sc.cno andcname='Maths'
注意本题也可以用嵌套做
4、找出选修课程号为C2或C4的学生学号。
Select distinctsno from sc where cno in ('C2','C4')
或:
Select distinct sno from sc where cno='C2' or cno='C4'
5、找出选修课程号为C2和C4的学生学号。
Select sno fromsc where cno='C2' and sno in
( Select sno from sc where cno='C4' )
注意本题也可以用连接做
思考:
Select distinct sno from sc where cno=‘C2’ andcno='C4’正确吗?
6、找出不学C2课程的学生姓名和年龄。
Selectsname,sage from s where sno not in
( Selectsno from sc where cno='C2'
或:
Select sname,sage from s where not exists
(Select * from sc where sno=s.sno and cno='C2' )
7、找出选修了数据库课程的所有学生姓名。(同3)
Select snamefrom s,sc,c
where s.sno=sc.snoand c.cno=sc.cno and cname='数据库'
8、找出数据库课程不及格的女生姓名。
连接:Select sname from s,sc,c
where s.sno=sc.sno andc.cno=sc.cno and cname='数据库'
and grade<60 and ssex='女'
嵌套:Select sname from s where ssex='女' and sno in
(Select sno from sc where grade<60 and cno in
( Select cno from c where cname='数据库' )
)
9、找出各门课程的平均成绩,输出课程名和平均成绩。
Selectcname,avg(grade) <