1018课后作业

select * from class_221;
select * from stu_221;
select * from score_221;

create table class_221 (年级 number,课程 varchar2(10));

insert into class_221 values(1,'语文');
insert into class_221 values(1,'数学');
insert into class_221 values(2,'语文');
insert into class_221 values(2,'数学');
insert into class_221 values(3,'语文');
insert into class_221 values(3,'数学');
insert into class_221 values(3,'英语');
commit;

drop table class_221;
select * from class_221;

create table stu_221(学号 varchar2(20)
                      ,  姓名 varchar2(20)
                     ,  年龄 number(20)
                     ,  年级 number(20)
                     );
                     
insert into stu_221 values('001','张三',  6,  1);
insert into stu_221 values('002','李四',  7,  2);
insert into stu_221 values('004','卫国',  7,  2);
insert into stu_221 values('003','王五',  8,  3);
commit;

select *
from  stu_221;

create table score_221(学号   varchar2(20)
                   ,课程  varchar2(20)
                   ,分数  number
                   );
insert into score_221 values('001','语文',88);
insert into score_221 values('001','数学',79);
insert into score_221 values('002','数学',85);
insert into score_221 values('003','语文',95);
insert into score_221 values('003','数学',80);
insert into score_221 values('003','英语',60);
insert into score_221 values('004','语文',95);
insert into score_221 values('004','数学',95);


select * from score_221;

---地址信息建表语句
create table zone (
                  name varchar2(20),
                  address varchar2(50)
                  );
 
 insert into zone values('张三','湖北-宜昌-五峰');
 insert into zone values('李四','内蒙古-呼和浩特-清水河');
 insert into zone values('小明','广东-深圳-宝安');

commit;
select * from zone;
drop table zone;

建表：create table dd (
        waybillno number(3),
        arr_time date,
        leave_time date);
        
    insert into dd values(1,to_date('2018-03-01 12:40:10','yyyy-mm-dd hh24:mi:ss'),
                           to_date('2018-03-01 13:50:10','yyyy-mm-dd hh24:mi:ss'));
    insert into dd values(2,to_date('2018-03-01 11:29:45','yyyy-mm-dd hh24:mi:ss'),
                           to_date('2018-03-01 12:40:18','yyyy-mm-dd hh24:mi:ss'));
    insert into dd values(3,to_date('2018-03-01 14:35:10' ,'yyyy-mm-dd hh24:mi:ss'),
                           to_date('2018-03-01 16:00:00','yyyy-mm-dd hh24:mi:ss'));
select * from dd;

to_date(20180301150000,'yyyymmddhh24miss')
to_date(20180301130000,'yyyymmddhh24miss')

1、用一条sql查询出每个年级参加每门科目考试的总人数及平均成绩。

select  t2.年级,t1.课程
       ,count(1) as count_1
       ,avg(t1.分数) as avg_sc
from score_221 t1 
inner join stu_221 t2 
        on t1.学号 = t2.学号
group by t2.年级,t1.课程;

2、用一条sql查询出每个年级参加了所有科目考试的学生的总人数。
–先确定这些学生是否参加了所有科目考试
实际参加的考试课程数 = 应该参加的考试课程数

select t3.年级
      ,count(1) as 总人数
from (
      select t2.学号,t2.年级
            ,count(1) as 实际参加的考试课程数
      from score_221 t1 
      inner join stu_221 t2 
              on t1.学号 = t2.学号
      group by t2.学号,t2.年级
     ) t3 
inner join (
            select  t.年级
                   ,count(1) as 应该参加的考试课程数
            from class_221 t
            group by t.年级
           ) t4
       on t3.年级 = t4.年级
      and t3.实际参加的考试课程数 = t4.应该参加的考试课程数
group by t3.年级;

3、用一条sql查询2年级参加了所有科目考试并且每门课程的成绩都在80分以上的学生信息。

select t1.学号
      ,count(1)
from score_221 t1 
inner join stu_221 t2 
        on t1.学号 = t2.学号
where t2.年级 = 2
and t1.分数 > 80
group by t1.学号
having count(1) = (
                   select count(2)
                   from class_221  t
                   where t.年级 = 2
                   );

–city

select * from zone;

select t.name, t.address
       ,substr(t.address
              ,instr(t.address,'-',1,1) + 1 --从哪个位置开始截取
              ,instr(t.address,'-',1,2) - instr(t.address,'-',1,1) -1  --截取的长度
              ) as city
from zone t 

—运单快递
如何统计出在2018-03-01 13:00到2018-03-01 15：00之间在该网点有停留的运单数
select * from dd;

         13                15

arr leav
arr leav
arr leav
arr leav

select count(1)
from dd t 
where t.arr_time <> t.leave_time
      and t.arr_time <= to_date(20180301150000,'yyyymmddhh24miss') 
	  and t.leave_time >= to_date(20180301130000,'yyyymmddhh24miss')