测试开发面经（五）SQL查询进阶

测试开发面经（四）SQL基础

测试开发面经（五）SQL查询-进阶

一、SQL代码

6.查询

36).查询籍贯是河北省的教师所教的课程信息

select teacher.tname,teacher.tno,teacher.home,course.* 
from teacher,course,teacher_course
where teacher.tno = teacher_course.tno
and teacher_course.cno = course.cno
and home like '河北%'
ORDER BY teacher.tno;

### 这个是完全符合题目要求的
select * from course
where cno in(
	select cno from teacher_course
    where tno in(
    	select tno from teacher 
        where home like '河北%'
    )
);

37).查询软件学院学生情况

select * from student
where dno in (
	select dno from department
    where dname='软件学院'
);

38).查询班级人数最多的班的学生情况 （count的应用）

select * from student 
where student.classno in(
	select s.classno from (
		select classno from student 
		group by student.classno
		order by count(*) desc
		LIMIT 1 ### 降序排列返回第一行
	) as s  ### 绕过limit 的限制 否则报错
);
  


### 不出错的写法
select * from student 
where classno in (
	select classno from student
	group by classno
	having count(*) >=all(
		select count(*) from student
		group by classno
	)
);

39).查询张姓学生选修的课程号、课程名

select cno,cname from course
where cno in (
	select cno from student_course
    where sno in (
    	select sno from student
        where sname like '张%'
    )
);

40).查询男学生选修课程的平均分

select cno,avg(score)
from student_course
where sno in (
	select sno
	from student
	where sex ='男'
)
### 每个男生的平均成绩
select sname,avg(score) 
from student,student_course
where student.sno = student_course.sno
group by stident_course.sno

41).查询哪些学生选修了授课学时为 54 的课程

select * from student
where sno in (
	select sno from student_course
    where cno in (
    	select cno from course
        where lecture = 54
    )
);

42).查询比软件学院学生年龄都小的其他系学生的信息。

select * from student
where year(birthday)>all(
	select year(birthday) 
	from student 
	where dno in(
		select dno from department
		where dname = '软件学院'
	)
);

43).查询比数信学院学院学生年龄大的教育学院学生信息。

select * from student 
where year(birthday)< any(
    select year(birthday) from student
    where dno in (
    	select dno from department
        where dname='数信学院'
    )
)and dno in(
	select dno from department
	where dname='教育学院'
)

44).查询班级号为 1 的班的学生 c 语言程序设计成绩的平均成绩

select avg(score) from student_course
where sno in(
	select sno from student
    where classno='1'
)and cno in (
	select cno from course
    where cname='c语言程序设计'
);

45).查询计算机导论平均成绩最高的班级。

select classno,avg(score) 
from student_course,student
where student.sno=student_course.sno 
and cno in(
	select cno from course
    where cname='计算机导论'
)group by classno
having avg(score)>= all(
	select avg(score)
    from student,student_course
    where student_course.sno=student.sno 
    and cno in (
        select cno 
        from course
    	where cname='计算机导论' 
        group by classno
    ) 
);

46).查询选修人数最多的课程是哪个老师教的，显示教师号，教师姓名

select tno,tname
from teacher where tno in(
	select DISTINCT tno from teacher_course
    where cno in(
    	select cno from student_course
        group by cno
        having count(*)>= all(
        	select count(sno) from student_course
            group by cno
        )
    ) 
);

47).查询余孝天老师所教各门课程的平均成绩

select cno,avg(score) from student_course
where cno in (
	select cno from teacher_course
	where tno in(
    	select tno
        from teacher 
        where tname='余孝天'
    )
)group by cno;

48).查询鲁婵娟老师所教课程的各个班级平均成绩

select avg(score) from student_course,student
where student_course.sno=student.sno and cno in (
    select cno from teacher_course
    where tno in (
        select tno from teacher 
        where tname = '鲁婵娟'
    )	
)group by classno;

49).查询鲁婵娟老师所教课程的学生的成绩

select student.sname,cno,score 
from student,student_course
where student.sno = student_course.sno and
cno in (
    select cno from teacher_course
    where tno in (
        select tno from teacher 
        where tname='鲁婵娟'
    )
)


### 学生的所有成绩

select student.sname,cno,score 
from student,student_course
where student.sno = student_course.sno and
student.sno in (
	select sno from student_course
    where cno in (
    	select cno from teacher_course
        where tno in (
        	select tno from teacher 
            where tname='鲁婵娟'
        )
    )
);

50).查询有多少人选修了《数据结构》课程的先修课。

select count(distinct sno) 
from student_course
where cno in (
	select cpno from course
	where cname='数据结构'
)