11.查询没学过"张三"老师讲授的任一门课程的学生姓名
SQl语句编写:
SELECT * FROM t_mysql_student WHERE sid NOT IN ( SELECT s.sid FROM t_mysql_score sc, t_mysql_teacher t, t_mysql_course c, t_mysql_student s WHERE c.cid = sc.cid AND c.tid = t.tid AND sc.sid = s.sid AND t.tname = '张三' )
crud操作结果:
12.查询两门及其以上不及格(低于60分)课程的同学的学号,姓名及其平均成绩
SQl语句编写:
SELECT s.*, sc.score FROM t_mysql_score sc, t_mysql_student s WHERE sc.sid = s.sid AND sc.score < 60 AND sc.cid = '01' ORDER BY sc.score DESC
crud操作结果:
13.检索" 01 "课程分数小于 60,按分数降序排列的学生信息
SQl语句编写:
SELECT s.*, sc.score FROM t_mysql_score sc, t_mysql_student s WHERE sc.sid = s.sid AND sc.score < 60 AND sc.cid = '01' ORDER BY sc.score DESC
crud操作结果:
14.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
SQl语句编写:
SELECT s.sid, s.sname, max( CASE WHEN sc.cid = '01' THEN sc.score END ) 语文, max( CASE WHEN sc.cid = '02' THEN sc.score END ) 数学, max( CASE WHEN sc.cid = '03' THEN sc.score END ) 英语, ROUND( AVG( sc.score ), 2 ) 平均成绩 FROM t_mysql_student s, t_mysql_score sc WHERE s.sid = sc.sid GROUP BY s.sid, s.sname ORDER BY 平均成绩 DESC
crud操作结果:
15.查询各科成绩最高分、最低分和平均分
以如下形式显示:课程 ID,课程 name,最高分,最低分,平均分,及格率,中等率,优良率,优秀率及格为>=60,中等为:70-80,优良为:80-90,优秀为:>=90
要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列
SQl语句编写:
SELECT c.cid, c.cname, max( sc.score ) 最高分, min( sc.score ) 最低分, ROUND( AVG( sc.score ), 2 ) 平均分, CONCAT(ROUND(sum( IF ( sc.score >= 60, 1, 0 ) ) / COUNT( sc.score ) * 100,2 ),'%' ) 及格率, CONCAT(ROUND(sum( IF ( sc.score >= 70 AND sc.score < 80, 1, 0 ) ) / COUNT( sc.score ) * 100,2 ),'%' ) 中等率, CONCAT(ROUND(sum( IF ( sc.score >= 80 AND sc.score < 90, 1, 0 ) ) / COUNT( sc.score ) * 100,2 ),'%' ) 优良率, CONCAT(ROUND(sum( IF ( sc.score >= 90, 1, 0 ) ) / COUNT( sc.score ) * 100,2 ),'%' ) 优秀率, COUNT( sc.score ) 选修人数 FROM t_mysql_score sc, t_mysql_course c WHERE sc.cid = c.cid GROUP BY sc.cid ORDER BY 选修人数 DESC, c.cid ASC;
crud操作结果:
以上就是有关MySQL的面试分享啦,希望对你有用祝你面试成功!!!