SQLscript - 副本

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 V ALUES('200215121','李勇','男', 20 ,'CS'); INSERT INTO S V ALUES('200215122','刘晨','女', 19 ,'CS'); INSERT INTO S V ALUES('200215123','王敏','女', 18 ,'MA'); INSERT INTO S V ALUES('200215125','张立','男', 19 ,'IS'); INSERT INTO C V ALUES('1','数据库','5', 4);INSERT INTO C V ALUES('2','数学',null, 2);INSERT INTO C V ALUES('3','信息系统','1',4);INSERT INTO C V ALUES('4','操作系统','6',3);INSERT INTO C V ALUES('5','数据结构','7',4);INSERT INTO C V ALUES('6','数据处理',null,2); INSERT INTO C V ALUES('7','PASCAL语言','6',4); INSERT INTO SC V ALUES('200215121','1', 92); INSERT INTO SC V ALUES('200215121','2', 85); INSERT INTO SC V ALUES('200215121','3', 88); INSERT INTO SC V ALUES('200215122','2', 90); INSERT INTO SC V ALUES('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 sage<20;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 '数据__' ;select cno, cname from c where cpno is null ;select cno, cname from c where cpno is not null ;select sname from s where sdept = 'CS' and sage < 20 ;select sno , grade from sc where cno ='3' order by grade desc; select * from s order by sdept asc, sage desc ;select count(*) from s ;select count(distinct sno) from sc ;select avg(grade) from sc where cno ='2' ;select max(grade) from sc where cno ='2' ;select sum(grade) from sc where sno ='200215121' ;select cno, count(*) from sc group by cno ;select sno from sc group by sno having count(*)>=3 ;select * from s, sc where s.sno = sc.sno ;select o, s.cpnofrom c f , c swhere f.cpno = o and s.cpno is not null;select o, ofrom c f , c swhere f.cpno =s.cpno and o<o ;select *from s, scwhere s.sno = sc.sno (+);select *from s left outer join sc on s.sno = sc.sno; select *from s,scwhere s.sno = sc.sno and cno='2' and grade >=90 select s.sno,s.sname,cname,gradefrom s,c,scwhere s.sno = sc.sno and o=oorder by s.sno;select * from swhere sdept in(select sdept from s where sname='刘晨') ; Select sno,sname from s where sno in(Select sno from sc where cno in(Select cno from c where cname = '信息系统')); Select sname,sage from sWhere sdept <> 'CS' and sage <(Select max(sage) from s where sdept = 'CS') ; Select sname,sage from sWhere sdept <> 'CS' and sage < any(Select sage from s where sdept = 'CS') ;Select sname,sage from sWhere sdept <> 'CS' and sage < all(Select sage from s where sdept = 'CS') ;In =anySelect * from s fWhere exists(Select * from swhere s.sname='刘晨' and f.sdept=s.sdept) ; select sname from s,scwhere s.sno=sc.sno and cno<>'1' ;select sname from swhere not exists(select * from scWhere s.sno=sc.sno and cno='1' ) ;Select sno from sc where cno='1'unionSelect sno from sc where cno='2' ;Select sno from sc where cno='1' or cno='2' Select sno from s where ssex='女'unionSelect sno from sc where grade>90;Select * from s where sdept = 'CS'intersectSelect * from s where sage <=19 ;Select sno from sMinusSelect sno from sc ;Insert into s(sno,sname,ssex,sdept,sage) values ('200215128', '陈冬', '男', 'IS',19); insert into sc(sno,cno) values('200215128', '1') ; insert into sdselect sdept,avg(sage)from sgroup by sdept;update sset sage = 22, ssex = '男'where sno = '200215121'update sset sage = sage + 1 ;update scset grade = 0where sno=any(select snoFrom sWhere sdept = 'CS')Delete from scWhere sno = '200215128'Delete from sWhere sno = '200215128' ;Delete from scWhere sno=any(select snoFrom sWhere sdept = 'CS') ;Delete from sc ;Drop table sc ;commit;rollback;create view is_sasselect * from swhere sdept = 'IS'create view is_s(isno,isname,issex,isage,isdept) asselect * from swhere sdept = 'IS'create view is_s1asselect is_s.sno, sname, ssex, sage, gradefrom is_s, scwhere is_s.sno = sc.snoand cno = '1' ;insert into sc(sno,cno,grade) values('200215125', '1',90) ; create view is_s2asselect *from is_s1where grade >= 90 ;create view s_g(sno, gavg)asselect sno, avg(grade)from scgroup by sno ;drop view is_s ;drop view is_s1 ;select *from s_gwhere gavg >85 ;create user u1 identified by u1;grant CREATE SESSION to u1;create user u2 identified by u2;grant CREATE SESSION to u2;grant select on s to u1;grant all on c to u1;grant insert(sno,sname,sage),update(sage) on s to u1; grant select on sc to public;grant update on sc to u1 with grant option;revoke update on sc from u1;create role r1;grant r1 to u1,u2;grant select on sc to r1;revoke select on sc from r1;Create table S(Sno char(9) primary key, Sname varchar(8) 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) ) ;create table d(dno char(2) primary key,dname varchar(20) not null,phone varchar(10)) ;insert into d values('CS','计算机系','5387');insert into d values('MA','数学系','5569');insert into d values('IS','信息系','2340');insert into d values('FL','外语系','4576');insert into d values('AT','美术系','8216');alter table dadd primary key(dno);alter table dadd constraint pk primary key(dno);alter table ddrop constraint pk ;Create table S(Sno char(9) primary key,Sname varchar(8) not null,Ssex char(2),Sage smallint,Sdept char(2),Foreign key(sdept) references d(dno)) ;alter table sadd foreign key(sdept) references d(dno);alter table sadd constraint fk_d foreign key(sdept) references d(dno) on delete cascade ;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) ) ; alter table SCadd (foreign key (Sno) references S(Sno), foreign key (Cno) references C(Cno) );Create table S(Sno char(9) primary key,Sname varchar(8) not null,Ssex char(2) check (ssex in ('男','女')),Sage smallint,Sdept char(2)) ;Alter table sAdd check (ssex in ('男','女'));Alter table sAdd constraint cksex check (ssex in ('男','女')); _alter table scadd check(grade between 0 and 100) ;alter table sadd check(sage between 16 and 25 and sdept='CS' or sage between 16 and 35 and sdept<>'CS');上机六-------------------------------------------alter table sadd check(ssex in ('男','女'));alter table sadd check(sage between 17 and 28 and ssex='男' or sage between 16 and 30 and ssex='女');alter table sadd check(ssex in ('男','女'))add check(sage between 17 and 28 and ssex='男' or sage between 16 and 30 and ssex='女'); alter table sadd check(ssex in ('男','女') and(sage between 17 and 28 and ssex='男' or sage between 16 and 30 and ssex='女') );minage smallint;BEGINSelect min(sage) into minage from s;Update s set sdept='IS' where sage=minage;END ;DECLAREMinage smallint;Maxage smallint;Avgage smallint;BEGINSelect min(sage), max(sage) into minage, maxage from s;Avgage:=(minage+maxage) / 2;Insert into R values(avgage);END ;DECLAREScount smallint;Minage smallint;BEGINSelect count(sno), min(sage) into scount, minage from s;If scount>100 thenDelete from s where sage=minage;ElseInsert into s values('200315128', '陈冬', '男', 19, 'IS') ;End if ;END;n integer;BEGINFOR n IN 1..100 LOOPInsert into R values(n) ;END LOOP ;END ;DECLAREn integer :=1;BEGINWHILE n<=30000 LOOPInsert into R values(n);n:=n*2;END LOOP;END ;DECLAREn integer :=1;BEGINLOOPInsert into R values(n);n:=n*2;IF n>=30000 THENEXIT;END IF;END LOOP;END ;DECLAREminage smallint;minageno char(9);BEGINSelect min(sage) into minage from s;Select sno into minageno from s where sage=minage;Insert into smin values(minageno);EXCEPTIONWHEN TOO_MANY_ROWS THENInsert into error values('返回多条记录！',sysdate); END ;-------------------------------------------Create table cb(i int primary key,no int,sqr int,cube int);Declarev_num int ;v_sq int;v_cb int;v_ct int;v_i int:=0;beginFOR v_num IN -20..-1 LOOPv_sq:= v_num * v_num;v_cb:= v_num * v_num * v_num;v_i:=v_i+1;If v_num=-10 thenInsert into cb values(v_i ,v_num, v_sq, -v_cb);ElseInsert into cb values(v_i ,v_num, v_sq, v_cb);End if;END LOOP;v_num:=2;select count(*) into v_ct from cb;WHILE v_num< v_ct+1 LOOPv_sq:= v_num * v_num;v_cb:= v_num * v_num * v_num;v_i:=v_i+1;Insert into cb values(v_i ,v_num, v_sq, v_cb);select count(*) into v_ct from cb;v_num:= v_num +2;END LOOP;Commit;END ;v_ct int;v_n int;v_no_s int:=0;v_no int;v_sqr int;v_sqr_s int:=0;v_cube int;v_cube_s int:=0;v_cct int:=0;v_int int;BeginSelect count(*) into v_ct from cb;For v_n in 1..v_ct loopSelect no,sqr,cube into v_no, v_sqr, v_cube from cb where i=v_n;If v_no>0 thenv_no_s:= v_no_s+ v_no;elsev_no_s:= v_no_s- v_no;end if;v_int:= v_sqr/3;if v_int *3= v_sqr thenv_sqr_s:= v_sqr_s+ v_sqr;end if;v_int:= v_cube/5;if v_int *5= v_cube thenIf v_cube>0 thenv_cube_s:= v_cube_s+ v_cube;elsev_cube_s:= v_cube_s- v_cube;end if;v_cct:= v_cct+1;end if;end loop;v_cube_s:= v_cube_s/ v_cct;Insert into cb values(v_ct+1 , v_no_s, v_sqr_s, v_cube_s);Commit;End;CREATE PROCEDURE delsno(no char)ASBEGINDelete from sc where sno=no ;Delete from s where sno=no ;Commit;END ;Call Delsno('200215121');BEGINDelsno('200215121');END;Grant execute on delsno to u1;CREATE OR REPLACE PROCEDURE delsno(no char)ASSct smallint;BEGINSelect count(*) into sct from s where sno=no and sdept='CS' ;If sct>0 thenDelete from sc where sno=no ;Delete from s where sno=no ;Commit;End if;END ;DROP PROCEDURE delsno;CREATE OR REPLACE PROCEDURE addc(cnum char) ASno smallint ;BEGINSelect count(*) into no from c where cno=cnum;If no=0 thenInsert into c values(cnum, '计算机基础',null,2);ElseUpdate c set ccredit=2 where cno=cnum;End if;Insert into sc(sno,cno)Select sno,cnumFrom sWhere sno not in(select sno from sc where cno = cnum);Commit;END ;call addc('1') ;call addc('9') ;CREATE FUNCTION snoc(no char) RETURN smallint ASsccount smallint ;BEGINSELECT count(*) INTO sccountFROM SCWHERE SNO=no ;RETURN sccount ;END ;Select s.*, snoc(sno) from s;Grant execute on snoc to u1;Drop FUNCTION snoc ;上机八-------------------------------------------CREATE OR REPLACE PROCEDURE ccb(n int)ASv_num int :=50;v_sqr int;v_cb int;v_i int;v_int int;beginselect count(*) into v_i from cb;v_sqr:= v_num* v_num;v_cb:= v_num* v_num * v_num;WHILE v_cb<=n LOOPv_i:= v_i+1;v_int:= v_num/10;if v_int*10 = v_num thenv_sqr:= -v_sqr;end if;Insert into cb values(v_i , v_num, v_sqr, v_cb);v_num:= v_num +2;v_sqr:= v_num* v_num;v_cb:= v_num* v_num * v_num;END LOOP;Commit;END ;Call ccb(300000);Beginccb(300000);End;Grant execute on ccb to u1;Call b(600000);。