------------------------------------------------------
create table students(st_id varchar(20),st_name varchar(50),sex varchar(10))

insert into  students(st_id,st_name,sex)
select 'st001','张杰', '男' union all
select 'st002', '公孙燕飞' ,'男' union all
select  'st003', '王楠', '女' union all
select  'st004', '王伟', '男'  union all
select 'st005','李燕纹', '女' union all
select  'st006', '孙武' ,'男' 
select *
from students
 


create table teachers(t_id varchar(20),t_name varchar(50),t_lesson varchar(50))

insert into teachers 

select 't001', '张老师' ,'数学' union all
select 't002', '李老师', '英语' 

delete   from results
create table results(r_id varchar(20),r_fenshu int,r_stid  varchar(50),r_tid varchar(50))

insert into results
select 'r001','90', 'st001', 't002' union all
select 'r002', '68', 'st005', 't001' union all
select 'r003', '92', 'st003' ,'t001' union all
select 'r004', '82', 'st006', 't002' union all
select 'r005', '70', 'st002', 't002' union all
select 'r006', '86', 'st002', 't001' union all
select 'r007', '57', 'st003', 't002' union all
select 'r008', '76', 'st006', 't001' union all
select 'r009', '55', 'st001', 't001' union all
select 'r010', '77', 'st004', 't002' union all
select 'r011', '58', 'st005', 't002'
----------------------
1.
select st_id
from students
where st_name = '王伟'

2.select st_id,st_name
 from students
 where st_name like '__燕%'

3 select st_name,len(st_name) as 名字长度
  from students
 where  sex ='男'

4 select min(r_fenshu) as 最低分数
  from teachers t inner join results  r on t.t_id =r.r_tid 
  where t_lesson ='数学' --这个是不考虑成绩中有null值的
5 select s.st_id  as 学生编号,r_fenshu as分数,r_tid  as 课目号
  from students s inner join results r on s.st_id =r.r_stid
  where s.sex='女'
 --如果还要课目的名称的话请用下面的
 select s.st_id  as 学生编号,r.r_fenshu as 分数,r.r_tid  as 课目号,t.t_lesson as 课目名称
  from students s inner join results r on s.st_id =r.r_stid  
  inner join teachers t on r.r_tid  = t.t_id
  where s.sex='女'

6 select avg(r.r_fenshu)
  from results r inner join teachers t on r.r_tid  = t.t_id 
  where t.t_lesson='英语'

7.select  *
 from students s inner join results r on s.st_id =r.r_stid 
                 inner join teachers t on  r.r_tid = t.t_id
where s.st_id in (select top 2  st_id from students  order by st_id desc)
order by s.st_id desc

8 select sum(r.r_fenshu) as 总分
  from  results r inner join students s on r.r_stid =s.st_id
  where s.st_name = '王楠'
9.select distinct s.st_id,s.st_name
 from students s inner join results r on s.st