SQL Bible [Electronic resources] نسخه متنی

SQL Scripts to Create ACME Database Objects

These statements create ACME database in DB2:

-- 
-- TABLE: ADDRESS 
--
CREATE TABLE ADDRESS(
ADDR_ID_N           INTEGER          NOT NULL,
ADDR_CUSTID_FN      INTEGER,
ADDR_SALESMANID_FN  INTEGER, 
ADDR_ADDRESS_S      VARCHAR(60),
ADDR_TYPE_S         VARCHAR(8),
ADDR_CITY_S         VARCHAR(18)      NOT NULL,
ADDR_STATE_S        CHAR(2),
ADDR_ZIP_S          VARCHAR(10)      NOT NULL,
ADDR_COUNTRY_S      CHAR(3),
CONSTRAINT CHK_ADDR_TYPE 
CHECK (ADDR_TYPE_S IN ('BILLING', 'SHIPPING')),
CONSTRAINT PK_ADDRPRIMARY PRIMARY KEY (ADDR_ID_N)
) 
;
-- 
-- TABLE: CUSTOMER 
--
CREATE TABLE CUSTOMER(
CUST_ID_N             INTEGER          NOT NULL,
CUST_PAYTERMSID_FN    INTEGER,
CUST_SALESMANID_FN    INTEGER,
CUST_STATUS_S         VARCHAR(1)       DEFAULT 'Y' NOT NULL,
CUST_NAME_S           VARCHAR(50)      NOT NULL,
CUST_ALIAS_S          VARCHAR(15),
CUST_CREDHOLD_S       VARCHAR(1)       DEFAULT 'Y' NOT NULL,
CONSTRAINT CHK_CUST_STATUS CHECK (CUST_STATUS_S IN ('N', 'Y')),
CONSTRAINT CHK_CUST_CREDHOLD CHECK (CUST_CREDHOLD_S IN ('N', 'Y')),
CONSTRAINT PK_CUSTPRIMARY PRIMARY KEY (CUST_ID_N)
) 
;
-- 
-- TABLE: ORDER_HEADER 
--
CREATE TABLE ORDER_HEADER(
ORDHDR_ID_N                INTEGER          NOT NULL,
ORDHDR_PAYTERMS_FN         INTEGER,
ORDHDR_STATUSID_FN         INTEGER,
ORDHDR_CUSTID_FN           INTEGER,
ORDHDR_SALESMANID_FN       INTEGER,
ORDHDR_NBR_S               VARCHAR(30)      NOT NULL,
ORDHDR_INVOICENBR_N        INTEGER,
ORDHDR_ORDERDATE_D         DATE,
ORDHDR_INVOICEDATE_D       DATE,
ORDHDR_CANCELDATE_D        DATE,
ORDHDR_CREDITHOLD_S        CHAR(1),
ORDHDR_READYTOINVOICE_S    CHAR(1)          DEFAULT 'N',
ORDHDR_NOTES_S             VARCHAR(60),
ORDHDR_CREATEDBY_S         VARCHAR(10),
ORDHDR_CREATEDATE_D        DATE,
CONSTRAINT CHK_ORDHDR_READY CHECK 
(ORDHDR_READYTOINVOICE_S IN ('N', 'Y')),
CONSTRAINT CHK_ORDHDR_CREDH CHECK (ORDHDR_CREDITHOLD_S IN ('N', 'Y')),
CONSTRAINT PK_ORDHDRPRIM PRIMARY KEY (ORDHDR_ID_N),
CONSTRAINT IDX_ORDHDR_ORDNBR  UNIQUE (ORDHDR_NBR_S)
) 
;
-- 
-- TABLE: ORDER_LINE 
--
CREATE TABLE ORDER_LINE(
ORDLINE_ID_N            INTEGER          NOT NULL,
ORDLINE_ORDHDRID_FN     INTEGER          NOT NULL,
ORDLINE_PRODID_FN       INTEGER,
ORDLINE_ORDQTY_N        INTEGER,
ORDLINE_SHIPQTY_N       INTEGER,
ORDLINE_CREATEDATE_D    DATE,
ORDLINE_CREATEDBY_S     VARCHAR(10),
CONSTRAINT PK_ORDLINEPRIM PRIMARY KEY (ORDLINE_ID_N)
) 
;
-- 
-- TABLE: ORDER_SHIPMENT 
--
CREATE TABLE ORDER_SHIPMENT(
ORDSHIP_ORDHDR_ID_FN      INTEGER    NOT NULL,
ORDSHIP_SHIPMENT_ID_FN    INTEGER    NOT NULL,
CONSTRAINT PK_ORDHDRSHIP 
PRIMARY KEY (ORDSHIP_ORDHDR_ID_FN,ORDSHIP_SHIPMENT_ID_FN)
) 
;
-- 
-- TABLE: PAYMENT_TERMS 
--
CREATE TABLE PAYMENT_TERMS(
PAYTERMS_ID_N           INTEGER          NOT NULL,
PAYTERMS_CODE_S         VARCHAR(6),
PAYTERMS_DESC_S         VARCHAR(60),
PAYTERMS_DISCPCT_N      DECIMAL(5,2),
PAYTERMS_DAYSTOPAY_N    INTEGER,
CONSTRAINT PK_PAYTERMS PRIMARY KEY (PAYTERMS_ID_N)
) 
;
-- 
-- TABLE: PHONE 
--
CREATE TABLE PHONE(
PHONE_ID_N          INTEGER          NOT NULL,
PHONE_CUSTID_FN     INTEGER,
PHONE_SALESMANID_FN INTEGER, 
PHONE_PHONENUM_S    VARCHAR(20),
PHONE_TYPE_S        VARCHAR(20),
CONSTRAINT CHK_PHONE_TYPE CHECK (PHONE_TYPE_S IN ('PHONE', 'FAX')),
CONSTRAINT PK_PHONERIMARY PRIMARY KEY (PHONE_ID_N)
) 
;
-- 
-- TABLE: PRODUCT 
--
CREATE TABLE PRODUCT(
PROD_ID_N             INTEGER          NOT NULL,
PROD_PRICE_N          DECIMAL(10,2),
PROD_NUM_S            VARCHAR(10),
PROD_DESCRIPTION_S    VARCHAR(44)      NOT NULL,
PROD_STATUS_S         CHAR(1)          DEFAULT 'Y',
PROD_BRAND_S          VARCHAR(20)      NOT NULL,
PROD_PLTWID_N         DECIMAL(5,2)     NOT NULL,
PROD_PLTLEN_N         DECIMAL(5,2)     NOT NULL,
PROD_NETWGHT_N        DECIMAL(10,3),
PROD_SHIPWEIGHT_N     DECIMAL(10,3),
CONSTRAINT CHK_PRODSTATUS CHECK (PROD_STATUS_S in ('N', 'Y')),
CONSTRAINT PK_PRODUCTPRIM PRIMARY KEY (PROD_ID_N)
) 
;
-- 
-- TABLE: SALESMAN 
--
CREATE TABLE SALESMAN(
SALESMAN_ID_N         INTEGER          NOT NULL,
SALESMAN_CODE_S       VARCHAR(2)       NOT NULL,
SALESMAN_NAME_S       VARCHAR(50)      NOT NULL,
SALESMAN_STATUS_S     CHAR(1)          DEFAULT 'Y',
CONSTRAINT CHK_SALESSTATUS CHECK (SALESMAN_STATUS_S in ('N', 'Y')),
CONSTRAINT PK_SALESMANPRIM PRIMARY KEY (SALESMAN_ID_N)
) 
;
-- 
-- TABLE: SHIPMENT 
--
CREATE TABLE SHIPMENT(
SHIPMENT_ID_N             INTEGER          NOT NULL,
SHIPMENT_BOLNUM_S         VARCHAR(6),
SHIPMENT_SHIPDATE_D       DATE,
SHIPMENT_ARRIVDATE_D      DATE,
SHIPMENT_TOTALCASES_N     INTEGER,
SHIPMENT_TRAILERNBR_S     VARCHAR(12),
SHIPMENT_SHPMNTFRGHT_N    DECIMAL(12,2),
SHIPMENT_FRTTERMS_S       VARCHAR(3),
SHIPMENT_CREATEDBY_S      VARCHAR(10),
SHIPMENT_CREATEDATE_D     TIMESTAMP,
CONSTRAINT CHK_SHIPFRTTERMS 
CHECK (SHIPMENT_FRTTERMS_S IN ('COL', 'PPD')),
CONSTRAINT PK_SHIPMENTRPRIM PRIMARY KEY (SHIPMENT_ID_N)
) 
;
-- 
-- TABLE: STATUS 
--
CREATE TABLE STATUS(
STATUS_ID_N      INTEGER          NOT NULL,
STATUS_CODE_S    CHAR(2),
STATUS_DESC_S    VARCHAR(30),
CONSTRAINT PK_STATUSPRIM PRIMARY KEY (STATUS_ID_N)
) 
;
-- 
-- TABLE: DISCOUNT
--
CREATE TABLE DISCOUNT(
DISC_MINAMOUNT_N DECIMAL(14,4)      NOT NULL,
DISC_MAXAMOUNT_N DECIMAL(14,4)      NOT NULL,
DISC_PCT         DECIMAL(5,3),
CONSTRAINT PK_DISCOUNT 
PRIMARY KEY(DISC_MINAMOUNT_N, DISC_MAXAMOUNT_N)
)
;
-- 
-- TABLE: RESELLER 
--
CREATE TABLE RESELLER ( 
RESELLER_ID_N         INT     NOT NULL, 
RESELLER_NAME_S       VARCHAR(30), 
RESELLER_SUPPLIER_ID  INT, 
CONSTRAINT PK_RESELLER
PRIMARY KEY (RESELLER_ID_N) 
)
;
-- 
-- INDEXES
--
CREATE INDEX IDX_ADDR_CUST ON ADDRESS(ADDR_CUSTID_FN)
;
CREATE INDEX IDX_CUST_PAYTERMS ON CUSTOMER(CUST_PAYTERMSID_FN)
;
CREATE INDEX IDX_CUST_SALESMAN ON CUSTOMER(CUST_SALESMANID_FN)
;
CREATE INDEX IDX_ORDHDR_CUST ON ORDER_HEADER(ORDHDR_CUSTID_FN)
;
CREATE INDEX IDX_ORDHDR_STATUS ON ORDER_HEADER(ORDHDR_STATUSID_FN)
;
CREATE INDEX IDX_ORDHDR_PAYTERM ON ORDER_HEADER(ORDHDR_PAYTERMS_FN)
;
CREATE INDEX IDX_ORDHDR_SALES ON ORDER_HEADER(ORDHDR_SALESMANID_FN)
;
CREATE INDEX IDX_ORDLINE_ORDHDR ON ORDER_LINE(ORDLINE_ORDHDRID_FN)
;
CREATE INDEX IDX_ORDLINE_PROD ON ORDER_LINE(ORDLINE_PRODID_FN)
;
CREATE INDEX IDX_ORDSHIP_ORD ON ORDER_SHIPMENT(ORDSHIP_ORDHDR_ID_FN)
;
CREATE INDEX IDX_ORDSHIP_SHIP ON ORDER_SHIPMENT(ORDSHIP_SHIPMENT_ID_FN)
;
CREATE INDEX IDX_PHONE_CUST ON PHONE(PHONE_CUSTID_FN)
;
CREATE INDEX IDX_RESELLER_RESSUPID ON RESELLER(RESELLER_SUPPLIER_ID)
;

-- 
--  FOREIGN KEYS 
--
ALTER TABLE ADDRESS ADD CONSTRAINT FK_ADDR_CUST 
FOREIGN KEY (ADDR_CUSTID_FN)
REFERENCES CUSTOMER(CUST_ID_N)
;
ALTER TABLE ADDRESS ADD  CONSTRAINT FK_ADDR_SALESMAN
FOREIGN KEY (ADDR_SALESMANID_FN) 
REFERENCES SALESMAN (SALESMAN_ID_N) ;
ALTER TABLE CUSTOMER ADD CONSTRAINT FK_CUST_PAYTERMS 
FOREIGN KEY (CUST_PAYTERMSID_FN)
REFERENCES PAYMENT_TERMS(PAYTERMS_ID_N)
;
ALTER TABLE CUSTOMER ADD CONSTRAINT FK_CUST_SALESMAN 
FOREIGN KEY (CUST_SALESMANID_FN)
REFERENCES SALESMAN(SALESMAN_ID_N)
;
ALTER TABLE ORDER_HEADER ADD CONSTRAINT FK_ORDHDR_PAYTERMS 
FOREIGN KEY (ORDHDR_PAYTERMS_FN)
REFERENCES PAYMENT_TERMS(PAYTERMS_ID_N)
;
ALTER TABLE ORDER_HEADER ADD CONSTRAINT FK_ORDHDR_CUSTOMER 
FOREIGN KEY (ORDHDR_CUSTID_FN)
REFERENCES CUSTOMER(CUST_ID_N)
;
ALTER TABLE ORDER_HEADER ADD CONSTRAINT FK_ORDHDR_STAT 
FOREIGN KEY (ORDHDR_STATUSID_FN)
REFERENCES STATUS(STATUS_ID_N)
;
ALTER TABLE ORDER_HEADER ADD CONSTRAINT FK_ORDHDR_SALES 
FOREIGN KEY (ORDHDR_SALESMANID_FN)
REFERENCES SALESMAN(SALESMAN_ID_N)
;

ALTER TABLE ORDER_LINE ADD CONSTRAINT FK_ORDLINE_ORDHDR 
FOREIGN KEY (ORDLINE_ORDHDRID_FN)
REFERENCES ORDER_HEADER(ORDHDR_ID_N)
;
ALTER TABLE ORDER_LINE ADD CONSTRAINT FK_ORDLINE_PRODUCT 
FOREIGN KEY (ORDLINE_PRODID_FN)
REFERENCES PRODUCT(PROD_ID_N)
;
ALTER TABLE ORDER_SHIPMENT ADD CONSTRAINT FK_ORDSH_ORD 
FOREIGN KEY (ORDSHIP_ORDHDR_ID_FN)
REFERENCES ORDER_HEADER(ORDHDR_ID_N)
;
ALTER TABLE ORDER_SHIPMENT ADD CONSTRAINT FK_ORDSH_SHIP 
FOREIGN KEY (ORDSHIP_SHIPMENT_ID_FN)
REFERENCES SHIPMENT(SHIPMENT_ID_N)
;
ALTER TABLE PHONE ADD CONSTRAINT FK_PHONE_CUST 
FOREIGN KEY (PHONE_CUSTID_FN)
REFERENCES CUSTOMER(CUST_ID_N)
;
ALTER TABLE PHONE ADD CONSTRAINT FK_SALESMAN_CUST
FOREIGN KEY (PHONE_SALESMANID_FN) 
REFERENCES SALESMAN (SALESMAN_ID_N)
;
ALTER TABLE RESELLER ADD CONSTRAINT FK_RESELLER_SUPPLIER
FOREIGN KEY (RESELLER_SUPPLIER_ID) 
REFERENCES RESELLER (RESELLER_ID_N)
;
--
-- VIEW V_CUSTOMER_TOTALS
--
CREATE VIEW   v_customer_totals
(
customer_name,
order_number,
total_price
)
AS
(
SELECT       customer.cust_name_s, 
order_header.ordhdr_nbr_s, 
sum(product.prod_price_n * order_line.ordline_ordqty_n) 
FROM         customer, 
order_header,
order_line,
product 
WHERE        customer.cust_id_n = order_header.ordhdr_custid_fn
AND          order_header.ordhdr_id_n = order_line.ordline_ordhdrid_fn
AND          product.prod_id_n = order_line.ordline_prodid_fn
AND          order_line.ordline_ordqty_n IS NOT NULL
GROUP BY     customer.cust_name_s,
order_header.ordhdr_nbr_s
)
;
--
-- CREATE VIEW V_CUSTOMER_STATUS
--
CREATE VIEW   v_customer_status 
( 
name,
status 
)
AS
SELECT        cust_name_s, 
cust_status_s
FROM          customer
;
--
--CREATE VIEW V_PHONE_NUMBER
--
CREATE VIEW   v_phone_number
( 
phone_id, 
phone_number
)
AS 
SELECT        phone_id_n, 
phone_phonenum_s
FROM          phone
WHERE         phone_type_s = 'PHONE';
--
--CREATE VIEW V_FAX NUMBER
--
CREATE VIEW   v_fax_number
( 
fax_id, 
fax_number
)
AS 
SELECT        phone_id_n, 
phone_phonenum_s
FROM          phone
WHERE         phone_type_s = 'FAX'
WITH CHECK OPTION
;

--
-- CREATE VIEW V_CUSTOMER_TOTALS_OVER_15000
--
CREATE VIEW   v_customer_totals_over_15000
AS
SELECT        * 
FROM          v_customer_totals 
WHERE         total_price > 15000;
--
-- CREATE VIEW V_CONTACT_LIST
--
CREATE VIEW   v_contact_list
(
name,
phone_number,
contact_type
)
AS
SELECT        cust_name_s, 
phone_phonenum_s,
'customer'
FROM          customer,
phone
WHERE         cust_id_n = phone_custid_fn 
AND           phone_type_s = 'PHONE'
UNION
SELECT        salesman_name_s,
phone_phonenum_s,
'salesperson'
FROM          salesman,
phone
WHERE         salesman_id_n = phone_salesmanid_fn
AND           phone_type_s = 'PHONE';
--
--CREATE VIEW V_WILE_BESS_ORDERS
--
CREATE VIEW   v_wile_bess_orders
(
order_number,
order_date
)
AS
SELECT        ordhdr_nbr_s, 
ordhdr_orderdate_d 
FROM          order_header 
WHERE         ordhdr_custid_fn IN
(
SELECT   cust_id_n
FROM     customer
WHERE    cust_name_s = 'WILE BESS COMPANY'
)
;
CREATE VIEW   v_customer_totals_wilebess
AS
SELECT        customer_name, 
total_price
FROM          v_customer_totals
WHERE         customer_name = 'WILE BESS COMPANY'
;

The same set of scripts with just a slight modification will create the ACME database with Oracle. All you would need to do is to replace the SHIPMENT_CREATEDATE_D column data type from TIMESTAMP to DATE in table SHIPMENT.

To create an MS SQL ACME database, the SHIPMENT_CREATEDATE_D column data type has to be changed to DATETIME (or SMALLDATETIME); in addition, all DATE columns need to be altered to the appropriate MS SQL datatypes.