Following the four requirements below, create the 5 tables shown later in this l
ID: 663005 • Letter: F
Question
Following the four requirements below, create the 5 tables shown later in this laboratory exercise, and enter all data as shown. Be sure to record your interactions with SQL*Plus using the spool command.
Use table names, attribute names, and data exactly as shown. The one exception is that you will omit the dollar sign and comma separators in the money amounts. Accurately enter the data as you will need these tables in future laboratory assignments.
Save all of your commands in a single script file for printing and submission. This file should each contain a minimum of your name and the date in comments at the beginning of the file, and any other comments you feel add to the understanding of the script file. Copy and edit your spool file to create the load_tablesXXX.sql file where XXX are your initials. The only items that should be in this load_tablesXXX.sql file are your comments and the SQL statements that drop tables, create tables, insert data values, and display table. Be sure to remove all incorrect commands and the Oracle responses to the correct commands from the file. Save this file for the future in case you have to rebuild these tables. Be sure to include a printout of this file in your report.
The second submission requirement is a printout of each of the 5 tables completely loaded with the specified data. Use the SET LINESIZE command to avoid line wrap around of your table data. Print your list file in landscape mode.
All primary key and foreign key constraints should be named according to the method presented in the classroom. If you have any questions about which attributes are primary and/or foreign keys, please ask about them.
The ultimate test is the execution of this SQL script file in the following form: @ <path_name>/load_tablesXXX.sql
<First Page of tables>
REP Table
REP_ID
REP_LNAME
REP_FNAME
REP_STREET
REP_CITY
REP_STATE
REP_ZIP
REP_COMM
REP_RATE
223
Roma
Theresa
735 First
Leonard
GA
25742
$21,756.50
0.06
237
Miller
Fred
643 High
Sheldon
GA
25753
$38,612.00
0.08
268
Rodriguez
Miguel
2737 Tyler
Springfield
GA
25758
$19,774.00
0.06
CUSTOMER Table
CUST_ID
CUST_NAME
CUST_STREET
CUST_CITY
CUST_STATE
CUST_ZIP
CUST_BALANCE
CUST_LIMIT
REP_ID
1159
Charles Appliance and Sport
3948 Brown
Leonard
GA
25742
$5,560.00
$7,500.00
223
1193
Streaming Direct
4938 Maple
Grove
GA
25721
$210.40
$10,000.00
237
1367
Hollister
REP_ID
REP_LNAME
REP_FNAME
REP_STREET
REP_CITY
REP_STATE
REP_ZIP
REP_COMM
REP_RATE
223
Roma
Theresa
735 First
Leonard
GA
25742
$21,756.50
0.06
237
Miller
Fred
643 High
Sheldon
GA
25753
$38,612.00
0.08
268
Rodriguez
Miguel
2737 Tyler
Springfield
GA
25758
$19,774.00
0.06
Explanation / Answer
Below is your load_tablesXXX.sql file. Run this file after spooling.
DATA :
//Dropping TABLES
DROP TABLE LINE;
DROP TABLE INVOICE;
DROP TABLE PRODUCT;
DROP TABLE CUSTOMER;
DROP TABLE REP;
//creating all the tables
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));
//setting up line size
SET LINESIZE 32000;
//inserting all the values in specific tables.
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);
//we need to convert date using to_date function
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);
//Displaying items from all the tables;
SELECT * FROM REP;
SELECT * FROM CUSTOMER;
SELECT * FROM PRODUCT;
SELECT * FROM INVOICE;
SELECT * FROM LINE;