How JustAnswer Works:

  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.

Ask Scott Your Own Question

Scott
Scott, MIT Graduate
Category: Homework
Satisfied Customers: 3040
Experience:  MIT Graduate (Math, Programming, Science, and Music)
3546829
Type Your Homework Question Here...
Scott is online now
A new question is answered every 9 seconds

I have a SQL developer assignment. I have to code some

Customer Question

I have a SQL developer assignment. I have to code some prompts for users. I have started the code but it is not executing correctly.

CREATE SQL HERE IS THE CODE TO CREATE THE TABLES SO WE CAN CONTINUE WITH WHAT I AM TRULY STUCK ON

set ECHO ON> -- Program to create tables for the project and populate them with sample data> -- (ORD, PART, SUPPLIER, SUPPPART)> -- Set environment variables> set DEFINE OFF> -- Drop tables if they already exist> -- Note: If you have existing tables with the same names, this script> --       will attemp to drop them.  However, due to your referential integrity> --       constraints, the script may fail.  So, you will need to drop them manually or> --       you can modify the drop command to include droping constraints: CASCADE CONSTRAINTS.> DROP TABLE ORDtable ORD dropped.> DROP TABLE SUPPPARTtable SUPPPART dropped.> DROP TABLE SUPPLIERtable SUPPLIER dropped.> DROP TABLE PARTtable PART dropped.> DROP SEQUENCE ORDERNUMBERsequence ORDERNUMBER dropped.> -- Create the tables and populate them with sample data> -- Note: Implicit inserts used for PART, SUPPLIER, & SUPPPART> --       and Explicit inserts used for ORD.> CREATE TABLE PART (PART_NUM               INTEGER,PART_DESCRIPTION       VARCHAR2(20),PART_QTYONHAND         INTEGER,CONSTRAINT PART_PART_NUM_PK PRIMARY KEY(PART_NUM))table PART created.> INSERT INTO PART VALUES (100,'Oil Filter',75)1 rows inserted.> INSERT INTO PART VALUES (101,'Air Filter',120)1 rows inserted.> INSERT INTO PART VALUES (102,'Oil Can',300)1 rows inserted.> INSERT INTO PART VALUES (103,'Spark Plugs',200)1 rows inserted.> INSERT INTO PART VALUES (104,'Antifreeze',100)1 rows inserted.> CREATE TABLE SUPPLIER (SUPPLIER_CODE          INTEGER,SUPPLIER_NAME          VARCHAR2(20),SUPPLIER_ADDRESS       VARCHAR2(20),SUPPLIER_CITY          VARCHAR2(20),SUPPLIER_STATE         CHAR(2),SUPPLIER_ZIP           CHAR(5),SUPPLIER_AREACODE      CHAR(3),SUPPLIER_PHONE         CHAR(8),CONSTRAINT SUPP_CODE_PK PRIMARY KEY(SUPPLIER_CODE))table SUPPLIER created.> INSERT INTO SUPPLIER VALUES (101,'ACE Auto','123 Able Lane','Long Beach','CA','90840','562','555-1234')1 rows inserted.> INSERT INTO SUPPLIER VALUES (102,'Better Auto Buys','456 Main Street','Huntington Beach','CA','92647','714','555-2222')1 rows inserted.> INSERT INTO SUPPLIER VALUES (103,'Cars R Us','321 Car Lane','Long Beach','CA','90840','562','555-3333')1 rows inserted.> INSERT INTO SUPPLIER VALUES (104,'Delta Parts','444 Auto Way','Long Beach','CA','90840','562','555-4444')1 rows inserted.> CREATE TABLE SUPPPART (SUPPLIER_CODE INTEGER,PART_NUM       INTEGER,CONSTRAINT SP_SUPPCODE_PARTNUM_PK PRIMARY KEY(SUPPLIER_CODE, PART_NUM),CONSTRAINT SP_SUPP_CODE_FK FOREIGN KEY(SUPPLIER_CODE) REFERENCES SUPPLIER,CONSTRAINT SP_PART_NUM_FK FOREIGN KEY(PART_NUM) REFERENCES PART)table SUPPPART created.> INSERT INTO SUPPPART VALUES (101,100)1 rows inserted.> INSERT INTO SUPPPART VALUES (101,101)1 rows inserted.> INSERT INTO SUPPPART VALUES (101,102)1 rows inserted.> INSERT INTO SUPPPART VALUES (101,103)1 rows inserted.> INSERT INTO SUPPPART VALUES (101,104)1 rows inserted.> INSERT INTO SUPPPART VALUES (102,101)1 rows inserted.> INSERT INTO SUPPPART VALUES (102,102)1 rows inserted.> INSERT INTO SUPPPART VALUES (103,102)1 rows inserted.> INSERT INTO SUPPPART VALUES (103,103)1 rows inserted.> INSERT INTO SUPPPART VALUES (104,102)1 rows inserted.> INSERT INTO SUPPPART VALUES (104,103)1 rows inserted.> INSERT INTO SUPPPART VALUES (104,104)1 rows inserted.> CREATE TABLE ORD (ORD_NUM                INTEGER,PART_NUM               INTEGER,SUPPLIER_CODE          INTEGER,ORD_QTY                INTEGER,ORD_DATE               DATE,ORD_RECQTY             INTEGER,ORD_RECDATE            DATE,CONSTRAINT ORD_NUM_PK PRIMARY KEY(ORD_NUM),CONSTRAINT ORD_PARTNUM_SUPPCODE_FK FOREIGN KEY(SUPPLIER_CODE, PART_NUM) REFERENCES SUPPPART)table ORD created.> INSERT INTO ORD (ORD_NUM, PART_NUM, SUPPLIER_CODE, ORD_QTY, ORD_DATE, ORD_RECQTY, ORD_RECDATE)   VALUES (1000, 101, 101, 20, '03-APR-2015', 20, '06-APR-2015')1 rows inserted.> INSERT INTO ORD (ORD_NUM, PART_NUM, SUPPLIER_CODE, ORD_QTY, ORD_DATE, ORD_RECQTY, ORD_RECDATE)   VALUES (1001, 102, 101, 30, '03-APR-2015', 30, '05-APR-2015')1 rows inserted.> INSERT INTO ORD (ORD_NUM, PART_NUM, SUPPLIER_CODE, ORD_QTY, ORD_DATE, ORD_RECQTY, ORD_RECDATE)   VALUES (1002, 103, 101, 30, '04-APR-2015', 30, '05-APR-2015')1 rows inserted.> INSERT INTO ORD (ORD_NUM, PART_NUM, SUPPLIER_CODE, ORD_QTY, ORD_DATE, ORD_RECQTY, ORD_RECDATE)   VALUES (1003, 102, 101, 10, '04-APR-2015', 10, '05-APR-2015')1 rows inserted.> INSERT INTO ORD (ORD_NUM, PART_NUM, SUPPLIER_CODE, ORD_QTY, ORD_DATE, ORD_RECQTY, ORD_RECDATE)   VALUES (1004, 102, 101, 10, '03-APR-2015', 10, '06-APR-2015')1 rows inserted.> INSERT INTO ORD (ORD_NUM, PART_NUM, SUPPLIER_CODE, ORD_QTY, ORD_DATE, ORD_RECQTY, ORD_RECDATE)   VALUES (1005, 102, 102, 25, '04-APR-2015', 25, '04-APR-2015')1 rows inserted.> INSERT INTO ORD (ORD_NUM, PART_NUM, SUPPLIER_CODE, ORD_QTY, ORD_DATE, ORD_RECQTY, ORD_RECDATE)   VALUES (1006, 101, 102, 20, '03-APR-2015', 20, '06-APR-2015')1 rows inserted.> INSERT INTO ORD (ORD_NUM, PART_NUM, SUPPLIER_CODE, ORD_QTY, ORD_DATE, ORD_RECQTY, ORD_RECDATE)   VALUES (1007, 103, 103, 40, '05-APR-2015', 40, '07-APR-2015')1 rows inserted.> INSERT INTO ORD (ORD_NUM, PART_NUM, SUPPLIER_CODE, ORD_QTY, ORD_DATE, ORD_RECQTY, ORD_RECDATE)   VALUES (1008, 102, 104, 10, '05-APR-2015', 10, '06-APR-2015')1 rows inserted.> INSERT INTO ORD (ORD_NUM, PART_NUM, SUPPLIER_CODE, ORD_QTY, ORD_DATE, ORD_RECQTY, ORD_RECDATE)   VALUES (1009, 103, 104, 10, '03-APR-2015', 10, '05-APR-2015')1 rows inserted.> -- Create sequence> CREATE SEQUENCE ORDERNUMBERSTART WITH 1010INCREMENT BY 1sequence ORDERNUMBER created.> -- Reset environment variables> set DEFINE ON

Receiving an Order (receive.sql) THIS IS WHAT I NEED DONE 

This is the transaction used to receive an order.  To initiate this program, the user runs the “receive” program.  The system displays a Receive Order Screen banner and prompts for an order number.  Next, relevant information regarding the order is displayed (See screen below).  The system asks the user if the information is OK and if he/she would like to continue processing the receipt.  If not, the user presses [CTRL] [C] twice to abort the program.  If the user wants to continue, he/she presses the [ENTER] key.  Notice that you CAN NOT receive a partial order.  By pressing [ENTER], you accept the entire quantity.  This is consistent with your observation at AA’s receiving dock.  After pressing the [ENTER] key, the system updates the database and displays the new quantity in stock.

A sample receipt transaction is as follows: * THE BOLD IS WHAT USER ENTERS, THE REST IS WHAT GETS DISPLAYED

SQL> @ c:\receive

**********  Receive Order Screen  **********

Date:  13-APR-15

Enter Order Number to Receive (format 9999): 1010

SP2-0137: DEFINE requires a value following equal sign

Order Found.  Verify the following:

Part Number               : 101

Part Description          : Air Filter

Current Inventory Quantity: 120

Supplier Code: 102

Supplier Name: Better Auto Buys

Date Ordered    : 13-APR-15

Date Received   :

Quantity Ordered: 20

** Again, verify order information:                               **

** In case of discrepancy (Order not found, Wrong quantity, etc.) **

**    Press [CTRL] [C] twice to ABORT                             **

** If correct, press [ENTER] to continue                          **

New Quantity in Stock: 140

SQL>

THIS IS WHAT I HAVE STARTED FOR THE CODING

SET VERIFY OFF

PROMPT ****** Receive Order Screen ****** PROMPT SELECT 'Date: ', TO_CHAR(SYSDATE,'MM/DD/YYYY') FROM DUAL;
PROMPT

DEFINE v_part_num = 'N/A'
DEFINE v_part_description = 'N/A'
DEFINE v_part_qtyonhand = 'N\A'
DEFINE v_supplier_code = 'N\A'
DEFINE v_supplier_name = 'N\A'
DEFINE v_ord_date = 'N\A'
DEFINE v_recdate = 'N\A'
DEFINE v_ord_qty = 'N\A'
DEFINE v_message = 'Order not found. RERUN THE PROGRAM!!'

-- Prompt user for order number
ACCEPT v_ordnum NUMBER FORMAT 9999 PROMPT 'Enter Order Number to Receive '

SET TERMOUT OFF
SPOOL c:\IS380\receiveinfo.sql

SELECT
'DEFINE v_part_num = ' || '''' || O.PART_NUM || ''''
'DEFINE v_part_description = ' || '''' || PART_DESCRIPTION || '''' || CHR(13) || CHR(10) ||
'DEFINE v_part_qtyonhand = ' || '''' || PART_QTYONHAND || ''''
'DEFINE v_supplier_code = ' || '''' || SUPPLIER_CODE || ''''
'DEFINE v_supplier_name = ' || '''' || SUPPLIER_NAME || '''' || CHR(13) || CHR(10) ||
'DEFINE v_ord_date = ' || '''' || ORD_DATE || ''''
'DEFINE v_ord_recdate = ' || '''' || ORD_RECDATE || ''''
'DEFINE v_ord_qty = ' || ''''' || ORD_QTY || ''''
'DEFINE v_message = Order Found. Verify the following'
FROM SUPPLIER S, PART P, ORD O, SUPPPART SP
WHERE S.SUPPLIER_CODE = SP.SUPPLIER_CODE AND O.PART_NUM = SP.PART_NUM AND SP.PART_NUM = P.PART_NUM;

SPOOL OFF

START c:\IS380\receiveinfo.sql

SET TERMOUT ON
PROMPT &v_message
PROMPT Part Number : &v_part_num
PROMPT Part Description : &v_part_description
PROMPT Current Inventory Quantity : &v_part_qtyonhand
PROMPT Supplier Code : &v_supplier_code
PROMPT Supplier Name : &v_supplier_name
PROMPT Date Ordered : &v_ord_date
PROMPT Date Received : &v_recdate
Prompt Quantity Ordered : &v_or_qty

PROMPT ** Again, verify order information: **
PROMPT ** In case of discrepancy (order not found, wrong quantity, etc)**
PAUSE ** Press [CTRL] [C] twice to ABORT If correct, press [ENTER] to continue **
PROMPT

UPDATE ORD
SET ORD_RECDATE = SYSDATE, ORD_RECQTY = ORD_QTY
WHERE ORD_NUM = &v_ordnum;

Submitted: 1 year ago.
Category: Homework
Expert:  LogicPro replied 1 year ago.
Hi,
Welcome here.
What is the deadline?
Customer: replied 1 year ago.
I need it by tomorrow afternoon
Expert:  LogicPro replied 1 year ago.
My apologies, I am out of ideas and opting out for other Professionals to answer. Please wait. You will be notified by email as soon as any Professional joins you.
I would be happy to help you with your other questions though.
Thank you for your patience.

Related Homework Questions