mysql面试题之二

1、查询出所有未参加考试的学生                  

 SELECT
	*
FROM
	student
WHERE
	s_id NOT IN (
	SELECT
		s_id
	FROM
		score );

2、查询至少有一门课与学号为"5"的同学所学相同的同学的信息

SELECT
	*
FROM
	student
WHERE
	s_id IN (
	SELECT
		s_id
	FROM
		score
	WHERE
		c_id IN (
		SELECT
			c_id
		FROM
			score
		WHERE
			s_id = 5));

3、查询和"6"号的同学学习的课程完全相同的其他同学的信息

SELECT
	*
FROM
	student
WHERE
	s_id IN (
	SELECT
		s_id
	FROM
		(
		SELECT
			s.s_id,
			sum(IF(a.s_id IS NULL, 0, 1)) ks,
			count(*) cnt
		FROM
			score s
		LEFT JOIN (
			SELECT
				*
			FROM
				score
			WHERE
				s_id = 6) a ON
			s.c_id = a.c_id
		GROUP BY
			s.s_id
		HAVING
			sum(IF(a.s_id IS NULL, 0, 1))=(
			SELECT
				count(c_id)
			FROM
				score
			WHERE
				s_id = 6)
			AND count(*)=(
			SELECT
				count(*)
			FROM
				score
			WHERE
				s_id = 6)
			AND s_id <> 6) B);

4、查询没学过"张三"老师讲授的任一门课程的学生姓名

SELECT
	*
FROM
	student
WHERE
	s_id NOT IN (
	SELECT
		s_id
	FROM
		score
	WHERE
		c_id IN (
		SELECT
			c_id
		FROM
			course
		WHERE
			t_id IN (
			SELECT
				t_id
			FROM
				teacher
			WHERE
				t_name = '张三')));

5、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

SELECT
	*
FROM
	student
WHERE
	s_id IN (
	SELECT
		s_id
	FROM
		(
		SELECT
			count(s_score),
			s_id
		FROM
			score
		WHERE
			s_score<60
		GROUP BY
			s_id
		HAVING
			count(s_score)>= 2) a);

6、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

SELECT
	avg(s_score),
	s.s_id,
	s.s_name
FROM
	student s
LEFT JOIN score c ON
	c.s_id = s.s_id
GROUP BY
	s.s_id
ORDER BY
	avg(s_score) DESC;

7、找到姓诸的同学

select s_name from student where s_name like '诸%' and s_name not like '诸葛%';