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.
- Save a copy of the file, Part 2 – Returns – Superstore DBST652.xls, to your workspace.
- 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.
- 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:
- Create at least two additional queries to further your understanding of the data.
- 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.
- 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.
- Query the RETURNS table to determine the number of returned orders by reason.
- Query the RETURNS table to determine which products are returned most often.
- Add comments to the analysis.txt to describe and document the purpose of each statement.
- Post the analysis.txt file to the group discussion.
- 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.
- Delete the column, Returned, from the “Returns” table.
- 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:
- Describe your findings in your analysis of the data. What further analysis of the data set do you want to make?
- What concerns (if any) do you have about the data in the temporary table? Explain why you have concerns.
- How did you determine if the ORDER_ID’s in the temporary table exist in the table SUPERSTORE?
- Describe the changes you made to the data in the RETURNS table and provide a reason for the change.
- How many orders were returned by reason?
- What products are most often returned?
- Insert screenshots that show you can successfully execute the example nested table object type.
- Describe your contribution to Part 2 of the group project.
- 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:
- Submit the Group Answer to the group assignment folder for Part 2.
- Submit the file named analysisPart2.txt to the group assignment folder for Part 2. The file is created in step 6.
- 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