ORACLE与数据库原理实验 实验四 查询与视图 （答案全）

ORACLE与数据库原理实验 实验四 查询与视图 （答案全）

实验四 查询与视图

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

[实验目的]

掌握 select 的基本使用。集合运算的语法。掌握建立视图的语法。掌握和理解外部连接的使用。掌握通过 select 方式建表和插入数据的方法。

[预备知识]

select 语句的基本语法create view 的语法外部链接的含义及语法

[实验内容]

查询所有顾客的姓名。 select customer_name from customer;查询所有分行的信息。 select * from branch;查询所有存款人的姓名。 select distinct customer_name from borrower;查询所有存款额大于 1200 元的存款信息。 select * from account where balance >1200;查询所有存款额大于 1200 元的存款账户号，存款分行及存款人姓名。 select account. account_number, branch_name,customer_name from account join depositor on ACCOUNT.ACCOUNT_NUMBER=DEPOSITOR.ACCOUNT_NUMBER where balance >1200;查询所有姓名以’M’字母开头的用户的信息。 select * from customer where customer_name like ‘M%’;查询所有居住街道名第二个字母为’a’的用户信息。 select * from customer where customer_street like ‘_a%’;查询借款额在 1000 到 2000 元的所有借款的信息。 select * from loan where amount between 1000 and 2000;查询所有借款的信息，包括借款人姓名，居住街道，居住城市，借款行，借款行所在城市，借款号，借款金额。 select borrower.customer_name,customer_street,customer_city,loan.branch_name,branch_city,loan.loan_number,loan.amount from customer join borrower on CUSTOMER.CUSTOMER_NAME=BORROWER.CUSTOMER_NAME join loan on BORROWER.LOAN_NUMBER=LOAN.LOAN_NUMBER join branch on LOAN.BRANCH_NAME=BRANCH.BRANCH_NAME;查询所有存款的信息，包括存款人的姓名，居住的街道，居住城市，存款行，存款行所在城市，存款号，存款金额。 select DEPOSITOR.CUSTOMER_NAME,customer_street,customer_city,ACCOUNT.BRANCH_NAME,branch_city,ACCOUNT.ACCOUNT_NUMBER,ACCOUNT.BALANCE from customer join depositor on CUSTOMER.CUSTOMER_NAME=DEPOSITOR.CUSTOMER_NAME join account on DEPOSITOR.ACCOUNT_NUMBER=ACCOUNT.ACCOUNT_NUMBER join branch on ACCOUNT.BRANCH_NAME=BRANCH.BRANCH_NAME;查询所有顾客中向银行借了钱或存了钱的顾客姓名。 select distinct customer_name from depositor union select distinct customer_name from borrower;查询所有顾客中存了钱，但没有借钱的顾客姓名。查询所有顾客中即借了钱又存了钱的顾客姓名。 select distinct depositor.customer_name from depositor join borrower on DEPOSITOR.CUSTOMER_NAME=BORROWER.CUSTOMER_NAME;建立一个视图，表示所有借款的信息，包括借款人姓名，居住街道，居住城市，借款行，借款行所在城市，借款号，借款金额。 create view v_c as select borrower.customer_name,customer_street,customer_city,loan.branch_name,branch_city,loan.loan_number,loan.amount from customer join borrower on CUSTOMER.CUSTOMER_NAME=BORROWER.CUSTOMER_NAME join loan on BORROWER.LOAN_NUMBER=LOAN.LOAN_NUMBER join branch on LOAN.BRANCH_NAME=BRANCH.BRANCH_NAME;

15.建立一个视图，表示所有存款的信息，包括存款人姓名，居住街道，居住城市，存款行，存款行所在城 市，存款号，存款金额。 create view v_a as select DEPOSITOR.CUSTOMER_NAME,customer_street,customer_city,ACCOUNT.BRANCH_NAME,branch_city,ACCOUNT.ACCOUNT_NUMBER,ACCOUNT.BALANCE from customer join depositor on CUSTOMER.CUSTOMER_NAME=DEPOSITOR.CUSTOMER_NAME join account on DEPOSITOR.ACCOUNT_NUMBER=ACCOUNT.ACCOUNT_NUMBER join branch on ACCOUNT.BRANCH_NAME=BRANCH.BRANCH_NAME;

建立一个视图，表示所有存借款的信息，包括类型（存款还是借款），顾客姓名，顾客居住街道，顾客居住城市，分行名称，分行所在城市，金额。create view v_bas select v_c.customer_name,v_c.customer_street,v_c.customer_city,v_c.branch_name,v_c.branch_city,v_c.loan_number,v_c.amount,v_a.account_number,v_a.balancefrom v_c full join v_a on v_c.CUSTOMER_NAME=v_a.CUSTOMER_NAME;

select * from v_b; 17. 查询存款金额最大的存款信息，包括存款人姓名，存款行，存款号，金额。 select customer_name,branch_name,account.account_number,balance from account join depositor on ACCOUNT.ACCOUNT_NUMBER=DEPOSITOR.ACCOUNT_NUMBER;

查询所有顾客的存款信息，即使这个人没有存款。 select CUSTOMER.CUSTOMER_NAME，A.ACCOUNT_NUMBER,B.BRANCH_NAME,B.BALANCE from depositor A full join account B on A.ACCOUNT_NUMBER=B.ACCOUNT_NUMBER right join customer on CUSTOMER.CUSTOMER_NAME=A.CUSTOMER_NAME;查询所有存款信息，并按存款行升序，存款金额逆序，存款号升序的排序优先级显示结果。 select * from account order by 1 asc,3 desc;查询’Downtown’分行的所有借款，包括借款号，借款人，借款金额，并按借款金额逆序排列显示结果。 select loan.loan_number,customer_name,amount from borrower full join loan on BORROWER.LOAN_NUMBER=LOAN.LOAN_NUMBER where branch_name=‘Downtown’ order by amount desc;建立一个表 t_branch,表结构与 branch 相同。（采用 create table tab_name as select 的方式）。 create table t_branch as select * from branch where 1>2;将 branch 表中的数据插入 t_branch 表中。（采用 insert into tab_name select 的方式）。 insert into t_branch select * from branch;

[实验报告]

SQL 都是用一条 SQL 完成。记录所有任务的 SQL 语句，及查询的结果。

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