sql1复习笔记8

sql1复习笔记8

1.查询练习-子查询

查询出“计算机系”教师所教课程的成绩表
思路

select * from teacher where depart='计算机系';

select * from course where tno in (select tno from teacher where depart='95032');

select * from score where cno in (select cno from course where tno in (select tno from teacher where depart='95032'));

一步步查，把选的语句作为条件。

2.查询练习-union和notin的使用

查询【95032与5032】不同职称的教师的tname和prof。
注意：题目是要不同职称的，都有的职称是不要的。
teacher表

select prof from teacher where depart='5032';

select * from teacher where depart='95032' and prof not in(select prof from teacher where depart='5032') 
union 
select * from teacher where depart='5032' and prof not in(select prof from teacher where depart='95032');

3.查询练习-any表示至少一个desc降序

查询选修编号为3-105课程且成绩【至少】高于选修编号3-245的其中一个同学的成绩的cno sno和degree。
并按degree从高到低排序

select * from score where cno='3-245';
select * from score where cno='3-105';
//至少？大于其中至少一个。
select * from score where cno='3-105' and degree>any(select degree from score where cno='3-245') 
order by degree desc;

order by是排序，any是至少一个，desc是降序。

4.查询练习-all表示所有

查询选修编号为3-105且成绩高于【全部】选修编号为3-245课程的同学的cno、sno和degree。

select * from score where cno='3-245';
select * from score where cno='3-105';

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

5.查询练习-as取别名-union求并集

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

select tname,tsex,tbirthday from teacher;
select sname,ssex,sbirthday from student;

select tname,tsex,tbirthday from teacher 
union 
select sname,ssex,sbirthday from student;

//发现第一行全是tname，加入别名
select tname as name,tsex as sex,tbirthday as birthday from teacher 
union 
select sname,ssex,sbirthday from student;