ORACLE与数据库原理实验 实验三 插入、更新与删除（答案全）

ORACLE与数据库原理实验 实验三 插入、更新与删除（答案全）

实验三 插入、更新与删除

​​[实验目的]​​​​[预备知识]​​​​[实验内容]​​

[实验目的]

1． 熟练掌握 insert， update， delete语句的使用； 2． 体会外键对于数据插入顺序的影响； 3． 理解提交与回滚的用法 4． 掌握脚本运行的方法。

[预备知识]

1． SQL中的insert，update，delete的语法 2． rollback与commit的含义

[实验内容]

1． 请在对应表中插入如下的数据 a) Branch（分行表） insert into branch values(‘Downtown’,‘Brooklyn’,‘900000’); insert into branch values(‘Redwood’,‘Palo Alto’,‘2100000’); insert into branch values(‘Perryridge’,‘Horseneck’,‘1700000’); insert into branch values(‘Mianus’,‘Horseneck’,‘400200’); insert into branch values(‘Round Hill’,‘Horseneck’,‘8000000’); insert into branch values(‘Pownal’,‘Bennington’,‘400000’); insert into branch values(‘North Town’,‘Rye’,‘3700000’); insert into branch values(‘Brighton’,‘Brooklyn’,‘7000000’); insert into branch values(‘Central’,‘Rye’,‘400280’); b) Customer（顾客表） insert into customer values(‘Jones’,‘Main’,‘Harrison’); insert into customer values(‘Smith’,‘Main’,‘Rye’); insert into customer values(‘Hayes’,‘Main’,‘Harrison’); insert into customer values(‘Curry’,‘North’,‘Rye’); insert into customer values(‘Lindsay’,‘Park’,‘Pittsfield’); insert into customer values(‘Turner’,‘Putnam’,‘Stamford’); insert into customer values(‘Williams’,‘Nassau’,‘Princeton’); insert into customer values(‘Adams’,‘Spring’,‘Pittsfield’); insert into customer values(‘Johnson’,‘Alma’,‘Palo Alto’); insert into customer values(‘Glenn’,‘Sand Hill’,‘Woodside’); insert into customer values(‘Brooks’,‘Senator’,‘Brooklyn’); insert into customer values(‘Green’,‘Walnut’,‘Stamford’); insert into customer values(‘Jackson’,‘University’,‘Salt Lake’); insert into customer values(‘Majeris’,‘First’,‘Rye’); insert into customer values(‘McBride’,‘Safety’,‘Rye’); c) Account（存款账号表） insert into account values(‘A-101’,‘Downtown’,‘500’); insert into account values(‘A-215’,‘Mianus’,‘700’); insert into account values(‘A-102’,‘Perryridge’,‘400’); insert into account values(‘A-305’,‘Round Hill’,‘350’); insert into account values(‘A-201’,‘Perryridge’,‘900’); insert into account values(‘A-222’,‘Redwood’,‘700’); insert into account values(‘A-217’,‘Brighton’,‘750’); insert into account values(‘A-333’,‘Central’,‘850’); insert into account values(‘A-444’,‘North Town’,‘625’);

d) Depositor（顾客 -存款账号表） insert into depositor values(‘Hayes’,‘A-101’); insert into depositor values(‘Hayes’,‘A-102’); insert into depositor values(‘Johnson’,‘A-101’); insert into depositor values(‘Johnson’,‘A-201’); insert into depositor values(‘Jones’,‘A-217’); insert into depositor values(‘Lindsay’,‘A-222’); insert into depositor values(‘Majeris’,‘A-333’); insert into depositor values(‘Smith’,‘A-215’); insert into depositor values(‘Smith’,‘A-444’); insert into depositor values(‘Turner’,‘A-305’);

e) Loan（借款表） insert into loan values(‘L-17’,‘Downtown’,‘1000’); insert into loan values(‘L-23’,‘Redwood’,‘2000’); insert into loan values(‘L-15’,‘Perryridge’,‘1500’); insert into loan values(‘L-14’,‘Downtown’,‘1500’); insert into loan values(‘L-93’,‘Mianus’,‘500’); insert into loan values(‘L-11’,‘Round Hill’,‘900’); insert into loan values(‘L-16’,‘Perryridge’,‘1300’); insert into loan values(‘L-20’,‘North Town’,‘7500’); insert into loan values(‘L-21’,‘Central’,‘570’);

f) Borrower（顾客-借款表) insert into borrower values(‘Adams’,‘L-16’); insert into borrower values(‘Curry’,‘L-93’); insert into borrower values(‘Hayes’,‘L-15’); insert into borrower values(‘Jackson’,‘L-15’); insert into borrower values(‘Jones’,‘L-17’); insert into borrower values(‘McBride’,‘L-20’); insert into borrower values(‘Smith’,‘L-11’); insert into borrower values(‘Smith’,‘L-21’); insert into borrower values(‘Smith’,‘L-23’); insert into borrower values(‘Williams’,‘L-17’);

2． 如果插入顺序不同，比如按以上表的相反的顺序插入记录到各个表中，会有什么结果。 插入失败，出现如下错误警告：

3． 保留下所有的插入操作脚本。在脚本前加入以下的语句： delete customer; delete branch; delete account; delete depositor; delete loan; delete borrower; 使用脚本执行运行整个脚本。 以下要求所有SQL必须使用一条SQL语句

4.更新’L-11’号借款的金额为1200元。 update loan set amount=1200 where loan_number=‘L-11’;

5.更新’L-20’号借款的借款行为’Perryridge’，金额为1400元。 update loan set branch_name=‘Perryridge’, amount=1400 where loan_number=‘L-20’;

6.给’Smith’的存款6%的利息。 update account set balance=balance*1.06 where account_number in(select account_number from depositor where customer_name=‘Smith’);

7． 给存款金额大于2000元的6%的利息，小于等于2000元的5%的利息。 update account set balance=balance*case when balance>2000 then (1.06) else(1.05) end;

8． 将’Smith’居住的城市更新为’Jones’居住的城市。 Smith和Jones居住的城市本就是一样的，只是街道不一样。 如果是想考察语句的话，如下所示： update customer a set A.CUSTOMER_CITY = (select B.CUSTOMER_CITY from customer b where b.CUSTOMER_NAME=‘Jones’) where a.customer_name=‘Smith’;

9． 将’Smith’居住的城市，街道更新为’Turner’居住的城市和街道。 update customer a set A.CUSTOMER_STREET = (select B.CUSTOMER_STREET from customer b where b.CUSTOMER_NAME=‘Turner’) where a.CUSTOMER_NAME=‘Smith’;

update customer a set A.CUSTOMER_CITY = (select B.CUSTOMER_CITY from customer b where b.CUSTOMER_NAME=‘Turner’) where a.CUSTOMER_NAME=‘Smith’;

10． 删除用户’Brooks’; delete from customer where CUSTOMER_NAME=‘Brooks’;

11． 删除没有存款或借款的用户信息； delete from customer where customer_name not in(select customer_name from depositor) and customer_name not in(select customer_name from borrower);

12． 开启另外一个会话，连接到服务器。在原会话上和新会话上分别查看所有修改或删除过的表的结果。

13．在原会话上的Tab中使用rollback回滚。 rollback;

14．重复以上的每一步，在每一步以后commit结果；然后在两个会话中分别查看对应表的查询结果。 commit; 两个表查询结果相同

[实验报告]

保留所有脚本并记录实验结果。外键对于插入数据顺序的影响。脚本执行的使用。记录所有任务的 SQL 语句，及查询的结果。回滚和提交的作用是什么？

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。