loaddw.sql

This file contains the commands necessary to load data from two sources:

• The files with the extension .unl in your demonstration directory
• Data selected from the stores_demo database

These SQL statements in loaddw.sql accomplish these actions:

connect to "stores_demo ";
load from "add_orders.unl" 
   insert into stores_demo :orders;
load from 'add_items.unl' 
   insert into stores_demo :items;

connect to "sales_demo";
load from 'costs.unl'
   insert into cost;
load from 'time.unl'
   insert into time;

insert into geography(district_name, state_code, state_name)
   select distinct c.city, s.code, s.sname
from stores_demo :customer c, stores_demo :state s
   where c.state = s.code;
update geography      -- converts state_code values to region values
   set region = 1
   where state_code = "CA";
update geography
   set region = 2
   where state_code <> "CA";

insert into customer (customer_code, customer_name, company_name)
   select c.customer_num, trim(c.fname) || " " || c.lname, c.company
   from stores_demo :customer c;

insert into product (product_code, product_name, vendor_code,
   vendor_name, product_line_code, product_line_name)
 select a.catalog_num, 
      trim(m.manu_name) || " "|| s.description,
      m.manu_code, m.manu_name, s.stock_num, s.description
   from stores_demo :catalog a, stores_demo :manufact m,
      stores_demo :stock s
   where a.stock_num = s.stock_num and
      a.manu_code = s.manu_code and
       s.manu_code = m.manu_code;
insert into sales (customer_code, district_code, 
   time_code, product_code,
   units_sold, revenue, cost, net_profit)
   select c.customer_num, g.district_code, t.time_code, p.product_code,
   SUM(i.quantity), SUM(i.total_price), 
   SUM(i.quantity * x.cost),
   SUM(i.total_price) - SUM(i.quantity * x.cost)
   from stores_demo :customer c, geography g, time t, 
       product p,
      stores_demo :items i, stores_demo :orders o, cost x
    where c.customer_num = o.customer_num and
       o.order_num = i.order_num and
       p.product_line_code = i.stock_num and
      p.vendor_code = i.manu_code and
      t.order_date = o.order_date and
      p.product_code = x.product_code and
      c.city = g.district_name
      GROUP BY 1,2,3,4;

connect to "stores_demo ";
load from 'add_orders.unl' 
   insert into stores_demo :orders;
load from 'add_items.unl' 
   insert into stores_demo :items;

connect to "sales_demo";
load from 'costs.unl'
   insert into cost;
load from 'time.unl'
   insert into time;

insert into geography(district_name, state_code, state_name)
   select distinct c.city, s.code, s.sname
from stores_demo :customer c, stores_demo :state s
   where c.state = s.code;
update geography      -- converts state_code values to region values
   set region = 1
   where state_code = "CA";
update geography
   set region = 2
   where state_code <> "CA";

insert into customer (customer_code, customer_name, company_name)
   select c.customer_num, trim(c.fname) || " " || c.lname, c.company
   from stores_demo :customer c;

insert into product (product_code, product_name, vendor_code,
   vendor_name, product_line_code, product_line_name)
 select a.catalog_num, 
      trim(m.manu_name) || " " || s.description,
      m.manu_code, m.manu_name, s.stock_num, s.description
   from stores_demo :catalog a, stores_demo :manufact m,
      stores_demo :stock s
   where a.stock_num = s.stock_num and
      a.manu_code = s.manu_code and
       s.manu_code = m.manu_code;

insert into sales (customer_code, district_code, 
   time_code, product_code,
   units_sold, revenue, cost, net_profit)
   select c.customer_num, g.district_code, t.time_code, p.product_code,
   SUM(i.quantity), SUM(i.total_price), 
   SUM(i.quantity * x.cost),
   SUM(i.total_price) - SUM(i.quantity * x.cost)
   from stores_demo :customer c, geography g, time t, product p,
      stores_demo :items i, stores_demo :orders o, cost x
    where c.customer_num = o.customer_num and
       o.order_num = i.order_num and
       p.product_line_code = i.stock_num and
      p.vendor_code = i.manu_code and
      t.order_date = o.order_date and
      p.product_code = x.product_code and
      c.city = g.district_name
      GROUP BY 1,2,3,4;