ORACLE与数据库原理实验 实验五 复杂查询（答案全）

[实验目的]

1. 掌握聚类函数的使用
2. 掌握子查询的使用
3. 掌握 with 子句的使用
4. 掌握 Top-K 查询的写法
5. 掌握交叉表查询的写法

[预备知识]

1. 常用聚类函数的使用语法。
2. in 子查询与 exists 子查询。
3. with 子句的语法。
4. decode 函数或 case 子句的语法。

[实验内容]

1. 查询最大的存款金额。
   select MAX(balance) from account;

2. 查询各分行最大的存款金额。
   select branch_name ,MAX(balance) 分行最大存款金额 from account group by branch_name;

3. 查询各分行放贷金额总数，及放贷笔数，并按放贷金额总数降序排列。
   select branch_name,sum(amount) 放贷总金额数,count(branch_name) 放贷笔数,avg(amount) 平均单笔放贷金额
   from loan group by branch_name
   order by sum(amount) desc;

4. 查询平均单笔借款金额超过 750 元的所有分行的放贷金额总数，及放贷笔数，平均单笔放贷金额，并按
   放贷金额总数降序排列。
   select branch_name,sum(amount) 放贷总金额数,count(amount) 放贷笔数,avg(amount) 平均单笔放贷金额
   from loan group by branch_name
   having avg(amount)>750 order by sum(amount) desc;

5. 查询各分行的平均存款额。
   select branch_name,avg(balance) 平均存款额
   from
   account group by branch_name;

6. 查询各分行存款额超过平均存款额的分行名，平均存款金额。
   select branch_name,avg(balance) from account group by branch_name having avg(balance)>641.66;

7. 使用 in 子句和 exists 子句分别查询，借了钱但是没有存钱的顾客姓名。
   select customer_name from borrower where customer_name not in(select customer_name from depositor);

select customer_name from borrower where
not exists(select customer_name from depositor where borrower.customer_name=depositor.customer_name);

8. 查询’Perryridge’， ‘Brighton’， ‘Downtown’分行存款信息，包括存款号，分行，金额，存款人姓名。
   select DEPOSITOR.ACCOUNT_NUMBER,branch_name,balance,customer_name
   from depositor join account on DEPOSITOR.ACCOUNT_NUMBER=ACCOUNT.ACCOUNT_NUMBER
   where branch_name in (‘Perryridge’,‘Brighton’,‘Downtown’);

9. 查询在’Hayes’存钱的分行都存了钱的顾客姓名。
   select customer_name from depositor join account
   on
   DEPOSITOR.ACCOUNT_NUMBER=ACCOUNT.ACCOUNT_NUMBER
   where branch_name in
   (select branch_name from depositor join account
   on
   DEPOSITOR.ACCOUNT_NUMBER=ACCOUNT.ACCOUNT_NUMBER
   where customer_name=‘Hayes’);

10. 查询存款额最多的前 3 位分行的名字。
    select branch_name
    from (select branch_name
    from account
    group by branch_name
    order by sum(balance) desc)
    where rownum<=3;

11. 查询’Perryridge’， ‘Brighton’， ‘Downtown’这三家分行，所有顾客的存款金额。列名分别为顾客名，
    Perryridge 分行存款额， Brighton 分行存款额， Downtown 分行存款额。

[实验报告]

1. SQL 都是用一条 SQL 完成。
2. 记录所有任务的 SQL 语句，及查询的结果。
3. 比较 in 子句与 exists 子句的区别。