Appendix D. CREATE TABLE Statements for Book Examples

This appendix is useful because it not only lists the CREATE TABLE statements used in the examples, but also gives you some of the syntax differences among the various database platforms. You can use these statements to create your own tables for performing hands-on exercises.

MySQL

EMPLOYEE_TBL

Click here to view code image

CREATE TABLE EMPLOYEE_TBL
(
EMP_ID              VARCHAR(9)       NOT NULL,
LAST_NAME           VARCHAR(15)      NOT NULL,
FIRST_NAME          VARCHAR(15)      NOT NULL,
MIDDLE_NAME         VARCHAR(15),
ADDRESS             VARCHAR(30)      NOT NULL,
CITY                VARCHAR(15)      NOT NULL,
STATE               CHAR(2)          NOT NULL,
ZIP                 INTEGER(5)       NOT NULL,
PHONE               CHAR(10),
PAGER               CHAR(10),
CONSTRAINT EMP_PK PRIMARY KEY (EMP_ID)
);

EMPLOYEE_PAY_TBL

Click here to view code image

CREATE TABLE EMPLOYEE_PAY_TBL
(
EMP_ID              VARCHAR(9)        NOT NULL    primary key,
POSITION            VARCHAR(15)       NOT NULL,
DATE_HIRE           DATE,
PAY_RATE            DECIMAL(4,2),
DATE_LAST_RAISE     DATE,
SALARY              DECIMAL(8,2),
BONUS               DECIMAL(6,2),
CONSTRAINT EMP_FK FOREIGN KEY  (EMP_ID) REFERENCES EMPLOYEE_TBL (EMP_ID)
);

CUSTOMER_TBL

Click here to view code image

CREATE TABLE CUSTOMER_TBL
(
CUST_ID             VARCHAR(10)    NOT NULL       primary key,
CUST_NAME           VARCHAR(30)    NOT NULL,
CUST_ADDRESS        VARCHAR(20)    NOT NULL,
CUST_CITY           VARCHAR(15)    NOT NULL,
CUST_STATE          CHAR(2)        NOT NULL,
CUST_ZIP            INTEGER(5)     NOT NULL,
CUST_PHONE          CHAR(10),
CUST_FAX            INTEGER(10)
);

ORDERS_TBL

Click here to view code image

CREATE TABLE ORDERS_TBL
(
ORD_NUM             VARCHAR(10)    NOT NULL     primary key,
CUST_ID             VARCHAR(10)    NOT NULL,
PROD_ID             VARCHAR(10)    NOT NULL,
QTY                 INTEGER(6)     NOT NULL,
ORD_DATE            DATE
);

PRODUCTS_TBL

Click here to view code image

CREATE TABLE PRODUCTS_TBL
(
PROD_ID        VARCHAR(10)    NOT NULL       primary key,
PROD_DESC      VARCHAR(40)    NOT NULL,
COST           DECIMAL(6,2)   NOT NULL
);

Oracle and SQL Server

EMPLOYEE_TBL

Click here to view code image

CREATE TABLE EMPLOYEE_TBL
(
EMP_ID              VARCHAR(9)       NOT NULL,
LAST_NAME           VARCHAR(15)      NOT NULL,
FIRST_NAME          VARCHAR(15)      NOT NULL,
MIDDLE_NAME         VARCHAR(15),
ADDRESS             VARCHAR(30)      NOT NULL,
CITY                VARCHAR(15)      NOT NULL,
STATE               CHAR(2)          NOT NULL,

ZIP                 INTEGER          NOT NULL,
PHONE               CHAR(10),
PAGER               CHAR(10),
CONSTRAINT EMP_PK PRIMARY KEY (EMP_ID)
);

EMPLOYEE_PAY_TBL

Click here to view code image

CREATE TABLE EMPLOYEE_PAY_TBL
(
EMP_ID              VARCHAR(9)        NOT NULL    primary key,
POSITION            VARCHAR(15)       NOT NULL,
DATE_HIRE           DATE,
PAY_RATE            DECIMAL(4,2),
DATE_LAST_RAISE     DATE,
SALARY              DECIMAL(8,2),
BONUS               DECIMAL(6,2),
CONSTRAINT EMP_FK FOREIGN KEY  (EMP_ID) REFERENCES EMPLOYEE_TBL (EMP_ID)
);

CUSTOMER_TBL

Click here to view code image

CREATE TABLE CUSTOMER_TBL
(
CUST_ID             VARCHAR(10)    NOT NULL       primary key,
CUST_NAME           VARCHAR(30)    NOT NULL,
CUST_ADDRESS        VARCHAR(20)    NOT NULL,
CUST_CITY           VARCHAR(15)    NOT NULL,
CUST_STATE          CHAR(2)        NOT NULL,
CUST_ZIP            INTEGER        NOT NULL,
CUST_PHONE          CHAR(10),
CUST_FAX            VARCHAR(10)
);

ORDERS_TBL

Click here to view code image

CREATE TABLE ORDERS_TBL
(
ORD_NUM             VARCHAR(10)    NOT NULL     primary key,
CUST_ID             VARCHAR(10)    NOT NULL,
PROD_ID             VARCHAR(10)    NOT NULL,
QTY                 INTEGER        NOT NULL,
ORD_DATE            DATE
);

PRODUCTS_TBL

Click here to view code image

CREATE TABLE PRODUCTS_TBL
(
PROD_ID        VARCHAR(10)    NOT NULL       primary key,
PROD_DESC      VARCHAR(40)    NOT NULL,
COST           DECIMAL(6,2)   NOT NULL
);