SQLscript - 副本.doc

Create table S (Sno char(9) primary key, Sname varchar(20) not null, Ssex char(2), Sage smallint, Sdept char(2)) ; Create table C (Cno varchar(2) primary key, Cname varchar(20) not null, Cpno varchar(2), Ccredit smallint) ; Create table SC (Sno char(9), Cno varchar(2), Grade smallint, primary key (Sno, Cno), foreign key (Sno) references S(Sno), foreign key (Cno) references C(Cno) ) ; DESC S; alter table S modify (sname varchar(8)); alter table S drop (sdept); alter table S add (sdept char(2)); drop table SC; drop table S; drop table C; CREATE INDEX S_AGE ON S(SAGE); DROP INDEX S_AGE; INSERT INTO S VALUES(200215121,李勇,男, 20 ,CS); INSERT INTO S VALUES(200215122,刘晨,女, 19 ,CS); INSERT INTO S VALUES(200215123,王敏,女, 18 ,MA); INSERT INTO S VALUES(200215125,张立,男, 19 ,IS); INSERT INTO C VALUES(1,数据库,5, 4); INSERT INTO C VALUES(2,数学,null, 2); INSERT INTO C VALUES(3,信息系统,1,4); INSERT INTO C VALUES(4,操作系统,6,3); INSERT INTO C VALUES(5,数据结构,7,4); INSERT INTO C VALUES(6,数据处理,null,2); INSERT INTO C VALUES(7,PASCAL语言,6,4); INSERT INTO SC VALUES(200215121,1, 92); INSERT INTO SC VALUES(200215121,2, 85); INSERT INTO SC VALUES(200215121,3, 88); INSERT INTO SC VALUES(200215122,2, 90); INSERT INTO SC VALUES(200215122,3, 80);  create index sind on s(sage); create unique index sind2 on s(sname desc); drop index sind2 ; select sno , sname from s ; select sname, sno , sdept from s ; select * from s ; select sname, 2012 - sage as birthyear from s ; select sno from sc; select distinct sno from sc; select all sno from sc; select sname from s where sdept= CS; select sname,sage from s where sage20; select sname,sdept,sage from s where sage between 20 and 23 ; select sname,sdept,sage from s where sage not between 20 and 23 ; select sname,sdept,sage from s where sage 20 or sage 23 ; select sname, ssex from s where sdept in (CS, MA, IS) ; select sname, ssex from s where sdept not in (CS, MA, IS) ; select sname, sno,ssex from s where sname like 刘%; select * from c where cname like 数据__ ; sele