Module Four Major Activity Database_Guerrero
docx
keyboard_arrow_up
School
Southern New Hampshire University *
*We aren’t endorsed by this school
Course
220
Subject
Industrial Engineering
Date
Jan 9, 2024
Type
docx
Pages
6
Uploaded by maryssaguerrero
DAD 220 Module Four Major Activity
1.
Import
the
data
from each file
into tables.
A.
Use the import utility of your database program to load the data from each file into the
table of the same name. You’ll perform this step three times, once for each table.
B.
Provide the SQL commands you ran against MySQL to complete this successfully in your
answer.
LOAD DATA INFILE '/home/codio/workspace/customers.csv'
Into table Customers
Fields terminated by ','
Enclosed by '"'
lines terminated by '\n';
LOAD DATA INFILE '/home/codio/workspace/orders.csv'
Into table Orders
Fields terminated by ','
Enclosed by '"'
lines terminated by '\n';
LOAD DATA INFILE '/home/codio/workspace/rma.csv'
Into table RMA
Fields terminated by ','
Enclosed by '"'
lines terminated by '\n';
2.
Write basic queries
against imported tables to organize and analyze targeted data
.
3.
For each query, include a screenshot of the query and its output. You should also include a 1- to
3-sentence description of the output.
A.
Write an SQL query that returns the count of orders for customers located only in the
city of Framingham, Massachusetts.
i.
How many records were returned?
1.
505 records were returned when using the query below. The query
counts the rows only if the conditions are met, which for this query the
condition is orders for customers located only in Framingham,
Massachusetts.
B.
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 that are located in Massachusetts.
ii.
Record an answer to the following question: How many records were returned?
1.
982 records were returned. This query is like the previous except we are
not joining tables to get a return and we are looking for customers
located in Massachusetts.
C.
Write an SQL query to insert four new records into the Orders and Customers tables
using the following data:
i.
Customers Table
CustomerID
FirstName
Lastname
StreetAddress
City
State
ZipCode
Telephone
100004
Luke
Skywalker
17 Maiden Lane
New York
NY
10222
212-555-1234
100005
Winston
Smith
128 Sycamore Street
Greensboro
NC
27401
919-555-6623
100006
MaryAnne
Jenkins
2 Coconut Way
Jupiter
FL
33458
321-555-8907
100007
Janet
Williams
58 Redondo Beach
Blvd
Torrence
CA
90501
310-555-5678
This query inserts the data into the Customers table.
ii.
Orders Table
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
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
OrderID
CustomerID
SKU
Description
1204307
100006
ENT-24-10F
Enterprise Switch 10GigE SFP+ 24 Port
1204308
100007
ENT-48-10F
Enterprise Switch 10GigE SFP+ 48 port
This query inserts the data into the orders table.
D.
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”?
1.
7 records are in the customer table. The query below counts all orders
that have Woonsocket as the city and Rhode Island as the state. This
query joins the Customers and Orders table to get information.
E.
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?
a.
The status is ‘Pending’ and the step is ‘Awaiting customer
Documentation’.
ii.
Write an SQL statement to update the
status
and
step
for the
OrderID
, 5175 to
status
= “Complete” and
step
= “Credit Customer Account.”
1.
What are the updated
status
and
step
values for this record? Provide a
screenshot of your work.
a.
The updated status is ‘Complete’ and updated step is ‘Credit
Customer Account.’ The query used below is used to put in new
values for a field, this one being status and step.
F.
Delete RMA records.
i.
Write an SQL statement to delete all records with a reason of “Rejected.”
1.
How many records were deleted?
a.
596 records were deleted.
4.
Create an output file
of the required query results
.
5.
Write an SQL statement to list the contents of the orders table and send the output to a file with
a .csv extension.
The query selects columns from the Orders table and outputs it to the output file ‘orders-
updated.csv’.
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help