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)
)