DAD 220 Module Four Major Activity Database Documentation

docx

School

Meru University College of Science and Technology (MUCST) *

*We aren’t endorsed by this school

Course

FINANCIAL

Subject

Business

Date

Nov 24, 2024

Type

docx

Pages

Uploaded by ColloLocco

DAD 220 Module Four Major Activity Database Documentation Name: Arun Lalotra Course: Date: Institution Affiliation:

1. Import the data from each file into tables. LOAD DATA INFILE '/home/codio/workspace/rma.csv' INTO TABLE RMAInformation FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n'; LOAD DATA INFILE '/home/codio/workspace/customers.csv' INTO TABLE CustomerInformation FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n'; LOAD DATA INFILE '/home/codio/workspace/orders.csv' INTO TABLE OrderInformation FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n'; 2

2. Write basic queries against imported tables to organize and analyze targeted data . i. How many records were returned? There were 505 records returned. This SQL query performs an inner join between the CustomerInformation and OrderInformation tables based on the CustomerID . It then filters the results to only include records where the city is 'Framingham' and the state is 'Massachusetts'. Finally, it counts the number of records that meet these criteria. B. Write an SQL query to select all of the customers located in the state of Massachusetts. 3

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

C. Write an SQL query to insert four new records into the Orders and Customers tables using the following data: i. Customers Table ii. Orders Table 4

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”? There are 7 records in the city Woonsocket 5

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? 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. 6

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

F. Delete RMA records. i. Write an SQL statement to delete all records with a reason of “Rejected.” 1. How many records were deleted? Provide a screenshot of your work. DELETE FROM RMA WHERE UPPER(reason) = 'Rejected'; 7

3. Create an output file of the required query results . 4. Write an SQL statement to list the contents of the orders table and send the output to a file with a .csv extension. 8

DAD 220 Module Four Major Activity Database Documentation

Related Documents