15道常用sql面试题

- 二、题目： 
-- 01）查询 " 01 "课程比" 02 "课程成绩高的学生的信息及课程分数
SELECT
  st.*,
  s1.score '01课程',
  s2.score '02课程'
FROM
  ( SELECT * FROM t_mysql_score WHERE cid = "01" ) s1,
  ( SELECT * FROM t_mysql_score WHERE cid = "02" ) s2,
  t_mysql_student st 
WHERE
  s1.sid = s2.sid 
  AND s1.sid = st.sid 
  AND s1.score > s2.score
--  02）查询同时存在 " 01 "课程和" 02 "课程的情况 
SELECT
  s.*,
  ( CASE WHEN s1.cid = "01" THEN s1.score END ) '01课程',
  ( CASE WHEN s2.cid = "02" THEN s1.score END ) '02课程' 
FROM
  ( SELECT * FROM t_mysql_score WHERE cid = "01" ) s1,
  ( SELECT * FROM t_mysql_score WHERE cid = "02" ) s2,
  t_mysql_student s 
WHERE
  s1.sid = s2.sid 
  AND s1.sid = s.sid
--  03）查询存在 " 01 "课程但可能不存在" 02 "课程的情况 ( 不存在时显示为 NULL )
SELECT
  s1.sid,
  s1.cid,
  s1.score '01课程',
  s2.score '02课程'
FROM
  ( SELECT * FROM t_mysql_score sc WHERE sc.cid = "01" ) s1
  LEFT JOIN ( SELECT * FROM t_mysql_score sc WHERE sc.cid = "02" ) s2 ON s1.sid = s2.sid
--  04）查询不存在 " 01 "课程但存在" 02 "课程的情况 
SELECT
  * 
FROM
  t_mysql_score 
WHERE
  sid IN ( SELECT sid FROM t_mysql_student WHERE sid NOT IN ( SELECT sid FROM t_mysql_score WHERE cid = '01' ) ) 
  AND cid = '02'
--  05）查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩 
SELECT
  s.sid,
  s.sname,
  ROUND( AVG( c.score ), 2 ) '平均成绩' 
FROM
  t_mysql_student s,
  t_mysql_score c 
WHERE
  s.sid = c.sid 
GROUP BY
  s.sid,
  s.sname 
HAVING
  avg( c.score ) >= 60
--  06）查询在 t_mysql_score表存在成绩的学生信息
SELECT
  s.* 
FROM
  t_mysql_score sc,
  t_mysql_student s 
WHERE
  sc.sid = s.sid 
GROUP BY
  s.sid
--  07）查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩 ( 没成绩的显示为 NULL )
SELECT
  s.sid,
  s.sname,
  count( sc.cid ) '选课总数',
  sum( sc.score ) '总成绩'
FROM
  t_mysql_student s,
  t_mysql_score sc 
WHERE
  s.sid = sc.sid 
GROUP BY
  s.sid,
  s.sname
--  08）查询「李」姓老师的数量
SELECT
  * 
FROM
  t_mysql_teacher t 
WHERE
  tname LIKE '李%'
--  09）查询学过「张三」老师授课的同学的信息
SELECT
  * 
FROM
  t_mysql_student 
WHERE
  sid IN (
SELECT
  sc.sid 
FROM
  t_mysql_teacher t,
  t_mysql_score sc,
  t_mysql_course c 
WHERE
  t.tid = c.tid 
  AND sc.cid = c.cid 
  AND t.tname = "张三" 
GROUP BY
  sc.sid 
  )
--  10）查询没有学全所有课程的同学的信息
SELECT
  s.*,
  count( sc.score ) '课程数量'
FROM
  t_mysql_student s,
  t_mysql_score sc 
WHERE
  s.sid = sc.sid 
GROUP BY
  s.sid,
  s.sname 
HAVING
  count( sc.score ) < ( SELECT count( 1 ) FROM t_mysql_course )
  11）查询没学过 "张三"老师讲授的任一门课程的学生姓名 
SELECT
  sname 
FROM
  t_mysql_student 
WHERE
  sid NOT IN (
SELECT
  sc.sid 
FROM
  t_mysql_score sc,
  t_mysql_course c,
  t_mysql_teacher t 
WHERE
  t.tid = c.tid 
  AND sc.cid = c.cid 
  AND t.tname = "张三" 
  )
--  12）查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩 
SELECT
  s.sid,
  s.sname,
  ROUND( AVG( sc.score ), 2 ) 平均成绩 
FROM
  t_mysql_student s,
  t_mysql_score sc 
WHERE
  sc.sid = s.sid 
  AND sc.score < 60 GROUP BY s.sid, s.sname HAVING count( sc.score ) >= 2
--  13）检索 " 01 "课程分数小于 60，按分数降序排列的学生信息 
SELECT
  s.*,
  sc.score 
FROM
  t_mysql_student s,
  t_mysql_score sc 
WHERE
  s.sid = sc.sid 
  AND sc.cid = "01" 
  AND sc.score < 60 
ORDER BY
  sc.score DESC
--  14）按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
SELECT
  s.sid,
  s.sname,
IF
  (
  max( CASE WHEN sc.cid = '01' THEN sc.score END ) > 0,
  max( CASE WHEN sc.cid = '01' THEN sc.score END ),
  0 
  ) 语文,
IF
  (
  max( CASE WHEN sc.cid = '02' THEN sc.score END ) > 0,
  max( CASE WHEN sc.cid = '02' THEN sc.score END ),
  0 
  ) 数学,
IF
  (
  max( CASE WHEN sc.cid = '03' THEN sc.score END ) > 0,
  max( CASE WHEN sc.cid = '03' THEN sc.score END ),
  0 
  ) 英语,
  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
--  15）查询各科成绩最高分、最低分和平均分： 以如下形式显示：课程 ID，课程 NAME，最高分，最低分，平均分，及格率，中等率，优良率，优秀率及格为 >= 60，中等为：70-80，优良为：80-90，优秀为： >= 90 要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列 
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;