Oracle课程设计题withansweranswerwith.docx

1、Oracle课程设计题withansweranswerwithOracle2014课程设计题with,answeranswerwith Oracle课程设计报告 姓 名 班级学号 指导教师 20XX年X月X 日 Oracle课程设计大纲 一、课程设计目的和要求 1. 掌握数据库的设计和管理方法，巩固SQL Server中数据库、表、视图、索引、存储过程、触发器等基本操作。 2. 掌握PL/SQL编程语言的应用。 3. 巩固数据查询的各种方法。 二、具体要求 1. 本课程设计使用软件为Oracle 11g。 2. 本课程设计共20学时， 设计结束上交课程设计报告一份。 3. 考核方式：考勤成绩（

2、20%）+报告成绩（80%）。 三、课程设计报告具体格式 1. 封面 2. 正文分三部分： （1）课程设计目的和要求 （2）课程设计内容 （3）课程设计总结 四、课程设计内容 在数据库中创建一个以自己姓名拼音首字母缩写的账户，密码为学号。在此方案下进行设计任务。 【任务1】创建数据表 1.使用OEM工具创建Book（图书表）和BookType（图书类型表） Book表 字段名称 数据类型 长度 说明 B_ID CHAR 9 图书编号 B_Name *2 40 图书名称 Author *2 20 作者 BT_ID CHAR 2 图书类别，外键 P_ID CHAR 4 出版社编号 PubDate

3、DATE 出版日期 Price NUMBER (5,2) 价格 BookType表 字段名称 数据类型 长度 说明 BT_ID CHAR 2 图书类别编号，主键 BT_Name *2 20 图书类别名称 BT_Info *2 50 描述信息 2.使用SQL语句创建Reader表（读者表）和ReaderType表（读者类型表）。 Reader表 字段名称 数据类型 长度 说明 R_ID CHAR 10 读者借书证编号，主键 R_Name *2 8 读者姓名 RT_ID CHAR 1 读者类型，外键 RDept *2 12 部门 RTel *2 11 联系电话 ReaderType表 字段名称 数

4、据类型 长度 说明 RT_ID CHAR 1 读者类型编号，主键 RT_Name *2 10 读者类型姓名 LimitNum NUMBER 限借数量 LimitDays NUMBER 限借天数 3.使用SQL Developer工具创建Borrow表（借阅表）和Publish表（出版社表）。 Borrow表 字段名称 数据类型 长度 说明 R_ID CHAR 10 读者借书证编号 B_ID CHAR 9 图书编号 LendDate DATE 借阅日期 ReturnDate DATE 还书日期 LimitDays NUMBER 限借天数 BorrowInfo *2 2 是否过期 Publish表

5、 字段名称 数据类型 长度 说明 P_ID CHAR 4 出版社编号 PubName *2 30 出版社名称 PubTel *2 20 联系电话 latto/latto create table Reader( R_ID char(10) primary key,R_Name varchar2(8),RT_ID number(1),RDept varchar2(10),RTel varchar2(11) tablespace users create table ReaderType( RT_ID char(1) primary key,RT_Name varchar2(10),LimitNu

6、m number,LimitDays number) tablespace users create table Reader( R_ID char(10) primary key,R_Name varchar2(8),RT_ID char(1),RDept varchar2(10),RTel varchar2(11) tablespace users alter table reader add constraint fk_RT_ID foreign key (RT_ID) references ReaderType (RT_ID) create table Borrow( R_ID cha

7、r(10) ,B_ID char(9),LendDate Date,ReturnDate Date,LimitDays number,BorrowInfo varchar2(2) tablespace users create table Publish( P_ID char(4) ,PubName varchar2(30),PubTel varchar2(20) tablespace users 【任务2】修改数据表 1.将Reader表中的联系电话字段的数据类型修改为*2(20)。 alter table reader modify rtel varchar2(20) 2.指定Borrow

8、表的借阅日期LendDate不允许为空。 alter table borrow modify lenddate date not null 3.删除Borrow表中的限借天数LimitDays字段。 alter table borrow drop column limitdays 4.为Book表增加ISBN字段，数据类型为*2(13)。 alter table book add ISBN varchar2(13) 【任务3】建立约束 1.为Book表添加主键，约束名为PK_Book。 alter table book add constraint PK_Book primary key(B_

9、ID) 2.为Borrow表添加主键，主键是(R_ID,B_ID,LendDate)，约束名为PK_Borrow。 alter table borrow add constraint PK_Borrow primary key(R_ID,B_ID,LendDate) 3.设置Book表的出版社编号字段P_ID是外键，参照Publish表中的P_ID，约束名为FK_book_pub。 alter table publish add constraint pk_pub primary key (P_ID) alter table book add constraint FK_book_pub fo

10、reign key(P_ID) references Publish(P_ID) 4.为Book表中的价格字段Price添加检查约束，要求价格必须大于0，约束名为CHK_ price。 alter table book add constraint chk_price check (price0) 5. 为Reader表增加性别（Rsex）字段，数据类型为CHAR(2)，并设默认值为“男”。 update reader set rdept=“保卫处 where r_name=张芳 3.使用SQL语言删除Publish表中“北京交大出版社”的记录。 delete from publish whe

11、re pubname=北京交大出版社 【任务6】简单查询及连接查询 1.查询所有图书的基本信息。 select * from book 2.查询所有的图书编号、图书名称和价格。 select b_id,b_name,price from book 3.查询教师一次可以借书数量以及借书天数，输出的结果字段名分别用借书本数和借书期限表示。 select limitnum as 借书本数,limitdays as 借书期限 from readertype where rt_name=教师 4.查询姓“张”读者的基本信息。 select * from reader where r_name like

12、张% 5.查询Borrow表中未还图书的记录。 select * from borrow where returndate is null 6.查询2014年的借阅记录。 select * from borrow where to_char(lenddate,yyyy)=2014 7.统计图书信息表中不同出版社出版的图书数目，把统计结果大于或等于2的结果输出。 select p_id,count(*) as pubnum from book group by p_id having count(*)=2 select p_id,count(p_id) as pubnum from book g

13、roup by p_id having count(p_id)=2 8.查询Borrow表中所有借书的读者的借书证号、姓名以及所借图书的图书证号。 select borrow.r_id,r_name,b_id from borrow,reader where borrow.r_id=reader.r_id 9.查询Borrow表中所有借书的读者的借书证号、姓名以及所借图书的图书的详细信息。 select reader.r_id,r_name,book.* from borrow,reader,book where borrow.r_id=reader.r_id and book.b_id=bo

14、rrow.b_id 10.查询借阅了书籍的读者和没有借阅的读者，显示他们的读者号、姓名、书名、借阅日期。 select reader.r_id,r_name,book.b_name,lenddate from book,reader,borrow where reader.r_id=borrow.r_id(+) and book.b_id(+)=borrow.b_id select b_name,price ,pubname from book,publish where book.p_id=publish.p_id and pubname in (人民邮电出版社,高等教育出版社) 11.查询

15、“人民邮电出版社”的图书中单价比“高等教育出版社”最高单价还高的图书名、单价。 select b_name,price from book,publish where book.p_id=publish.p_id and pubname=人民邮电出版社 and priceall(select price from book,publish where book.p_id=publish.p_id and pubname=高等教育出版社) 12.查询从未被借阅过的图书信息。 select * from book where b_id not in (select b_id from borrow

16、) / select b_name from book,borrow where book.b_id=borrow.b_id select p_id,count(*) as pubnum from book group by p_id 13.查询每一个出版社出版的书籍的数量。 select pubname,count(*) as pubnum from book,publish where book.p_id=publish.p_id group by pubname 14.查询与SQL SERVER应用技术同一类型的所有图书的名称、作者、ISBN号。 select b_name,author

17、,isbn from book where bt_id=(select bt_id from book where b_name=SQL SERVER应用技术) 15.查询所有单价小于平均单价的图书号、书名、出版社。 16.查询与SQL SERVER应用技术同一出版社的所有图书的图书名称、作者、ISBN号。 17.查询姓名为“杨倩”的读者的借阅记录。 select r_name,borrow.* from reader,borrow where borrow.r_id=reader.r_id and r_name=杨倩 18.查询姓名为“杨倩”的读者的所借图书的详细信息。 select boo

18、k.* from reader,borrow,book where borrow.r_id=reader.r_id and book.b_id=borrow.b_id and r_name=杨倩 19.查询借阅了“高等教育出版社”出版的书名中包含有“数据库”3个字的图书，或者借阅了“中国铁道出版社”出版的书名中含有“SQL”3个字的图书的读者姓名、书名。 select r_name,b_name from book,borrow,publish,reader where reader.r_id=borrow.r_id and book.b_id=borrow.b_id and book.p_i

19、d=publish.p_id and pubname=高等教育出版社 and b_name like %数据库% union select r_name,b_name from book,borrow,publish,reader where reader.r_id=borrow.r_id and book.b_id=borrow.b_id and book.p_id=publish.p_id and pubname=中国铁道出版社 and b_name like %SQL% 20.查询借阅了“高等教育出版社”出版的书名中包含有“数据库”3个字的图书，并且也借阅了“中国铁道出版社”出版的书名中

20、含有“SQL”3个字的图书的读者姓名。 select r_name from book,borrow,publish,reader where reader.r_id=borrow.r_id and book.b_id=borrow.b_id and book.p_id=publish.p_id and pubname=高等教育出版社 and b_name like %数据库% intersect select r_name from book,borrow,publish,reader where reader.r_id=borrow.r_id and book.b_id=borrow.b_

21、id and book.p_id=publish.p_id and pubname=中国铁道出版社 and b_name like %SQL% 21.查询借阅了“高等教育出版社”出版的书名中包含有“数据库”3个字的图书，但是没有借阅了“中国铁道出版社”出版的书名中含有“SQL”3个字的图书的读者姓名。 select r_name from book,borrow,publish,reader where reader.r_id=borrow.r_id and book.b_id=borrow.b_id and book.p_id=publish.p_id and pubname=高等教育出版社

22、 and b_name like %数据库% minus select r_name from book,borrow,publish,reader where reader.r_id=borrow.r_id and book.b_id=borrow.b_id and book.p_id=publish.p_id and pubname=中国铁道出版社 and b_name like %SQL% 【任务7】索引、视图、序列及同义词 1.为Book图书表的书名列创建惟一索引idx_Bname。 create unique index idx_bname on book(b_name) 2.将索引

23、“idx_Bname”重命名为index_Bname。 alter index idx_bname rename to index_bname 3.删除索引index_Bname，将命令写在实验报告中。 drop index index_bname 4.建立“人民邮电出版社”所出版的图书视图V_Pub,视图中包含书号、书名、出版社信息。 grant create view to latto; create view v_pub as select b_id,b_name,publish.* from book,publish where book.p_id=publish.p_id and p

24、ubname=人民邮电出版社 5.创建一个借阅统计视图，名为V_Count_1，包含读者的借书证号和总借阅本数，要求该视图为只读。 create view v_count_1(借书证号,总借阅本数) as select r_id,count(*) from borrow group by r_id with read only 6.创建一个借阅统计视图，名为V_Count_2，包含借阅总本数大于两本的读者号和总借阅本数。 create view v_count_2(读者号,总借阅本数) as select r_id,count(*) from borrow group by r_id havi

25、ng count(*)=2 7.修改视图V_Pub，要求增加图书的单价信息，并且该视图进行的更新操作只涉及“人民邮电出版社”。 create or replace view v_pub as select b_id,b_name,price,publish.* from book,publish where publish.p_id=book.p_id and pubname=人民邮电出版社 with check option 8.删除视图V_Pub。 drop view v_pub 9.创建序列S_BookUser，要求初值为1，序列增量为2，没有最大值限制。 create sequence

26、 s_bookuser start with 1 increment by 2 10.修改序列S_BookUser的最大值为1000。 alter sequence s_bookuser maxvalue 1000 11.新建表Test（UserID NUMBER,UserName *2(10)），向表中插入两条数据，其中UserID字段由序列S_BookUser提供，并查看表test是否插入成功。 create table test(UserID NUMBER,UserName *2(10) insert into test values(s_bookuser.nextval,tom) 12

27、.删除序列S_BookUser。 drop sequence s_bookuser 【任务8】常量、变量和系统函数 1.编写程序实现将Reader表中借书证号为“*-*01”的读者的姓名赋值给变量r_name，并输出该变量的值。 declare r_name varchar2(20); begin select R_name into r_name from reader where r_id=*-*01; dbms_output.put_line(*-*001读者名为：|r_name); end; 2.输出当前系统日期月份和年份。 begin dbms_output.put_line(ext

28、ract(month from sysdate); dbms_output.put_line(extract(year from sysdate); end; 3.使用字符函数统计字符串“ SQL Server 2008 ”的长度。 begin dbms_output.put_line(length( SQL Server 2008 ); end; 4.使用函数删除字符串“ SQL Server 2008 ”左右两端的窗格并输出。 begin dbms_output.put_line(trim( SQL Server 2008 ); end; 【任务9】流程控制语句 1.编写PL/SQL语句块

29、，求2500之间的素数和。 declare s number:=0; flag boolean:=true; begin for i in 2.500 loop for j in 2.i-1 loop if mod(i,j)=0 then flag:=false; end if; end loop; if flag then s:=s+i; end if; flag:=true; end loop; dbms_output.put_line(sum is|s); end; 2.编写PL/SQL语句块，使用IF语句求出3个数中最大的数。 declare i number; j number; k

30、 number; maxnum number; begin i:=12; j:=9; k:=7; maxnum:=i; if maxnumj then maxnum:=j; end if; if maxnum n and ml then maxnum:=m; elsif nl then maxnum:=n; else maxnum:=l; end if; dbms_output.put_line(max is |maxnum); END; 3.编写PL/SQL语句块，要求使用循环结构来计算10！。 declare s number:=1; begin for i in 1.10 loop s:

31、=s*i; end loop; dbms_output.put_line(sum is|s); end; 4.查询图书中有没有英语书和SQL Server方面的书，如果有则统计其册数。 declare e_count number:=0; o_count number:=0; cursor bookcur is select b_name from book; bname_book book.b_name%type; begin if bookcur%isopen=false then open bookcur; end if; fetch bookcur into bname_book; while bookcur%found loop if instr(bname_book,英语)0 then e_count:=e_count+1; elsif instr(bname_book,SQL SERVER)0 then o_count:=o_count+1; end if; fetch bookcur into bname_book; exit when bookcur%notfound; end loop; close bookcur; dbms_output.put_line(英语书的数量为：|e_coun