数据库——4、写出SQL语句进行相关操作（面试写SQL）

1、数据库表信息

假设教务系统有3张表：

1、学生信息表S

SNO(学号)	SNAME（姓名）	AGE（年龄）	SEX（性别）
1	王小二	19	男
2	李其一	18	女

2、选课信息表SC

SNO(学号)	CNO（课程号）	SCGRADE（成绩）
1	1001	97
1	1002	80
1	1003	75
2	1001	88
2	1002	99
2	1003	76

3、课程信息表C

CNO(课程号)	CNAME（课程名称）	CTEACHER（任课老师）
1001	概率论与数理统计	刘老师
1002	线性代数	罗老师
1003	大学物理	王老师

2、运用SQL完成以下操作

（1）把SC表中每门课程的平均成绩插入到另一个已经存在的表SC_C（CNO,CNAME,AVG_GRADE）中，其中AVG_GRADE表示每门课程的平均成绩。

INSERT INTO sc_c (
	sc_c.cno,
	sc_c.cname,
	sc_c.avg_grade,
	sc_c.count
) SELECT
	sc.cno,
	c.cname,
	avg(sc.SCGRADE),
	count(sc.CNO)
FROM
	sc
LEFT JOIN c ON sc.cno = c.cno
GROUP BY
	sc.CNO
ORDER BY
	avg(sc.SCGRADE) DESC

成功插入：

（2）从SC表中把选刘老师的所授课程的女生的选课记录删掉。

DELETE
FROM
	sc
WHERE
	sc.CNO IN (
		SELECT
			c.CNO
		FROM
			c
		WHERE
			c.CTEACHER = '刘老师'
	)
AND sc.SNO IN (
	SELECT
		s.SNO
	FROM
		s
	WHERE
		s.SEX = '女'
)

（3）写出两种从S表中删除数据的方法。

DELETE:
执行删除的过程是每次从表中删除一行，并且同时将该行的删除操作作为事务记录在日志中保存以便进行进行回滚操作。

TRUNCATE TABLE :
则一次性地从表中删除所有的数据并不把单独的删除操作记录记入日志保存，删除行是不能恢复的。并且在删除的过程中不会激活与表有关的删除触发器。执行速度快。

DELETE from s where s.SNO=3

truncate table s

（4）找出没有选修过刘老师课程的所有学生的姓名。

SELECT
	s.SNAME
FROM
	s
WHERE
	s.SNO NOT in (
		SELECT
			sc.SNO
		FROM
			sc
		LEFT JOIN c ON sc.CNO = c.CNO
		WHERE
			c.CTEACHER = '刘老师'
	)

（5）列出有两门以上（含两门）优秀成绩（成绩大于90）的学生的姓名及其平均成绩。

SELECT
	s.SNAME,
	A.avg
FROM
	s,
	(
		SELECT
			sc.SNO,
			AVG(sc.SCGRADE) AS avg
		FROM
			sc
		WHERE
			sc.SCGRADE > 90
		GROUP BY
			sc.SNO
		HAVING
			COUNT(sc.CNO) >= 2
	) A
WHERE
	s.SNO = A.SNO

（6）列出既学过1001又学过1003课程的所有学生的姓名。

SELECT
	s.sname
FROM
	s,
	(
		SELECT
			sc.SNO
		FROM
			sc
		WHERE
			sc.CNO IN (1001, 1003)
		GROUP BY
			sc.SNO
		HAVING
			COUNT(DISTINCT sc.CNO) = 2
	) A
WHERE
	s.SNO = A.sno

（7）列出1001课程成绩比1002课程成绩高的所有学生的学号。

SELECT
	s.SNO
FROM
	s
LEFT JOIN (
	SELECT
		*
	FROM
		sc
	WHERE
		sc.CNO = 1001
) sc1 ON s.sno = sc1.sno
LEFT JOIN (
	SELECT
		*
	FROM
		sc
	WHERE
		sc.CNO = 1002
) sc2 ON s.sno = sc2.sno
WHERE
	1 = 1
AND sc1.SCGRADE > sc2.SCGRADE

（8）列出1001课程成绩比1002课程成绩高的所有学生的学号，以及其1001课程与1002课程的成绩。

SELECT
	s.SNO ,sc1.SCGRADE,sc2.SCGRADE
FROM
	s
LEFT JOIN (
	SELECT
		*
	FROM
		sc
	WHERE
		sc.CNO = 1001
) sc1 ON s.sno = sc1.sno
LEFT JOIN (
	SELECT
		*
	FROM
		sc
	WHERE
		sc.CNO = 1002
) sc2 ON s.sno = sc2.sno
WHERE
	1 = 1
AND sc1.SCGRADE > sc2.SCGRADE

（9）找出课程号为1002的前3名学生的成绩的记录？查看2-4名学生成绩的SQL的记录？

从位置为0的记录开始，取3条。

select * from sc
where 1=1 
and sc.CNO='1002'
order by sc.SCGRADE
limit 0,3

从位置为1的记录开始，取3条。

select * from sc
where 1=1 
and sc.CNO='1002'
order by sc.SCGRADE
limit 1,3