create table checks (check# number(6) not null, payee varchar(20) not null, amount number(6,2) not null, remarks varchar(20) not null); create table deposits (deposit# number(8) not null, whopaid varchar(25) not null, amount number(6,2) not null, remarks varchar(20) not null); create table bikes (name varchar(15) not null, framesize number(10,2) not null, composition varchar(12) not null, milesridden number(10) not null, type varchar(8) not null); create table price (item varchar(15) not null, wholesale number(4,2) not null); create table hilow (state varchar(10) not null, lows number(8) not null, highs number(8) not null, difference number(10) null); create table remains (numerator number(10) not null, denominator number(12) not null); create table precedence (n1 number(10) not null, n2 number(10) not null, n3 number(10) not null, n4 number(10) not null); create table friends (lastname varchar(15) not null, firstname varchar(15) not null, areacode number(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 number(10) not null); create table vacation (lastname varchar(15) not null, employeenum number(11) not null, years number(8) not null, leavetaken number(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 number(3) not null, hits number(4) not null, walks varchar(5) not null, singles varchar(7) not null, doubles varchar(7) not null, triples varchar(7) not null, hr number(2) not null, so varchar(2) not null); create table project (task varchar(14) not null, startdate date, enddate date); create table numbers (a number(10,4) not null, b number(10,4) not null); create table characters (lastname varchar(15) not null, firstname varchar(15) not null, m char(1) null, code number(10) not null); create table convert (name varchar(15) not null, testnum number(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 number(10,2) not null, sickleave number(10) not null, annualleave number(11) not null); create table part (partnum number(10) not null, description varchar(20) not null, price number(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 number(10) not null, quantity number(10) not null, remarks varchar(30) not null); create table bills (name varchar(25) not null, amount number(6) not null, account_id number(10) not null); create table salaries (name varchar(30) not null, salary number(9) not null, age number(10) not null); create table products (pro char(3) not null, product_name varchar(30) not null, unit_cost number(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 13 Tables */ CREATE TABLE COMPANY (NAME CHAR(30) NOT NULL, ADDRESS CHAR(50), CITY CHAR(30), STATE CHAR(2)); CREATE TABLE BANK_ACCOUNTS (ACCOUNT_ID NUMBER NOT NULL, TYPE CHAR(30), BALANCE NUMBER, BANK CHAR(30)); CREATE TABLE BILLS (NAME CHAR(30) NOT NULL, AMOUNT NUMBER, ACCOUNT_ID NUMBER NOT NULL); /* Lesson 14 Tables */ CREATE TABLE CUSTOMERS (NAME VARCHAR(30), ADDRESS VARCHAR(50), CITY VARCHAR(30), STATE CHAR(2), ZIP NUMERIC(5), CUSTOMER_ID NUMERIC(2)); CREATE TABLE BALANCES (AVERAGE_BAL DECIMAL(8,2), CURR_BAL DECIMAL(8,2), ACCOUNT_ID NUMERIC(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;