SQL练习题

-- 9、 查询“95031”班的学生人数。
select count(1) from student where class = 95031;
-- 10、查询Score表中的最高分的学生学号和课程号。
select sno, cno from score where degree = (select max(degree) from score );
-- 11、查询‘3-105’号课程的平均分。
select AVG(degree) from score where cno = '3-105';
-- 12、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。
select avg(degree),cno from score where cno like '3%' group by cno having count(sno) > 5;
-- 13、查询最低分大于70，最高分小于90的Sno列。
select sno from score group by cno having min(degree) > 70 and max(degree) < 90;
-- 14、查询所有学生的Sname、Cno和Degree列。
select sname, cno, degree from student, score where student.sno = score.sno;
-- 15、查询所有学生的Sno、Cname和Degree列。
select sno, cname, degree from score,course where score.cno = course.cno;
-- 16、查询所有学生的Sname、Cname和Degree列。
select sname, cname, degree from student, course, score where student.sno = score.sno and score.cno = course.cno;
-- 17、查询“95033”班所选课程的平均分。
select class, score.cno, avg(degree) from student, score where class= 95033 and student.sno = score.sno group by score.cno;
-- 18、假设使用如下命令建立了一个grade表：
create table grade(low   int(3),upp   int(3),rank   varchar(1));
insert into grade values(90,100,'A');
insert into grade values(80,89,'B');
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 score.sno, score.cno, grade.rank from score, grade where score.degree between grade.low and grade.upp;
-- 19、查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。
select  score.* from student, score where student.sno = score.sno and score.degree > all (select degree from score where sno = 109) and  score.cno = '3-105';
SELECT A.* FROM SCORE A JOIN SCORE B WHERE A.CNO='3-105' AND A.DEGREE>B.DEGREE AND B.SNO='109' AND B.CNO='3-105';
-- 20、查询score中选学一门以上课程的同学中分数为非最高分成绩的记录。
select * from score where degree < (select max(degree) from score) group by sno having count(1) > 1
-- 21、查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。
select * from score where degree > all (select degree from score where sno = 109) and cno = '3-105';
-- 22、查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列。
select sno, sname, sbirthday from student where sbirthday = (select sbirthday from student where sno = 108);
-- 23、查询“张旭“教师任课的学生成绩。
select score.* from teacher, course, score where teacher.tno = course.tno and course.cno = score.cno and teacher.tname = '张旭';
-- 24、查询选修某课程的同学人数多于5人的教师姓名。
select teacher.tname from teacher, score, course where teacher.tno = course.tno and score.cno = course.cno group by score.cno having count(score.cno)> 5;
SELECT A.TNAME FROM TEACHER A JOIN (COURSE B, SCORE C) ON (A.TNO=B.TNO AND B.CNO=C.CNO) GROUP BY C.CNO HAVING COUNT(C.CNO)>5;
-- 25、查询95033班和95031班全体学生的记录。
select * from student where class = 95033 OR class = 95031;
-- 26、查询存在有85分以上成绩的课程Cno.
select * from score where degree > 85 group by cno;
-- 27、查询出“计算机系“教师所教课程的成绩表。
select score.* from score, course, teacher where teacher.tno = course.tno and course.cno = score.cno and teacher.depart = '计算机系';
-- 28、查询“计算机系”与“电子工程系“不同职称的教师的Tname和Prof。
select tname, prof from teacher where depart='计算机系' and prof not in (select prof from teacher where depart ='电子工程系')
select tname,prof from teacher where depart='计算机系' and prof not in (select prof from teacher where depart='电子工程系');
-- 29、查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的Cno、Sno和Degree,并按Degree从高到低次序排序。
select cno, sno, degree from score where cno='3-105' and degree > any (select degree from score where cno = '3-245') order by degree desc;
SELECT * FROM SCORE WHERE DEGREE>ANY(SELECT DEGREE FROM SCORE WHERE CNO='3-245') ORDER BY DEGREE DESC;
-- 30、查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的Cno、Sno和Degree.
select cno, sno, degree from score where cno='3-105' and degree > all (select degree from score where cno = '3-245') order by degree desc;
-- 31、查询所有教师和同学的name、sex和birthday.
select sname, ssex, sbirthday from student union select tname, tsex, tbirthday from teacher;
-- 32、查询所有“女”教师和“女”同学的name、sex和birthday.
select tname, tsex, tbirthday from teacher where tsex = '女' union select sname, ssex, sbirthday from student where ssex = '女';
-- 33、查询成绩比该课程平均成绩低的同学的成绩表。
select t1.* from score as t1 join (select cno, avg(degree) as avg_degree from score group by cno) as t2 on t1.cno = t2.cno and t1.degree < t2.avg_degree
-- 34、查询所有任课教师的Tname和Depart.
select tname, depart from teacher;
-- 35  查询所有未讲课的教师的Tname和Depart. 
select tname, depart from teacher where tname not in (select distinct tname from teacher, course where teacher.tno = course.tno) 
-- 36、查询至少有2名男生的班号。
select class from student where ssex = '男' group by class having count(class) >=2
-- 37、查询Student表中不姓“王”的同学记录。
select * from student where sname not like '王%'
-- 38、查询Student表中每个学生的姓名和年龄。
select sname,(2018-year(sbirthday)) as age from student; 
-- 39、查询Student表中最大和最小的Sbirthday日期值。
select max(sbirthday), min(sbirthday) from student
select sname,sbirthday as THEMAX from student where sbirthday =(select min(SBIRTHDAY) 


from student)
union
select sname,sbirthday as THEMIN from student where sbirthday =(select max(SBIRTHDAY) from 


student);
-- 40、以班号和年龄从大到小的顺序查询Student表中的全部记录。
select class, (year(now())-year(sbirthday)) as age from student order by class desc, age desc;
-- 41、查询“男”教师及其所上的课程。
select t1.*, t2.cno from teacher as t1, course as t2 where t1.tno = t2.tno and tsex = '男'
-- 42、查询最高分同学的Sno、Cno和Degree列。
select sno, cno, degree from score group by cno having degree = max(degree);
select t1.* from score as t1 where degree = (select max(degree) from score as t2 )
SELECT A.* FROM SCORE A WHERE DEGREE=(SELECT MAX(DEGREE) FROM SCORE B );
-- 43、查询和“李军”同性别的所有同学的Sname.
select sname from student where ssex = (select ssex from student where sname = '李军')
-- 44、查询和“李军”同性别并同班的同学Sname.
select sname from student where ssex = (select ssex from student where sname = '李军') and class = (select class from student where sname = '李军')
-- 45、查询所有选修“计算机导论”课程的“男”同学的成绩表
select t2.* from student as t1, score as t2, course as t3 where t1.sno = t2.sno and t2.cno = t3.cno and t1.ssex = '男' and t3.cname = '计算机导论'
SELECT A.* FROM SCORE A JOIN (STUDENT B,COURSE C) USING(sno,CNO) WHERE B.SSEX='男' AND C.CNAME='计算机导论';