十道SQL常见面试题

一共五张表

tab_class

tab_course

tab_score

tbl_student

tbl_teacher

建表语句和数据

CREATE TABLE `tbl_class` (
  `class_id` int(10) NOT NULL AUTO_INCREMENT COMMENT '班级ID',
  `caption` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`class_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

/*Data for the table `tbl_class` */

insert  into `tbl_class`(`class_id`,`caption`) values (1,'0808'),(2,'0829'),(3,'0919');

/*Table structure for table `tbl_course` */

DROP TABLE IF EXISTS `tbl_course`;

CREATE TABLE `tbl_course` (
  `course_id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) DEFAULT NULL,
  `teacher_id` int(10) DEFAULT NULL,
  PRIMARY KEY (`course_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

/*Data for the table `tbl_course` */

insert  into `tbl_course`(`course_id`,`name`,`teacher_id`) values (1,'java',1),(2,'spring',2),(3,'mybatis',3),(4,'h5',4),(5,'git',5);

/*Table structure for table `tbl_score` */

DROP TABLE IF EXISTS `tbl_score`;

CREATE TABLE `tbl_score` (
  `score_id` int(10) NOT NULL AUTO_INCREMENT,
  `student_id` int(11) DEFAULT NULL,
  `course_id` int(11) DEFAULT NULL,
  `number` int(11) DEFAULT NULL,
  PRIMARY KEY (`score_id`)
) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=utf8;

/*Data for the table `tbl_score` */

insert  into `tbl_score`(`score_id`,`student_id`,`course_id`,`number`) values (1,1,1,58),(2,2,1,78),(3,3,1,78),(4,2,2,89),(5,1,2,98),(6,4,3,100),(7,1,3,99),(8,1,4,91),(9,1,5,71),(10,2,3,99),(11,2,4,79),(12,2,5,59),(13,3,2,99),(14,3,3,99),(15,3,4,69),(16,3,5,49),(17,4,1,69),(18,4,2,59),(19,4,4,84),(20,4,5,80),(21,5,1,49),(22,5,2,68),(23,5,3,67),(24,5,4,89);

/*Table structure for table `tbl_student` */

DROP TABLE IF EXISTS `tbl_student`;

CREATE TABLE `tbl_student` (
  `student_id` int(10) NOT NULL AUTO_INCREMENT,
  `class_id` int(10) DEFAULT NULL,
  `name` varchar(30) DEFAULT NULL,
  `gender` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`student_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

/*Data for the table `tbl_student` */

insert  into `tbl_student`(`student_id`,`class_id`,`name`,`gender`) values (1,1,'张三','男'),(2,1,'李四','男'),(3,2,'李五','男'),(4,2,'李红','女'),(5,3,'李丽','女');

/*Table structure for table `tbl_teacher` */

DROP TABLE IF EXISTS `tbl_teacher`;

CREATE TABLE `tbl_teacher` (
  `teacher_id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`teacher_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

/*Data for the table `tbl_teacher` */

insert  into `tbl_teacher`(`teacher_id`,`name`) values (1,'佟湘玉'),(2,'张晨一'),(3,'封捷'),(4,'李贺'),(5,'李玉婷');

试题一、找出平均成绩大于60的所有学生的学号（即student_id)、姓名和平均分数

select s2.student_id,name,avg(number)
from tbl_score s1 inner join tbl_student s2 on s1.student_id=s2.student_id
GROUP BY student_id
HAVING avg(number)>60

试题二、查询所有学生的学号，姓名，选课数和总成绩

select s2.student_id,name,count(course_id),sum(number)
from tbl_score s1 inner join tbl_student s2 on s1.student_id=s2.student_id
GROUP BY student_id

试题三、查找名字中含“佟”字的老师的总数

SELECT count(teacher_id)
FROM tbl_teacher
where name like "%佟%"

试题四、查询没有学过李玉婷老师课的同学的学号、姓名

select DISTINCT a.name,a.student_id
from
		(select DISTINCT s2.name name,s2.student_id
		from tbl_course c inner join tbl_teacher t inner join tbl_score s1 inner join tbl_student s2 on c.teacher_id = t.teacher_id and c.course_id=s1.course_id and s1.student_id=s2.student_id) a

	left join 

		(select DISTINCT s2.name name,s2.student_id
		from tbl_course c inner join tbl_teacher t inner join tbl_score s1 inner join tbl_student s2 on c.teacher_id = t.teacher_id and c.course_id=s1.course_id and s1.student_id=s2.student_id
		where t.name = '李玉婷')b 
	on a.name=b.name 

WHERE b.name is null

select DISTINCT name,student_id
from  tbl_student 
where student_id not in (select student_id from tbl_score 
where course_id in (select course_id from tbl_course 
where teacher_id in(SELECT teacher_id from tbl_teacher where name = '李玉婷')))

select DISTINCT name,student_id
from tbl_student 
where student_id not in(
	select DISTINCT s2.student_id
	from tbl_course c inner join tbl_teacher t inner join tbl_score s1 inner join tbl_student s2 on c.teacher_id = t.teacher_id and c.course_id=s1.course_id and s1.student_id=s2.student_id
	where t.name = '李玉婷')

试题五、查询学过"4"且学过编号"5"课程的同学的学号

SELECT student_id
from tbl_score s
where s.course_id=4 and student_id in (
	SELECT student_id
	from tbl_score s
	where s.course_id=5)

试题六、查询编号"1"成绩比编号"2"成绩低的学生的学号

select t1.student_id,t1.number course1,t2.number course2
from 
		(select student_id,number from tbl_score WHERE course_id=1) t1
	inner join
		(select student_id,number from tbl_score WHERE course_id=2) t2
	on t1.student_id=t2.student_id
where t1.number<t2.number

试题七、找出有一门课程低于60分的学生的学号和名字

SELECT DISTINCT s2.student_id,name
from tbl_score s1,tbl_student s2
where s1.student_id=s2.student_id and s1.number<60

试题八、查询选完全部课程的学生的学号

select student_id
from tbl_score
GROUP BY student_id
HAVING COUNT(course_id)=(SELECT count(course_id) FROM tbl_course)

试题九、按平均成绩从高到低，显示所有学生的各科课程成绩

select s.student_id,s.score_id,s.number,a.avg_number
from tbl_score s
	inner join
		(select student_id,avg(number) avg_number from tbl_score group by student_id ) a
	on s.student_id=a.student_id
order by a.avg_number desc

试题十、查询各科成绩的最高分和最低分及对应的学生姓名学号

SELECT *
from
	(SELECT a.course_id course_id,a.max,b.name,b.student_id student_id 
		from 
			(SELECT course_id,max(number) max
				from tbl_score
				GROUP BY course_id) a 
			left join 
				(select name,s2.student_id student_id,course_id,number
				from tbl_score s1 inner join tbl_student s2 on s1.student_id=s2.student_id
				) b 
			on a.course_id=b.course_id 
	where a.max=b.number )a2 
inner join

	(SELECT a.course_id course_id,a.min,b.name,b.student_id student_id 
		from 
			(SELECT course_id,min(number) min
				from tbl_score
				GROUP BY course_id) a 
			left join 
				(select name,s2.student_id student_id,course_id,number
				from tbl_score s1 inner join tbl_student s2 on s1.student_id=s2.student_id
				) b 
			on a.course_id=b.course_id 
where a.min=b.number ) b2

on a2.course_id = b2.course_id