Create a new connection to the oracle database

Assignment Help Database Management System

Reference no: EM131882742

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.

Reference no: EM131882742

Questions Cloud

Estimate when installation would be more appropriate : Estimate when installation would be more appropriate and include the factors that would influence your decision.

Get a full nights sleep : When Skooter finally does get a full night's sleep, what kind of changes would you expect to occur in his sleep cycle? Explain in a paragraph.

What are some characteristic symptoms of autism : What are some characteristic symptoms of autism?

Demonstrates a cloud-based distributed architecture : Using Lucid Chart create a diagram that demonstrates a Cloud-based distributed architecture for an imaginary company of your choice.

Create a new connection to the oracle database : Review the text files provided for this assignment. Use NotePad++ to view each file and make any edits to the data or files.

Different attitudes about death in the us : What are your own attitudes about death in terms of your fears, optimism about a long life, and not wanting significant others to die?

Describe a scenario in which the sdlc might be employed : Describe a scenario in which the SDLC might be employed in a business. Include a discussion of potential challenges in the SDLC process.

Euthanasia as a result of an advanced directive : How would you differentiate between suicide and active euthanasia as a result of an advanced directive?

Grieve the loss of a loved one : What are the different ways people grieve the loss of a loved one? What are the findings about how grief is overcome as time passes?

User Account

All Pages