【正确答案】select sno, sum (grade) as/总成绩，avg (grade) as/平均成绩，count (*) as/选课门数 from sc
group by sno having count (*) ＞2
【答案解析】[解析] 本题考查的是SQL操作。
问答题
列出总成绩超过200分的学生，要求列出学号、总成绩。
【正确答案】select sno, sum (grade) 总成绩 from sc
group by sno having sum (grade) ＞200
【答案解析】
问答题
查询选修了“C02”号课程的学生的姓名和所在系。
【正确答案】select sname, sdept from Student join SC on Student. Sno =SC. Sno
where cno= 'C02'
【答案解析】
问答题
查询成绩在80分以上的学生的姓名、课程名和成绩，并将结果按成绩的降序排列。
【正确答案】select sname, cno, grade
from student s join sc on s. sno=sc. sno
where grade ＞ 80
Order by grade desc
【答案解析】
问答题
查询计算机系男生修了“数据库基础”的学生的姓名、姓名和成绩。
【正确答案】select sname，ssex, grade
from student s join sc on s. sno=sc. sno
join course c on c. cno=sc. cno
where sdept= '计算机系' and ssex= '男'
and cname= '数据库基础'
【正确答案】select s. sno, sname, cno, grade from Student s left join SC
on s. Sno=SC. Sno
【答案解析】
问答题
列出“数据库基础”课程考试成绩前三名的学生的学号、姓名、所在系和考试成绩。
【正确答案】select top 3 s. sno, sname, sdept, grade
from Student s join sc on s. Sno=sc. Sno
join Course c on c. Cno = sc. Cno
where cname = '数据库基础'
order by grade desc
【答案解析】
问答题
查询哪些学生合选了一门课程，要求列出合选课程的学生的学号和课程号。
【正确答案】select t1. sno, t2. sno, t1.cno
from sc as t1 join sc as t2
on t1. cno = t2. cno
where t1. sno ＜ t2. sno
【答案解析】
问答题
查询哪些课程没人选，要求列出课程号和课程名。
【正确答案】select c. cno, cname from course c left join sc
on c. cno = sc. cno
where sc. cno is null
【答案解析】
问答题
查询计算机系学生考试成绩高于全体学生的总平均成绩的学生的姓名、考试的课程号和考试成绩。
【正确答案】select sname, cname, grade
from student s join sc on s. sno = sc. sno
join course c on c.cno = sc. cno
where sdept ='计算机系'
and grade ＞ (select avg(grade) from sc)
【答案解析】
问答题
查询VB考试成绩最低的学生的姓名、所在系和VB成绩。
【正确答案】select sname, sdept, grade
from student s join sc on s. sno = sc. sno
join course c on c. cno = sc. cno
where grade = (
select min(grade) from sc
where cno in (
select cno from course where cname ='vb'))
and cname = 'vb'
【正确答案】select s. sno 学号,sname 姓名,
case sdept
when'计算机系'then'CS'
when'信息系'then'IS'
when'数学系'then'MA'
else'OTHER'
end as 所在系,grade 成绩
from student s join sc on s. sno = sc. sno
join course c on c. cno = sc. cno where cname = 'vb'
【正确答案】select c. cno,
case
when count(sc. cno) ＞ 100 then'人多'
when count(sc. cno) between 40 and 100 then'一般'
when count(c. cno) ＜ 40 then'较少'
when count(sc. cno) = 0 then'无人选'
end as
选课人数
from sc right join course c on sc. cno = c. cnogroup by c. cno
【正确答案】insert into Course values('C100','Java',32,'选修')
【答案解析】
问答题
删除修课成绩小于50分的学生的修课记录。
【正确答案】delete from sc where grade＜50
【答案解析】
问答题
删除信息系修课成绩小于50分的学生的修课记录。
【正确答案】(a)用连接查询实现
delete from sc from sc join student s on s. sno = sc. sno
where sdept ='信息系'and grade ＜ 50
(b)用子查询实现
delete from sc where sno in (
select sno from student where sdept = '信息系')
and grade ＜ 50
【答案解析】
问答题
将所有选修了“C01”号课程的学生的成绩加10分。
【正确答案】update sc set grade = grade + 10
where cno = 'c01'
【答案解析】
问答题
将计算机系所有选修了“计算机文化学”课程的学生的成绩加10分。
【正确答案】(a)用子查询实现
update sc set grade = grade + 10
where sno in (
select sno from student where sdept = '计算机系')
and cno in (
select cno from course where cname = '计算机文化学')
(b)用连接实现
update sc set grade = grade - 10
from student s join sc on s. sno = sc. sno
join course c on c. cno = sc. cno
where sdept ='计算机系'and cname ='计算机文化学'