--1012课堂内容--查询教师所有的单位即不重复的Depart列。select  distinct t. depart from teacher t;--两种条件相加去除重复内容select  distinct t. depart ,t.prof from teacher t;--查询Score表中成绩在60到80之间的所有记录。select  *from score where  degree between 60 and 80;  --小的在前，大的在后--查询Score表中成绩为85，86或88的记录。select  *from score where degree =85 or degree =‘86‘ or degree =88;select  *from score where degree in(85,86,88)--查询男生的成绩--括号select后必须是单个字段select *from score where  sno in (select sno from student where ssex=‘男‘);   --子查询--模糊查询--查询不姓王的同学--字符数量不固定  %任意数量的字符select *from student where sname not  like ‘王%‘;--字符数量固定   _单个字符select *from student where sname like ‘王_‘;select *from student where sname like ‘王_王‘;--查询“95031”班的学生人数。--内置函数--1.聚合函数   返回单个值--记录条数select count (sno)from student where sclass=95031;--查平均成绩select sum（degree）/count (1) from score;select avg(degree) 平均值 from score;--合计总成绩select sum（degree） from score;--最高成绩select * from score order by degree desc;select max (degree)最大值, min (degree)最小值,avg(degree) 平均值 from  score;--日期select min(t.sbirthday)from student t;select max (s.degree)from score s-- 查询Score表中的最高分的学生学号和课程号。（子查询或者排序）select sno, cno from score where Degree in (select max (degree)from score );--子查询select *from (select * from score  order by degree desc) where rownum=1;  --排序--select sno, cno from score where Degree in (select degree from score);--伪列select * from score  where rownum=1;--查询每门课的平均成绩。--按照课程编号进行分组，在计算每组的平均成绩select  cno,avg (degree) 平均值 from score group by cno;--按照课程编号进行分组，在计算每组的平均成绩,对分组后的结果进行过滤select cno ,avg(degree) 平均值, count (1) , sum(degree) ，(sum(degree) /count (1))平均值 from  score group by cnohaving avg(degree)>80;  --分组select *from (select cno ,avg(degree) a , count (1) , sum(degree) ，(sum(degree) /count (1))平均值 from  score group by cno ) where a>80 select avg(degree) 平均值 from score group by cno;--12、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。select cno from score where cno like ‘3%‘    --以3开头的课程select cno, avg (degree)  from score where cno like ‘3%‘ group by cno--至少有5人选修  考试--在成绩表中课程的考试的学生数>=5个select  cno, a ,c 考试人数 from(select  cno, avg(degree) a ，count(degree) c  from score where cno like ‘3%‘ group by cno) where  c>=5--查询分数大于70，小于90的Sno列。select sno from score where degree between 70  and 90--14、查询所有学生的Sname、Cno和Degree列。Select cno,degree from score where Select sname from student--表连接，一定有主外键关系：都好隔开两个表形成笛卡尔积，再进行where筛选，通过表主外键关系筛选，where只是个筛选条件select Sname, Cno, Degree from Student,Score where Student.Sno = Score.Sno;--查询所有学生的Sno、Cname和Degree列。

select sname, ssex,sclass from student;--查询教师所有的单位即不重复的Depart列。select  distinct  depart from teacher;-- 查询Student表的所有记录。select * from student;--查询Score表中成绩在60到80之间的所有记录。select *from score s where s. degree>=60 and s.degree<=80;--查询成绩是85  86 88 的select * from score s where s.degree=85 or s.degree=86 or s.degree=88;--查询Student表中“95031”班或性别为“女”的同学记录。select *from student s where s.sclass=‘95031‘or s.ssex=‘女‘;--以Class降序查询Student表的所有记录select * from student  order by sclass desc;-- 以Cno升序、Degree降序查询Score表的所有记录。--建表，并添加数据select * from score s order by s. cno , s.degree desc;create table grade(low  number(3),upp  number (3),rank  char(1));insert into grade values(90,100,‘A‘);insert into grade values(80,89,‘B‘);insert into grade values(70,79,‘C‘);insert into grade values(60,69,‘D‘);insert into grade values(0,59,‘E‘);select * from grade;