Oracle-數(shù)據(jù)庫(kù)上機(jī)試驗(yàn)指導(dǎo)1(答案)

1、精選優(yōu)質(zhì)文檔-傾情為你奉上Oracle 數(shù)據(jù)庫(kù)上機(jī)試驗(yàn)指導(dǎo) 實(shí)驗(yàn)一試驗(yàn)1 DDL & DML試驗(yàn)?zāi)康? 熟悉Oracle下數(shù)據(jù)庫(kù)基表的創(chuàng)建&修改&刪除 熟悉Oracle下數(shù)對(duì)數(shù)據(jù)的插入&修改&刪除試驗(yàn)步驟: 運(yùn)行給定腳本(該腳步是創(chuàng)建一個(gè)用戶,并賦予該用戶一定的權(quán)限!)啟動(dòng)Oracle數(shù)據(jù)庫(kù)Dos控制臺(tái)下運(yùn)行sqlplus /nolog命令Conn stud/stud as sysdba;-stud是一個(gè)不存在賬戶，主要用sysdba來登錄，可以show user 來查看一下當(dāng)前用戶 c:createUser.sql; -該文件在實(shí)驗(yàn)材料文件夾里，請(qǐng)將其

2、考入到C:根目錄下conn csj/csj -完成用戶CSJ的創(chuàng)建 二 在CSJ用戶模式下完成以下工作(自己寫sql腳本,然后調(diào)試運(yùn)行)創(chuàng)建一張學(xué)生表創(chuàng)建一張課程表創(chuàng)建一張選課表分別向?qū)W生表,課程表,選課表插入下面數(shù)據(jù)思考以下問題:如何得到所有男生/女生人數(shù)?如何得到某一門課程(如數(shù)學(xué))的選課人員清單?如何得到某一門課程(如數(shù)學(xué))的選課人員總數(shù)?如何得到某一門課程(如數(shù)學(xué))的選課人員的平均成績(jī)?三. 上交腳本,并且以”姓名 學(xué)號(hào).sql”上交腳本(當(dāng)場(chǎng)上交,逾期不收).學(xué)生表(student)姓名(非空)學(xué)號(hào)(PK)性別(只能輸入男or 女)住址李白男和平區(qū)杜甫男河西區(qū)李清照女河北區(qū)白居易男紅

3、橋區(qū)蔡琰女紅橋區(qū)課程表(course)課程名稱(不允許重名)課程編號(hào)(pk)上課教師課程學(xué)分(1,3,5)數(shù)學(xué)M1001奧巴馬3軍事理論J2002拿破侖5摔跤S3003普京1選課表(courSelc)記錄編號(hào)(PK,自增)學(xué)號(hào)(FK)課程編號(hào)(FK)考試成績(jī)(0,100)1M2001702J2002603S3003804M2001755J2002856S3003957M2001508J2002559S30034510M20011011J20021512S300325參考腳本：- 創(chuàng)建學(xué)生表create table student( studid char(6) primary key, stu

4、dname varchar2(16) not null, gender char(4) check(gender in('男','女'), addr varchar2(256);- 創(chuàng)建課程表create table course( courseid char(6) primary key, coursename varchar2(32) not null unique, teacher varchar2(16) , score number check(score in (1,3,5);- 創(chuàng)建選課表create table courSelc( recid n

5、umber(4) primary key, studid char(6), courseid char(6), score number(2) check(score >=0 and score <=100), constraint fk_stud foreign key(studid) references student(studid), constraint fk_cour foreign key(courseid) references course(courseid);- 插入數(shù)據(jù)insert into student values('','李白&

6、#39;,'男','和平區(qū)');insert into student values('','杜甫','男','河西區(qū)');insert into student values('','李清照','女','河北區(qū)');insert into student values('','蔡琰','女','紅橋區(qū)');insert into course values('M1

7、001','數(shù)學(xué)','奧巴馬','3');insert into course values('J2002','軍事理論','拿破侖','5');insert into course values('S3003','摔跤','普京','1');insert into courSelc values(1,'','M1001',70);insert into courSelc valu

8、es(2,'','J2002',60);insert into courSelc values(3,'','S3003',80);insert into courSelc values(4,'','M1001',75);insert into courSelc values(5,'','J2002',85);insert into courSelc values(6,'','S3003',95);insert into courSelc

9、 values(7,'','M1001',50);insert into courSelc values(8,'','J2002',55);insert into courSelc values(9,'','S3003',45);insert into courSelc values(10,'','M1001',10);insert into courSelc values(11,'','J2002',15);insert into c

10、ourSelc values(12,'','S3003',25);select count(*) from student where gender='男'select count(*) from student where gender='女'select s.studid,s.studname,c.coursename,cs.score from student s,course c,courSelc cswhere s.studid=cs.studid and c.courseid=cs.courseid and c.coursename='數(shù)學(xué)'select count(*) from courSelc cs join course c on c.coursei