

create table checks
(`check`          integer       not null,
 payee          varchar(20)      not null,
 amount         decimal(6,2)     not null,
 remarks        varchar(20)     not null);


create table deposits
(deposit          integer       not null,
 whopaid          varchar(25)      not null,
 amount           decimal(6,2)     not null,
 remarks          varchar(20)      not null);


create table bikes
(name          varchar(15)      not null,
 framesize     decimal(10,2)    not null,
 composition   varchar(12)      not null,
 milesridden   integer      not null,
 type          varchar(8)       not null);


create table price
(item          varchar(15)     not null,
 wholesale     decimal(4,2)    not null);



create table hilow
(state         varchar(10)     not null,
 lows          integer      not null,
 highs         integer      not null,
 difference    integer         null);



create table remains
(numerator     numeric(10)     not null,
 denominator   numeric(12)     not null);




create table precedence
(n1          numeric(10)      not null,
 n2          numeric(10)     not null,
 n3          numeric(10)     not null,
 n4          numeric(10)     not null);




create table friends
(lastname      varchar(15)     not null,
 firstname     varchar(15)     not null,
 areacode      numeric(9)      null,
 phone         varchar(10)     null,
 st            char(2)         not null,
 zip           varchar(5)      not null);



create table parts
(name          varchar(15)     not null,
 location      varchar(15)     not null,
 partnumber    numeric(10)     not null);




create table vacation
(lastname        varchar(15)    not null,
 employeenum     numeric(11)    not null,
 years           numeric(8)     not null,
 leavetaken      numeric(11)    null);




create table football
(name          varchar(20)     not null);




create table softball
(name          varchar(20)     not null);



create table teamstats
(name        varchar(10)    not null,
 pos         varchar(3)     not null,
 ab          numeric(3)     not null,
 hits        numeric(4)     not null,
 walks       varchar(5)     not null,
 singles     varchar(7)     not null,
 doubles     varchar(7)     not null,
 triples     varchar(7)     not null,
 hr          numeric(2)     not null,
 so          varchar(2)     not null);




create table project
(task          varchar(14)  not null,
 startdate     date,
 enddate       date);



create table numbers
(a          decimal(10,4)     not null,
 b          decimal(10,4)     not null);




create table characters
(lastname      varchar(15)     not null,
 firstname     varchar(15)     not null,
 m             char(1)         null,
 code          numeric(10)     not null);



create table `convert`
(name          varchar(15)   not null,
 testnum       numeric(9)    not null);



create table puzzle
(name          varchar(15)     not null,
 location      varchar(14)     not null);




create table orgchart
(name          varchar(15)     not null,
 team          varchar(11)     not null,
 salary        decimal(10,2)   not null,
 sickleave     numeric(10)     not null,
 annualleave   numeric(11)     not null);



create table part
(partnum         numeric(10)      not null,
 description     varchar(20)      not null,
 price           decimal(10,2)    not null);




create table customer
(name          varchar(10)     not null,
 address       varchar(10)     not null,
 state         varchar(6)      not null,
 zip           varchar(10)     not null,
 phone         varchar(10)     null,
 remarks       varchar(30)     not null);



create table orders
(orderedon     date,
 name          varchar(10)      not null,
 partnum       numeric(10)      not null,
 quantity      numeric(10)      not null,
 remarks       varchar(30)      not null);



create table bills
(name          varchar(25)      not null,
 amount        numeric(6)       not null,
 account_id    numeric(10)      not null);



create table salaries
(name          varchar(30)     not null,
 salary        numeric(9)      not null,
 age           numeric(10)     not null);



create table products
(pro            char(3)       not null,
 product_name   varchar(30)      not null,
 unit_cost      decimal(9,2)     not null);




create table states
(st          char(2)          not null);




create table ssn_table
(ssn             varchar(9)      not null);


create table job_tbl
(name          varchar(30)     not null,
 job           varchar(15)     not null,
 department    varchar(10)     not null);



/* Lesson 14 Tables  */

create table customers
(name        char(30),
 address     char(50),
 city        char(30),
 state       char(2),
 zip         int(5),
 customer_id int(2));

create table balances
(average_bal  decimal(8,2),
 curr_bal     decimal(8,2),
 account_id int(2));

/* Lesson 19 Tables  */
create table artists (
       name char(30),
       homebase char(40),
       style char(20),
       artist_id int);

create table media (
       media_type int,
       description char(30),
       price float);

create table recordings (
       artist_id int,
       media_type int,
       title char(50),
       year int);

/* Lesson 22 Tables */
CREATE TABLE EMP_TBL
(EMP_ID            CHAR(9)         NOT NULL,
LAST_NAME          VARCHAR(15)    NOT NULL,
FIRST_NAME         VARCHAR(15)    NOT NULL,
MIDDLE_NAME        VARCHAR(15)    NULL,
ST_ADD             VARCHAR(20)    NOT NULL,
CITY               VARCHAR(20)    NOT NULL,
ST                 CHAR(2)         NOT NULL,
ZIP                VARCHAR(5)     NOT NULL,
HOME_PH            VARCHAR(10)    NULL,
EMER_CONT          VARCHAR(30)    NULL,
EMER_PH            VARCHAR(10)    NULL,
CONSTRAINT EMP_PK PRIMARY KEY (EMP_ID));

CREATE TABLE EMP_PAY_TBL
(EMP_ID            CHAR(9)         NOT NULL,
DT_START           DATE            NOT NULL,
POSITION           VARCHAR(15)    NOT NULL,
SALARY             DECIMAL(8,2)     NULL,
HR_PAY             DECIMAL(4,2)     NULL,
COUNTY             VARCHAR(15)    NOT NULL,
DEPARTMENT         VARCHAR(15)    NOT NULL,
CONSTRAINT EMP_PAY_PK PRIMARY KEY (EMP_ID));

CREATE TABLE MEMBER_TBL
(MEM_NO            VARCHAR(30)    NOT NULL,
MEM_LAST_NAME      VARCHAR(20)    NOT NULL,
MEM_FIRST_NAME     VARCHAR(20)    NOT NULL,
MEM_MID_NAME       VARCHAR(20)    NULL,
MEM_ST_ADD         VARCHAR(20)    NOT NULL,
MEM_CITY           VARCHAR(15)    NOT NULL,
MEM_ST             CHAR(2)         NOT NULL,
MEM_ZIP            VARCHAR(5)     NOT NULL,
DT_LAST_ORDER      DATE,
PREFERENCE         VARCHAR(8)     NULL,
MUSIC_STY_1        VARCHAR(15)    NULL,
MUSIC_STY_2        VARCHAR(15)    NULL,
MUSIC_STY_3        VARCHAR(15)    NULL,
CONSTRAINT MEM_PK PRIMARY KEY (MEM_NO));

CREATE TABLE FORMER_MEM_TBL
(FMEM_NO           VARCHAR(30)    NOT NULL,
FMEM_LAST_NAME     VARCHAR(20)    NOT NULL,
FMEM_FIRST_NAME    VARCHAR(20)    NOT NULL,
FMEM_MID_NAME      VARCHAR(20)    NULL,
FMEM_ST_ADD        VARCHAR(20)    NOT NULL,
FMEM_CITY          VARCHAR(20)    NOT NULL,
FMEM_ST            CHAR(2)         NOT NULL,
FMEM_ZIP           VARCHAR(5)     NOT NULL,
FDT_LAST_ORDER     DATE,
FPREFERENCE        VARCHAR(8)     NULL,
FMUSIC_STY_1       VARCHAR(15)    NULL,
FMUSIC_STY_2       VARCHAR(15)    NULL,
FMUSIC_STY_3       VARCHAR(15)    NULL,
REASON_TERM        VARCHAR(50)    NULL,
CONSTRAINT FMEM_PK PRIMARY KEY (FMEM_NO));

CREATE TABLE MEM_PROFILE_TBL
(MEM_NO            VARCHAR(30)    NOT NULL,
REGION             VARCHAR(10)    NOT NULL,
SEX                VARCHAR(6)     NULL,
DOB                DATE,
INCOME             DECIMAL(8,2)     NULL,
CONSTRAINT MEM_PRO_PK PRIMARY KEY (MEM_NO));

CREATE TABLE PROD_PROFILE_TBL
(PROD_NO           VARCHAR(15)    NOT NULL,
VEND_NO            VARCHAR(20)    NOT NULL,
ARTIST             VARCHAR(30)    NOT NULL,
TITLE              VARCHAR(30)    NOT NULL,
COST               DECIMAL(4,2)     NOT NULL,
STYLE              VARCHAR(15)    NULL,
FORMAT             VARCHAR(8),
CONSTRAINT PROD_PRO_PK PRIMARY KEY (PROD_NO));

CREATE TABLE INVENTORY_TBL
(PROD_NO           VARCHAR(15)    NOT NULL,
FORMAT             VARCHAR(8)     NOT NULL,
QTN                DECIMAL(20)      NOT NULL,
ON_ORDER           DECIMAL(20)      NULL,
DATE_ORDERED       DATE,
DELIVERY_DATE      DATE,
CONSTRAINT INV_PK PRIMARY KEY (PROD_NO));

CREATE TABLE RETURNS_TBL
(PROD_NO           VARCHAR(15)    NOT NULL,
MEM_NO             VARCHAR(30)    NOT NULL,
QTN                DECIMAL(20)      NOT NULL,
STYLE              VARCHAR(15)    NOT NULL,
FORMAT             VARCHAR(8)     NOT NULL,
DISTRIBUTOR        VARCHAR(20)    NULL,
REASON_RT          VARCHAR(50)    NULL,
CONSTRAINT RTNS_PK PRIMARY KEY (PROD_NO, MEM_NO));

CREATE TABLE VENDOR_TBL
(VEND_NO           VARCHAR(15)    NOT NULL,
VEND_NAME          VARCHAR(25)    NOT NULL,
VEND_ST_ADD        VARCHAR(20)    NOT NULL,
VEND_CITY          VARCHAR(15)    NOT NULL,
VEND_ST            CHAR(2)         NOT NULL,
VEND_ZIP           VARCHAR(5)     NOT NULL,
VEND_PH            VARCHAR(10)    NOT NULL,
VEND_FAX_PH        VARCHAR(10)    NULL,
CONTACT            VARCHAR(25)    NULL,
TYPE_PROD          VARCHAR(20)    NOT NULL,
CONSTRAINT VEND_PK PRIMARY KEY (VEND_NO));

CREATE TABLE MEM_ORDER_TBL
(MEM_NO            VARCHAR(30)    NOT NULL,
PROD_NO            VARCHAR(25)    NOT NULL,
DATE_ORD           DATE,
STYLE              VARCHAR(15)    NOT NULL,
FORMAT             VARCHAR(8)     NOT NULL,
QTN                DECIMAL(20)      NOT NULL,
SHIP_COST          DECIMAL(5,2)     NOT NULL,
CONSTRAINT MEM_ORD_PK PRIMARY KEY (MEM_NO, PROD_NO));
CREATE TABLE EMP_HIGH_PAY_CITY_TBL AS SELECT * FROM EMP_TBL;