SQL lab
Omega6
sql_code.docxFull SQL CODE:
CREATE TABLE REP(REP_ID NUMBER(5) constraint REPID_pk primary key, REP_LNAME VARCHAR2(20), REP_FNAME VARCHAR2(20), REP_STREET VARCHAR2(20), REP_CITY VARCHAR2(20), REP_STATE VARCHAR2(20), REP_ZIP NUMBER(10), REP_COMM NUMBER(10,2), REP_RATE NUMBER(10,2));
CREATE TABLE CUSTOMER(CUST_ID NUMBER(5) CONSTRAINT CUSTID_PK PRIMARY KEY,CUST_NAME VARCHAR2(50), CUST_STREET VARCHAR2(30), CUST_CITY VARCHAR2(30), CUST_STATE VARCHAR2(20), CUST_ZIP NUMBER(10), CUST_BALANCE NUMBER(10,2),CUST_LIMIT NUMBER(10,2),REP_ID NUMBER(5) CONSTRAINT CUSTREPID_FK REFERENCES REP(REP_ID));
CREATE TABLE PRODUCT(PROD_ID VARCHAR2(10) CONSTRAINT PRODID_PK PRIMARY KEY, PROD_DESC VARCHAR2(20), PROD_QUANTITY NUMBER(5), PROD_TYPE VARCHAR2(10),PROD_WAREHOUSE VARCHAR2(10),PROD_PRICE NUMBER(10,2));
CREATE TABLE INVOICE(INVOICE_NUM NUMBER(5) CONSTRAINT INVOICENUM_PK PRIMARY KEY, INVOICE_DATE DATE, CUST_ID NUMBER(5) CONSTRAINT CUSTID_INVOICE_FK REFERENCES CUSTOMER(CUST_ID));
CREATE TABLE LINE(INVOICE_NUM NUMBER(5) CONSTRAINT LINE_INVOICENUM_FK REFERENCES INVOICE(INVOICE_NUM), PROD_ID varchar2(10) CONSTRAINT LINE_PRODID_FK REFERENCES PRODUCT(PROD_ID), LINE_NUM_ORDERED NUMBER(5), LINE_PRICE NUMBER(10,2));
SET LINESIZE 32000;
INSERT INTO REP VALUES(223, 'Roma', 'Theresa', '735 First', 'Leonard', 'GA', 25742, 21756.50,0.06); INSERT INTO REP VALUES(237, 'Miller', 'Fred', '643 High', 'Sheldon', 'GA', 25753, 38612.00,0.08); INSERT INTO REP VALUES(268,'Rodriguez','Miguel', '2737 Tyler', 'Springfield', 'GA', 25758, 19774.00,0.06);
INSERT INTO CUSTOMER VALUES(1159, 'Charles Appliance and Sport', '3948 Brown', 'Leonard', 'GA', 25742, 5560.00, 7500.00, 223);
INSERT INTO CUSTOMER VALUES(1193, 'Streaming Direct', '4938 Maple', 'Grove', 'GA', 25721, 210.40, 10000.00, 237);
INSERT INTO CUSTOMER VALUES(1367,'Hollisters','493 Oakwood','Farmerville','GA',25546,6896.00,7500.00,268);
INSERT INTO CUSTOMER VALUES(1419,'Everything Sports Shop','2939 Cardinal','Crystal','GA',25503,5396.36,5000.00,237);
INSERT INTO CUSTOMER VALUES(1462,'Bargain House','4930 Main','Grove','GA',25721,4523.00,10000.00,268);
INSERT INTO CUSTOMER VALUES(1524,'Jacksons','946 Second','Leonard','GA',25742,13817.00,15000.00,223); INSERT INTO CUSTOMER VALUES(1619,'Murrays Department Store','483 Cambridge','Sheldon','GA',25753,2217.00,10000.00,268); INSERT INTO CUSTOMER VALUES(1687,'Lawrence Sport and Appliance','393 Jefferson','Lafayette','GA',25752,3962.00,5000.00,237); INSERT INTO CUSTOMER VALUES(1725,'Dustins All Seasons','171 Washington','Sheldon','GA',25753,359.00,7500.00,237); INSERT INTO CUSTOMER VALUES(1842,'Four Seasons Store','19 Front','Grove','GA',25721,8113.00,7500.00,223); INSERT INTO CUSTOMER VALUES(1873,'Suburban Appliance','128 High','Springfield','GA',25758,1257.50,5000.00,268);
INSERT INTO PRODUCT VALUES('BT105','Blender',52,'HW','A',24.95); INSERT INTO PRODUCT VALUES('BZ117','Exercise Bicycle',47,'SG','C',283.95); INSERT INTO PRODUCT VALUES('CE163','Convection Oven',28,'AP','B',186.00); INSERT INTO PRODUCT VALUES('DM182','Electric Screwdriver',23,'HW','A',49.95); INSERT INTO PRODUCT VALUES('DS104','Electric Range',6,'AP','C',395.00); INSERT INTO PRODUCT VALUES('DW111','Clothes Washer',14,'AP','A',399.99); INSERT INTO PRODUCT VALUES('FP132','Plasma Television',15,'HW','B',999.95); INSERT INTO PRODUCT VALUES('KM173','Clothes Dryer',16,'AP','B',349.95); INSERT INTO PRODUCT VALUES('KW114','Dishwasher',7,'AP','A',435.00); INSERT INTO PRODUCT VALUES('KG130','Home Workout Center',5,'SG','C',1390.00); INSERT INTO PRODUCT VALUES('RD147','HD Radio',7,'HW','B',280.00);
INSERT INTO INVOICE VALUES(42419,to_date('10/09/2007','DD/MM/YYYY'),1159); INSERT INTO INVOICE VALUES(42420,to_date('10/09/2007','DD/MM/YYYY'),1367); INSERT INTO INVOICE VALUES(42433,to_date('12/09/2007','DD/MM/YYYY'),1419); INSERT INTO INVOICE VALUES(42434,to_date('12/09/2007','DD/MM/YYYY'),1193); INSERT INTO INVOICE VALUES(42447,to_date('13/09/2007','DD/MM/YYYY'),1619); INSERT INTO INVOICE VALUES(42449,to_date('13/09/2007','DD/MM/YYYY'),1159); INSERT INTO INVOICE VALUES(42453,to_date('13/09/2007','DD/MM/YYYY'),1619); INSERT INTO LINE VALUES(42419,'BT105',9,26.35); INSERT INTO LINE VALUES(42420,'DS104',2,495.00); INSERT INTO LINE VALUES(42420,'DW111',1,399.99); INSERT INTO LINE VALUES(42433,'KM173',3,379.95); INSERT INTO LINE VALUES(42434,'KW114',2,595.00); INSERT INTO LINE VALUES(42447,'BZ117',2,794.95); INSERT INTO LINE VALUES(42447,'CE163',3,199.95); INSERT INTO LINE VALUES(42449,'DS104',1,495.00); INSERT INTO LINE VALUES(42453,'KG130',3,1290.00);
