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

Oracle 數(shù)據(jù)庫(kù)上機(jī)試驗(yàn)指導(dǎo)試驗(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)限!)1. 啟動(dòng)Oracle數(shù)據(jù)庫(kù)2. Dos控制臺(tái)下運(yùn)行sqlplus /nolog命令3. Conn stud/stud as sysdba;-stud是一個(gè)不存在賬戶，主要用sysdba來(lái)登錄，可以show user 來(lái)查看一下當(dāng)前用戶4. c:createUser.sql; -該文件在實(shí)驗(yàn)材料文件夾里，請(qǐng)將其考入到C:根目錄下5. conn csj/csj -完成用戶CSJ的創(chuàng)建 二 在CSJ用戶模式下完成以下工作(自己寫(xiě)sql腳本,然后調(diào)試運(yùn)行)1 創(chuàng)建一張學(xué)生表2 創(chuàng)建一張課程表3 創(chuàng)建一張選課表4 分別向?qū)W生表,課程表,選課表插入下面數(shù)據(jù)5 思考以下問(wèn)題:a) 如何得到所有男生/女生人數(shù)?b) 如何得到某一門(mén)課程(如數(shù)學(xué))的選課人員清單?c) 如何得到某一門(mén)課程(如數(shù)學(xué))的選課人員總數(shù)?d) 如何得到某一門(mén)課程(如數(shù)學(xué))的選課人員的平均成績(jī)?三. 上交腳本,并且以”姓名 學(xué)號(hào).sql”上交腳本(當(dāng)場(chǎng)上交,逾期不收).學(xué)生表(student)姓名(非空)學(xué)號(hào)(PK)性別(只能輸入男or 女)住址李白100001男和平區(qū)杜甫100002男河西區(qū)李清照100003女河北區(qū)白居易100004男紅橋區(qū)蔡琰100005女紅橋區(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)1100001M2001702100001J2002603100001S3003804100002M2001755100002J2002856100002S3003957100003M2001508100003J2002559100003S30034510100004M20011011100004J20021512100004S300325參考腳本：- 創(chuàng)建學(xué)生表create table student( studid char(6) primary key, studname 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 number(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(100001,李白,男,和平區(qū));insert into student values(100002,杜甫,男,河西區(qū));insert into student values(100003,李清照,女,河北區(qū));insert into student values(100004,蔡琰,女,紅橋區(qū));insert into course values(M1001,數(shù)學(xué),奧巴馬,3);insert into course values(J2002,軍事理論,拿破侖,5);insert into course values(S3003,摔跤,普京,1);insert into courSelc values(1,100001,M1001,70);insert into courSelc values(2,100001,J2002,60);insert into courSelc values(3,100001,S3003,80);insert into courSelc values(4,100002,M1001,75);insert into courSelc values(5,100002,J2002,85);insert into courSelc values(6,100002,S3003,95);insert into courSelc values(7,100003,M1001,50);insert into courSelc values(8,100003,J2002,55);insert into courSelc values(9,100003,S3003,45);insert into courSelc valu