Your cart is currently empty!
For this assignment you will write CREATE TABLE and INSERT INTO statements in order to implement a five-table database in Oracle’s SQL*Plus. All the information you need about the database is contained in an MS Excel workbook comprising five worksheets (one worksheet for each table). The MS Excel file accompanies these instructions in the Assignment…
For this assignment you will write CREATE TABLE and INSERT INTO statements in order to implement a five-table database in Oracle’s SQL*Plus. All the information you need about the database is contained in an MS Excel workbook comprising five worksheets (one worksheet for each table). The MS Excel file accompanies these instructions in the Assignment 4 item in Canvas.
More specifically, each worksheet includes the following information about its associated table:
Note that you must swap my initials (jr) with your initials for all table names. Thus, for Mike Smith, the jr_customer table becomes ms_customer, the jr_supplier table becomes ms_supplier, and so on. If your initials have already been taken, choose another set of initials.
Example code for INSERT INTO statements can be found below. Example CREATE TABLE code can be found in the SQL Code Part 1 file available on Canvas.
Things to remember
Example INSERT code for each table
Example INSERT code for the xx_customer table
INSERT INTO jr_customer (
customerID, customerLastName, customerFirstName,
customerCity, customerState, customerZIP, customerEmail,
jr_customerPhoneNum ) VALUES (1,’Alexander’,’Michael’,
‘Highland Heights’,’KY’,’41076′,’alexander@yahoo.com’,NULL) ;
Example INSERT code for the xx_supplier table
INSERT INTO jr_supplier (
supplierID, supplierName, supplierCity, supplierState, supplierContactName, supplierContactTitle, supplierContactEmail ) VALUES (1,’Reaper’,’Denton’,’TX’,
‘John Franklin’,’Product Marketing Manager’,’jfranklin@reaper.com’) ;
Example INSERT code for the xx_order table
INSERT INTO jr_order (
orderID, customerID, orderDate, orderStatus, orderShippedDate )
VALUES (1,1,TO_DATE(‘2018-03-01′,’yyyy-mm-dd’),’SP’,TO_DATE(‘2018-03-04′,’yyyy-mm-dd’) ) ;
Example INSERT code for the xx_product table
INSERT INTO jr_product (
productCode, supplierID, productName, suggestedPrice,
unitsInStock, unitsOnOrder, discontinued ) VALUES
(‘IM511′,2,’Cloaked Assassin’,4.95,4,1,’N’) ;
Example INSERT code for the xx_orderLine table
INSERT INTO jr_orderLine (
orderLineID, orderID, productCode, quantity, unitPrice )
VALUES (1,1,’IM511′,1,5.49) ;
Deliverable
For this assignment, you need only state the initials you used in the Comments portion of the assignment submission in Canvas. If your name is Mike Smith, and you indicate that you used the initials ‘ms’, then I will look to see that all five tables (i.e., ms_customer, ms_supplier, ms_order, ms_product, and ms_orderLine) appear in SQL*Plus. I will also write SELECT statements to make sure that you populated each table with all the data.
Sales Data Model