Create Table & Insert Statements for《Sams Teach Yourself SQL in 24 Hours, Fifth Edition》?examples,MySQL version. Hope this will save your typing
?
Create Table:
create TABLE EMPLOYEE_TBL (EMP_ID VARCHAR(9) NOT NULL,LAST_NAME VARCHAR(15) NOT NULL,FIRST_NAME VARCHAR(15) NOT NULL,MIDDLE_NAME VARCHAR(15),ADDRESS VARCHAR(30) NOT NULL,CITY VARCHAR(15) NOT NULL,STATE CHAR(2) NOT NULL,ZIP INTEGER(5) NOT NULL,PHONE CHAR(10),PAGER CHAR(10),CONSTRAINT EMP_PK PRIMARY KEY (EMP_ID));CREATE TABLE EMPLOYEE_PAY_TBL(EMP_ID VARCHAR(9) NOT NULL primary key,POSITION VARCHAR(15) NOT NULL,DATE_HIRE DATE,PAY_RATE DECIMAL(4,2),DATE_LAST_RAISE DATE,SALARY DECIMAL(8,2),BONUS DECIMAL(6,2),CONSTRAINT EMP_FK FOREIGN KEY (EMP_ID) REFERENCES EMPLOYEE_TBL (EMP_ID));CREATE TABLE CUSTOMER_TBL(CUST_ID VARCHAR(10) NOT NULL primary key,CUST_NAME VARCHAR(30) NOT NULL,CUST_ADDRESS VARCHAR(20) NOT NULL,CUST_CITY VARCHAR(15) NOT NULL,CUST_STATE CHAR(2) NOT NULL,CUST_ZIP INTEGER(5) NOT NULL,CUST_PHONE CHAR(10),CUST_FAX VARCHAR(10));CREATE TABLE ORDERS_TBL(ORD_NUM VARCHAR(10) NOT NULL primary key,CUST_ID VARCHAR(10) NOT NULL,PROD_ID VARCHAR(10) NOT NULL,QTY INTEGER(6) NOT NULL,ORD_DATE DATE);CREATE TABLE PRODUCTS_TBL(PROD_ID VARCHAR(10) NOT NULL primary key,PROD_DESC VARCHAR(40) NOT NULL,COST DECIMAL(6,2) NOT NULL);
?
Insert Statments:
INSERT INTO EMPLOYEE_TBL VALUES('311549902', 'STEPHENS', 'TINA', 'DAWN','RR 3 BOX 17A', 'GREENWOOD','IN', '47890', '3178784465',NULL);INSERT INTO EMPLOYEE_TBL VALUES('442346889', 'PLEW', 'LINDA', 'CAROL', '3301 BEACON', 'INDIANAPOLIS','IN', '46224', '3172978990', NULL);INSERT INTO EMPLOYEE_TBL VALUES('213764555', 'GLASS', 'BRANDON', 'SCOTT', '1710 MAIN ST', 'WHITELAND','IN', '47885', '3178984321', '3175709980');INSERT INTO EMPLOYEE_TBL VALUES('313782439', 'GLASS', 'JACOB', NULL, '3789 WHITE RIVER BLVD','INDIANAPOLIS', 'IN', '45734', '3175457676','8887345678');INSERT INTO EMPLOYEE_TBL VALUES('220984332', 'WALLACE', 'MARIAH', NULL, '7889 KEYSTONE AVE','INDIANAPOLIS', 'IN', '46741', '3173325986', NULL);INSERT INTO EMPLOYEE_TBL VALUES('443679012', 'SPURGEON', 'TIFFANY', NULL, '5 GEORGE COURT','INDIANAPOLIS', 'IN', '46234', '3175679007', NULL);INSERT INTO EMPLOYEE_PAY_TBL VALUES('311549902', 'MARKETING', '1999-05-23',NULL,'2009-05-01','40000', NULL);INSERT INTO EMPLOYEE_PAY_TBL VALUES('442346889', 'TEAM LEADER', '2000-06-17', '14.75', '2009-06-01', NULL,NULL);INSERT INTO EMPLOYEE_PAY_TBL VALUES('213764555', 'SALES MANAGER', '2004-08-14',NULL, '2009-08-01', '30000','2000');INSERT INTO EMPLOYEE_PAY_TBL VALUES('313782439', 'SALESMAN', '2007-06-28',NULL, NULL, '20000', '1000');INSERT INTO EMPLOYEE_PAY_TBL VALUES('220984332', 'SHIPPER', '2006-07-22', '11.00', '1999-07-01', NULL, NULL);INSERT INTO EMPLOYEE_PAY_TBL VALUES('443679012', 'SHIPPER', '2001-01-14', '15.00', '1999-01-01', NULL, NULL);INSERT INTO CUSTOMER_TBL VALUES('232', 'LESLIE GLEASON', '798 HARDAWAY DR', 'INDIANAPOLIS','IN', '47856', '3175457690', NULL);INSERT INTO CUSTOMER_TBL VALUES('109', 'NANCY BUNKER', 'APT A 4556 WATERWAY', 'BROAD RIPPLE','IN', '47950', '3174262323', NULL);INSERT INTO CUSTOMER_TBL VALUES('345', 'ANGELA DOBKO', 'RR3 BOX 76', 'LEBANON', 'IN', '49967','7658970090', NULL);INSERT INTO CUSTOMER_TBL VALUES('090', 'WENDY WOLF', '3345 GATEWAY DR', 'INDIANAPOLIS', 'IN','46224', '3172913421', NULL);INSERT INTO CUSTOMER_TBL VALUES('12', 'MARYS GIFT SHOP', '435 MAIN ST', 'DANVILLE', 'IL', '47978','3178567221', '3178523434');INSERT INTO CUSTOMER_TBL VALUES('432', 'SCOTTYS MARKET', 'RR2 BOX 173', 'BROWNSBURG', 'IN','45687', '3178529835', '3178529836');INSERT INTO CUSTOMER_TBL VALUES('333', 'JASONS AND DALLAS GOODIES', 'LAFAYETTE SQ MALL','INDIANAPOLIS', 'IN', '46222', '3172978886', '3172978887');INSERT INTO CUSTOMER_TBL VALUES('21', 'MORGANS CANDIES AND TREATS', '5657 W TENTH ST','INDIANAPOLIS', 'IN', '46234', '3172714398', NULL);INSERT INTO CUSTOMER_TBL VALUES('43', 'SCHYLERS NOVELTIES', '17 MAPLE ST', 'LEBANON', 'IN','48990', '3174346758', NULL);INSERT INTO CUSTOMER_TBL VALUES('287', 'GAVINS PLACE', '9880 ROCKVILLE RD', 'INDIANAPOLIS','IN', '46244', '3172719991', '3172719992');INSERT INTO CUSTOMER_TBL VALUES('288', 'HOLLYS GAMEARAMA', '567 US 31 SOUTH', 'WHITELAND','IN', '49980', '3178879023', NULL);INSERT INTO CUSTOMER_TBL VALUES('590', 'HEATHERS FEATHERS AND THINGS', '4090 N SHADELAND AVE','INDIANAPOLIS', 'IN', '43278', '3175456768', NULL);INSERT INTO CUSTOMER_TBL VALUES('610', 'REGANS HOBBIES INC', '451 GREEN ST', 'PLAINFIELD', 'IN','46818', '3178393441', '3178399090');INSERT INTO CUSTOMER_TBL VALUES('560', 'ANDYS CANDIES', 'RR 1 BOX 34', 'NASHVILLE', 'IN','48756', '8123239871', NULL);INSERT INTO CUSTOMER_TBL VALUES('221', 'RYANS STUFF', '2337 S SHELBY ST', 'INDIANAPOLIS', 'IN','47834', '3175634402', NULL);INSERT INTO ORDERS_TBL VALUES('56A901', '232', '11235', '1', '2009-10-22');INSERT INTO ORDERS_TBL VALUES('56A917', '12', '907', '100', '2009-09-30');INSERT INTO ORDERS_TBL VALUES('32A132', '43', '222', '25', '2009-10-10');INSERT INTO ORDERS_TBL VALUES('16C17', '090', '222', '2', '2009-10-17');INSERT INTO ORDERS_TBL VALUES('18D778', '287', '90', '10', '2009-10-17');INSERT INTO ORDERS_TBL VALUES('23E934', '432', '13', '20', '2009-10-15');INSERT INTO PRODUCTS_TBL VALUES('11235', 'WITCH COSTUME', '29.99');INSERT INTO PRODUCTS_TBL VALUES('222', 'PLASTIC PUMPKIN 18 INCH', '7.75');INSERT INTO PRODUCTS_TBL VALUES('13', 'FALSE PARAFFIN TEETH', '1.10');INSERT INTO PRODUCTS_TBL VALUES('90', 'LIGHTED LANTERNS', '14.50');INSERT INTO PRODUCTS_TBL VALUES('15', 'ASSORTED COSTUMES', '10.00');INSERT INTO PRODUCTS_TBL VALUES('9', 'CANDY CORN', '1.35');INSERT INTO PRODUCTS_TBL VALUES('6', 'PUMPKIN CANDY', '1.45');INSERT INTO PRODUCTS_TBL VALUES('87', 'PLASTIC SPIDERS', '1.05');INSERT INTO PRODUCTS_TBL VALUES('119', 'ASSORTED MASKS', '4.95');
?