DROP TABLE CUSTOMER;
DROP SEQUENCE CUSTID;

CREATE SEQUENCE CUSTID
INCREMENT BY 1
START WITH 109;

CREATE TABLE CUSTOMER (
 CUSTID              NUMBER (6) NOT NULL,
 USERNAME            CHAR (45),
 PASSWORD            CHAR (45),
 HINT                CHAR (45),
 SALUTATION          CHAR (10),
 FIRSTNAME           CHAR (45),
 MIDDLENAME          CHAR (45),
 LASTNAME            CHAR (45),
 COMPANY             CHAR (45),
 TITLE               CHAR (45),
 ADDRESS1            CHAR (40),
 ADDRESS2            CHAR (40),
 ADDRESS3            CHAR (40),
 SUBURB              CHAR (30),
 STATE               CHAR (3),
 POSTCODE            CHAR (4),
 COUNTRY             CHAR (20),
 EMAIL               CHAR (50),
 URL                 CHAR (50),
 WORKPHONE           CHAR (15),
 HOMEPHONE           CHAR (15),
 MOBILEPHONE         CHAR (15),
 FAX                 CHAR (15),
 COMMENTS            VARCHAR (2000),
 CONSTRAINT CUSTOMER_PRIMARY_KEY PRIMARY KEY (CUSTID),
 CONSTRAINT CUSTID_ZERO CHECK (CUSTID > 0));

 CREATE UNIQUE INDEX IDX1 ON CUSTOMER(USERNAME, PASSWORD);

INSERT INTO CUSTOMER (CUSTID, USERNAME, PASSWORD, HINT, SALUTATION, FIRSTNAME, MIDDLENAME, LASTNAME,
  COMPANY, ADDRESS1, ADDRESS2, ADDRESS3, SUBURB, STATE, POSTCODE, COUNTRY, EMAIL, URL,
  WORKPHONE, HOMEPHONE, MOBILEPHONE, FAX, COMMENTS)
VALUES (CUSTID.NEXTVAL, 'test1', 'test1', 'quality', 'MR', 'John', '', 'Smith',
  'Emerging Software', '18 Rusden St', '', '', 'Brighton', 'Vic', '3186', 'Australia', 'john@esoft.com.au', 'www.esoft.com.au',
  '9280 5088', '9528 1491', '0419 565-408', '', 'Nice guy');

INSERT INTO CUSTOMER (CUSTID, USERNAME, PASSWORD, HINT, SALUTATION, FIRSTNAME, MIDDLENAME, LASTNAME,
  COMPANY, ADDRESS1, ADDRESS2, ADDRESS3, SUBURB, STATE, POSTCODE, COUNTRY, EMAIL, URL,
  WORKPHONE, HOMEPHONE, MOBILEPHONE, FAX, COMMENTS)
VALUES (CUSTID.NEXTVAL, 'test2', 'test2', 'quality', 'MS', 'Amanda', '', 'Smith',
  'Emerging Software', '18 Rusden St', '', '', 'Brighton', 'Vic', '3186', 'Australia', 'amanda@esoft.com.au', 'www.esoft.com.au',
  '9280 5088', '9528 1491', '0419 565-408', '', 'Nice gal');

commit;
-- exit