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

“For ATLProg Only” I need some assistance with the assignment

Customer Question

“For ATLProg Only” I need some assistance with the assignment listed below. It’s SQL programming. I will send you the database files in order to complete this assignment if you accept. Please let me know if you can assist or not. I need this completed by Thursday. Thanks.

1. Create a PL/SQL block to complete the followings.
o Output the cheapest movie information. You can use the view you created in project 1 - task 1
o In your block, referential type should be used to receive the cursor return.
o Use DBMS_OUTPUT to output your result.
o Possible Exception should be handled in exception handling section. You are not required to use user-defined exception. Use Oracle predefined exception. When exception occurs, you need to output error code and error message.
o Spool the output to a text file. Don't forget to use "Set serveroutput on"
o Submit your code as .sql file and spooled output

2. Create a stored procedure based on task 1 with an input parameter movie_id. Modify your cursor to use movie_id to select desired movie information. And execute the procedure and spool the execution result.
o Spool the output to a text file. Don't forget to use "set serveroutput on"
o Submit your code as .sql file and spooled output

3. Create a statement trigger on orders table. The trigger fires after updating the table. When the trigger fires one record insert into the following temp table using the insert statement shown below:
Note: you need to create temp_table and its sequence using the following code.
o PROMPT creating table temp_table ...............
DROP TABLE temp_table;
CREATE TABLE temp_table
( num_col NUMBER(5) not null primary key,
char_col VARCHAR2(30),
date_col VARCHAR2(30));

PROMPT creating SEQUENCE trigger_seq ...............
DROP SEQUENCE trigger_seq;
CREATE SEQUENCE trigger_seq
START WITH 1
INCREMENT BY 1;

INSERT INTO temp_table (num_col, char_col, date_col)
VALUES (trigger_seq.NEXTVAL, 'After Statement trigger', TO_CHAR(sysdate, 'DD-MON-YYY HH24:MI:SS'));
o Submit your code as .sql file

4. Create a row trigger on order_items table to fire after inserting the data into order_items table. When the trigger fires it inserts a record into temp_table using the following insert statement:
o INSERT INTO temp_table (num_col, char_col, date_col)VALUES (trigger_seq.NEXTVAL, 'After Row Trigger', TO_CHAR(sysdate, 'DD-MON-YYY HH24:MI:SS'));
o Submit your code as .sql file

5. Test your triggers.
o Update orders table to set total payment to 300. fire statement trigger on orders table.
o Insert one record into order_items. Fire row trigger on order_items table.
o Query temp_table to get the inserted records for trigger firing.
o Spool the output to a text file and submit it.
Submitted: 3 years ago.
Category: Homework
Expert:  ATLProg replied 3 years ago.
Hello Welcome. Thank you for your question, I’m ATLProg and I’ll try to help you.

How soon do you need this answer ?
Can you please send me a pdf/word document/URL of your assignment description ?
Can you please send me the files you have been working with ?
Can you please go to www.ge.tt and upload your file ? Once you do that you will get a page that has the "Download Link". Copy the "Download Link" and please paste it in your reply here.

Related Homework Questions