测试开发面经（六）SQL增删改查

文章目录

• 测试开发面经（六）SQL增删改查
• • • 6. 查询（续）
    • • 53).查询未授课教师的姓名和系
      • 54).按职称显示软件学院的教师人数。
      • 55).查询成绩高于《数据结构》平均成绩的学生信息。
      • 60).查询选修了全部课程的学生的信息。(带exists谓词)
      • 61).查询至少选修了鲁婵娟所教的课程的学生信息 (至少 not exists)
      • 64).查询选修了 3 门以上课程学生信息 (group by 在having前)
      • 66).查询软件学院年龄小于 25 岁的老师的信息 (日期计算)
      • 67).查询低于该门课程平均分的学生的成绩信息。(起别名)
    • 6.2 集合查询
    • • 69).查询不教课的老师信息。 (not in)
    • 7.更新
    • 8. 删除
    • • （7）删除没有成绩的选课记录。
      • （10）将 2000 年 9 月 1 日以前入学的学生记录删除。
    • 8.视图

但行好事，莫问前程

测试开发面经（六）SQL增删改查

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

6. 查询（续）

51).查询软件学院教师所教课程信息

select * from course
where cno in(
	select cno from teacher_course
    where tno in(
    	select tno from teacher
        where dno in (
        	select dno from department
            where dname='软件学院'
        )
    )
);

52).查询软件学院教师所教课程的成绩，将结果按课程号降序排列。

select score from student_course
where cno in(
	select cno from teacher_course 
    where tno in(
    	select tno from teacher 
        where dno in(
        	select dno from department
            where dname='软件学院'
        )
    )
)order by cno desc;

53).查询未授课教师的姓名和系

select tname,dno from teacher
where tno not in (
	select tno from teacher_course
    where cno in(
    	select cno from student_course
    )
    
);

54).按职称显示软件学院的教师人数。

select pno,count(distinct tno) from teacher
where dno in(
	select dno from department
    where dname='软件学院'
)group by pno;

55).查询成绩高于《数据结构》平均成绩的学生信息。

select * from student 
where sno in (
	select sno from student_course
    where cno in (
    	select cno from course
        where cname='数据结构'
    ) and score>(
    	select avg(score) from student_course
        where cno in(
        	select cno from course
       	 	where cname='数据结构'
        )
    )
)

56).查询学生选修课程的情况，显示学号、姓名、教师姓名、课程名称、成绩。

select student.sno,sname,tname,course.cname,score
from student,student_course,teacher_course,teacher,course
where student.sno = student_course.sno and
student_course.cno = course.cno and 
course.cno = teacher_course.cno and
teacher_course.tno=teacher.tno

57).查询法政学院教师第一学期所带班级

select distinct classno from student 
where sno in (
	select sno from student_course
    where cno in (
    	select cno from teacher_course
        where tno in(
        	select tno from teacher
            where dno in(
            	select dno from department
                where dname ='法政学院'
            )
        )
    )and cno in(
    	select cno from course
        where semester =1
    ) 
)

58).查询第1 学期哪些教师在公教楼上课。

select * from teacher
where tno in (
	select tno from teacher_course
    where semester='1' 
    and classroom like '公教楼%' 
)

59).查询数信学院的学生各科平均成绩，显示课程名、平均成绩。

select cname,avg(score) from course,student_course
where course.cno = student_course.cno 
and student_course.cno in (
	select cno from teacher_course
    where tno in (
    	select tno from teacher 
        where dno in(
        	select dno from department
            where dname='数信学院'
        ) 
    )
)
group by course.cno;

60).查询选修了全部课程的学生的信息。(带exists谓词)

​ 查询这样的学生，没有一门课不选

select * from student 
where not exists (
    # 所有课都不选
    select * from course
    where not exists(
    	select * from student_course
        where student_course.sno = student.sno
        and student_course.cno = course.cno
    )
);

61).查询至少选修了鲁婵娟所教的课程的学生信息 (至少 not exists)

查询这样的学生，排除不选鲁婵娟老师的学生

select * from student 
where not exists(
	select * from student_course
    where not exists(
    	select cno from teacher_course
        where tno in (
        	select tno from teacher
            where tname='鲁婵娟'
        )
    )
);

62).查询和张小兵同一个班级的其他学生的信息

select * from student
where classno in (
	select classno from student
    where sname='张小兵' 
)and sname!='张小兵';

63).查询和刘英伟同年出生的学生信息（参考 sql 日期操作函数）

select * from student 
where year(birthday) in(
    select year(birthday) from student 
    where sname ='刘英伟'
)and sname!='刘英伟';

64).查询选修了 3 门以上课程学生信息 (group by 在having前)

select * from student 
where sno in (
	select sno from student_course
    group by sno
    having count(cno)>3
)

65).查询至少有 2 名女生的班级的学生信息

select * from student
where classno in (
	select classno from student
    where sex ='女'
    group by classno
    having count(sex)>=2
    
);

66).查询软件学院年龄小于 25 岁的老师的信息 (日期计算)

select * from teacher
where 2021-year(birthday)<25 
and dno in(
	select dno from department 
    where dname='软件学院'
);

67).查询低于该门课程平均分的学生的成绩信息。(起别名)

select * from student_course sc
having score< (
	select avg(score) from  student_course sc2
    where sc.cno = sc2.cno
    group by sc2.cno
);

6.2 集合查询

68).年龄小于 23 岁的女老师和女学生的信息

select * from teacher
where 2021-year(birthday)<23

select * from student where sex ='女'

69).查询不教课的老师信息。 (not in)

select * from teacher
where tno not in  (
	select tno from teacher_course
)

70).查询班级号为 3,且性别为女的学生的信息

select * from student 
where classno='3' and sex ='女'

7.更新

（1）将年龄小于 18 岁的男同学的年龄都增大 1 岁。

update student set birthday = birthday-1
where 2021-year(birthday)>18

（2）将王英老师的联系电话改为 83421236。

update teacher 
set tel='83421236'
where tname='王英'

（3）将“数据结构”课程的上课地点改为“D403”。

update teacher_course 
set classroom='D403' 
where cno in (
	select cno 
    from course
    where cname='数据结构'
)

（4）将“数据库原理”课程成绩在 70 分以下的加 5 分。

update student_course set score = score+5
where cno in(
	select cno from course
    where cname='数据库原理'
) and score < 70;

（5）将所有计算机系学生的“高等数学”课的成绩加 10 分。

update student_course 
set score = score+10
where sno in (
	select sno from student
    where dno in(
    	select dno from department 
        where dname ='计算机系'
    )
)and cno in(
	select cno from course
    where cname='高等数学'
)

（6）将所有数学系学生的联系电话删除。

8. 删除 （7）删除没有成绩的选课记录。

delete from student_course 
where score is null;

（8）删除学生中姓名有“强”的记录。

delete from student 
where sname='%强%'

（9）删除数学系女同学的记录。

delete from student 
where sno in (
	select sno from student_course
    where dno in (
    	select dno from department
        where dname ='数学系'
    )
) and sex='女';

（10）将 2000 年 9 月 1 日以前入学的学生记录删除。

delete from student
where entime< '2000-9-1';

（11）删除计算机系所有教师的上课记录。

delete from teacher_course
where tno in (
	select tno from teacher
    where dno in(
    	select dno from department
        where dname='计算机系'
    )
)

（12）将平均成绩最高的课程学分加 1。

update course set credit=credit+1
where cno in(
	select distinct cno from student_course
    group by cno
    having avg(score)>=all(
    	select avg(score)
        from student_course
        group by cno
    )
)

（13）将实验时数为 36 的课程的成绩加上 10%。

update student_course set score = score +score*0.1
where cno in (
	select cno from course
    where experiment=36
);

（14）将所有没有上课的教师记录删除。

delete from teacher 
where tno not in(
	select tno from teacher_course
)

8.视图

（1）将女教师的基本情况定义为视图。

create view woman
as 
select * from teacher
where sex='女'

（2）将数学系班级号为 2008 的学生的学号、姓名、性别和专业号定义为视图。

create view 28class
as
select sno,sname,sex,dno
from student
where classno='2008'
and dno in (
	select dno from department
    where dname='数学系'
)

（3）将各个学院的学生人数定义为视图，包括学院编号、人

create view pnum(dno,人数) 
as
select dno,count(distinct sno)
from student
group by dno

（4）将平均成绩大于 90 分的课程定义为视图，包括课程号、课程名和平均成 绩。

create view 90score (cno,cname,平均成绩)
as
select course.cno,course.cname,avg(score)
from course,student_course
where course.cno = student_course.cno
group by student_course.cno
having avg(score)>90

（5）将各个教师所教课程的选课人数定义为视图，包括教师编号、选课人数。

create view teacherTostudent(tno,cno,number)
as 
select tno,teacher_course.cno,count(distinct sno) 
from teacher_course,student_course
where teacher_course.cno=student_course.cno
group by teacher_course.cno

（6）查询人数超过 500 人的学院名称。

select dname from department
where dno in (
	select dno from student
    group by dno
    having count(distinct sno) >500
)  

（7）查询选修王强老师所教课程的人数

select count(distinct sno) 
from student_course
where cno in(
	select cno from teacher_course
    where tno in(
    	select tno from teacher
        where tname='王强'
    )
)group by cno;