DAD 220 6-1 Project One Creating a Database and Querying Data! RATED A+, Assignments of Information Technology

Download DAD 220 6-1 Project One Creating a Database and Querying Data! RATED A+ and more Assignments Information Technology in PDF only on Docsity!

DAD 220 Module 6 Project one Creating a database and

querying data

Introduction to Structured Query Language (Southern New Hampshire University)

1 6-1 Project One: Creating a Database and Querying Data DAD-220-T387 Intro to Struct Database Env 21EW Professor

3 In the picture below you can see how I have created tables based on the entity relationship diagram. In the customers table the primary key is the customer ID. In the orders tables table the primary key is the order ID, and in the RMA table the primary key is the RMA ID.

4

2. Create a database schema called QuantigrationUpdates. List out the database name. Provide the SQL commands you ran against MySQL to successfully complete this in your answer: In the picture below you can see how I created a databases called QuantigrationUpdates

6 b. A table named orders in the QuantigrationUpdates database as defined on the project ERD. Provide the SQL commands you ran against MySQL to complete this successfully in your answer: In the picture below you can see I have created an order table with the primary key of order ID

7 c. A table named rma in the QuantigrationUpdates database as defined on the project ERD. Provide the SQL commands you ran against MySQL to complete this successfully in your answer: In the picture below you can see that I have created an RMA table with the primary key being RMA ID

9

4. Update your existing table from “Customer” to “Collaborator” using SQL based on this change in requirements. Provide the SQL commands you ran against MySQL to complete this successfully in your answer: In the picture below you can see that I have created a view for collaborators.

10 Step Two: Load and Query the Data

5. Import the data from each file into tables.

● Use the QuantigrationUpdates database, the three tables you created, and

the three CSV files preloaded into Codio.

● Use the import utility of your database program to load the data from

each file into the table of the same name. You will perform this step three times, once for each table. In the picture below you can see that I have loaded the requested data.

12

● Write an SQL query to select all of the customers located in the state

of Massachusetts. i. Use a WHERE clause to limit the number of records in the customers table to only those who are located in Massachusetts. ii. Record an answer to the following question: How many records were returned? 982 In the picture below you can see that I had a return of 982 customers, I was able to find this using a where statement selecting customer.state as Massachusetts.

13

● Write a SQL query to insert four new records into the orders and

customers tables using the following data: i. Customers Table CustomerI D FirstName LastNam e StreetAddress City State ZipCod e Telephone 100004 Luke Skywalke r 15 Maiden Lane New York NY 10222 212-555- 100005 Winston Smith 123 Sycamore Street Greensbo ro

NC 27401 919-555-

100006 MaryAnne Jenkins 1 Coconut Way Jupiter FL 33458 321-555- 100007 Janet Williams 55 Redondo Beach Blvd Torrence CA 90501 310-555- In the picture below you can see that I have inserted the table above into the customers table.

15 OrderID CustomerID SKU Description 1204305 100004 ADV-24-10C Advanced Switch 10GigE Copper 24 port 1204306 100005 ADV-48-10F Advanced Switch 10 GigE Copper/Fiber 44 port copper 4 port fiber 1204307 100006 ENT-24-10F Enterprise Switch 10GigE SFP+ 24 Port 1204308 100007 ENT-48-10F Enterprise Switch 10GigE SFP+ 48 port In the picture below you can see that I have added the table above into the orders table

● In the customers table, perform a query to count all records where the city

is Woonsocket, Rhode Island. i. How many records are in the customers table where the field “city” equals “Woonsocket”? 7 customers

16 In the picture below you can see that there are 7 customers from Woonsocket in the customers table.

● In the rma database, update a customer’s records.

i. Write an SQL statement to select the current fields of status and step for the record in the rma table with an orderid value of “5175.”

1. What are the current status and step? The current status is pending and awaiting customer documentation.

18 In the picture below you can see that I have updated the order 5175 to complete and credit customer account.

● Delete rma records.

i. Write an SQL statement to delete all records with a reason of “Rejected.”

1. How many records were deleted? 596 were deleted

19 In the picture below you can see that 596 records were deleted

6. Create an output file of the required query results. Write an SQL statement to list the contents of the orders table and send the output to a file that has a .csv extension. In the picture below you can see that I have created an output file with the name QuantigrationUpdates-Mackie-orders-data.csv