23-30

23.查询95033班和95031班全体学生的记录

select * from student where class in('95031','95033') order by class;

24.查询存在85分以上成绩的课程c_no

select * from score where degree>85;

25.查出所有’计算机系’ 教师所教课程的成绩表
思路是 先找出course 表中的所有计算机系课程编号，然后再根据这个编号查询score表
通过 course 表查询该教师的课程编号

 select no from course where t_no in(
 select no from teacher where department='计算机系'
 );

根据课程好查询成绩表

select * from score where c_no in(
select no from course where t_no in(
select no from teacher where department='计算机系'
 );

26、查询 计算机系 与 电子工程系 中的不同职称的教师。
用到两个关键字
not ：非
union:合并两个子集

select * from teacher where department='计算机系' 
and profession not in ( select profession from teacher where department='电子工程系')
union
select  * from teacher where department='电子工程系'
and profession not in( select profession from teacher where department='计算机系'
);

27, 查询选修编号为"3-105"课程且成绩至少高于选修编号为’3-245’同学的c_no,s_no和degree,并且按照degree从高到地次序排序.
这里用到any这个关键字因为出现至少这个关键字

select * from score where c_no='3-105' 
and degree> any(select degree from score where c_no='3-245'
) order by degree desc;

28.查询选修编号为"3-105"且成绩高于选修编号为"3-245"课程的同学的 score 表。
关键字all 所有条件

 select * from score where c_no='3-105' 
 and degree > all(select degree from score where c_no='3-245'
 );

29、查询所有教师和同学的 name ,sex, birthday

select name,sex,birthday from student
union
select name,sex,birthday from teacher;

30、查询所有’女’教师和’女’学生的name,sex,birthday

select name,sex birthday from student where sex='女'
union 
select name,sex,birthday from teacher where sex='女';