實(shí)驗(yàn)報(bào)告模板

1、數(shù)據(jù)庫原理及應(yīng)用實(shí)驗(yàn)報(bào)告題 目SQL語言在oracle中的綜合應(yīng)用日期姓名鄭云云學(xué)號(hào)141201144成績(jī)1、 實(shí)驗(yàn)環(huán)境（操作系統(tǒng)、數(shù)據(jù)庫管理系統(tǒng)）： Win7 ,sql 實(shí)驗(yàn)內(nèi)容與完成情況：1.模式定義：新建一個(gè)用戶，其名稱為您的姓名簡(jiǎn)拼，密碼自定；create user zyy identified by abc;2.建立一個(gè)名為MS的表空間；create tablespace MS datafile 'D:141201144ms3kj.dbf' size 100M;grant connect,resource,dba to zyy;conn zyy/abc;1. 3表定義

2、1） 在MS表空間中建立三個(gè)基本表（students、course、sc）；create table students(sno char(9),sname char(20) ,ssex char(2),sage smallint sdept char(20);course create table course(cno char(4),cname char(40),cpno char(4),ccredit smallint); create table sc(sno char(9),cno char(4),grade smallint); 2） (2) 在student中添加一個(gè)名為“LOC”

3、的字段，類型為字符型，長(zhǎng)度為50； alter table students add loc char(50);3） (3) 在student中刪除名為“LOC”的字段4） alter table students drop loc; A. 實(shí)體完整性1） student表的主碼為sno；alter table students add constraint c1 primary key (sno)2） course表的主碼為cno；alter table course add constraint c2 primary key (cno)3） sc表的主碼為(sno,cno)。alter t

4、able sc add constraint c3 primary key (sno,cno)B. 參照完整性1） sc表的sno參照student表的sno；sc foreign key (sno) references student (sno)2） sc表的cno參照course表的cno。sc foreign key (cno) references course (cno)C. 用戶自定義的完整性1） student表中姓名不能取空值、性別只能是男或女、年齡在18到30之間；2） alter table studentsadd constraint c3 sname char(20)

5、 NOT NULL;add constraint c4 check (ssex in(男,女)add constraint c5 check (sage between 18 and 30);2）SC表中成績(jī)?cè)?到100之間。alter table c6add constraint c6 check (grade between 0 and 100);4.視圖定義1）創(chuàng)建一個(gè)計(jì)算機(jī)科學(xué)系選修了2號(hào)課程的學(xué)生視圖（包含學(xué)號(hào)、姓名、成績(jī)）；create view view1 as select student.sno,students.sname,sc.grade from students in

6、ner join sc on students.sno=sc.sno where students.sdept='CS'and sc.sco='2'2）創(chuàng)建一個(gè)選修了“數(shù)據(jù)庫”課程的學(xué)生視圖（包含學(xué)號(hào)、課程名稱、成績(jī)）create view view2 as select sc.sno, ame,sc.grade from sc inner join course on sc.sco=o where ame='數(shù)據(jù)庫' （二）數(shù)據(jù)操縱1、按教材第7778頁內(nèi)容錄入三張表的信息；students:insert into students(sno,s

7、name,ssex,sage,sdept) values('201215121','李勇','男',20,'CS');insert into students(sno,sname,ssex,sage,sdept) values('201215122','劉晨','女',19,'CS');insert into students(sno,sname,ssex,sage,sdept) values('201215123','王敏','

8、;女',18,'MA');insert into students(sno,sname,ssex,sage,sdept) values('201215125','張立','男',19,'IS');course:insert into course(cno,cname,cpno,ccredit) values('1','數(shù)據(jù)庫','5',4);insert into course(cno,cname,ccredit) values('2',

9、9;數(shù)學(xué)',2);insert into course(cno,cname,cpno,ccredit) values('3','信息系統(tǒng)','1',4);insert into course(cno,cname,cpno,ccredit) values('4','操作系統(tǒng)','6',3);insert into course(cno,cname,cpno,ccredit) values('5','數(shù)據(jù)結(jié)構(gòu)','7',4);insert into

10、 course(cno,cname,ccredit) values('6','數(shù)據(jù)處理',2);insert into course(cno,cname,cpno,ccredit) values('7','PASCAL語言','6',4);sc:insert into sc(sno,sco,grade) values('201215121','1',92);insert into sc(sno,sco,grade) values('201215121','2&

11、#39;,85);insert into sc(sno,sco,grade) values('201215121','3',88);insert into sc(sno,sco,grade) values('201215122','2',90);insert into sc(sno,sco,grade) values('201215122','3',80);2、將所有“數(shù)據(jù)庫”課程的學(xué)生成績(jī)?cè)谠瓉淼幕A(chǔ)上加5分；update sc set grade=grade+5 where sno=(selec

12、t cno from course where cname='數(shù)據(jù)庫');3、刪除所有成績(jī)?yōu)榭盏膶W(xué)生選課信息。delete * from sc where grade is null;（三）數(shù)據(jù)查詢1、查詢所有被選修的課程號(hào)，要求不重復(fù)；select distinct(sno) from sc;2、查詢考試成績(jī)不及格的學(xué)生人次；select count(sno)from sc where grade<60;3、查詢所有姓“李”的學(xué)生信息；select sname from students where sname like '李%'4、查詢各個(gè)課程號(hào)及相應(yīng)

13、的選課人數(shù)；select count(sno) form sc group by cno;5、查詢有三人以上選修的課程號(hào)；select cno from sc having count(sno)>=3 group by cno;6、查詢平均成績(jī)大于或等于90分的課程號(hào)和平均成績(jī)；select avg(grade),cno from sc group by cno having avg(grade)>=90;7、查詢每一門課的間接先修課；select sc.Cno,course.Cpno. from Course innor join scwhere course .cno=o; 8

14、、查詢選修了“數(shù)據(jù)庫”課程且成績(jī)?cè)?0分以上的學(xué)生學(xué)號(hào)和成績(jī)；select sc.sno,sc.grade from sc where grade >90 and cno=(select cno from course where cname=數(shù)據(jù)庫)9、查詢與“劉晨”在同一個(gè)系學(xué)習(xí)的學(xué)生；select sname from students where sdept = (select sdept from students where sname='劉晨');10、查詢每個(gè)學(xué)生超過他自己選修課程平均成績(jī)的課程號(hào)；Select sno from sc where grad

15、e > avg(grade= (select grade from sc group by cno);11、查詢所有選修了“數(shù)據(jù)庫”課程的學(xué)生姓名和成績(jī)；select students.sname,sc.grade from students inner join sc on students .sno=sc.sno where o=(select cno from course =數(shù)據(jù)庫)12、查詢沒有選修1號(hào)課程的學(xué)生姓名；select sname from students where sno in (select sno from sc where sco!='1'

16、; )3、查詢選修了所有課程的學(xué)生姓名；select sname from students where sno in (select sno from sc where count (cno)=7 group by sno)14、查詢至少選修了學(xué)生201215122選修的全部課程的學(xué)生學(xué)號(hào)；SQL> select distinct(sno) from sc scx where not exists (select * from sc scy where scy.sno='20121522'and not exists (select * from sc scz wher

17、e scz.sno=scx.sno and o=o);15、查詢年齡大于計(jì)算機(jī)科學(xué)系的任意一個(gè)學(xué)生年齡的其他院系的學(xué)生姓名和年齡; select sname,sage from student where(sdept not in ('cs')and sage>any(select sage from student where sdept='CS');16、查詢既不是最大年齡，也不是最小年齡的學(xué)生姓名和年齡；select sname,sage from student having sage>(select min(sage) from stude

18、nt) and sage<(select max(sage) from student) group by sname,sage;17、查詢年齡大于同院系的平均年齡的學(xué)生姓名和年齡；select sname,sage from student a1 where sage>(select avg(sage) from student a2 where a1.sdept=a2.sdept);18、查詢不是計(jì)算機(jī)科學(xué)系的學(xué)生學(xué)號(hào)和姓名；select sno,sname from student where sdept!='CS'19、查詢各院系的學(xué)生的平均年齡；select avg(sage) from student group by sdept;20、查詢學(xué)生的學(xué)號(hào)及調(diào)整后的成績(jī)(原成績(jī)加5分 )。select sno,(grade+5) from sc;1. 1. 使用哪個(gè)用戶登錄數(shù)據(jù)庫才能建立新用戶？ System 用戶2. 新建好的用戶可以連接數(shù)據(jù)庫嗎？若不能，應(yīng)如何操作就可以連接數(shù)據(jù)庫？不能。