9ENrpvpA

1drop table if exists order_details;
2drop table if exists orders;
3drop table if exists customers;
4drop table if exists products;
5
6-- products --
7
8create table products (
9    id          integer primary key,
10    category    char(3),
11    description varchar(4096),
12    price       decimal(10,2) not null
13);
14
15insert into products (id, category, description, price) values
16    (1, 'CPU', 'Intel Core 2 Duo', 200.00);
17insert into products (id, category, description, price) values
18    (2, 'CPU', 'Intel Pentium D', 98.99);
19insert into products (id, category, description, price) values
20    (3, 'CPU', 'AMD Athlon 64', 74.49);
21insert into products (id, category, description, price) values
22    (4, 'CPU', 'AMD Athlon 64x2', 115.98);
23insert into products (id, category, description, price) values
24    (5, 'HD', 'Seagate 320G', 77.49);
25insert into products (id, category, description, price) values
26    (6, 'HD', 'Maxtor 250G', 60.89);
27
28-- customers --
29
30create table customers (
31    id          integer primary key,
32    first_name  varchar(255),
33    last_name   varchar(255),
34    address     varchar(255)
35);
36
37insert into customers (id, first_name, last_name, address) values
38    (1, 'John', 'Doe', '123 Main St., Los Angeles, CA 90001');
39insert into customers (id, first_name, last_name, address) values
40    (2, 'Jane', 'DOE', '123 Main St., Los Angeles, CA 90001');
41insert into customers (id, first_name, last_name, address) values
42    (3, 'tom', 'smith', '456 State St., Los Angeles, CA 90002');
43
44-- orders --
45
46create table orders (
47    id              integer primary key,
48    customer_id     integer references customers(id),
49    date_ordered    date,
50    date_shipped    date
51);
52
53insert into orders (id, customer_id, date_ordered, date_shipped) values
54    (1, 1, '2011-08-29', '2011-09-05');
55insert into orders (id, customer_id, date_ordered, date_shipped) values
56    (2, 2, '2011-09-01', null);
57
58-- order_details --
59
60create table order_details (
61    order_id    integer references orders(id),
62    product_id  integer references products(id),
63    quantity    integer,
64  primary key (order_id, product_id)
65);
66
67insert into order_details (order_id, product_id, quantity) values (1, 1, 2);
68insert into order_details (order_id, product_id, quantity) values (1, 5, 2);
69insert into order_details (order_id, product_id, quantity) values (2, 2, 1);