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

[实验目的]

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

[预备知识]

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

[实验内容]

1. 查询所有顾客的姓名。
   select customer_name from customer;

2. 查询所有分行的信息。
   select * from branch;

3. 查询所有存款人的姓名。
   select distinct customer_name from borrower;

4. 查询所有存款额大于 1200 元的存款信息。
   select * from account where balance >1200;

5. 查询所有存款额大于 1200 元的存款账户号，存款分行及存款人姓名。
   select account. account_number, branch_name,customer_name
   from account join depositor on ACCOUNT.ACCOUNT_NUMBER=DEPOSITOR.ACCOUNT_NUMBER
   where balance >1200;

6. 查询所有姓名以’M’字母开头的用户的信息。
   select * from customer where customer_name like ‘M%’;

7. 查询所有居住街道名第二个字母为’a’的用户信息。
   select * from customer where customer_street like ‘_a%’;

8. 查询借款额在 1000 到 2000 元的所有借款的信息。
   select * from loan where amount between 1000 and 2000;

9. 查询所有借款的信息，包括借款人姓名，居住街道，居住城市，借款行，借款行所在城市，借款号，借款金额。
   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;

10. 查询所有存款的信息，包括存款人的姓名，居住的街道，居住城市，存款行，存款行所在城市，存款号，存款金额。
    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;

11. 查询所有顾客中向银行借了钱或存了钱的顾客姓名。
    select distinct customer_name from depositor
    union
    select distinct customer_name from borrower;

12. 查询所有顾客中存了钱，但没有借钱的顾客姓名。

13. 查询所有顾客中即借了钱又存了钱的顾客姓名。
    select distinct depositor.customer_name
    from depositor join borrower on DEPOSITOR.CUSTOMER_NAME=BORROWER.CUSTOMER_NAME;

14. 建立一个视图，表示所有借款的信息，包括借款人姓名，居住街道，居住城市，借款行，借款行所在城市，借款号，借款金额。
    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;

16. 建立一个视图，表示所有存借款的信息，包括类型（存款还是借款），顾客姓名，顾客居住街道，顾客居住城市，分行名称，分行所在城市，金额。
    create view v_b
    as 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.balance
    from 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;

18. 查询所有顾客的存款信息，即使这个人没有存款。
    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;

19. 查询所有存款信息，并按存款行升序，存款金额逆序，存款号升序的排序优先级显示结果。
    select * from account order by 1 asc,3 desc;

20. 查询’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;

21. 建立一个表 t_branch,表结构与 branch 相同。（采用 create table tab_name as select 的方式）。
    create table t_branch as select * from branch where 1>2;

22. 将 branch 表中的数据插入 t_branch 表中。（采用 insert into tab_name select 的方式）。
    insert into t_branch select * from branch;

[实验报告]

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