













Study with the several resources on Docsity
Earn points by helping other students or get them with a premium plan
Prepare for your exams
Study with the several resources on Docsity
Earn points to download
Earn points by helping other students or get them with a premium plan
Community
Ask the community for help and clear up your study doubts
Discover the best universities in your country according to Docsity users
Free resources
Download our free guides on studying techniques, anxiety management strategies, and thesis advice from Docsity tutors
DAD 220 6-1 Project One Creating a Database and Querying Data
Typology: Assignments
1 / 21
This page cannot be seen from the preview
Don't miss anything!
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
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
10 Step Two: Load and Query the Data
the three CSV files preloaded into Codio.
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
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
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
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
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.
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.”
18 In the picture below you can see that I have updated the order 5175 to complete and credit customer account.
i. Write an SQL statement to delete all records with a reason of “Rejected.”
19 In the picture below you can see that 596 records were deleted