The Objective Of This Activity Is To Hone Your Skills At Man

The Objective Of This Activity Is To Hone Your Skills At Manipulating

The objective of this activity is to hone your skills at manipulating data using SQL DML statements. Use Query Editor throughout this exercise. Additional Sample Data for Customer Table: CustomerID, CustomerName, CustomerAddressID. Additional Sample Data for Address Table: AddressID, Street, City, State, Zip Code. Construct and execute a query to list all customers with their corresponding cities and states. The list should be sorted in ascending numerical order by ZIP code, followed by customer name alphabetically. Take a screenshot of the Query Editor after you have executed this SQL command to show that it was completed successfully. Construct and execute a SQL query to count the number of customers in each state. The list should be ordered by the number of customers in descending order, then by state code in ascending order. Take a screenshot of the Query Editor after you have executed this SQL command to show that it was completed successfully. Construct and execute a SQL query to list the full names of all customers who have Harper in their name. Take a screenshot of the Query Editor after you have executed this SQL command to show that it was completed successfully. After you have completed this Critical Thinking Assignment, submit one MS Word document that contains the three screenshots. Insert all of the screenshots into the document, label each of them clearly.

Paper For Above instruction

SQL Data Manipulation Exercises and Queries

The primary goal of this exercise is to develop and refine skills in manipulating data within relational databases using Structured Query Language (SQL) Data Manipulation Language (DML) statements. This practice involves creating specific SQL queries that extract, count, and filter data based on provided tables and sample data. All tasks should be performed in a Query Editor environment, with outcomes documented through screenshots for submission.

Preparation of Sample Data

Two tables are used for the exercises: the Customer table and the Address table. The Customer table contains the following columns:

• CustomerID
• CustomerName
• CustomerAddressID

The Address table contains:

• AddressID
• Street
• City
• State
• Zip Code

Sample data provided include:

• Customer Table:
  • CustomerID: 1, CustomerName: Western Supply Co., CustomerAddressID: 2
  • CustomerID: 2, CustomerName: Jake Harper, CustomerAddressID: 4
  • CustomerID: 3, CustomerName: Elizabeth Harper, CustomerAddressID: 1
  • CustomerID: 4, CustomerName: Abacus Consulting, CustomerAddressID: 3
• Address Table:
  • AddressID: 1, Street: 2400 Broadway, City: New York, State: NY, Zip: 75201
  • AddressID: 2, Street: St Street, City: Atlanta, State: GA, Zip: (missing)
  • AddressID: 3, Street: Skyline Blvd, City: Seattle, State: WA, Zip: (missing)
  • AddressID: 4, Street: Park Avenue, City: Dallas, State: TX, Zip: (missing)

Task 1: Listing Customers with City and State

Create an SQL query that joins the Customer and Address tables. This query should output the customer name, city, and state for each customer, sorted primarily by ZIP code in ascending order, and secondarily by customer name alphabetically. After executing, take a screenshot of the Query Editor showing the results.

Task 2: Counting Customers by State

Construct a SQL query to count how many customers are in each state. The resulting list should display the state alongside the customer count, ordered by the number of customers in descending order, and then by the state code in ascending order. Execute and take a screenshot of the completed query.

Task 3: Listing Customers with 'Harper' in Name

Develop an SQL statement to find and list the full names of all customers whose names include the substring "Harper." After execution, capture a screenshot of the query results.

Submission Instructions

Compile all three screenshots into a single MS Word document. Clearly label each screenshot to indicate which task it corresponds to. Submit this document as your completed assignment.