-- This script creates two tables, the PRESEERVE and EMPLOYEE tables.
-- These are the only tables that will be referenced in
-- Chapters 1-5 in The FREE SQL Book.
 
-- Important: SQL Server users should remove the two COMMIT statements from this script.

DROP TABLE PRESERVE;
DROP TABLE EMPLOYEE;

CREATE TABLE PRESERVE
(PNO   INTEGER       	NOT NULL UNIQUE,
 PNAME VARCHAR (25) 	NOT NULL,
 STATE CHAR (2)      	NOT NULL,
 ACRES INTEGER	     	NOT NULL,
 FEE   DECIMAL (5,2) 	NOT NULL);

INSERT INTO PRESERVE VALUES (5,  'HASSAYAMPA RIVER', 		'AZ', 660,   3.00);
INSERT INTO PRESERVE VALUES (3,  'DANCING PRAIRIE',  		'MT', 680,   0.00);
INSERT INTO PRESERVE VALUES (7,  'MULESHOE RANCH',   		'AZ', 49120, 0.00);
INSERT INTO PRESERVE VALUES (40, 'SOUTH FORK MADISON', 	        'MT', 121,   0.00);
INSERT INTO PRESERVE VALUES (14, 'MCELWAIN-OLSEN', 	 	'MA', 66,    0.00);
INSERT INTO PRESERVE VALUES (13, 'TATKON', 		 	'MA', 40,    0.00);
INSERT INTO PRESERVE VALUES (9,  'DAVID H. SMITH',  	 	'MA', 830,   0.00);
INSERT INTO PRESERVE VALUES (11, 'MIACOMET MOORS', 	 	'MA', 4,     0.00);
INSERT INTO PRESERVE VALUES (12, 'MOUNT PLANTAIN', 	 	'MA', 730,   0.00);
INSERT INTO PRESERVE VALUES (1,  'COMERTOWN PRAIRIE', 	        'MT', 1130,  0.00);
INSERT INTO PRESERVE VALUES (2,  'PINE BUTTE SWAMP', 	 	'MT', 15000, 0.00);
INSERT INTO PRESERVE VALUES (80, 'RAMSEY CANYON',    	 	'AZ', 380,   3.00);
INSERT INTO PRESERVE VALUES (10, 'HOFT FARM', 		 	'MA', 90,    0.00);
INSERT INTO PRESERVE VALUES (6,  'PAPAGONIA-SONOITA CREEK',	'AZ', 1200,  3.00);

COMMIT;	-- SQL Server users should remove this statement

CREATE TABLE EMPLOYEE
(ENO	CHAR (4)       NOT NULL UNIQUE,
 ENAME  VARCHAR(25)    NOT NULL,
 SALARY DECIMAL(7,2)   NOT NULL,
 DNO    INTEGER        NOT NULL);

INSERT INTO EMPLOYEE VALUES ('1000', 'MOE',    2000.00, 20);
INSERT INTO EMPLOYEE VALUES ('2000', 'LARRY',  2000.00, 10);
INSERT INTO EMPLOYEE VALUES ('3000', 'CURLY',  3000.00, 20);
INSERT INTO EMPLOYEE VALUES ('4000', 'SHEMP',   500.00, 40);
INSERT INTO EMPLOYEE VALUES ('5000', 'JOE',     400.00, 10);
INSERT INTO EMPLOYEE VALUES ('6000', 'GEORGE', 9000.00, 20);

COMMIT;  -- SQL Server users should remove this statement