实训6-视图

use finance1;

-- 创建包含所有保险资产记录的详细信息的视图v_insurance_detail，包括购买客户的名称、客户的身份证号、保险名称、保障项目、商品状态、商品数量、保险金额、保险年限、商品收益和购买时间。

-- 请用1条SQL语句完成上述任务：

create view v_insurance_detail as

select

    c_name, 

    c_id_card,

    i_name,

    i_project,

    pro_status,

    pro_quantity,

    i_amount,

    i_year,

    pro_income,

    pro_purchase_time

from client, property, insurance

where c_id = pro_c_id 

and pro_type = 2 

and pro_pif_id = i_id;

/*   end  of your code  */

-- 基于上一关创建的视图v_insurance_detail进行分组统计查询，列出每位客户的姓名，身份证号，保险投资总额(insurance_total_amount)和保险投资总收益(insurance_total_revenue),结果依保险投资总额降序排列。

--  请用一条SQL语句实现该查询：

select

    c_name, 

    c_id_card, 

    sum(pro_quantity * i_amount) as insurance_total_amount, 

    sum(pro_income) as insurance_total_revenue 

from v_insurance_detail 

group by c_id_card 

order by insurance_total_amount desc;

/*  end  of  your code  */