-- SQL statements for table creation for CAP database
-- also copies data in from corresponding tables of Max's.

-- Initially drops the tables in case old versions exist.
-- Don't worry about the error messages if there are no
-- old versions to drop.

drop table customers;
drop table agents;
drop table products;
drop table orders;

create table customers (
  cid char(4) not null,
  cname varchar(13),
  city varchar(20),
  discnt real,
  primary key(cid)
  );

create table agents (
  aid char(3) not null,
  aname varchar(13),
  city varchar(20),
  percent smallint,
  primary key (aid)
  );

create table products (
  pid char(3) not null,
  pname varchar(13),
  city varchar(20),
  quantity integer,
  price double precision,
  primary key(pid));

create table orders (
  ordno integer not null,
  month char(3),
  cid char(4),
  aid char(3),
  pid char(3),
  qty integer,
  dollars double precision,
  primary key(ordno));

insert into customers(cid, cname, city, discnt)
  select cid, cname, city, discnt from max.customers;

insert into agents(aid, aname, city, percent)
  select aid, aname, city, percent from max.agents;
  
insert into products(pid, pname, city, quantity, price)
  select pid, pname, city, quantity, price from max.products;
  
insert into orders(ordno, month, cid, aid, pid, qty, dollars)
  select ordno, month, cid, aid, pid, qty, dollars from max.orders;