sql语句常见面试题

sql语句常见面试题

1.建表
(1)student学生信息表

CREATE TABLE student (
	id INT (20) NOT NULL,
	NAME VARCHAR (20) NOT NULL,
	age INT (20),
	sex VARCHAR (2),
	PRIMARY KEY (id)
)
INSERT INTO `student` VALUES ('1', '张三', '23', '男');
INSERT INTO `student` VALUES ('2', '李四', '22', '女');
INSERT INTO `student` VALUES ('3', '王五', '24', '男');
INSERT INTO `student` VALUES ('4', '赵六', '34', '女');
INSERT INTO `student` VALUES ('5', '周芷若', '30', '女');

（2）couse课程表

CREATE TABLE couse (
	id INT (20) NOT NULL,
	name VARCHAR (20) NOT NULL,
	t_id INT (20) NOT NULL,
	PRIMARY KEY (id)
)
INSERT INTO `couse` VALUES ('1001', '语文', '1');
INSERT INTO `couse` VALUES ('1002', '数学', '2');
INSERT INTO `couse` VALUES ('1003', '英语', '3');
INSERT INTO `couse` VALUES ('1004', '武术', '4');

（3）grade 成绩表

CREATE TABLE grade (
	s_id INT (20) NOT NULL,
	c_id INT (20) NOT NULL,
	score INT (20) NOT NULL
)
INSERT INTO `grade` VALUES ('1', '1001', '65');
INSERT INTO `grade` VALUES ('1', '1002', '76');
INSERT INTO `grade` VALUES ('1', '1003', '67');
INSERT INTO `grade` VALUES ('2', '1001', '87');
INSERT INTO `grade` VALUES ('2', '1002', '78');
INSERT INTO `grade` VALUES ('2', '1003', '54');
INSERT INTO `grade` VALUES ('3', '1001', '91');
INSERT INTO `grade` VALUES ('3', '1002', '89');
INSERT INTO `grade` VALUES ('3', '1003', '68');
INSERT INTO `grade` VALUES ('4', '1001', '72');
INSERT INTO `grade` VALUES ('4', '1002', '74');
INSERT INTO `grade` VALUES ('4', '1003', '57');
INSERT INTO `grade` VALUES ('5', '1001', '100');
INSERT INTO `grade` VALUES ('5', '1002', '90');
INSERT INTO `grade` VALUES ('5', '1003', '97');
INSERT INTO `grade` VALUES ('1', '1004', '79');
INSERT INTO `grade` VALUES ('4', '1004', '65');

（4）teacher教师表

CREATE TABLE `teacher` (
  `id` int(20) NOT NULL,
  `name` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
)
INSERT INTO `teacher` VALUES ('1', '张无忌');
INSERT INTO `teacher` VALUES ('2', '张三丰');
INSERT INTO `teacher` VALUES ('3', '霍都');
INSERT INTO `teacher` VALUES ('4', '杨过');

2、查询“1001”课程比“1002”成绩高的所有学生的学号及姓名。

SELECT
	student.id,
	student.`name`
FROM
	student
WHERE
	id in (
		SELECT
			a.s_id
		FROM
			(
				SELECT
					s_id,
					score
				FROM
					grade
				WHERE
					c_id = 1001
			) a,
			(
				SELECT
					s_id,
					score
				FROM
					grade
				WHERE
					c_id = 1002
			) b
		WHERE
			a.score > b.score
		AND a.s_id = b.s_id
	);

3、查询平均成绩大于80分的同学的学号和平均成绩

SELECT
	s_id,
	avg(score)
FROM
	grade
GROUP BY
	s_id
HAVING
	avg(score) > 80;

4、查询所有同学的学号、姓名、选课数、总成绩；
方法1：

SELECT
	student.id,
	student.`name`,
	count(grade.c_id),
	sum(grade.score)
FROM
	student,
	grade
WHERE
	student.id = grade.s_id
GROUP BY
	student.id;

方法2：

SELECT
	student.id,
	student. name,
	count(grade.student_id),
	sum(grade.achievement)
FROM
	student
LEFT JOIN grade ON student.id = grade.student_id
GROUP BY
	student.id,
	student.`name`

5、查询姓“张”的老师的个数

SELECT
	count(name)
FROM
	teacher
WHERE
	NAME LIKE "张%";

6、查询没学过“杨过”老师课的同学的学号、姓名；
方法1：

SELECT
	id,
	NAME
FROM
	student
WHERE
	id NOT IN (
		SELECT
			s_id
		FROM
			grade
		WHERE
			c_id = (
				SELECT
					id
				FROM
					couse
				WHERE
					t_id = (
						SELECT
							id
						FROM
							teacher
						WHERE
							NAME = '杨过'
					)
			)
	)

方法2：

SELECT
	id,
	name
FROM
	student
WHERE
	id NOT IN (
		SELECT
			grade.s_id
		FROM
			grade,
			couse,
			teacher
		WHERE
			teacher.`name` = '杨过'
		AND couse.t_id = teacher.id
		AND couse.id = grade.c_id
	);

7、查询学过“1001”并且也学过编号“1004”课程的同学的学号、姓名；

SELECT
	grade.s_id,
	student.`name`
FROM
	student,
	grade
WHERE
	grade.s_id = student.id
AND grade.c_id = 1001
AND EXISTS (
	SELECT
		*
	FROM
		grade
	WHERE
		s_id = student.id
	AND c_id = 1004
);

8、查询学过“杨过”老师所教的所有课的同学的学号、姓名；

SELECT
	student.id,
	student.`name`
FROM
	student
WHERE
	student.id IN (
		SELECT
			grade.s_id
		FROM
			grade,
			couse,
			teacher
		WHERE
			grade.c_id = couse.id
		AND teacher.id = couse.t_id
		AND teacher.`name` = '杨过'
		GROUP BY
			grade.s_id
		HAVING
			count(grade.c_id) = (
				SELECT
					count(couse.id)
				FROM
					couse,
					teacher
				WHERE
					teacher.id = couse.t_id
				AND teacher.`name` = '杨过'
			)
	);

9、查询所有课程成绩小于80分的同学的学号、姓名；

SELECT
	student.id,
	student.`name`
FROM
	student
WHERE
	student.id NOT IN (
		SELECT
			s_id
		FROM
			grade
		WHERE
			score > 80
		GROUP BY
			s_id
	)

10、查询没有学全所有课的同学的学号、姓名

SELECT
	student.id,
	student. NAME
FROM
	student
WHERE
	student.id NOT IN (
		SELECT
			grade.s_id
		FROM
			grade,
			couse
		WHERE
			grade.c_id = couse.id
		GROUP BY
			grade.s_id
		HAVING
			count(grade.c_id) = (SELECT count(id) FROM couse)
	);

11、查询至少有一门课与学号为“1”的同学所学相同的同学的学号和姓名；

SELECT
	student.id,
	student.`name`
FROM
	student
WHERE
	student.id IN (
		SELECT
			grade.s_id
		FROM
			grade,
			couse
		WHERE
			grade.c_id = couse.id
		AND grade.c_id IN (
			SELECT
				grade.c_id
			FROM
				grade
			WHERE
				grade.s_id = 1
		)
		AND student.id != 1
	);