mytest_order_create.sql
/*==============================================
* *
* create tables orders and items *
* *
==============================================*/
USE mytest; -- you must have created the database mytest
IF OBJECT_ID ('orders', 'U') IS NOT NULL
BEGIN
PRINT 'The table orders already exists. Delete it.'
RETURN
END
IF OBJECT_ID ('items', 'U') IS NOT NULL
BEGIN
PRINT 'The table items already exists. Delete it.'
RETURN
END
-- =========== create tables order and items ===========
-- create table orders
CREATE TABLE orders (
oid INT NOT NULL IDENTITY(30000,1) PRIMARY KEY,
custid INT NOT NULL,
order_date DATE NOT NULL,
shipping_date DATE NULL,
to_street VARCHAR(20) NULL,
to_city VARCHAR(20) NULL,
to_country VARCHAR(20) NULL,
total NUMERIC(12, 2) NULL
)
CREATE INDEX idx_custid ON orders(custid) -- to lookup quickly the orders of a customer
CREATE INDEX idx_order_date ON orders(order_date)
-- create table items
CREATE TABLE items (
oid INT NOT NULL,
line_no INT NOT NULL,
prodid INT NOT NULL,
quantity INT NOT NULL,
price NUMERIC(12, 2) NOT NULL,
CONSTRAINT pk_oid_line PRIMARY KEY (oid, line_no)
)