select sno,max(degree)as maxdegree,min(degree) as mindegree from Score group by sno having max(degree)<90 and min(degree)>70
-- 14、查询所有学生的Sname、Cno和Degree列。 select sname,cno,degree from student join score on student.sno=score.sno;
-- 15、查询所有学生的Sno、Cname和Degree列。 select sno,cname,degree from Score join course on Score.cno=course.cno;
-- 16、查询所有学生的Sname、Cname和Degree列。 select sname,cname,degree from student join score on student.sno=score.sno join course on Score.cno=course.cno;
-- 17、查询“95033”班所选课程的平均分。
select avg(degree) as avgdegree from score where sno in(select sno from student where class='95033') 18、假设使用如下命令建立了一个grade表：
create table grade(low int(3),upp int(3),rank char(1)) insert into grade values(90,100,’A’) insert into grade values(80,89,’B’)
数据库
wzm
insert into grade values(70,79,’C’) insert into grade values(60,69,’D’) insert into grade values(0,59,’E’) --现查询所有同学的Sno、Cno和rank列。 select sno,cno,
(case when degree between 90 and 100 then 'A' when degree between 80 and 89 then 'B' when degree between 70 and 79 then 'C' when degree between 60 and 69 then 'D' when degree between 0 and 59 then 'E' END) as rank from score;
-- 19、 查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。 select * from score where cno='3-105'and degree>(select degree from score where sno='109' and cno='3-105');
-- 20、查询score中选学多门课程的同学中分数为非最高分成绩的记录。 select * from score where sno in--选学多门课程的同学中分数为非最高分成绩的同学的全记录 (select sno from score group by sno having count(cno)>1--选学多门课程的同学 intersect--取交集为选学多门课程的同学中分数为非最高分成绩的同学。
数据库 wzm
select distinct sno from score where sno not in( --分数为非最高分成绩的同学 select sno from score where degree=(select max(degree) from score)))--分数最高成绩的同学
-- 21、查询score中选学多门课程的同学中分数为非同课程最高分成绩的记录。 方法1：select * from score where sno in--选学多门课程的同学中分数为非同课程最高分成绩的同学的全记录 (select sno from score group by sno having count(cno)>1--选学多门课程的同学 intersect--取交集为选学多门课程的同学中分数为非同课程最高分成绩的同学。 select distinct sno from score where sno not in(--非同课程分数最高成绩的同学 select distinct sno from score where degree in (--同课程分数最高成绩的同学 select max(degree)from score group by cno)))--同课程分数最高成绩 方法2：select * from score where sno in--选学多门课程的同学中分数为非同课程最高分成绩的同学的全记录 (select sno from score group by sno having count(cno)>1-- 选学多门课程的同学 intersect -- 取交集为选学多门课程的同学中分数为同课程非最高分成绩的同学 select distinct sno from score where sno not in -- 选出非同课程最高分成绩的同学
数据库
wzm
(select distinct sno from score as s1
where degree=(select max(degree) from score as s2 where s1.cno=s2.cno group by cno)));-- 使用关联子查询选出同课程最高分成绩的同学
-- 22、查询1975年之后出生的学生的所学课程以及成绩。 select sname,Cname,degree from student join score on student.sno=score.sno join course on score.cno=course.cno where sbirthday>='1975-01-01';
-- 23、查询和学号为107的同学同年出生的所有学生的Sno、Sname和Sbirthday列。 select sno,sname,sbirthday from student where datepart(year,sbirthday)=
(select datepart(year,sbirthday) from student where sno='107')--学号为107的同学的出生年份 and sno not in('107');--排除学号为107的同学
-- 24、查询“张旭”教师任课的学生成绩。 select degree from score where cno= (select cno from course
join teacher on teacher.tno=course.tno where tname='张旭');--张旭老师所任课程
数据库
wzm
-- 25、查询选修某课程的同学人数多于5人的教师姓名。 select tname from teacher
join course on teacher.tno=course.tno where cno in (select cno from score
group by cno having count(*)>5);-- 多于5名同学选修的课程
-- 26、查询95033班和95031班全体学生的记录。 select * from student where class in('95033','95031');
-- 27、查询存在有85分以上成绩的课程Cno. select distinct cno from score where degree>85;
-- 28、查询出“计算机系”教师所教课程的成绩表。 select score.cno,degree from score join course on score.cno=course.cno where tno in
(select tno from teacher where depart='计算机系');--计算机系教师的教师编号
-- 29、查询“计算机系”与“电子工程系”不同职称的教师的Tname和Prof。 select tname,prof from teacher where depart in('计算机系','电子工程系')--“计算机系”与“电子工程系”所有教师Tname和Prof
数据库
wzm