Question

-- Drop the tables if they existDROP TABLE REP CASCADE CONSTRAINTS;DROP TABLE CUSTOMER CASCADE CONSTRAINTS;DROP TABLE ORDERS CASCADE CONSTRAINTS;DROP TABLE ITEM CASCADE CONSTRAINTS;DROP TABLE ORDER_LINE CASCADE CONSTRAINTS;-- You are to change CUSTOMER to CUSTOMERS for the Colonial database to avoid conflict with the CUSTOMER table in Tal.-- Create the tablesCREATE TABLE REP(REP_NUM CHAR(2) PRIMARY KEY,LAST_NAME CHAR(15),FIRST_NAME CHAR(15),STREET CHAR(15),CITY CHAR(15),STATE CHAR(2),POSTAL_CODE CHAR(5),COMMISSION DECIMAL(7,2),RATE DECIMAL(3,2) );CREATE TABLE CUSTOMER(CUSTOMER_NUM CHAR(3) PRIMARY KEY,CUSTOMER_NAME CHAR(35) NOT NULL,STREET CHAR(20),CITY CHAR(15),STATE CHAR(2),POSTAL_CODE CHAR(5),BALANCE DECIMAL(8,2),CREDIT_LIMIT DECIMAL(8,2),REP_NUM CHAR(2) );CREATE TABLE ORDERS(ORDER_NUM CHAR(5) PRIMARY KEY,ORDER_DATE DATE,CUSTOMER_NUM CHAR(3) );CREATE TABLE ITEM(ITEM_NUM CHAR(4) PRIMARY KEY,DESCRIPTION CHAR(30),ON_HAND DECIMAL(4,0),CATEGORY CHAR(3),STOREHOUSE CHAR(1),PRICE DECIMAL(6,2) );CREATE TABLE ORDER_LINE(ORDER_NUM CHAR(5),ITEM_NUM CHAR(4),NUM_ORDERED DECIMAL(3,0),QUOTED_PRICE DECIMAL(6,2),PRIMARY KEY (ORDER_NUM, ITEM_NUM) );

-- Note above how the primary key is created when it is a composite key (multiple attributes -- 2 in this case)-- You will need to do this in the Colonial database for one or more tables.

-- populate the tablesINSERT INTO REP VALUES('15','Campos','Rafael','724 Vinca Dr.','Grove','CA','90092',23457.50,0.06);INSERT INTO REP VALUES('30','Gradey','Megan','632 Liatris St.','Fullton','CA','90085',41317.00,0.08);INSERT INTO REP VALUES('45','Tian','Hui','1785 Tyler Ave.','Northfield','CA','90098',27789.25,0.06);INSERT INTO REP VALUES('60','Sefton','Janet','267 Oakley St.','Congaree','CA','90097',0.00,0.06);INSERT INTO CUSTOMER VALUES('126','Toys Galore','28 Laketon St.','Fullton','CA','90085',1210.25,7500.00,'15');INSERT INTO CUSTOMER VALUES('260','Brookings Direct','452 Columbus Dr.','Grove','CA','90092',575.00,10000.00,'30');INSERT INTO CUSTOMER VALUES('334','The Everything Shop','342 Magee St.','Congaree','CA','90097',2345.75,7500.00,'45');INSERT INTO CUSTOMER VALUES('386','Johnson''s Department Store','124 Main St.','Northfield','CA','90098',879.25,7500.00,'30');INSERT INTO CUSTOMER VALUES('440','Grove Historical Museum Store','3456 Central Ave.','Fullton','CA','90085',345.00,5000.00,'45');INSERT INTO CUSTOMER VALUES('502','Cards and More','167 Hale St.','Mesa','CA','90104',5025.75,5000.00,'15');INSERT INTO CUSTOMER VALUES('586','Almondton General Store','3345 Devon Ave.','Almondton','CA','90125',3456.75,15000.00,'45');INSERT INTO CUSTOMER VALUES('665','Cricket Gift Shop','372 Oxford St.','Grove','CA','90092',678.90,7500.00,'30');INSERT INTO CUSTOMER VALUES('713','Cress Store','12 Rising Sun Ave.','Congaree','CA','90097',4234.60,10000.00,'15');INSERT INTO CUSTOMER VALUES('796','Unique Gifts','786 Passmore St.','Northfield','CA','90098',124.75,7500.00,'45');INSERT INTO CUSTOMER VALUES('824','Kline''s','945 Gilham St.','Mesa','CA','90104',2475.99,15000.00,'30');INSERT INTO CUSTOMER VALUES('893','All Season Gifts','382 Wildwood Ave.','Fullton','CA','90085',935.75,7500.00,'15');INSERT INTO ORDERS VALUES ('51608','12-OCT-2015','126');INSERT INTO ORDERS VALUES ('51610','12-OCT-2015','334');

INSERT INTO ORDERS VALUES ('51613','13-OCT-2015','386');INSERT INTO ORDERS VALUES ('51614','13-OCT-2015','260');INSERT INTO ORDERS VALUES ('51617','15-OCT-2015','586');INSERT INTO ORDERS VALUES ('51619','15-OCT-2015','126');INSERT INTO ORDERS VALUES ('51623','15-OCT-2015','586');INSERT INTO ORDERS VALUES ('51625','16-OCT-2015','796');-- Note the CORRECT date format above: '16-OCT-2015'INSERT INTO ITEM VALUES('AH74','Patience',9.00,'GME','3',22.99);INSERT INTO ITEM VALUES('BR23','Skittles',21.00,'GME','2',29.99);INSERT INTO ITEM VALUES('CD33','Wood Block Set (48 piece)',36.00,'TOY','1',89.49);INSERT INTO ITEM VALUES('DL51','Classic Railway Set',12.00,'TOY','3',107.95);INSERT INTO ITEM VALUES('DR67','Giant Star Brain Teaser',24.00,'PZL','2',31.95);INSERT INTO ITEM VALUES('DW23','Mancala',40.00,'GME','3',50.00);INSERT INTO ITEM VALUES('FD11','Rocking Horse',8.00,'TOY','3',124.95);INSERT INTO ITEM VALUES('FH24','Puzzle Gift Set',65.00,'PZL','1',38.95);INSERT INTO ITEM VALUES('KA12','Cribbage Set',56.00,'GME','3',75.00);INSERT INTO ITEM VALUES('KD34','Pentominoes Brain Teaser',60.00,'PZL','2',14.95);INSERT INTO ITEM VALUES('KL78','Pick Up Sticks',110.00,'GME','1',10.95);INSERT INTO ITEM VALUES('MT03','Zauberkasten Brain Teaser',45.00,'PZL','1',45.79);INSERT INTO ITEM VALUES('NL89','Wood Block Set (62 piece)',32.00,'TOY','3',119.75);INSERT INTO ITEM VALUES('TR40','Tic Tac Toe',75.00,'GME','2',13.99);INSERT INTO ITEM VALUES('TW35','Fire Engine',30.00,'TOY','2',118.95);INSERT INTO ORDER_LINE VALUES ('51608','CD33',5.00,86.99);INSERT INTO ORDER_LINE VALUES ('51610','KL78',25.00,10.95);INSERT INTO ORDER_LINE VALUES ('51610','TR40',10.00,13.99);INSERT INTO ORDER_LINE VALUES ('51613','DL51',5.00,104.95);INSERT INTO ORDER_LINE VALUES ('51614','FD11',1.00,124.95);INSERT INTO ORDER_LINE VALUES ('51617','NL89',4.00,115.99);INSERT INTO ORDER_LINE VALUES ('51617','TW35',3.00,116.95);INSERT INTO ORDER_LINE VALUES ('51619','FD11',2.00,121.95);INSERT INTO ORDER_LINE VALUES ('51623','DR67',5.00,29.95);INSERT INTO ORDER_LINE VALUES ('51623','FH24',12.00,36.95);INSERT INTO ORDER_LINE VALUES ('51623','KD34',10.00,13.10);INSERT INTO ORDER_LINE VALUES ('51625','MT03',8.00,45.79);

PLEASE NOTE:  This project is to create and populate tables in Oracle using SQL.

Read the following instructions carefully.

This assignment is to be completed in Oracle. Be sure to read all the files in Module 1 before you get started.

SQL Project1: Part 1: You are required to do the following:

• Connect to the server via remote terminal

• Access Oracle

• Run the script that will create the five tables from TAL Distributors database found in Chapter 3 of your SQL Guide.  

• • The script file to create the tables is attached here.
  • Be sure to read all the comments in the file (they start with a double dash --)

SQL Project1: Part 2: You are required to do the following:

• Enter all of the TAL Distributors data found in Chapter 1 of your

• SQL Guide. All of the data are found on pages 4, 5, and 6 of

• the SQL Guide.

  • The script file to insert these data is attached here.

  • Be sure to read all the comments in the file (they start with a

  • double dash --)

SQL Project1: Part3: You are required to do the following:

• Create the Five tables from Colonial Adventure Tours found in

• Chapter 3 of your SQL Guide. The structure of the tables is found on pages 92 and 93 of the SQL Guide.

  • Be sure to enforce the primary key constraint on all tables.
    

• COLONIAL:  Enter all of the Colonial Adventure Tours data found

in Chapter 1 of your SQL Guide. All of the data are found on pages 9 through 13 of the SQL Guide.

Answer 1

Note:In insert statements above Date format is changed as per the instructions.

Output Screenshots:

Thank you have great Day...please do like...:)-