SQL

C. Your company deals with many international customers. Therefore, each time someone wants to lookup the total revenue for an international customer, a conversion rate must be calculated. To save your employees time, you decide to create a materialized view.

Q.PIII.C1. Create a materialized view that stores the total revenue for each customer, and the conversion rate for at least 5 different (actual) currencies of your choice.

You can assume that original amount is in USD. Also note that you can retrieve the revenue from the LINEORDER table, which is different than the TotalOrderPrice stored in CUSTOMER_T2.

Paste in browser:

http://dbgroup.cdm.depaul.edu/CSC355_Fall2018/FinalProjectData.zip

Schemas:

create table customer_t1(

c_custkey integer not null,

c_name varchar(25) not null,

c_address varchar(25) not null,

c_city varchar(10) not null

);

create table customer_t2(

c_custkey integer not null,

c_nation varchar(15) not null,

c_region varchar(12) not null,

c_phone varchar(15) not null,

c_mktsegment varchar(10) not null,

c_totalorderprice integer

);

create table lineorder( lo_orderkey integer not null, lo_linenumber integer not null, lo_custkey integer not null, lo_partkey integer not null, lo_suppkey integer not null, lo_orderdate integer not null, lo_orderpriority varchar(15) not null, lo_shippriority varchar(1) not null, lo_quantity integer not null, lo_extendedprice integer not null, lo_ordertotalprice integer not null, lo_discount integer not null, lo_revenue integer not null, lo_supplycost integer not null, lo_tax integer not null, lo_commitdate integer not null, lo_shipmode varchar(10) not null );