You will import data into an Oracle database from an Excel workbook using the SQL Developer connection you used in Lab Part 3.
Then you will execute some Oracle commands against the data to get familiar with the dataset. Finally, you will incorporate a new table into an existing database.
Contents
Objectives: 1
Business situation 1
Delete data 3
Deliverables 3
Objectives:
• Learn about basic database maintenance techniques
• Demonstrate "before" and "after" views of data updates
• Learn how to incorporate new tables into a database
• Learn how to use SQL Loader to populate database tables
• Learn how to export table data as INSERT statements
Business situation
You are responsible for the design of a database for use in a future study of customer purchases and demographics. A future study on returned purchases requires a change in the preliminary database design. You will analyze an extraction of returned purchases, and incorporate the returned information into your database design.
The extraction of returned purchases is given to you in an Excel workbook. The name of the worksheet containing the data is Returns.
For each returned order, assume that the entire order was returned.
Analyze and clean the data in the "Returns" worksheet for any issues. One way to do this is to create a temporary table that you will use to analyze the data in Oracle and then clean the data in the worksheet based on your findings. For example, you will want to determine if for every returned order, an order actually exists in your database.
Next, incorporate the three columns of the cleaneddata from the worksheet into a new table named "Returns" without dropping and recreating the entire database. Ensure to create the proper primary and foreign keys. Use SQL Loader to populate the "Returns" table.
As you complete each task below, answer the questions that follow. Write all answers to the questions on this document.
1. Import the data from the Excel workbook into SQL Developer using the HRconnection or the connection.
a. Right-click Tables and select Import Data. Browse to the Excel workbook (part1). Name the table SUPERSTORE. Adjust column names, data types and size/precision as necessary.
b. Once that is imported, open the part2.txt and create the tables.
c. Next, expend the connection, click on Tables.
d. Right-click Tables and select Import Data. Browse to the Excel workbook (part3). Name the table Temporary. Adjust column names, data types and size/precision as necessary.
e. After creating and populating the table, insert a screen clipping below that shows the result of the QUICK DDL. (Right-click the table name). Your screen clipping will look something like this:
f. Why was it necessary to use an attribute name ORDER_ID instead of ORDER?
g. Save a copy of the DDL to a file with the name QUICK_DDL_TEMPORARY.sql
2. Analyze the data. Execute the following queries and discuss the meaning of the result of each query.
3. Determine if the ORDER_ID's in the temporarytable exist in the ORDERS table.
a. Execute the following query and discuss the meaning of the result set.
4. Rename the temporary table to RETURNS.
a. Right-click the temporary table and select Table\Rename.
Do you have any problems renaming the table?
5. Create a primary key for the RETURNStable and create a foreign key on RETURNStable that references the ORDERS tables.
a. Describe how you accomplished this task.
6. Query the RETURNS table and determine the number of returned orders by reason
a. How many orders were returned because of damage, ordered accidently, dissatisfaction, inaccurate description, late delivery, or incorrect products?
b. Describe how you determined the answer to 6a. Provide the sql commands in the space below.
7. Query the RETURNS table and list the product id's of the returned products
a. Describe the returned products. Determine
i. what products are returned
ii. which products are returned most often
b. Provide the sql commands in the space below.
8. Delete the column, Returned, from the "Returns" table.
a. Described how you deleted the "Returned" column in the space below.
9. Export the database - DDL and data to a file. Name the file Lab_4_Exported.sql.
10. Complete the tutorial on Oracle Command Line Tools.
a. Describe your experience with the tutorial.
11. Let us see if you can use SQL Loader to populate a table using the data from an Excel workbook. First, save the Excel worksheet named Returns as a comma delimited file. (csv).
12. Next, create a table. Use the table definition from step 1 -- execute the DDL from file QUICK_DDL_TEMPORARY.sql(from step 1) to createa table named temporary.
a. Use SQL Loader to populate the temporarytable with the data from the csv file. (Let me know if you had to change the file extension to txt.)
b. Describe your experience with tasks 11- 12.
13. List at least three resources in APA style that you used to complete the tasks in this assignment.
14. List three things that you learned from this assignment.