TEST DB for Online Orders

1. TABLESPACE / USER CREATION :

/*Create a tablespace first*/
CREATE TABLESPACE tutorials
DATAFILE 'C:\oraclexe\app\oracle\oradata\xe\tutorials01.dbf'
SIZE 10M AUTOEXTEND ON NEXT 10M MAXSIZE 100M;


/*Create new user*/
CREATE USER lakshman01
IDENTIFIED BY passwd
DEFAULT TABLESPACE tutorials
TEMPORARY TABLESPACE temp;


/*Grant permissions to the newly created user*/
GRANT CREATE SESSION TO lakshman01;
GRANT CREATE TABLE TO lakshman01;
GRANT CREATE VIEW TO lakshman01;
GRANT CREATE SEQUENCE TO lakshman01;

2. TABLE CREATION

CREATE TABLE customers
(
customer_id INT PRIMARY KEY NOT NULL,
customer_name varchar2(255),
customer_address varchar2(255),
customer_city varchar2(255),
customer_state varchar2(255)
);


CREATE TABLE orders
(
order_id INT PRIMARY KEY NOT NULL,
order_date DATE NOT NULL,
customer_id INT REFERENCES customers(customer_id)
);


CREATE TABLE items
(
item_id INT PRIMARY KEY NOT NULL,
item_description varchar2(255),
item_price varchar2(255)
);


/*Junction table between ORDERS and ITEMS*/
CREATE TABLE order_items
(
order_id INT REFERENCES orders(order_id),
item_id INT REFERENCES items(item_id),
item_qty varchar2(255),
CONSTRAINT pk_order PRIMARY KEY (order_id, item_id)
);


ALTER TABLE orders
ADD order_comments varchar2(255) DEFAULT NULL;


3. DATA INSERTION :

INSERT INTO customers VALUES
(1, 'Robin Smith', '100 Oracle Plaza', 'Sunnyvale', 'CA');

INSERT ALL
INTO customers VALUES (2, 'John Lincoln', '200 Oracle Plaza', 'Sunnyvale', 'CA')
select * from dual;

INSERT ALL
INTO customers VALUES (3, 'Peter Doje', '300 Oracle Plaza', 'Sunnyvale', 'CA')
INTO customers VALUES (4, 'David Crow', '400 Oracle Plaza', 'Sunnyvale', 'CA')
select * from dual;

INSERT ALL
INTO customers VALUES (5, 'Test User1', '500 Oracle Plaza', 'Santa Clara', 'CA')
INTO customers VALUES (6, 'Test User2', '600 Oracle Plaza', 'Santa Clara', 'CA')
INTO customers VALUES (7, 'Test User3', '700 Oracle Plaza', 'Palo Alto', 'CA')
INTO customers VALUES (8, 'Test User4', '800 Oracle Plaza', 'Palo Alto', 'CA')
INTO customers VALUES (9, 'Test User5', '900 Oracle Plaza', 'Palo Alto', 'CA')
INTO customers VALUES (10, 'Test User6', '1000 Oracle Plaza', 'Menlo Park', 'CA')
select * from dual;

INSERT ALL
INTO items VALUES (1, 'Pen', 10)
INTO items VALUES (2, 'Pencil', 10)
INTO items VALUES (3, 'Eraser', 10)
INTO items VALUES (4, 'Notebook', 20)
INTO items VALUES (5, 'Stapler', 30)
INTO items VALUES (6, 'Backpack', 40)
select * from DUAL;

INSERT ALL
INTO ORDERS VALUES (1, to_date('01/23/2012','mm/dd/yyyy'), 3)
INTO ORDERS VALUES (2, to_date('01/23/2012','mm/dd/yyyy'), 6)
INTO ORDERS VALUES (3, to_date('01/23/2012','mm/dd/yyyy'), 9)
INTO ORDERS VALUES (4, to_date('01/24/2012','mm/dd/yyyy'), 10)
select * from DUAL;

INSERT ALL
INTO ORDER_ITEMS VALUES (1, 1, 5)
INTO ORDER_ITEMS VALUES (1, 2, 5)
INTO ORDER_ITEMS VALUES (1, 3, 5)
INTO ORDER_ITEMS VALUES (1, 4, 10)
INTO ORDER_ITEMS VALUES (2, 1, 10)
INTO ORDER_ITEMS VALUES (2, 4, 5)
INTO ORDER_ITEMS VALUES (2, 5, 1)
INTO ORDER_ITEMS VALUES (3, 1, 2)
INTO ORDER_ITEMS VALUES (3, 2, 4)
INTO ORDER_ITEMS VALUES (3, 3, 2)
INTO ORDER_ITEMS VALUES (3, 4, 4)
INTO ORDER_ITEMS VALUES (3, 6, 1)
INTO ORDER_ITEMS VALUES (4, 5, 2)
INTO ORDER_ITEMS VALUES (4, 6, 2)
select * from DUAL; 

Any update or delete?
-----------------------
UPDATE customers SET customer_name='Peter Dove' where customer_id = 3;
DELETE FROM customers WHERE customer_id = 3 OR customer_id = 4;
 

REFERENCE ER : http://phlonx.com/resources/nf3/