Oracle-數(shù)據(jù)庫上機試驗指導1(答案)

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

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

3、橋區(qū)蔡琰女紅橋區(qū)課程表(course)課程名稱(不允許重名)課程編號(pk)上課教師課程學分(1,3,5)數(shù)學M1001奧巴馬3軍事理論J2002拿破侖5摔跤S3003普京1選課表(courSelc)記錄編號(PK,自增)學號(FK)課程編號(FK)考試成績(0,100)1M2001702J2002603S3003804M2001755J2002856S3003957M2001508J2002559S30034510M20011011J20021512S300325參考腳本：- 創(chuàng)建學生表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ù)學','奧巴馬','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ù)學'select count(*) from courSelc cs join course c on c.coursei