CREATE TABLE inventory(StockNumber INTEGER PRIMARY KEY,Descrip VARCHAR(50),OnHandQuan INTEGER,PackQty INTEGER,PackCost FLOAT);
INSERT INTO inventory(StockNumber,Descrip,OnHandQuan,PackQty,PackCost)VALUES (51002,'AA Dry Cells 4 Pack',173,12,9.00);
INSERT INTO inventory(StockNumber,Descrip,OnHandQuan,PackQty,PackCost)VALUES (51004,'AA Dry Cells 8 Pack',5,12,16.80);
INSERT INTO inventory(StockNumber,Descrip,OnHandQuan,PackQty,PackCost)VALUES (43512,'10W-30 Motor Oil, Quart',36,12,18.20);
INSERT INTO inventory(StockNumber,Descrip,OnHandQuan,PackQty,PackCost)VALUES (51013,'D Dry Cells 8 Pack',19,12,90.20);
INSERT INTO inventory(StockNumber,Descrip,OnHandQuan,PackQty,PackCost)VALUES (23155,'Shovel Pointed Long Handle',1500,1,9.82);
INSERT INTO inventory(StockNumber,Descrip,OnHandQuan,PackQty,PackCost)VALUES (51001,'AAA Dry Cells 4 Pack ',92,12,9.00);
INSERT INTO inventory(StockNumber,Descrip,OnHandQuan,PackQty,PackCost)VALUES (43111,'White Gas Gallon Can',14,4,14.75);
CREATE TABLE datatype(linenum INTEGER PRIMARY KEY,testdata INTEGER);
INSERT INTO datatype(linenum,testdata) VALUES(1,-34);
INSERT INTO datatype(linenum,testdata) VALUES(2,'This a text field');
INSERT INTO datatype(linenum,testdata) VALUES(3,3.1415);
INSERT INTO datatype(linenum,testdata) VALUES(4,NULL);
/*Demonstrating auto increment by placing null in primary key field.*/
INSERT INTO datatype(linenum,testdata) VALUES(NULL,'Placing NULL in Primary Key ');
CREATE TABLE colorlist (color TEXT);
INSERT INTO colorlist VALUES('red');
INSERT INTO colorlist VALUES('yellow');
INSERT INTO colorlist VALUES('blue');
CREATE TABLE employee(EmpIDparent INTEGER PRIMARY KEY,FirstName TEXT,LastName TEXT,StartDate CHAR(10 ),EndDate CHAR(10),PayGrade CHAR(2) , PayRate Real);
INSERT INTO employee (EmpIDparent,FirstName,LastName,StartDate,EndDate,PayGrade,PayRate) VALUES(153,'Melvin','Roberts','2009-09-19',NULL,'L3',18.19);
INSERT INTO employee (EmpIDparent,FirstName,LastName,StartDate,EndDate,PayGrade,PayRate) VALUES(154,'Alan','Jones','2009-09-11',NULL,'L3',17.79);
CREATE TABLE timecard(PayPeriod INTEGER,Hours REAL,EmpIDchild INTEGER,PRIMARY KEY (PayPeriod,EmpIDchild), FOREIGN KEY(EmpIDchild) REFERENCES employee(EmpIDparent));
INSERT INTO timecard(PayPeriod,EmpIDchild,Hours) VALUES(1,153,38.5);
INSERT INTO timecard(PayPeriod,EmpIDchild,Hours) VALUES(1,154,41.25);
CREATE TABLE product(brand TEXT,descrip TEXT, onhand_quan INTEGER, on_order INTEGER);
INSERT INTO "product" VALUES('Shady Oak','Milk',12,0);
INSERT INTO "product" VALUES('Cloverleaf','2 % Milk - Quart',31,12);
INSERT INTO "product" VALUES('Cloverleaf','1 % Milk - Quart',13,12);
INSERT INTO "product" VALUES('Cloverleaf','Skim Milk - Quart',42,0);
INSERT INTO "product" VALUES('Shady Oak','2 % Milk - Gallon',6,0);
INSERT INTO "product" VALUES('Acme','Soy Milk - Quart',0,48);
INSERT INTO "product" VALUES('Shady Oak','Whole Milk - Vitamin D',2,20);
CREATE TABLE booklist(author TEXT,title TEXT);
INSERT INTO booklist(author,title)VALUES('John Doe','Metalurgy and Casting');
INSERT INTO booklist(author,title)VALUES ('John C. Doe','Probability and Error Estimation');
INSERT INTO booklist(author,title)VALUES ('John Charles Doe','Statistics - Tools for Decision Making');
INSERT INTO booklist(author,title)VALUES ('Joanna Carla Doe','Business Statistics and Applications');
INSERT INTO booklist(author,title)VALUES ('Mike J. Doe','Methods of Error Estimation');
INSERT INTO booklist(author,title)VALUES ('John Albert Doe','Methods of Analytic Chemistry');
CREATE TABLE inventory(StockNumber INTEGER PRIMARY KEY,Descrip VARCHAR(50),OnHandQuan INTEGER,PackQty INTEGER,PackCost FLOAT);
INSERT INTO inventory(StockNumber,Descrip,OnHandQuan,PackQty,PackCost) VALUES (51002,'AA Dry Cells 4 Pack ',173,12,9.00);
INSERT INTO inventory(StockNumber,Descrip,OnHandQuan,PackQty,PackCost) VALUES (51004,'AA Dry Cells 8 Pack',5,12,16.80);
INSERT INTO inventory(StockNumber,Descrip,OnHandQuan,PackQty,PackCost) VALUES (43512,'10W-30 Motor Oil, Quart',36,12,18.20);
INSERT INTO inventory(StockNumber,Descrip,OnHandQuan,PackQty,PackCost) VALUES (51013,'D Dry Cells 8 Pack',19,12,90.20);
INSERT INTO inventory(StockNumber,Descrip,OnHandQuan,PackQty,PackCost) VALUES (23155,'Shovel Pointed Long Handle',1500,1,9.82);
INSERT INTO inventory(StockNumber,Descrip,OnHandQuan,PackQty,PackCost) VALUES (51001,'AAA Dry Cells 4 Pack ',92,12,9.00);
INSERT INTO inventory(StockNumber,Descrip,OnHandQuan,PackQty,PackCost) VALUES (43111,'White Gas Gallon Can',14,4,14.75);
CREATE TABLE requisition(ReqNumber INTEGER PRIMARY KEY,Requestor VARCHAR(30) NOT NULL,Auth VARCHAR(30) NOT NULL,ReqDate CHAR(10) NOT NULL);
INSERT INTO requisition(ReqNumber,Requestor,Auth,ReqDate) VALUES (1000,'Carl Jones','A. Robinson Mgr','2007/10/30');
INSERT INTO requisition(ReqNumber,Requestor,Auth,ReqDate) VALUES (1001,'Peter Smith','A. Robinson Mgr','2007/11/05');
INSERT INTO requisition(ReqNumber,Requestor,Auth,ReqDate) VALUES (1002,'Carl Jones','A. Robinson Mgr','2007/11/06');
CREATE TABLE ReqDetail(ReqNumber INTEGER,StockNumber INTEGER,Quantity INTEGER,ItemCost REAL);
INSERT INTO ReqDetail(ReqNumber,StockNumber,Quantity,ItemCost) VALUES(1000,51013,2,7.52);
INSERT INTO ReqDetail(ReqNumber,StockNumber,Quantity,ItemCost) VALUES(1000,51002,4,.75);
INSERT INTO ReqDetail(ReqNumber,StockNumber,Quantity,ItemCost) VALUES(1000,43512,4,1.52);
INSERT INTO ReqDetail(ReqNumber,StockNumber,Quantity,ItemCost) VALUES(1001,23155,1,9.82);
INSERT INTO ReqDetail(ReqNumber,StockNumber,Quantity,ItemCost) VALUES(1001,43111,1,3.69);
INSERT INTO ReqDetail(ReqNumber,StockNumber,Quantity,ItemCost) VALUES(1002,51001,1,.75);
INSERT INTO ReqDetail(ReqNumber,StockNumber,Quantity,ItemCost) VALUES(1002,23155,1,9.82);
--CARTESIAN PRODUCT example.db
--SQLite Page 5
CREATE TABLE storelist (store_number INTEGER PRIMARY KEY NOT NULL , city VARCHAR(35) NOT NULL , state CHAR(2) NOT NULL );
INSERT INTO storelist VALUES(23,'Bowie','MD');
INSERT INTO storelist VALUES(27,'Scranton','PA');
INSERT INTO storelist VALUES(35,'Allentown','PA');
CREATE TABLE managerlist (last_name VARCHAR(30) NOT NULL , first_name VARCHAR(30) NOT NULL , middle_name VARCHAR(30), start_date DATETIME, store_assn INTEGER, employee_id INTEGER PRIMARY KEY NOT NULL );
INSERT INTO managerlist VALUES('Walters','Joanna','Lehman','2006-06-14',23,456);
INSERT INTO managerlist VALUES('Niels','Matthew','Christopher','2007-02-09',35,532);
INSERT INTO managerlist VALUES('Simpson','Robert','T.','2008-01-21',27,637);
-- Customers Table Spec
CREATE TABLE Customers (AcctNumber INTEGER PRIMARY KEY, Custname VARCHAR(50), Addr1 VARCHAR(50), Addr2 VARCHAR(50), City VARCHAR(30), State CHAR(2), Zipcode VARCHAR(10), Contact VARCHAR(30), Phone VARCHAR(10));
INSERT INTO Customers VALUES(130169,'Acme Widgets','1744 Alder Road','Apt 31C'
,'Springfield','VA','20171','Alan Allen','5715551267');
INSERT INTO Customers VALUES(130208,'Nike Missiles Inc','5946 Oak Drive','' ,'Springfield' ,'VA','20171','Lucy Baker' ,'5715558762');
INSERT INTO Customers VALUES(130247,'Charlies Bakery','7116 Ginko St','suite 100','Springfield','VA','20171' ,'Susan Nordstrom','5715552363');
INSERT INTO Customers VALUES(130286,'Unisales Inc.','8438 Maple Ave','' ,'Springfield' ,'VA','20171-3521' ,'Roger Norton' ,'5715551418');
INSERT INTO Customers VALUES(130325,'M.I. Sinform & Sons' ,'1785 Elm Avenue','P.O. Box 31' ,'Springfield' ,'VA' ,'20171' ,'Mike I. Sinform' ,'5715558760');
INSERT INTO Customers VALUES(130364,'Big Dents Towing Inc.','7578 Spruce St.','Building 31 A' ,'Springfield','VA','20171-5231' ,'George Spencer' ,'5715557855');
INSERT INTO Customers VALUES(130365,'Weneverpay Inc' ,'428 Holly Ct' ,'' ,'Springfield' ,'VA' ,'20171','Peter Norton','5715554313');
--Invoice Table Spec
CREATE TABLE "cust_invoice" ("InvoiceNo" INTEGER PRIMARY KEY NOT NULL , "AcctNumber" INTEGER, "SaleDate" DATETIME, "BillDate" DATETIME)
INSERT INTO cust_invoice(InvoiceNo,AcctNumber,SaleDate,BillDate) VALUES(21001,130208,'2008-11-21','2008-11-27');
INSERT INTO cust_invoice(InvoiceNo,AcctNumber,SaleDate,BillDate) VALUES(21015,130286,'2008-11-25','2008-12-11');
INSERT INTO cust_invoice(InvoiceNo,AcctNumber,SaleDate,BillDate) VALUES(21023,130247,'2008-12-05','2008-12-12');
INSERT INTO cust_invoice(InvoiceNo,AcctNumber,SaleDate,BillDate) VALUES(21027,130286,'2008-12-15','2008-12-18');
-- Invoice items Table Spec
CREATE TABLE catalogsales(AcctNumber INTEGER,InvoiceNo INTEGER,ItemNo INTEGER,Price DOUBLE,Quan INTEGER,RecordNo INTEGER PRIMARY KEY);
INSERT INTO catalogsales VALUES(130208,21001,4501,13.53,10,1);
INSERT INTO catalogsales VALUES(130208,21001,5700,24.95,12,2);
INSERT INTO catalogsales VALUES(130208,21001,4437,6.53,4,3);
INSERT INTO catalogsales VALUES(130208,21001,4551,13.53,10,4);
INSERT INTO catalogsales VALUES(130208,21001,4502,17.95,10,5);
INSERT INTO catalogsales VALUES(130286,21027,4501,13.53,10,6);
INSERT INTO catalogsales VALUES(130286,21027,5700,24.95,17,7);
INSERT INTO catalogsales VALUES(130286,21027,4437,6.53,25,8);
INSERT INTO catalogsales VALUES(130286,21027,3570,291.32,2,9);
INSERT INTO catalogsales VALUES(130286,21015,4501,13.53,3,10);
INSERT INTO catalogsales VALUES(130286,21015,4502,17.95,10,11);
INSERT INTO catalogsales VALUES(130286,21015,5390,1499.99,1,12);
INSERT INTO catalogsales VALUES(130247,21023,4502,17.95,27,13);
INSERT INTO catalogsales VALUES(130247,21023,4501,13.53,10,14);
INSERT INTO catalogsales VALUES(130247,21023,5700,24.95,7,15);
INSERT INTO catalogsales VALUES(130247,21023,4437,6.53,15,16);
CREATE TABLE contactlist1 (FirstName TEXT,LastName TEXT,Email TEXT NOT NULL PRIMARY KEY);
INSERT INTO "contactlist1" VALUES('Peter','Nelson','pnelson@oldmail.fake');
INSERT INTO "contactlist1" VALUES('Alan','Reed','aj.reed@oldmail.fake');
CREATE TABLE contactlist2 (FirstName TEXT NOT NULL,LastName TEXT NOT NULL,Email TEXT NOT NULL PRIMARY KEY);
INSERT INTO "contactlist2" VALUES('Peter','Nelson','pnelson@oldmail.fake');
INSERT INTO "contactlist2" VALUES('Alan','Reed','aj.reed@oldmail.fake');
CREATE TABLE newcontacts(FirstName TEXT,LastName TEXT, Email TEXT);
INSERT INTO "newcontacts" VALUES('James','Doe','james.doe@mymail.fake');
INSERT INTO "newcontacts" VALUES('Roberta','Allen','r.allen@mymail.fake');
INSERT INTO "newcontacts" VALUES('George',NULL,'gpmillford@mymail.fake');
INSERT INTO "newcontacts" VALUES('Kim','Simpson','ka.simpson@mymail.fake')
;
CREATE TABLE tbl_1 (id_1 INTEGER PRIMARY KEY NOT NULL ,field_1 TEXT);
CREATE TABLE tbl_2 (id_2 INTEGER PRIMARY KEY NOT NULL , field_2 TEXT);
INSERT INTO tbl_2(id_2,field_2)VALUES(100,'alpha');
INSERT INTO tbl_2(id_2,field_2)VALUES(101,'bravo');
INSERT INTO tbl_2(id_2,field_2)VALUES(102,'charlie');
INSERT INTO tbl_2(id_2,field_2)VALUES(103,'delta');
INSERT INTO tbl_1(id_1)VALUES(99);
INSERT INTO tbl_1(id_1)VALUES(100);
INSERT INTO tbl_1(id_1)VALUES(101);
INSERT INTO tbl_1(id_1)VALUES(102);
CREATE TABLE tbl_3 (idnum INTEGER PRIMARY KEY NOT NULL ,city TEXT,state TEXT);
CREATE TABLE tbl_4 (idnum INTEGER PRIMARY KEY NOT NULL ,city TEXT,state TEXT);
INSERT INTO tbl_3(idnum,city,state)VALUES(100,'Springfield','MA');
INSERT INTO tbl_3(idnum,city,state)VALUES(101,'Washington','DC');
INSERT INTO tbl_3(idnum,city,state)VALUES(102,'Mobile','AL');
INSERT INTO tbl_4(idnum)VALUES(100);
INSERT INTO tbl_4(idnum)VALUES(101);
INSERT INTO tbl_4(idnum)VALUES(102);
CREATE TABLE order_list(order_date CHAR(10),order_num INTEGER,order_stat VARCHAR(20),cust_id INTEGER);
INSERT INTO order_list(order_date,order_num,order_stat,cust_id) VALUES ('2/7/2008',101281,'Completed',650);
INSERT INTO order_list(order_date,order_num,order_stat,cust_id) VALUES('2/25/2008',101288,'Completed',453);
INSERT INTO order_list(order_date,order_num,order_stat,cust_id) VALUES('03/09/2008',101313,'Cancelled',219);
INSERT INTO order_list(order_date,order_num,order_stat,cust_id) VALUES('3/01/2008',101301, 'Billing Pending',243);
INSERT INTO order_list(order_date,order_num,order_stat,cust_id) VALUES('06/9/2008',101316,'In Progress',650);
INSERT INTO order_list(order_date,order_num,order_stat,cust_id) VALUES('12/17/2008',101419,'Cancelled',219);
CREATE TABLE Sales2008(month CHAR(3),period INTEGER, sales_amount REAL, expenses REAL);
INSERT INTO Sales2008(month,period,sales_amount, expenses) VALUES('Jan',1, 12021.93, 11980.15);
INSERT INTO Sales2008(month,period,sales_amount, expenses) VALUES('Feb',2, 14935.12, 12093.19);
INSERT INTO Sales2008(month,period,sales_amount, expenses) VALUES('Mar' ,3, 15453.41, 12032.55);
INSERT INTO Sales2008(month,period,sales_amount, expenses) VALUES('Apr' ,4, 10561.5, 11890.25);
INSERT INTO Sales2008(month,period,sales_amount, expenses) VALUES('May' ,5, 11770.54, 11923.87);
INSERT INTO Sales2008(month,period,sales_amount, expenses) VALUES('Jun',6, 13958.89, 11995.00);
INSERT INTO Sales2008(month,period,sales_amount, expenses) VALUES('Jul' ,7, 9054.27, 11732.87);
INSERT INTO Sales2008(month,period,sales_amount, expenses) VALUES('Aug' ,8, 10581.56, 10789.31);
INSERT INTO Sales2008(month,period,sales_amount, expenses) VALUES('Sep' ,9, 10205.84, 10860.37);
INSERT INTO Sales2008(month,period,sales_amount, expenses) VALUES('Oct',10, 8787.56, 9635.44);
INSERT INTO Sales2008(month,period,sales_amount, expenses) VALUES('Nov' ,11, 8694.51, 9858.65);
INSERT INTO Sales2008(month,period,sales_amount, expenses) VALUES('Dec' ,12, 8237.63, 9304.68);
CREATE TABLE CurrencyTest (amount REAL);
INSERT INTO CurrencyTest VALUES(1.0);
INSERT INTO CurrencyTest VALUES(1.1);
INSERT INTO CurrencyTest VALUES(1.021);
INSERT INTO CurrencyTest VALUES(1.01);
INSERT INTO CurrencyTest VALUES(100.2);
INSERT INTO CurrencyTest VALUES(25.257);
INSERT INTO CurrencyTest VALUES('.586');
INSERT INTO CurrencyTest VALUES(299.9999);
INSERT INTO CurrencyTest VALUES(53.0);
INSERT INTO CurrencyTest VALUES(500.0);
INSERT INTO CurrencyTest VALUES(3600.23);
INSERT INTO CurrencyTest VALUES(35000.12);