实验三:SQL语言(待填空).doc - 实验三 SQL...

  • No School
  • AA 1
  • 15

This preview shows page 1 - 5 out of 15 pages.

实验三 SQL 语言 实验目的 熟悉并掌握创建表,插入记录,查询记录,删除记录,修改记录。 创建索引,删除索引。 创建视图,使用视图,删除视图。 实验内容 现有一个单位内部的小型图书借阅系统,假设每本图书的数量无限制,并且可以借给 任何单位成员,每个单位成员可以借多本书,单位成员与图书的关系是多对多的关系。假 设系统中仅有三个关系模式。 数据表结构 Reader 属性名 类型 长度 是否空 含义 RNO varchar2 4 员工编号(主码) Rname varchar2 10 员工姓名 Rsex varchar2 2 性别 Rage integer integer 年龄 Rboss varchar2 10 直接上司 Raddress varchar2 30 办公地点 Book 属性名 类型 长度 是否空 含义 BNO varchar2 4 书 本 编 号 ( 主 码) Bname varchar2 50 书名 Bauthor varchar2 50 作者 Bpress varchar2 50 出版社 Bprice varchar2 numeric(6,2) 价格 RB 属性名 类型 长度 是否空 含义 RNO varchar2 4 员工编号 BNO varchar2 4 书本编号 RBdate date 借阅日期
Image of page 1

Subscribe to view the full document.

部分提示(黄色底的代码可以直接粘贴运行): A. 提示: 请严格按照指导书顺序做实验; 每次只用一个连接,关闭多余连接; 修改表格后,在连接 ( 例如 ccconect ) 右击表,选刷新,才能看到修改后的内容;修改表 格元组内容则可在表格 数据页选 刷新即可。
Image of page 2
B. 实验内容 1 以(用户 sys 密码 oracle )连接成功后: 输入以下 sql 语句创建新的用户并授权: create user cc identified by ccpassword ; grant resource, connect, DBA to cc; 2 以用户 CC 密码 ccpassword 的身份建立连接,并在此连接下执行后面的操作;
Image of page 3

Subscribe to view the full document.

3 拷贝代码运行,删去旧的同名数据表: Declare tmp integer default 0; Begin select count(*) into tmp from user_tables where table_name='RB'; if(tmp>0) then execute immediate 'drop table RB'; end if; select count(*) into tmp from user_tables where table_name='READER'; if(tmp>0) then execute immediate 'drop table READER'; end if; select count(*) into tmp from user_tables where table_name='BOOK'; if(tmp>0) then execute immediate 'drop table BOOK'; end if; end; 问:为何要先删去 RB ?能不能先删去 READER 4 、 建立表格 Reader create table Reader ( RNO varchar2(4) primary key, Rname varchar2(10) not null, Rsex varchar2(2), Rage integer, Rboss varchar2(10), Raddress varchar2(30) ) ; 5 、 拷贝代码运行,向 Reader 表格中插入十条数据 insert into Reader (RNO,Rname,Rage,Rsex,Rboss, Raddress) values('R001',' 张 三 ',20,' ',' ','416'); insert into Reader (RNO,Rname,Rage,Rsex,Rboss, Raddress) values('R002',' ',35,' ',null,'417'); insert into Reader (RNO,Rname,Rage,Rsex,Rboss, Raddress) values('R003',' ',30,' ',null,'416'); insert into Reader (RNO,Rname,Rage,Rsex,Rboss, Raddress) values('R004',' ',20,' ',null,'417'); insert into Reader (RNO,Rname,Rage,Rsex,Rboss, Raddress) values('R005',' ',40,' ',null,'416'); insert into Reader (RNO,Rname,Rage,Rsex,Rboss, Raddress) values('R006',' ',20,' ',null,'417');
Image of page 4
Image of page 5
  • Fall '19

What students are saying

  • Left Quote Icon

    As a current student on this bumpy collegiate pathway, I stumbled upon Course Hero, where I can find study resources for nearly all my courses, get online help from tutors 24/7, and even share my old projects, papers, and lecture notes with other students.

    Student Picture

    Kiran Temple University Fox School of Business ‘17, Course Hero Intern

  • Left Quote Icon

    I cannot even describe how much Course Hero helped me this summer. It’s truly become something I can always rely on and help me. In the end, I was not only able to survive summer classes, but I was able to thrive thanks to Course Hero.

    Student Picture

    Dana University of Pennsylvania ‘17, Course Hero Intern

  • Left Quote Icon

    The ability to access any university’s resources through Course Hero proved invaluable in my case. I was behind on Tulane coursework and actually used UCLA’s materials to help me move forward and get everything together on time.

    Student Picture

    Jill Tulane University ‘16, Course Hero Intern

Ask Expert Tutors You can ask 0 bonus questions You can ask 0 questions (0 expire soon) You can ask 0 questions (will expire )
Answers in as fast as 15 minutes