2.16
create table Studentinfo
 (Snumber char(8) not  null,
Sname char(8) not  null,
sex char(2) not null,
Sbirthday datetime,
Sdepartment char(12));

2.17
alter table Studentinfo
add   Stel  char(16);


2.18
alter table Studentinfo 
add ɼ decimal(3,0);
alter table studentinfo 
drop column Sscore;


2.19
alter table Studentinfo 
modify  Stel  int;


2.20
alter table Studentinfo 
drop unique(Sname);

2.21
drop table Studentinfo

2.22
CREATE  CLUSTER  INDEX StuSname ON  Studentinfo (Snumber desc)

2.23
DROP  INDEX  StuSname

2.24
select XMing,  XHao,  YXi
from  XSheng;

2.25
select  *
from  XSheng;
òѯȼڣ
select  XHao,  XMing,  XBie,  NLing,  YXi
from  XSheng;

2.26
select XMing,  2005-NLing 
from  XSheng 


2.27
select  XMing,   ݡ, 2005-NLing,  YXi
from  XSheng; 

2.28
select  XMing
from  XSheng
where  YXi=ϵ;

2.29
select  XMing, XHao, NLing
from   XSheng
where  NLing<=22;

2.30
select  XMing, Xbie, YXi
from  XSheng
where NLing between 20 and 23;

2.31
select  XMing, XBie
from  XSheng 
where  YXi  not  in (ϵ, ѧϵ, ѧԺ);

2.32
select   *
from  XSheng
where  XHao   like  04020001;

2.33
select  XMing, XBie
from  XSheng 
where  XMing  like  04%

2.34
select  KChHao, MCheng, XFen
from   KCheng
where  MCheng  like  C\_Language ESCAPE  \; 

2.35
select  XHao, KChHao
from  XKe
where  ChJi  is  NULL;


2.36
select  XMing
from  XSheng 
where  YXi=ѧԺ  and  NLing<20;


2.37
select  XHao,  ChJi
from   XKe
where  KChHao=3    
order  by  ChJi  desc;


2.38
select  *
from  XSheng
order  by  YXi, NLing desc;


2.39
select  count(*)
from  XSheng;


2.40
select  count(distinct  XHao)
from  XKe;

2.41
select  avg(ChJi)
from  XKe
where  KChHao=4;


2.42
select   max(ChJi)
from   XKe
where  KChHao=3;

2.43 
select  KChHao,  count(XHao)
from  XKe
group by  KChHao; 

2.44
select  XHao
from  XKe
group  by  XHao
having  count(*)>3;

2.45
select  XSheng.*,  XKe.*
 from XSheng, XKe
 where  XSheng.XHao= XKe.XHao; 

2.46
select  XSheng.XHao,  XMing, XBie,  NLing, YXi,  KChHao,  ChJi
from  XSheng,  XKe
where  XSheng.XHao= XKe.XHao;

2.47
select   first.KChHao,  second.XXKe
from  KCheng  first,   KCheng  second
where  first.XXKe=second.KChHao;

2.48
select  XSheng.XHao,  XMing
from  XSheng,  XKe
where  XSheng.XHao= XKe.XHao  and  XKe.KChHao=2  and  XKe.ChJi>90;


2.49
select  XSheng.XHao,  XMing,  MCheng,  ChJi
from  XSheng,  XKe,  KCheng
where  XSheng.XHao= XKe.XHao  and  XKe.KChHao=KCheng.KChHao;


2.50
select  XHao,  XMing,  YXi
from  XSheng
where  YXi  in
 ( select  YXi
from  XSheng
where  XMing=);  

2.51
select  XMing,  NLing
from  XSheng
where  NLing< any (select  NLing
              from  XSheng
              where  YXi=ϵ )  and  YXi<> ϵ; 

2.52
select  XMing
from  XSheng 
where  exists
 ( select  *
from  XKe
where  XHao=XSheng.XHao  and  KChHao=1);

2.53
select  *
from XSheng
where  YXi=ѧԺ
union
select  *
from XSheng
where  NLing<=20;

2.54
select *
from XSheng
where YXi=ѧԺ and NLing<=20;


2.55
select *
from XSheng
where YXi=ѧԺ and NLing>20;


2.56
insert  
into  XSheng 
values(05020020,,С, 21, ϵ);


2.57
insert   
into  XKe(XHao,KChHao)
values(05020020, 1); 


2.58
create  table  deptage
 (YXi  char(15) 
  avgage  smallint);

insert
into  deptage (YXi, avgage)
select   YXi,  avg(NLing)
from XSheng
groupe  by  YXi;

2.59
update  XSheng
set  NLing=23
where  XHao=05020025;


2.60
update  XSheng
set  NLing=NLing+1;

2.61
update   XKe
set  ChJi=0

where  ѧԺ=
 ( select  YXi
  from   XSheng
  where   XSheng.XHao=XKe.XHao); 

2.62
delete 
from  XSheng
where  XHao=05020025;

2.63
delete  
from   XKe;

2.64
delete
from  XKe
where  ѧԺ= 
( select  YXi
from  XSheng
where  XSheng.XHao=XKe.XHao);


2.65
create   view  Jsj_ Studentinfo
as 
select XHao,Xhao,SsexXBie,NLing
from XSheng
Where YXi='ѧԺ';


2.66
create  view Jsj_s1 (XHao,XMing, ChJi)
as
select  XSheng.XHao,  XMing  ChJi
from XSheng, XKe

2.67
create  view  Jsj _s2
as
select  XHao, XMing, ChJi
from  Jsj_s1
where  ChJi>=90;  

2.68
create  view  birth_st(XHao, XMing,ChShNF)
as   
select XHao, XMing, 2005-NLing
from  XSheng;

2.69
create  view  XH_ChJ(XHao,  ChJAvg)
as   select  XHao,  avg(ChJi)
from  XKe
group by  XHao;


2.70
create   view  Nan_st(XHao,XMing,XBie,NLing,YXi)
as 
select  *
from  XSheng
where  XBie=С;

2.71
DROP VIEW Jsj_ Studentinfo;

2.72
drop  view   Jsj_s1;

2.73
Select * from Jsj_ Studentinfo where NLing<20;

2.74
select  XHao, XMing
from  Jsj_ Studentinfo,  XKe
where  Jsj_ Studentinfo .XHao= XKe. XHao  and  XKe.KChHao=1;


2.75 
select * 
from XH_ChJ
where ChJAvg > =75;

2.76
UPDATE Jsj_ Studentinfo  
SET YXit = 'ѧԺ'
WHERE XMing = 'С'; 

2.77
INSERT
INTO  Jsj_ Studentinfo 
VALUES ( 04050006, , 18);

2.78
DELETE 
FROM  Jsj_ Studentinfo
WHERE Snumber= 04050006;


2.79
grant  select
on   table  XSheng
to user1;

2.80
grant  all  privileges
on  table  XSheng, KCheng
to  user2,  user3;

2.81
grant  select
on   table  XKe
to  public;

2.82
grant  update(XHao),  select
on  table  XSheng
to  user4;

2.83
grant  insert
on  table  XKe
to  user5
with  grant  option;

grant  insert  
on  table   XKe
to  user6
with   grant  option;

2.84
revoke  update(XHao)  on  table  XSheng  from  user4;

2.85
revoke  select  on  table XKe from  public;

2.86
revoke  insert  on  table  Xke  from  user5;



