MySQL 查询练习题

1、创建表和增加测试数据 create table student( sno varchar(10) primary key, sname varchar(20), sage varchar(2), ssex varchar(5), Class int(2) ) insert into STUDENT (SNO, SNAME, SAGE, SSEX,class) values ('s001', '张三', 23, '男',2); insert into STUDENT (SNO, SNAME, SAGE, SSEX,class) values ('s002', '李四', 23, '男',2); insert into STUDENT (SNO, SNAME, SAGE, SSEX,class) values ('s003', '吴鹏', 25, '男',1); insert into STUDENT (SNO, SNAME, SAGE, SSEX,class) values ('s004', '琴沁', 20, '女',1); insert into STUDENT (SNO, SNAME, SAGE, SSEX,class) values ('s005', '王丽', 20, '女',1); insert into STUDENT (SNO, SNAME, SAGE, SSEX,class) values ('s006', '李波', 21, '男',1); insert into STUDENT (SNO, SNAME, SAGE, SSEX,class) values ('s007', '刘玉', 21, '男',3); insert into STUDENT (SNO, SNAME, SAGE, SSEX,class) values ('s008', '萧蓉', 21, '女',3); insert into STUDENT (SNO, SNAME, SAGE, SSEX,class) values ('s009', '陈萧晓', 23, '女',3); insert into STUDENT (SNO, SNAME, SAGE, SSEX,class) values ('s010', '陈美', 22, '女',3); create table teacher( tno varchar(10) primary key, tname varchar(20) ); insert into TEACHER (TNO, TNAME) values ('t001', '刘阳'); insert into TEACHER (TNO, TNAME) values ('t002', '谌燕'); insert into TEACHER (TNO, TNAME) values ('t003', '胡明星'); create table course( cno varchar(10), cname varchar(20), tno varchar(20), constraint pk_course primary key (cno,tno) ); insert into COURSE (CNO, CNAME, TNO) values ('c001', 'J2SE', 't002'); insert into COURSE (CNO, CNAME, TNO) values ('c002', 'Java Web', 't002'); insert into COURSE (CNO, CNAME, TNO) values ('c003', 'SSH', 't001'); insert into COURSE (CNO, CNAME, TNO) values ('c004', 'Oracle', 't001'); insert into COURSE (CNO, CNAME, TNO) values ('c005', 'SQL SERVER 2005', 't003'); insert into COURSE (CNO, CNAME, TNO) values ('c006', 'C#', 't003'); insert into COURSE (CNO, CNAME, TNO) values ('c007', 'JavaScript', 't002'); insert into COURSE (CNO, CNAME, TNO) values ('c008', 'DIV+CSS', 't001'); insert into COURSE (CNO, CNAME, TNO) values ('c009', 'PHP', 't003'); insert into COURSE (CNO, CNAME, TNO) values ('c010', 'EJB3.0', 't002'); create table sc( sno varchar(10), cno varchar(10), score decimal(4,2), constraint pk_sc primary key (sno,cno) ); insert into SC (SNO, CNO, SCORE) values ('s001', 'c001', 78.9); insert into SC (SNO, CNO, SCORE) values ('s002', 'c001', 80.9); insert into SC (SNO, CNO, SCORE) values ('s003', 'c001', 81.9); insert into SC (SNO, CNO, SCORE) values ('s001', 'c002', 82.9); insert into SC (SNO, CNO, SCORE) values ('s002', 'c002', 72.9); insert into SC (SNO, CNO, SCORE) values ('s003', 'c002', 81.9); insert into SC (SNO, CNO, SCORE) values ('s001', 'c003', 59); insert into SC (SNO, CNO, SCORE) values ('s004', 'c004', 65); insert into SC (SNO, CNO, SCORE) values ('s002', 'c004', 89); 2、所以sql题及答案 1、查询“c001”课程比“c002”课程成绩高的所有学生的学号; SELECT sc1.sno FROM sc sc1, sc sc2 WHERE sc1.sno = sc2.sno AND sc1.cno = 'c001' AND sc2.cno = 'c002' AND sc1.score > sc2.score 2、查询平均成绩大于60 分的同学的学号和平均成绩; SELECT sno, avg( score ) FROM sc GROUP BY sno HAVING avg( score ) > 60 3、查询所有同学的学号、姓名、选课数、总成绩; SELECT student.sno, student.sname, count( 1 ), sum( sc.score ) FROM student INNER JOIN sc ON student.sno = sc.sno INNER JOIN course ON course.cno = sc.cno GROUP BY student.sno, student.sname 4、查询姓“刘”的老师 的个数; select count(1) from teacher where teacher.tname like "刘%" 5、查询没学过“谌燕”老师课的同学的学号、姓名; SELECT student.sno,student.sname FROM student WHERE student.sno NOT IN ( SELECT DISTINCT sc.sno FROM sc WHERE sc.cno IN ( SELECT course.cno FROM teacher, course WHERE teacher.tno = course.tno AND teacher.tname = '谌燕' )); 6、查询学过“c001”并且也学过编号“c002”课程的同学的学号、姓名; select sno ,sname from student where student.sno in ( select sc1.sno from sc sc1, sc sc2 where sc1.cno='c001' and sc2.cno='c002' and sc1.sno=sc2.sno) 7、查询学过“谌燕”老师所教的所有课的同学的学号、姓名; select sno ,sname from student where student.sno in ( select distinct sno from sc where sc.cno in( select cno from teacher ,course where teacher.tno=course.tno and teacher.tname='谌燕' )) 8、查询课程编号“c002”的成绩比课程编号“c001”课程低的所有同学的学号、姓名; select student.sno,student.sname from student where student.sno in ( select sc2.sno from sc sc1 ,sc sc2 where sc1.sno=sc2.sno and sc1.cno='c001' and sc2.cno='c002' and sc2.score60) and sc.sno=student.sno 2、所有成绩小于60的同学(sc表没有成绩的) select student.sno,student.sname from student where student.sno not in( select distinct sno from sc where sc.score>60) 10、查询没有学全所有课的同学的学号、姓名; select * from student where student.sno not in ( select b.sno from (select count(1) zs from course ) a , (select sno ,count(1) zs from sc GROUP BY sno ) b where a.zs=b.zs ) 11、查询至少有一门课与学号为“s001”的同学所学相同的同学的学号和姓名; select sno ,sname from student where student.sno in( select sno from sc where sc.cno in (select cno from sc where sc.sno='s001') and sc.sno 's001'); select s.sno,s.sname,c.zs from student s,( select sno,count(1) zs from sc where sc.cno in ( select cno from sc where sc.sno='s001') and sc.sno 's001' group by sno) c where s.sno=c.sno; 12、查询学过学号为“s001”同学其中一门课的 其他 同学学号和姓名; select student.sno,sname from student where student.sno in ( select sno from sc where sc.cno in( select cno from sc where sc.sno='s001') and sc.sno 's001' group by sno having count(1) = 1) 14、查询和“s001”号的同学学习的课程 完全相同 的其他同学学号和姓名; select student.sno , student.sname from student INNER JOIN (select s1.sno,count(1) from sc s1 join sc s2 on s1.cno=s2.cno where s1.sno's001' and s2.sno ='s001' GROUP BY s1.sno having count(1)= (select count(1) zs from sc where sc.sno='s001')) a USING(sno) 15、删除学习“谌燕”老师课的SC 表记录; delete from sc where sc.cno in (select course.cno from teacher INNER JOIN course On teacher.tno=course.tno where teacher.tname='谌燕') 16、向SC 表中插入一些记录,这些记录要求符合以下条件:没有上过编号“c002”课程的同学学号、“c002”号课的平均成绩; insert into sc select sno ,'c002' ,score from (select 'c002',avg(score) score from sc where sc.cno='c002') a join (select distinct sno from sc s1 where not exists(select sno from sc s2 where s1.sno=s2.sno and s2.cno='c002')) b 17、查询各科成绩最高和最低的分:以如下形式显示:课程ID,最高分,最低分 select cno '课程ID',max(sc.score) '最高分',min(sc.score) '最低分' from sc GROUP BY cno 18、按各科平均成绩从低到高和及格率的百分数从高到低顺序 select cno,avg(score) '平均分',CONCAT(cast(100*sum(case WHEN sc.score>=60 then 1 else 0 end)/count(1)as decimal),"%") as '及格率'from sc GROUP BY cno ORDER BY avg(score) desc ,100*sum(case WHEN sc.score>=60 then 1 else 0 end)/count(1) asc 19、查询不同老师所教不同课程平均分从高到低显示 select teacher.tname,course.cname,AVG(sc.score) from sc INNER JOIN course ON sc.cno=course.cno INNER JOIN teacher on teacher.tno=course.tno group by teacher.tname,course.cname ORDER BY AVG(sc.score) desc 20、统计列印各科成绩,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60] SELECT course.cno "课程ID", course.cname "课程名称", count( CASE WHEN sc.score BETWEEN 85 and 100 THEN 1 ELSE null END ) "[100-85]", count( CASE WHEN sc.score BETWEEN 70 and 85 THEN 1 ELSE null END ) "[85-70]", count( CASE WHEN sc.score BETWEEN 60 and 70 THEN 1 ELSE null END ) "[70-60]", count( CASE WHEN sc.score BETWEEN 0 and 60 THEN 1 ELSE null END ) "[ 1 27、1995 年出生的学生名单(注:Student 表中Sage 列的类型是number) select * from student where student.sage = (SELECT DATE_FORMAT(NOW(), '%Y')-1995) 28、查询每门课程的平均成绩,结果按平均成绩升序排列,平均成绩相同时,按课程号降序排列 select sc.cno,avg(sc.score) from sc group by sc.cno ORDER BY avg(sc.score) asc ,sc.cno desc 29、查询平均成绩大于85 的所有学生的学号、姓名和平均成绩 select student.sno,student.sname,avg(sc.score) from student ,sc where sc.sno=student.sno GROUP BY sno,sname HAVING avg(sc.score)>85 30、查询课程名称为“数据库”,且分数低于60 的学生姓名和分数 select student.sname,sc.score from sc ,course,student where sc.cno=course.cno and course.cname='Oracle' and sc.sno=student.sno and sc.score<60 31、查询所有学生的选课情况; select student.sname,course.cname from sc ,course,student where sc.cno=course.cno and student.sno=sc.sno 32、查询任何一门课程成绩在70 分以上的姓名、课程名称和分数; select student.sname,course.cname,sc.score from student ,sc,course where student.sno=sc.sno and sc.cno=course.cno and sc.sno not in(select sno from sc where sc.score<70 ) 33、查询不及格的课程,并按课程号从大到小排列 select cno from sc where sc.score80 35、求选了课程的学生人数 select count( DISTINCT sc.sno) from sc 36、查询选修“谌燕”老师所授课程的学生中,成绩最高的学生姓名及其成绩 select student.sname,max(sc.score) from course ,teacher,sc,student where course.cno=sc.cno and course.tno=teacher.tno and student.sno=sc.sno and sc.cno in (select cno from course ,teacher where course.tno=teacher.tno and teacher.tname='谌燕') GROUP BY student.sname ORDER BY max(sc.score) desc limit 1 37、查询各个课程及相应的选修人数 select course.cname ,count(1) zs from course ,sc where sc.cno=course.cno GROUP BY course.cname UNION ALL select course.cname,'0' zs from course where course.cno not in (select DISTINCT sc.cno from course ,sc where sc.cno=course.cno ) 38、查询不同课程成绩相同的学生的学号、课程号、学生成绩 select * from sc where sc.score in ( select sc.score from sc GROUP BY sc.score having count(1) >1) and sc.cno in( select a.cno from( select * from sc where sc.score in ( select sc.score from sc GROUP BY sc.score having count(1) >1) ) a group by a.cno having count(1)=1) 39、查询每门功课成绩最好的前两名 select s1.* from sc s1 where (select count(1) from sc s2 where s1.cno=s2.cno and s2.score>=s1.score) 10 ORDER BY count(1) desc 41、检索至少选修两门课程的学生学号 select sno from sc group by sno having count(1)>=2 43、查询没学过“谌燕”老师讲授的任一门课程的学生姓名 select sname from student where student.sno not in( select distinct sno from sc where sc.cno in( select cno from teacher ,course where teacher.tno=course.tno and teacher.tname='谌燕')) 44、查询两门以上不及格课程的同学的学号及其平均成绩 select sno ,avg(sc.score) from sc GROUP BY sno having count(case when sc.score=2 45、查询每个学生不及格课数 select sno, count(case when sc.score<60 then 1 else null end) from sc GROUP BY sno 46、检索“c004”课程分数小于60,按分数降序排列的同学学号 select * from sc where sc.cno='c004' and sc.score<60 ORDER BY sc.score asc; 47、删除“s002”同学的“c001”课程的成绩 delete from sc where sc.sno='s002' and sc.cno='c001' 以上sql题是几年前做的现在也没看是否都对,效率如何,若有那题错误还请指点出来 立刻会更正  感谢留言指点

提供全面的网站源码正版坑位,小程序、APP、H5、支付、游戏、区块链、商城、直播、影音、小说、公众号等源码学习交流。
精品源码资源网 » MySQL 查询练习题
喜欢我嘛?喜欢就按“ctrl+D”收藏我吧!♡