数据库练习及答案

//练习1
1.查询计算机系的所有老师
select Tname
from T
where TDept='计算机系'

2.查询所有女同学的姓名和年龄
select Sname,Sage
from S
where Sex='女'

3.查询计算机系教师开设的所有课程的课号和课名
select o,ame
from C,T
where C.Tno=T.Tno
and T.TDept='计算机系'

4.查询所有女同学所选课程的课程号
select o
from SC,S
where SC.Sno=S.Sno
and S.Sex='女'

5.查询至少有一门课程的成绩高于90分的学生的姓名和年龄
select distinct S.Sname,S.Sage
from S,SC
where S.Sno=SC.Sno
and SC.grade>90

6.查询选修“数据库原理“的所有学生的姓名和成绩
select S.Sname,SC.grade
from S,SC,C
where S.Sno=SC.Sno
and o=o
and ame='数据库原理'


7@查询未选修“VB“的学生的学号和姓名
select Sno,Sname
from S
where Sno not in
( select Sno from SC where Cno in
(
select Cno from C where Cname='VB'
)
)

8.@查询不是计算机系教师所讲授的课程的课名和课号
select Cname,Cno
from C
where Tno not in
(
select Tno
from T
where TDept='计算机系'
)

9.查询未选修“21“号课的学生的学号和课号
select SC.Sno,o
from SC,C
where o=o
and SC.Sno not in
(
select Sno from SC where Cno='21'
)

10.查询年龄在18~20岁（包括20和18）之间的所有学生的信息
select S.*,SC.grade
from S,SC
where S.Sno=SC.Sno
and S.Sage>=18
and S.Sage<=20

11.@查询至少选修“21“和”41“两门课程的学生的学号
select Sno
from SC
where Cno='21'
and Sno in (
select Sno
from SC
where Cno='41'
)

12.查询选修王军老师所讲授课程的学生的学号和成绩
select S.Sno,SC.grade
from SC,S
where SC.Sno=S.Sno
and o in (
select Cno
from C,T
where C.Tno=T.Tno
and T.Tname='王军'
)

//练习2
1.@查询至少选修了两门课程的学生的学号
select Sno
from SC
group by Sno
having count(*)>=2

2.查询选修高等数学课程的人数
select count(*)
from SC,C
where o=o
and ame='高数'

3.查询高等数学课程的平均分
select AVG(SC.grade)
from SC,C
where o=o
and ame='高数'

4.查询李姓的所有学生的姓名、年龄和性别
select S.Sname,S.Sage,S.Sex
from S
where S.Sname like '李%'

5.查询学号比‘陆文’同学大而年龄比他小

的学生姓名
select S.Sname
from S
where S.Sno>(select Sno from S where Sname='陆文')
and S.Sage<(select Sage from S where Sname='陆文')

6.查询年龄大于所有女同学平均年龄的男学生的姓名和年龄
select S.Sname,S.Sage
from S
where S.Sage>(select AVG(Sage) from S where S.Sex='女')
and S.Sex='男'

7.@查询只选了一门课程的学生的学号和姓名
select Sno,Sname
from S
where Sno in
(
select Sno
from SC
group by Sno
having count(*)=1
)

8.@将每一门课程的成绩均大于等于80分的学生的学号、姓名和性别插入到
另一个已经存在的基本表STU（S#，SNAME，SEX）中
insert into STU(S#,SNAME,SEX)
select S.Sno,S.Sname,Sex
from S,SC
where SC.Sno=S.Sno
and S.Sno not in
(
select Sno
from SC
where grade<80
)

9.@将英语课程的任课教师号修改为412
update C
set Tno=412
where Cname='英语'

10.@将低于总平均成绩的女同学的成绩提高5%
update SC
set grade=grade*1.05
where grade< (
select AVG(grade) from SC
)
and Sno in (
select Sno from S where Sex='女'
)

11.@从基本表C中删除张梅老师的任课信息
delete from C
where Tno in
(
select Tno from T where Tname='张梅'
)

12.@查询只少讲授两门课程的教师姓名和其所在的系
select Tname,TDept
from C,T
where C.Tno=T.Tno
group by Tname,TDept
having count(*)>=2