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;