练习题是从网上查到的，我们以小白的身份去一题一题的去解决他们；

特别感谢大佬们把自己总结的经验分享出来可以使我们能够快速入手。每一题都是自己思考并结合原博主的解题思路写出来的，道路还挺长，我们共勉。

#查询Score表中成绩在60到80之间的所有记录 select * from SCORE where degree>60 and degree<80;

#查询 score 表中成绩为85，86或88的记录 SELECT * from SCORE where degree in (85,86,88);

#以 cno 升序、degree降序查询 score 表的所有记录 select * from SCORE order by cno asc, degree desc; #查询“95031”班的学生人数。

select count(*) as sumstudent from STUDENT where class=‘95031’;

#查询Score表中的最高分的学生学号和课程号 select sno,cno from SCORE order by degree desc limit 1;

#查询‘3-105’号课程的平均分。 select AVG(degree) from SCORE where cno=‘3-105’;

#查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。 select cno,avg(degree) from SCORE where cno like ‘3%’ group by cno having count(*)>=5;

#查询最低分大于70，最高分小于90的Sno列。 select sno from SCORE group by sno having min(degree)>70 and max(degree)<90;

SELECT sno FROM SCORE GROUP BY sno HAVING min(degree) > 70 AND max(degree) < 90;

#查询所有学生的Sname、Cno和Degree列。

select SNAME,CNO,DEGREE from STUDENT,SCORE where STUDENT.sno=SCORE.sno;

#查询所有学生的Sno、Cname和Degree列。 select SNO,CNAME,DEGREE from SCORE,COURSE where SCORE.CNO=COURSE.CNO;

#查询所有学生的Sname、Cname和Degree列。 select SNAME,CNAME,DEGREE from STUDENT,COURSE,SCORE where STUDENT.SNO=SCORE.SNO and COURSE.CNO=SCORE.CNO;

select SNAME,CNAME,DEGREE FROM STUDENT JOIN (COURSE,SCORE) ON STUDENT.SNO=SCORE.SNO AND COURSE.CNO=SCORE.CNO;

#查询“95033”班所选课程的平均分。

select avg(degree) from SCORE where sno in (select sno from STUDENT WHERE CLASS=‘95033’);

SELECT AVG(degree) FROM SCORE WHERE sno IN (SELECT sno FROM STUDENT WHERE class = ‘95033’);

#查询所有同学的Sno、Cno和rank列。

 

#查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。

SELECT degree from SCORE where sno=‘109’ and cno=‘3-105’

select sno,cno,degree from SCORE where cno=‘3-105’ and degree>(SELECT degree from SCORE where sno=‘109’ and cno=‘3-105’);

SELECT * FROM SCORE WHERE cno = ‘3-105’ AND degree > (SELECT degree FROM SCORE WHERE sno = ‘109’ AND cno = ‘3-105’);

#查询和学号为107的同学同年出生的所有学生的Sno、Sname和Sbirthday列。

SELECT SBIRTHDAY from STUDENT where sno=‘107’

select SNO,SNAME,SBIRTHDAY from STUDENT where year(SBIRTHDAY)=(SELECT year(SBIRTHDAY) from STUDENT where sno=‘107’);

#查询“张旭“教师任课的学生成绩。

select TNO from TEACHER where TNAME=‘张旭’

SELECT CNO FROM COURSE WHERE TNO=(select TNO from TEACHER where TNAME=‘张旭’)

SELECT * FROM SCORE WHERE CNO in (SELECT CNO FROM COURSE WHERE TNO=(select TNO from TEACHER where TNAME=‘张旭’))

SELECT * FROM SCORE WHERE cno = (SELECT cno FROM COURSE JOIN TEACHER ON COURSE.tno = TEACHER.tno AND tname = ‘张旭’);

#查询选修某课程的同学人数多于5人的教师姓名。

select CNO from SCORE group by cno having count(*)>5

SELECT TNO FROM COURSE WHERE cno=(select CNO from SCORE group by cno having count(*)>5)

SELECT TNAME FROM TEACHER WHERE TNO=(SELECT TNO FROM COURSE WHERE cno=(select CNO from SCORE group by cno having count(*)>5))

#查询95033班和95031班全体学生的记录。 SELECT * FROM STUDENT WHERE CLASS in (‘95033’,‘95031’)

#查询存在有85分以上成绩的课程Cno。

SELECT cno,degree FROM SCORE WHERE degree>85

SELECT cno FROM SCORE group by cno having max(degree)>85

#查询出“计算机系“教师所教课程的成绩表。 select TNO from TEACHER WHERE DEPART=‘计算机系’

SELECT CNO FROM COURSE WHERE TNO in (select TNO from TEACHER WHERE DEPART=‘计算机系’)

SELECT * FROM SCORE WHERE cno in (SELECT CNO FROM COURSE WHERE TNO in (select TNO from TEACHER WHERE DEPART=‘计算机系’))

SELECT * FROM SCORE WHERE cno IN (SELECT cno FROM COURSE, TEACHER WHERE depart = ‘计算机系’ AND COURSE.tno = TEACHER.tno);

#查询所有教师和同学的name、sex和birthday。

SELECT tname name, tsex sex, tbirthday birthday FROM TEACHER union SELECT sname name, ssex sex, sbirthday birthday FROM STUDENT;

#查询所有“女”教师和“女”同学的name、sex和birthday。

select TNAME name,TSEX sex,TBIRTHDAY birthday from TEACHER WHERE TSEX=‘女’ union select SNAME name,SSEX sex,SBIRTHDAY birthday FROM STUDENT WHERE SSEX=‘女’

SELECT tname name, tsex sex, tbirthday birthday FROM TEACHER WHERE tsex = ‘女’ UNION SELECT sname name, ssex sex, sbirthday birthday FROM STUDENT WHERE ssex = ‘女’;

#查询成绩比该课程平均成绩低的同学的成绩表。 SELECT a.* FROM SCORE a where a.degree< (SELECT avg(degree) from SCORE WHERE SCORE.cno=a.cno) #查询所有任课教师的Tname和Depart。 SELECT TNAME,DEPART FROM TEACHER

SELECT tname, depart FROM TEACHER a WHERE tno IN (SELECT DISTINCT tno FROM COURSE);

#查询至少有2名男生的班号。 SELECT * FROM STUDENT WHERE SSEX=‘男’ GROUP BY CLASS HAVING count(SSEX)>1

#查询Student表中不姓“王”的同学记录。 SELECT * from STUDENT WHERE SNAME not LIKE’王%’

#查询Student表中每个学生的姓名和年龄。 SELECT SNAME name,2021-YEAR(SBIRTHDAY) year FROM STUDENT

SELECT sname, YEAR(NOW()) - YEAR(sbirthday) FROM STUDENT;

 

 

摘自点我