Database SQL statements used in the analysis to a text file named analysis

Each group member will post the summary section of Part 2 and other deliverables to the group discussion for review by the group members. 

One group member is required to submit the document Group Answer  and Part 2 deliverables in the group assignment folder for Part 2.

  1. Save a copy of the file, Part 2 – Returns – Superstore DBST652.xls, to your workspace.
  2. Connect to the Oracle database with Oracle SQL Developer; use the connection and username used for Part 1. Expand the connection, click on Tables. Right-click Tables and select Import Data.

 

Browse to the Excel workbook named Part 2 – Returns – Superstore DBST652.xls. 

Name the table Temporary. Adjust column names, data types and size/precision as necessary. 

  1. Begin the analysis of the data. Save all SQL statements used in the analysis to a text file named analysis.txt. 

Execute the following statements:

Timeline Description automatically generated
  1. Create at least two additional queries to further your understanding of the data.
  2. Determine if the ORDER_ID’s in the temporary table exist in the table SUPERSTORE. Write an SQL statement that will provide you this information.
  3. Make a copy of the temporary table and name the copy RETURNS. Make changes to the RETURNS table by either deleting rows or modifying rows to clean-up the data. 
  4. Query the RETURNS table to determine the number of returned orders by reason.
  5. Query the RETURNS table to determine which products are returned most often.
  6. Add comments to the analysis.txt to describe and document the purpose of each statement. 
  7. Post the analysis.txt file to the group discussion.
  8. After reviewing and discussing each member’s analysis, determine the most useful queries for evaluating the dataset. Save only the most useful queries used in the analysis to a sql file named analysisPart2.txt.
  9. Delete the column, Returned, from the “Returns” table.
  10. Study the Sample Application Using Object-Relational Features of Oracle from Oracle’s 

Then study the following example that demonstrates how to create a nested table object type that contains the following information: CUSTOMER_ID, ORDER_DATE, and the PRODUCT_ID’s and QUANTITY. 

Before creating a nested table, we first create an object type with the name Orders_type with four attributes:  CUSTOMER_ID, ORDER_DATE, PRODUCT_ID, and QUANTITY. We used the CREATE TYPE statement to create the Orders_type object as seen below with screenshot of successful execution. 

 

CREATE OR REPLACE TYPE Orders_type AS OBJECT

(

CUSTOMER_ID   VARCHAR2(25),

ORDER_DATE   VARCHAR2(15),

PRODUCT_ID   VARCHAR2(25),

QUANTITY     NUMBER

)

/

 

Next procedure is to create a nested table with a user-defined object type named ‘Orders_type’ which we created earlier using the CREATE OR REPLACE TYPE statement. 

 

CREATE OR REPLACE TYPE Orders_table AS TABLE OF Orders_type;

/

 

The last procedure is to create the table ‘orders_nt’ which has 2 columns ‘col_id’ and ‘orders_info’. The first column is of Number datatype while the second column is of Nested Table type. This means that the second column contains a table and that table is our nested table ‘Orders_table’. The full SQL statement is below with screenshot of successful execution.

 

CREATE TABLE orders_nt

(

col_id      NUMBER,

orders_info Orders_table

)

NESTED TABLE orders_info STORE AS orders_nest_tab;

 

Part 2

Answer the following questions:

  1. Describe your findings in your analysis of the data. What further analysis of the data set do you want to make? 
  2. What concerns (if any) do you have about the data in the temporary table? Explain why you have concerns.
  3. How did you determine if the ORDER_ID’s in the temporary table exist in the table SUPERSTORE?
  4. Describe the changes you made to the data in the RETURNS table and provide a reason for the change. 
  5. How many orders were returned by reason?
  6. What products are most often returned? 
  7. Insert screenshots that show you can successfully execute the example nested table object type.
  8. Describe your contribution to Part 2 of the group project.
  9. If you could not complete or understand parts of this assignment, describe your difficulties in the space that follows. Describe any assistance you received and/or requested from your group members.

Deliverables for Part 2

The deliverables for this assignment are:

  1. Submit the Group Answer to the group assignment folder for Part 2.

 

  1. Submit the file named analysisPart2.txt to the group assignment folder for Part 2. The file is created in step 6.

 

  1. Each group member must submit their version of individual work to the assignment folder named Group Project – Individual Submissions. Submit the summary section of part 2 with individual answers to the Group Project – Individual Submissions assignment folder.

 

 Welcome to one of the bestassignmenthelpcompanies  online .

·         Do you want to order for a customized assignment help task?

·          Click on the order now button 

·         Set up your topic, Fix the number of pages, Fix your Order instructions 

·         Set up your deadline, upload the necessary files required to complete the task, Complete the payment.

 We delivery high quality and non plagiarized tasks within the stipulated time given 

SL