1、 * * oracleoracleddl oracleoracle 1 sql1-1 () studentssex(c,2) sname sname(c,8) 1student create table student ( sno char(5) ,sname char(10) not null, sdept char(2) not null, sclass char(2) not null, ssex char(2), sage number(2),constraint sno_pk primary key(sno) 2course create table course (cno char
2、(3),cname char(16) unique, ctime number(3),constraint cno_pk primary key(cno); 3teach create table teach (tname char(8), tsex char(2), cno char(3), tdate date, tdept char(2),constraint tname_pk primary key(tname,cno,tdept),constraint cno_fk foreign key(cno) references course(cno); 4score create tabl
3、e score (sno char(5), cno char(3), score number(5,2),constraint scno_fk foreign key(cno) references course(cno), constraint ssno_fk foreign key(sno) references student(sno); 1studentinsert into student(sno,sname,sdept,sclass,ssex,sage) values(96001,cs,01,21);insert into student(sno,sname,sdept,sclas
4、s,ssex,sage) values(96002,cs,01,18);insert into student(sno,sname,sdept,sclass,ssex,sage) values(96003,ma,01,18);insert into student(sno,sname,sdept,sclass,ssex,sage) values(96004,is,02,20);insert into student(sno,sname,sdept,sclass,ssex,sage) values(97001,ma,02,19);insert into student(sno,sname,sde
5、pt,sclass,ssex,sage) values(97002,cs,01,20);insert into student(sno,sname,sdept,sclass,ssex,sage) values(97003,is,03,19);insert into student(sno,sname,sdept,sclass,ssex,sage) values(97004,cs,02,19);insert into student(sno,sname,sdept,sclass,ssex,sage) values(96005,cs,02,18); 2courseinsert into cours
6、e values(001,144); insert into course values(002,144); insert into course values(003,-,72); insert into course values(004,72); insert into course values(005,64); insert into course values(006,-,64); insert into course values(007,db_design,48);insert into course values(008,56); 3teachinsert into teac
7、h values(,004,05-9-1999,cs); insert into teach values(,003,05-9-1999,cs); insert into teach values(,001,05-9-1999,ma); insert into teach values(,004,05-9-1999,is); insert into teach values(,003,23-2-00,ma); insert into teach values(,006,23-2-00,cs); insert into teach values(,004,23-2-00,is); insert
8、into teach values(,008,06-9-00,cs); insert into teach values(,007,05-9-1999,cs); 4scoreinsert into score values(96001,001,77.5); insert into score values(96001,003,89); insert into score values(96001,004,86); insert into score values(96001,005,82); insert into score values(96002,001,88); insert into
9、 score values(96002,003,92.5); insert into score values(96002,006,90); insert into score values(96005,004,92); insert into score values(96005,005,90); insert into score values(96005,006,89); insert into score values(96005,007,76); insert into score values(96003,001,69); insert into score values(9600
10、1,001,96); insert into score values(96001,008,95); insert into score values(96004,001,87); insert into score values(96003,003,91); insert into score values(96002,003,91); insert into score(sno,cno) values(96002,004); insert into score values(96002,006,92); insert into score values(96004,005,90); ins
11、ert into score values(96004,006,85); insert into score values(96004,008,75);insert into score values(96003,001,59); insert into score values(96003,003,58);1-2 () studentsex(c,2) alter table student add sex char(2);1-3 () studentsexssex alter table student rename column sex to ssex;1-4 () studentsnam
12、e sname(c,10) alter table student modify(sname char(10) not null);1-5 () scoresc_grade create index sc_grade on score(sno asc,score desc); 1-6 () sc_grade drop index sc_grade;1-7 () s1(sno,sname,sd,sa)student(sno,sname,sdept,sage) create table s1 (sno char(5),sname char(10) not null, sd char(2) not
13、null, sa number(2),constraint s1sno_pk primary key(sno);1-8 () score alter table score drop constraint scno_fk; alter table score drop constraint ssno_fk;1-9 () scorealter table score add constraint scno_fk foreign key(cno) references course(cno); alter table score add constraint ssno_fk foreign key
14、(sno) references student(sno); 1-10 () s1student_temp rename s1 to student_temp; oracleoracle sql sql sql sqlplusoracle dml 2 sql2-1 () 2-2() student(cs)s1insert into student_temp select sno,sname,sdept,sage from student where student.sdept=cs;2-3() student_grcreate table student_gr(sno char(5) prim
15、ary key,avgscore number(5,2);insert into student_gr(sno,avgscore) select sno,avg(score) from score group by sno;2-4 () student_temp2update student_temp set sa=sa+2;2-5 () courseupdate course set ctime=(select ctime from course where cname=) where cname=;2-6 () score98001, 001, 95-insert into score v
16、alues(98001,001,95);constraint ssno_fk foreign key(sno) references student(sno) student980012-7 () score97001, 010, 80-insert into score values(97001,010,80);constraint cno_fk foreign key(cno) references course(cno) course0102-8 () student96001-delete from student where sno=96001;constraint ssno_fk
17、foreign key(sno) references student(sno)2-9 () course003 -delete from course where cno=003;constraint cno_fk foreign key(cno) references course(cno)2-10 () student_temp96delete from student_temp where sno like 96%;rollback2-11 () student_tempdelete from student_temp;2-12() student_tempstudent_grdrop
18、 table student_temp cascade constraint;drop table student_gr cascade constraint; sql sql sql oracle sql 3 sql3-1 () select sno,sname,ssex,sage from student3-2 () select distinct sno from score where cno is not null3-3 () select * from student3-4 () select sno,sname,2011-sage as year of birth from st
19、udent3-5 () select sno as,2011-sage as from student3-6 () 19select sname,sage from student where sage193-7 () 18select sname,sdept,sage from student where sdept in(cs,is)3-8 () 1922(2022)select sno,sage from student where sage between 19 and 223-9 () 1922select sno,sage from student where sage not b
20、etween 19 and 223-10() select * from student where sdept in(ma,cs)3-11() select * from student where sdept not in(ma,cs)3-12() select sname from student where sname like % 3-13() select sname from student where sname like %3-14() select sname from student where sname like %_3-15() 001003809096xxxsel
21、ect sno,cno,score from scorewhere cno in (001,003) and score between 80 and 90 and sno like 96% 3-16() _ select cno,cname,ctime from course where cname like %a_% escapea 3-17() select sno,cno from score where score is null3-18() 003004select * from scorewhere cno in(003,004)order by cno ,score desc3
22、-19() select count(*) from student3-20() select count(distinct sno) from score3-21() select avg(sage) from student where sdept=cs3-22() 001select max(score),min(score),avg(score) from scorewhere cno=0013-23() select avg(score),sum(score) from scoregroup by cno3-24() select count(sno),avg(sage) from
23、studentgroup by sdept,sclass3-25() -select snamesdeptcount*from student where sdept=cs group by sdept;error 1 :ora-00979: group by 3-26() select sage from student group by sno;error 1 :ora-00979: group by 3-27() 3select sdept,count(sno) from studentgroup by sdepthaving count(sno)33-28() 01select sde
24、pt,sclass,count(sno) from studentwhere sclass!=01group by sdept,sclass3-29() 3-30() 0017090select sname,cname,score from student,course,scorewhere o=001and (score90 or score70)and o=oand student.sno=score.sno;3-31() select sname,cno,score from student,scorewhere student.sno=score.sno3-32() select x.
25、sname,x.sdept,x.sage from student x,student ywhere x.sagey.sageand y.sname=3-33() 002003002003select score.sno,o,ame,score,ctime from course,scorewhere o in (002,003)and o=o(+)3-34() select sname,sdept from studentwhere sage in (select sage from student where sname=)3-35() select sno,sname from stud
26、entwhere sno in(select sno from scorewhere cno=(select cno from coursewhere cname=) ) 3-36(any) select sname,sdept from studentwhere sageany(select sage from student where sdept=ma)and sdeptma3-37(all) select sname,sdept from studentwhere sageall(select sage from student where sdept=ma)and sdeptma3-
27、38(exists) 004select sname from studentwhere exists( select * from scorewhere cno=004and sno=student.sno)3-39() select sname,sdept from studentwhere sdept=(select sdept from studentwhere sname= )and sage=(select sage from studentwhere sname= )3-40() select * from studentwhere sdept in(select sdept f
28、rom studentwhere sname=)and sage=(select sage from studentwhere sname=)3-41() select x.sname,x.sage from student x,student ywhere x.sage=y.sageand x.sdept=maand y.sdept=maand x.snoy.sno3-42() select sname from studentwhere sno in(select sno from scorewhere cno in (select cno from teachwhere tname=)and ssex=)3-43() 3select tname fr
