虽然平时都是用SQLyog比较多,但学校要求这次用SQL Server就用吧,感觉其实用法差别也不大,当然有些关键字是不能用的
测试题
1.查询“c001”课程比“c002”课程成绩高的所有学生的学号;
select a.sno from student a
inner join sc b on a.sno=b.sno
inner join sc c on a.sno=c.sno
Where (b.cno='c001' and c.cno='c002') and b.score>c.score
2.查询平均成绩大于60 分的同学的学号和平均成绩
select sno,avg(score) from sc group by sno having avg(score)>60
3.查询所有同学的学号、姓名、选课数、总成绩;
select student.sno,student.sname,count(sc.cno)AS 选修课,sum(sc.score)AS 总成绩
from student left join sc on student.sno=sc.sno
group by student.sname,student.sno
4.查询姓“刘”的老师的个数;
select COUNT(tname) from teacher where tname like '刘_'
5.查询没学过“谌燕”老师课的同学的学号、姓名;
select student.sname,student.sno from student
EXCEPT
select student.sname,student.sno from sc join student on sc.sno = student.sno inner join course on sc.cno=course.cno where course.cno in
(select course.cno from course left join teacher on course.tno=teacher.tno where teacher.tname ='谌燕')
group by student.sname,student.sno
6.查询学过“c001”并且也学过编号“c002”课程的同学的学号、姓名;
select a.sno,a.sname from student a
inner join sc b on a.sno=b.sno
inner join sc c on a.sno=c.sno
where (b.cno='c001' and c.cno='c002')
7.查询学过“谌燕”老师所教的所有课的同学的学号、姓名;
not exists 不存在的
select student.sno,student.sname from student where not exists
(select course.cno from course where course.tno=(select teacher.tno from teacher where teacher.tname='谌燕')
and course.cno not in (select sc.cno from sc where sc.sno=student.sno))
8.查询课程编号“c002”的成绩比课程编号“c001”课程低的所有同学的学号、姓名;
select a.sno,a.sname from student a
inner join sc b on a.sno=b.sno
inner join sc c on a.sno=c.sno
Where (b.cno='c002' and c.cno='c001') and b.score<c.score
9.查询所有课程成绩小于60 分的同学的学号、姓名;
select distinct student.sno,student.sname from student left join sc on student.sno=sc.sno where sc.score<60
10.查询没有学全所有课的同学的学号、姓名;
select a.sno,a.sname from student a
group by a.sno,a.sname
having (select count(1) from sc where sc.sno=a.sno)!=(select count(1) from course)
11.查询至少有一门课与学号为“s001”的同学所学相同的同学的学号和姓名;
select distinct student.sno,student.sname from student
inner join sc on student.sno=sc.sno
where sc.cno in(select sc.cno from student inner join sc on student.sno=sc.sno where student.sno='s001')