DROP TABLE authors;

CREATE TABLE authors

  (

  au_id    CHAR(3)     NOT NULL,

  au_fname VARCHAR(15) NOT NULL,

  au_lname VARCHAR(15) NOT NULL,

  phone    VARCHAR(12)         ,

  address  VARCHAR(20)         ,

  city     VARCHAR(15)         ,

  state    CHAR(2)             ,

  zip      CHAR(5)             ,

  CONSTRAINT pk_authors PRIMARY KEY (au_id)

  );

INSERT INTO authors VALUES('A01','Sarah','Buchman','718-496-7223','75 West 205 St','Bronx','NY','10468');

INSERT INTO authors VALUES('A02','Wendy','Heydemark','303-986-7020','2922 Baseline Rd','Boulder','CO','80303');

INSERT INTO authors VALUES('A03','Hallie','Hull','415-549-4278','3800 Waldo Ave, #14F','San Francisco','CA','94123');

INSERT INTO authors VALUES('A04','Klee','Hull','415-549-4278','3800 Waldo Ave, #14F','San Francisco','CA','94123');

INSERT INTO authors VALUES('A05','Christian','Kells','212-771-4680','114 Horatio St','New York','NY','10014');

INSERT INTO authors VALUES('A06',' ','Kellsey','650-836-7128','390 Serra Mall','Palo Alto','CA','94305');

INSERT INTO authors VALUES('A07','Paddy','O''Furniture','941-925-0752','1442 Main St','Sarasota','FL','34236');

DROP TABLE publishers;

CREATE TABLE publishers

  (

  pub_id   CHAR(3)     NOT NULL,

  pub_name VARCHAR(20) NOT NULL,

  city     VARCHAR(15) NOT NULL,

  state    CHAR(2)             ,

  country  VARCHAR(15) NOT NULL,

  CONSTRAINT pk_publishers PRIMARY KEY (pub_id)

  );

INSERT INTO publishers VALUES('P01','Abatis Publishers','New York','NY','USA');

INSERT INTO publishers VALUES('P02','Core Dump Books','San Francisco','CA','USA');

INSERT INTO publishers VALUES('P03','Schadenfreude Press','Hamburg',NULL,'Germany');

INSERT INTO publishers VALUES('P04','Tenterhooks Press','Berkeley','CA','USA');

DROP TABLE titles;

CREATE TABLE titles

  (

  title_id   CHAR(3)      NOT NULL,

  title_name VARCHAR(40)  NOT NULL,

  type       VARCHAR(10)          ,

  pub_id     CHAR(3)      NOT NULL,

  pages      INTEGER              ,

  price      DECIMAL(5,2)         ,

  sales      INTEGER              ,

  pubdate    DATE                 ,

  contract   SMALLINT     NOT NULL,

  CONSTRAINT pk_titles PRIMARY KEY (title_id)

  );

INSERT INTO titles VALUES('T01','1977!','history','P01',107,21.99,566,DATE '2000-08-01',1);

INSERT INTO titles VALUES('T02','200 Years of German Humor','history','P03',14,19.95,9566,DATE '1998-04-01',1);

INSERT INTO titles VALUES('T03','Ask Your System Administrator','computer','P02',1226,39.95,25667,DATE '2000-09-01',1);

INSERT INTO titles VALUES('T04','But I Did It Unconsciously','psychology','P04',510,12.99,13001,DATE '1999-05-31',1);

INSERT INTO titles VALUES('T05','Exchange of Platitudes','psychology','P04',201,6.95,201440,DATE '2001-01-01',1);

INSERT INTO titles VALUES('T06','How About Never?','biography','P01',473,19.95,11320,DATE '2000-07-31',1);

INSERT INTO titles VALUES('T07','I Blame My Mother','biography','P03',333,23.95,1500200,DATE '1999-10-01',1);

INSERT INTO titles VALUES('T08','Just Wait Until After School','children','P04',86,10.00,4095,DATE '2001-06-01',1);

INSERT INTO titles VALUES('T09','Kiss My Boo-Boo','children','P04',22,13.95,5000,DATE '2002-05-31',1);

INSERT INTO titles VALUES('T10','Not Without My Faberge Egg','biography','P01',NULL,NULL,NULL,NULL,0);

INSERT INTO titles VALUES('T11','Perhaps It''s a Glandular Problem','psychology','P04',826,7.99,94123,DATE '2000-11-30',1);

INSERT INTO titles VALUES('T12','Spontaneous, Not Annoying','biography','P01',507,12.99,100001,DATE '2000-08-31',1);

INSERT INTO titles VALUES('T13','What Are The Civilian Applications?','history','P03',802,29.99,10467,DATE '1999-05-31',1);

DROP TABLE title_authors;

CREATE TABLE title_authors

  (

  title_id      CHAR(3)      NOT NULL,

  au_id         CHAR(3)      NOT NULL,

  au_order      SMALLINT     NOT NULL,

  royalty_share DECIMAL(5,2) NOT NULL,

  CONSTRAINT pk_title_authors PRIMARY KEY (title_id, au_id)

  );

INSERT INTO title_authors VALUES('T01','A01',1,1.0);

INSERT INTO title_authors VALUES('T02','A01',1,1.0);

INSERT INTO title_authors VALUES('T03','A05',1,1.0);

INSERT INTO title_authors VALUES('T04','A03',1,0.6);

INSERT INTO title_authors VALUES('T04','A04',2,0.4);

INSERT INTO title_authors VALUES('T05','A04',1,1.0);

INSERT INTO title_authors VALUES('T06','A02',1,1.0);

INSERT INTO title_authors VALUES('T07','A02',1,0.5);

INSERT INTO title_authors VALUES('T07','A04',2,0.5);

INSERT INTO title_authors VALUES('T08','A06',1,1.0);

INSERT INTO title_authors VALUES('T09','A06',1,1.0);

INSERT INTO title_authors VALUES('T10','A02',1,1.0);

INSERT INTO title_authors VALUES('T11','A03',2,0.3);

INSERT INTO title_authors VALUES('T11','A04',3,0.3);

INSERT INTO title_authors VALUES('T11','A06',1,0.4);

INSERT INTO title_authors VALUES('T12','A02',1,1.0);

INSERT INTO title_authors VALUES('T13','A01',1,1.0);

DROP TABLE royalties;

CREATE TABLE royalties

  (

  title_id     CHAR(3)      NOT NULL,

  advance      DECIMAL(9,2)         ,

  royalty_rate DECIMAL(5,2)         ,

  CONSTRAINT pk_royalties PRIMARY KEY (title_id)

  );

INSERT INTO royalties VALUES('T01',10000,0.05);

INSERT INTO royalties VALUES('T02',1000,0.06);

INSERT INTO royalties VALUES('T03',15000,0.07);

INSERT INTO royalties VALUES('T04',20000,0.08);

INSERT INTO royalties VALUES('T05',100000,0.09);

INSERT INTO royalties VALUES('T06',20000,0.08);

INSERT INTO royalties VALUES('T07',1000000,0.11);

INSERT INTO royalties VALUES('T08',0,0.04);

INSERT INTO royalties VALUES('T09',0,0.05);

INSERT INTO royalties VALUES('T10',NULL,NULL);

INSERT INTO royalties VALUES('T11',100000,0.07);

INSERT INTO royalties VALUES('T12',50000,0.09);

INSERT INTO royalties VALUES('T13',20000,0.06);

DROP TABLE au_orders;

CREATE TABLE au_orders (

  title_id CHAR(3),

  author1  CHAR(3),

  author2  CHAR(3),

  author3  CHAR(3)

  );

INSERT INTO au_orders VALUES('T01','A01',NULL,NULL);

INSERT INTO au_orders VALUES('T02','A01',NULL,NULL);

INSERT INTO au_orders VALUES('T03','A05',NULL,NULL);

INSERT INTO au_orders VALUES('T04','A03','A04',NULL);

INSERT INTO au_orders VALUES('T05','A04',NULL,NULL);

INSERT INTO au_orders VALUES('T06','A02',NULL,NULL);

INSERT INTO au_orders VALUES('T07','A02','A04',NULL);

INSERT INTO au_orders VALUES('T08','A06',NULL,NULL);

INSERT INTO au_orders VALUES('T09','A06',NULL,NULL);

INSERT INTO au_orders VALUES('T10','A02',NULL,NULL);

INSERT INTO au_orders VALUES('T11','A06','A03','A04');

INSERT INTO au_orders VALUES('T12','A02',NULL,NULL);

INSERT INTO au_orders VALUES('T13','A01',NULL,NULL);

DROP TABLE dups;

CREATE TABLE dups (

  id         INTEGER,

  title_name VARCHAR(40),

  type       VARCHAR(10),

  price      DECIMAL(5,2)

  );

INSERT INTO dups VALUES(1,'Book Title 5','children',15.00);

INSERT INTO dups VALUES(2,'Book Title 3','biography',7.00);

INSERT INTO dups VALUES(3,'Book Title 1','history', 10.00);

INSERT INTO dups VALUES(4,'Book Title 2','children',20.00);

INSERT INTO dups VALUES(5,'Book Title 4','history', 15.00);

INSERT INTO dups VALUES(6,'Book Title 1','history', 10.00);

INSERT INTO dups VALUES(7,'Book Title 3','biography',7.00);

INSERT INTO dups VALUES(8,'Book Title 1','history', 10.00);

DROP TABLE empsales;

CREATE TABLE empsales (

  emp_id CHAR(3) NOT NULL PRIMARY KEY,

  sales  INTEGER NOT NULL

  );

INSERT INTO empsales VALUES('E01',600);

INSERT INTO empsales VALUES('E02',800);

INSERT INTO empsales VALUES('E03',500);

INSERT INTO empsales VALUES('E04',500);

INSERT INTO empsales VALUES('E05',700);

INSERT INTO empsales VALUES('E06',500);

INSERT INTO empsales VALUES('E07',300);

INSERT INTO empsales VALUES('E08',400);

INSERT INTO empsales VALUES('E09',900);

INSERT INTO empsales VALUES('E10',700);

DROP TABLE roadtrip;

CREATE TABLE roadtrip (

  seq   INTEGER     NOT NULL PRIMARY KEY,

  city  VARCHAR(17) NOT NULL,

  miles REAL        NOT NULL

  );

INSERT INTO roadtrip VALUES(1,'Seattle, WA',0);

INSERT INTO roadtrip VALUES(2,'Portland, OR',174);

INSERT INTO roadtrip VALUES(3,'San Francisco, CA',808);

INSERT INTO roadtrip VALUES(4,'Monterey, CA',926);

INSERT INTO roadtrip VALUES(5,'Los Angeles, CA',1251);

INSERT INTO roadtrip VALUES(6,'San Diego, CA',1372);

DROP TABLE telephones;

CREATE TABLE telephones (

  au_id    CHAR(3)  NOT NULL,

  tel_type CHAR(1)  NOT NULL,

  tel_no   CHAR(12) NOT NULL,

  PRIMARY KEY (au_id, tel_type)

  );

INSERT INTO telephones VALUES('A01','H','111-111-1111');

INSERT INTO telephones VALUES('A01','W','222-222-2222');

INSERT INTO telephones VALUES('A02','W','333-333-3333');

INSERT INTO telephones VALUES('A04','H','444-444-4444');

INSERT INTO telephones VALUES('A04','W','555-555-5555');

INSERT INTO telephones VALUES('A05','H','666-666-6666');

DROP TABLE temps;

CREATE TABLE temps (

  id      INTEGER NOT NULL PRIMARY KEY,

  hi_temp INTEGER NOT NULL

  );

INSERT INTO temps VALUES( 1,49);

INSERT INTO temps VALUES( 2,46);

INSERT INTO temps VALUES( 3,48);

INSERT INTO temps VALUES( 4,50);

INSERT INTO temps VALUES( 5,50);

INSERT INTO temps VALUES( 6,50);

INSERT INTO temps VALUES( 7,51);

INSERT INTO temps VALUES( 8,52);

INSERT INTO temps VALUES( 9,53);

INSERT INTO temps VALUES(10,50);

INSERT INTO temps VALUES(11,50);

INSERT INTO temps VALUES(12,47);

INSERT INTO temps VALUES(13,50);

INSERT INTO temps VALUES(14,51);

INSERT INTO temps VALUES(15,52);

DROP TABLE time_series;

CREATE TABLE time_series (

  seq   INTEGER NOT NULL PRIMARY KEY,

  price REAL    NOT NULL

  );

INSERT INTO time_series VALUES( 1,10.0);

INSERT INTO time_series VALUES( 2,10.5);

INSERT INTO time_series VALUES( 3,11.0);

INSERT INTO time_series VALUES( 4,11.0);

INSERT INTO time_series VALUES( 5,10.5);

INSERT INTO time_series VALUES( 6,11.5);

INSERT INTO time_series VALUES( 7,12.0);

INSERT INTO time_series VALUES( 8,13.0);

INSERT INTO time_series VALUES( 9,15.0);

INSERT INTO time_series VALUES(10,13.5);

INSERT INTO time_series VALUES(11,13.0);

INSERT INTO time_series VALUES(12,12.5);

INSERT INTO time_series VALUES(13,12.0);

INSERT INTO time_series VALUES(14,12.5);

INSERT INTO time_series VALUES(15,11.0);

DROP TABLE employees;

CREATE TABLE employees (

  emp_id   CHAR(3)     NOT NULL PRIMARY KEY,

  emp_name VARCHAR(20) NOT NULL,

  boss_id  CHAR(3)

  );

INSERT INTO employees VALUES('E01','Lord Copper',NULL);

INSERT INTO employees VALUES('E02','Jocelyn Hitchcock','E01');

INSERT INTO employees VALUES('E03','Mr. Salter','E01');

INSERT INTO employees VALUES('E04','William Boot','E03');

INSERT INTO employees VALUES('E05','Mr. Corker','E03');

DROP TABLE hier;

CREATE TABLE hier (

  emp_id    CHAR(3)     NOT NULL PRIMARY KEY,

  emp_title VARCHAR(20) NOT NULL,

  boss_id   CHAR(3)

  );

INSERT INTO hier VALUES('E01','CEO',NULL);

INSERT INTO hier VALUES('E02','VP1','E01');

INSERT INTO hier VALUES('E03','VP2','E01');

INSERT INTO hier VALUES('E04','DIR1','E02');

INSERT INTO hier VALUES('E05','DIR2','E02');

INSERT INTO hier VALUES('E06','DIR3','E03');

INSERT INTO hier VALUES('E07','WS1','E04');

INSERT INTO hier VALUES('E08','WS2','E04');

INSERT INTO hier VALUES('E09','WS3','E04');

INSERT INTO hier VALUES('E10','WS4','E06');

INSERT INTO hier VALUES('E11','WS5','E06');