Assignment
In this task, you will import data from more than one text file to create an Oracle database. Next, you will add some constraints and test the constraints.Finally, you will write and execute queries.
Objectives:
• Improve your understanding of primary key, foreign key, check, unique and NOT NULL constraints
• Improve your understanding of subqueries, outer joins and DISTINCT
Business situation
You are responsible for responding to a variety of requests that require you to write some queries for an existing database. You are provided an extraction from an Oracle database to useto build and test your queries. You will create a test database from the extraction and build the queries.
As you complete each task below, answer the questions that follow. Write all answers to the questions on this document.
1. Review the text files provided for this assignment. Use NotePad++ to view each file and make any edits to the data or files before importing them into SQL Developer.
a. Did you make any edits to the files provided? Explain.
2. Connect to the Oracle database using SQL Developer. Create a new user named spring2018. Create a new connection to the Oracle database. Name the connection Spring2018; use username Spring2018.
a. Were you able to create a new user and connection for this assignment? If not, use an already existing connection throughout this assignment.
3. Import the data from the text files into SQL Developer using the Spring2018 connection. Create one table for each text file provided.
a. Describe how you created or imported the tables from the text files.
4. Add constraints to your test database. Add primary key, foreign key, check, unique and NOT NULL constraints.
a. Describe the constraints you added to the database and write the sql for these constraints. Place the sql for the constraints and other commands that you used to create the constraints in a text file with comments. Name the file Part5Constraints.sql.
b. For each constraint type, write at least one "test" to validate your constraints are working. For example, write the sql statement to update a foreign key that does not reference an existing primary key. Place the sql for the test and other commands that you used to test the constraints in a text file with comments. Name the file Part5TestConstraints.sql.
5. For each queryshown in the screen clipping below, explain if it is a subquery or join and determine the type of subquery or join. Write a question that the return set of the query answers. See the notes at the end of this document. Show evidence you successfully ran the queries.
6. Write a query whose result set answers the questions that follow. Show the query and the result set. Place the sql for the queries and other commands that you used to create the queries in a text file with comments. Name the file Part5Queriess.sql.
a. What customers have not ordered any products?
b. What order is the second most profitable?
c. How many orders has each customer placed?
d. Can you write a query that demonstrates your understanding of a correlated subquery?
e. Can you write a query that demonstrates your understanding of an out join?
f. Can you write a query that demonstrates your understanding of the DISTINCT key word?
7. Export your database -- DDL only. Name the file Part5DDL.sql.
8. List at least three resources in APA style that you used to complete the tasks in this assignment.
9. List three things that you learned.