15道常用sql面试题

题目：十五道综合的MySQL面试题 01）查询" 01 “课程比” 02 "课程成绩高的学生的信息及课程分数 02）查询同时存在 " 01
“课程和” 02 "课程的情况 SELECT 03）查询存在 " 01 “课程但可能不存在” 02 “课程的情况 ( 不存在时显示为
NULL ) SELECT 04）查询不存在” 01 “课程但存在” 02 “课程的情况 05）查询平均成绩大于等于 60
分的同学的学生编号和学生姓名和平均成绩 06）查询在t_mysql_score表存在成绩的学生信息
07）查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null) 08）查询「李」姓老师的数量
09）查询学过「张三」老师授课的同学的信息 10）查询没有学全所有课程的同学的信息 11）查询没学过"张三"老师讲授的任一门课程的学生姓名
12）查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩 13）检索” 01 "课程分数小于 60，按分数降序排列的学生信息
14）按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩 15）查询各科成绩最高分、最低分和平均分：

- 二、题目： 
-- 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;
 

总结：实现SQL代码的思路可以分为以下几个步骤：

设计数据库结构：根据需求分析，设计数据库的表结构，包括表的字段、数据类型和约束等。

创建数据库：使用数据库管理系统（如MySQL、Oracle等）创建一个新的数据库。

创建表：根据设计的数据库结构，在创建的数据库中创建相应的表，使用CREATE TABLE语句。

插入数据：使用INSERT INTO语句向表中插入数据，可以一次插入一条记录，或者批量插入多条记录。

更新数据：使用UPDATE语句修改表中的数据。

查询数据：使用SELECT语句从表中查询数据，可以使用WHERE子句进行条件过滤，使用ORDER
BY子句进行排序，使用LIMIT子句限制返回的记录数等。

删除数据：使用DELETE语句从表中删除数据，可以使用WHERE子句进行条件过滤。

修改表结构：根据需求变更，使用ALTER TABLE语句修改已创建的表的结构，包括增加、修改和删除字段等。

索引和优化：根据需求和性能要求，使用CREATE INDEX语句创建索引，优化查询语句等。

数据备份和恢复：定期进行数据备份，以防数据丢失，需要时可以使用备份文件进行数据恢复。

以上是实现SQL代码的一般思路，具体实施过程中需要根据具体的需求和数据库管理系统的特点进行调整和优化。