**
**
–1.看到一张新表（或者一套新表）应该做什么 1.首先看表里的字段有什么内容，整张表是关于什么的 2.判断这张表里的首要字段是什么（后边会涉及到主键-即唯一非空的那个字段，能够与其他数据区分开的字段） 3.如果是多张表，判断表与表之间的联系（尤其是通过什么字段有联系） 4.看一下具体内容，哪些字段是主要的，哪些字段是存的具体数据，哪些字段是存的码值
select * from students; select * from courses; select * from scores; select * from teachers;–2.如何判断何时使用表连接，何时使用子查询 1.若查询时用于展示的数据来源于不同表，必须使用表连接（员工及其部门信息，员工与上级） 2.若查询时用于判断的条件来源于不同表，可以使用子查询（某部门名称下的员工信息）
–3.子查询与表连接的思路区别 子查询（多步）：先理解题意，将题目拆解成多个步骤，前一步放在后一步的子查询中 表连接（多表）：先判断需要哪些表的数据，然后通过表之间的关联关系将其连接，最后将连接产生的结果集当做一个普通的表进行后续的查询
试题 1、 查询Student表中的所有记录的Sname、Ssex和Class列。
select sname,ssex,class from students;2、 查询教师所有的单位即不重复的Depart列.
select distinct depart from teachers;3、 查询Student表的所有记录。
select * from students;4、 查询Score表中成绩在60到80之间的所有记录。
select * from scores where score between 60 and 80;5、 查询Score表中成绩为85，86或88的记录。
select * from scores where score in (85,86,88)6、 查询Student表中“95031”班或性别为“女”的同学记录。
select * from students where class=95031 or ssex ='女';7、 以class降序查询Student表的所有记录。
select * from students order by class desc;8、 以Cno升序、score降序查询Score表的所有记录。
select * from scores order by cno,score desc;9、 查询“95031”班的学生人数。
select count(sno) from students where class=95031;10、查询Score表中的最高分的学生学号和课程号。
select sno,cno from scores where score =(select max(score) from scores );11、查询‘3-105’号课程的平均分。
select avg(score ) from scores where cno='3-105';12、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。
select cno, avg(score) from scores where cno like '3%' group by cno having count(sno) >= 5;13、查询最低分大于70，最高分小于90的Sno列。
select sno from scores group by sno having min(score)>70 and max(score)<90;14、查询所有学生的Sname、Cno和score列。–开始表连接
select t1.sname,t2.cno ,t2.score from students t1 join scores t2 on t1.sno=t2.sno;15、查询所有学生的Sno、Cname和score列。
select t1.sno , t2.cname , t1.score from scores t1 join courses t2 on t1.cno=t2.cno16、查询每个班级的平均分。
select t1.class , avg(score) from students t1 join scores t2 on t1.sno=t2.sno group by t1.class17、查询“95033”班所选课程的平均分。
select cno ,avg(score) from scores t1 join students t2 on t1.sno=t2.sno where t2 .class =95033 group by t1.cno19、查询选修“3-105”课程的、成绩高于“109”号同学成绩的所有同学的记录。
select * from scores t1 where exists (select 1 from scores t2 where t1.cno = t2.cno and t1.score > t2.score and t2.sno = 109 and t2.cno = '3-105');20、查询score中选学一门以上课程的同学中分数为非最高分成绩的记录。–多个子查询，可以使用开窗排序（用哪个？）
select * from socres t1 join (select sno, max(score) max_score from scores group by sno having count(cno) >= 2) t2 on t1.sno = t2.sno where t1.score <> t2.max_score;21、查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。–与19一样
22、查询和学号为105的同学同年出生的所有学生的Sno、Sname和
Sbirthday列。--EXISTS? select t1.sno, t1.sname, t1.Sbirthday from students t1 where exists (select 1 from students t2 where substr(t1.Sbirthday, 1, 4) = substr(t2.Sbirthday, 1, 4) and t2.sno = 105 and t1.sno <> 105);23、查询“张旭“教师任课的学生成绩。–子查询？表连接？
select t1.score from scores t1 join courses t2 on t1.cno = t2.cno join teachers t3 on t2.tno = t3.tno where t3.tname = '张旭';24、查询选修某课程的同学人数多t于5人的教师姓名。–子查询？表连接？
1.select tname from teachers t1 where exists (select 1 from courses t2 where exists (select 1 from scores t3 where t2.cno = t3.cno group by t3.cno having count(sno) > 5) and t2.tno = t1.tno);25、查询95033班和95031班全体学生的记录。
select * from students where class in (95033,95031);26、查询存在有85分以上成绩的课程Cno.
select distinct cno from scores where score>85;27、查询出“计算机系“教师所教课程的成绩表。
select * from scores t1 join courses t2 on t1.cno=t2.cno join teachers t3 on t2.tno=t3.tno where t3.depart ='计算机系';28、查询“计算机系”与“电子工程系“不同职称的教师的Tname和Prof。
select tname ,prof from teachers t1 where prof in (select prof from teachers t2 group by prof having count(1)<2) and depart in('计算机系','电子工程系');29、查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的Cno、Sno和score,并按score从高到低次序排序。
select cno , sno , score from scores t1 where exists ( select 1 from scores t2 where t2.cno='3-245' and t1.cno='3-105' group by t2.cno having t1.score>min(t2.score )) order by t1.score desc;30、查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的Cno、Sno和score.
select cno , sno , score from scores t1 where exists ( select 1 from scores t2 where t2.cno='3-245' and t1.cno='3-105' group by t2.cno having t1.score>max(t2.score ));31、查询所有教师和同学的name、sex和birthday.
select sname name ,ssex sex ,sbirthday birthday from students union all select tname ,tsex ,tbirthday from teachers;32、查询所有“女”教师和“女”同学的name、sex和birthday.
select sname name ,ssex sex ,sbirthday birthday from students where ssex='女' union all select tname ,tsex ,tbirthday from teachers where tsex='女' ;33、查询成绩比该课程平均成绩低的同学的成绩表。
select * from scores t1 join (select cno ,avg(score) avg_score from scores group by cno ) t2 on t1.cno=t2.cno where t1.score < t2.avg_score;34、查询所有任课教师的Tname和Depart.
select tname, depart from teachers t1 where exists (select 1 from courses t2 join scores t3 on t2.cno = t3.cno where t2.tno = t1.tno)35 查询所有未讲课的教师的Tname和Depart. --NOT EXISTS
select tname, depart from teachers t1 where not exists (select 1 from courses t2 join scores t3 on t2.cno = t3.cno where t2.tno = t1.tno) select * from teachers t1 left join courses t2 on t1.tno = t2.tno left join scores t3 on t2.cno = t3.cno where t2.cname is null;36、查询至少有2名男生的班号。
select class from students where ssex ='男' group by class having count(*)>=2;37、查询Student表中不姓“王”的同学记录。
select * from students where sname not like '王%';38、查询Student表中每个学生的姓名和年龄。
select sname,to_char(sysdate,'yyyy')-substr(sbirthday,1,4) age from students39、查询Student表中最大和最小的Sbirthday日期值。
select max(sbirthday),min(sbirthday) from students40、以班号和年龄从大到小的顺序查询Student表中的全部记录。
select * from students order by class desc ,sbirthday ;41、查询“男”教师及其所上的课程。
select cname from teachers t1 join courses t2 on t1.tno =t2.tno where t1.tsex='男';42、查询最高分同学的Sno、Cno和score列。
select sno ,cno ,score from scores where score = (select max(score ) from scores )43、查询和“李军”同性别的所有同学的Sname.
select sname from students where ssex in (select ssex from students where sname='李军') and sname<>'李军';44、查询和“李军”同性别并同班的同学Sname.
select sname from students where (class,ssex) in (select class,ssex from students where sname='李军') and sname<>'李军';45、查询所有选修“计算机导论”课程的“男”同学的成绩表
select * from scores t1 left join students t2 on t1.sno=t2.sno left join courses t3 on t1.cno=t3.cno where t3.cname='计算机导论' and t2 .ssex ='男';