Project Three - SQLite Project

Steps Taken:

Initial Query: I began by updating the WHERE clause in the SQL statement to use the LIKE operator with the % wildcard. This allowed me to select cities where the BillingCity starts with 'B', with the wildcard handling any characters following 'B'. Running this query, I found 62 invoices from cities starting with 'B'.

Further Request: Management then wanted to know the number of invoices from cities containing 'B' anywhere in their name. I modified the query by placing the % wildcard before and after 'B', which allowed for any 'B' in the city name. This adjustment resulted in 83 invoices.

The updated SQL query effectively met the requirements, and I reported that there were 83 invoices billed in cities with 'B' anywhere in the name.

Steps Taken:

Update Query Criteria: I modified the existing query to filter for cities starting with 'P' or 'D'. I used the LIKE operator with the % wildcard character to handle partial matches for both criteria.

Constructed SQL Statement: I specified the BillingCity field and created two conditions: one for cities starting with 'P' and another for cities starting with 'D'. I used the OR operator to include records that meet either condition. The final query was WHERE BillingCity LIKE 'P%' OR BillingCity LIKE 'D%'.

Executed Query: Running the updated query, I retrieved 56 invoices where the billing city starts with 'P' or 'D'.

I reported to WSDA Music that there are 56 invoices satisfying the request, meeting the criteria of cities beginning with 'P' or 'D'.

Steps Taken:

Created Categories Using SQL: I employed the CASE statement in SQL to define categories based on invoice totals. Each range was assigned a label: baseline purchase, low purchase, target purchase, or top performer. The CASE statement allowed me to create a new column called purchase_type with these labels.

Executed and Verified Query: I ran the query and verified that the new purchase_type column accurately reflected the specified categories. The results included invoices classified correctly according to the defined ranges.

Refined Analysis: To meet further management needs, I applied a WHERE clause to filter for only top-performing sales. This adjustment focused the analysis on cities contributing to high-value invoices, revealing that many top-performing cities were outside the United States.

By implementing these SQL techniques, I provided WSDA Music with detailed insights into their sales data, enabling more targeted decision-making.

Steps Taken:

Aliased Tables: To streamline the query and avoid ambiguity, I created aliases for the tables. I aliased the invoice table as I and the customer table as C. This simplification made it easier to reference fields from both tables in the query.

Updated SQL Syntax: I modified the SQL query to use these aliases. Instead of writing out full table names, I used I for invoice and C for customer. This approach not only reduced the amount of typing but also clarified which table each field came from.

Selected Relevant Fields: I specified the fields relevant to the request: the customer’s last name and first name, and from the invoice table, the invoice ID, customer ID, invoice date, and total amount. I ensured that each field was properly referenced using the table aliases.

Steps Taken:

Constructed the Query: I began by building the SQL query to address the request. I started with the Invoice table, which I aliased as i. I then joined the Invoice table with the Customer table, aliased as c, on the CustomerId field. This allowed me to link invoice details to customer information.

Added the Employee Table: Next, I joined the Employee table, aliased as e, using the support representative field from the Customer table and the EmployeeId field from the Employee table. This step connected each invoice to the employee who handled the customer.

Specified the Fields: In the SELECT statement, I included fields such as employee first and last names, employee ID, customer first and last names, support representative ID, customer ID, and the invoice total.

Steps Taken: Constructed a Comprehensive Mailing Address:
I began by creating a single field that combines the customer’s first name, last name, and full address. Using SQL string functions, I concatenated these fields to build a complete mailing address.

I used the double pipe operator (||) to concatenate the FirstName, LastName, and Address fields. To ensure readability, I added spaces and commas where appropriate.

The final address format included the first and last names, followed by the street address, city, state, and zip code. I formatted the output to make it easy to read and labelled this new field as MailingAddress. Shortened Postal Codes:

Steps Taken: Formatted Birthdates: To address the request from WSDA Music, I first needed to convert the birthdate field from a full date-time format (year, month, day, hour, minute, second) to just a date format (year, month, day).

I achieved this by using the strftime function in SQL. I specified the format as '%Y-%m-%d' which denotes the year, month, and day.

I added a new column to the query that used strftime to format the birthdate field. I aliased this column as Birthdate_No_Time_Code to clearly indicate that it only includes the date component without the time.

Steps Taken: Calculated Total Global Sales:
I started by focusing on the invoice table, as it contains the sales data needed.

I used the SUM aggregate function to calculate the total global sales. I specified the total field within the invoice table and aliased this result as Total_Sales.

Running this query provided a grand total of $3,329.46, which answered WSDA Music Management's request for the total global sales of all time.

Steps Taken:

I started by drafting a SQL query that specified the Invoice table in the FROM clause. To display the average invoice totals, I included the BillingCity field and used the AVG function on the total column in the SELECT clause. Initially, this query provided a global average for all invoices instead of the average by each city.

To correct this, I incorporated a GROUP BY clause, grouping the results by BillingCity. This change ensured that the AVG function calculated the average invoice amount separately for each city. Additionally, I added an ORDER BY clause to sort the results in ascending order by city name.

To further enhance the report, I wrapped the AVG function with a ROUND function to format the average amounts to two decimal places. This adjustment provided a clearer, more precise view of the average invoice amounts by city.

With these modifications, the report now effectively displays the average invoice amount for each billing city, meeting the request from WSDA Music Management.

Steps Taken:

I began by updating our SQL query to reflect the new requirement. Initially, our query used a WHERE clause to filter billing cities, but this did not work for filtering based on aggregate functions like AVG. I replaced the WHERE clause with a HAVING clause, which is specifically designed for filtering results based on aggregate functions.

I included the HAVING clause after the GROUP BY clause and specified the condition that the average invoice total should be greater than $5. This adjustment correctly applied the filter to our aggregated data. After running the updated query, I verified that it now displayed average invoice totals exceeding $5 without errors.

The final query now accurately reflects the average invoice amounts that meet the specified condition, effectively fulfilling the request from WSDA Music Management.

Steps Taken:

To address the task, I constructed a SQL query using a subquery to handle the comparison. First, I identified the average invoice total from our invoice table, which was $8.06. Next, I used this average value to filter the invoices.

In the main query, I included a WHERE clause with a subquery. This subquery, enclosed in parentheses, computed the average invoice total from the invoice table. By placing this subquery inside the WHERE clause, I could compare each invoice's total to the computed average.

After running the query, I verified that it successfully returned all invoices with totals less than $8.06. The results were sorted in descending order, confirming that they were correctly filtered according to the average amount. This approach efficiently met the request from WSDA Music Management.

Steps Taken:

To fulfil this request, I crafted a SQL query that provided a side-by-side comparison of each city's average sales and the global average. I started by selecting data from the invoice table and included the billing city field, as well as the average total for each city, using the GROUP BY clause to aggregate the results by city.

Next, to include the global average sales for comparison, I used a subquery within the SELECT clause. This subquery calculated the global average by selecting the average total from the entire invoice table. I then aliased the columns for clarity, labelling them as city average and global average.

By running the query, I obtained a result showing each city’s average sales alongside the global average, enabling an easy comparison. This successfully addressed WSDA Music Management's query about city performance relative to the global average.

Steps Taken:

To address this request, I first created a subquery to identify the invoice dates for the three specific invoices. This involved using the IN operator to select these invoices and retrieve their corresponding dates.

Next, I used this subquery within a larger query to determine if any purchases were made on these dates. I wrapped the original query (which retrieved the invoice dates) in parentheses to form a subquery. In the outer query, I selected relevant fields from the invoice table, such as the invoice date, billing address, and billing city. I then employed the IN clause in the WHERE condition to filter invoices that match the dates obtained from the subquery.

By executing this query, I successfully identified any purchases made on the specified dates, effectively responding to the request from WSDA Music Management.

Steps Taken:

To address this request, I first ran a query to get a distinct list of all tracks from the invoice line table to determine which tracks had been purchased. By using the DISTINCT keyword, I was able to filter out duplicate entries, reducing the record count from 2,240 to 1,984 rows.

Next, I modified my approach to identify tracks that are not included in this list. I created a subquery that selected track IDs from the invoice line table. I then used this subquery in the outer query to find tracks from the track table that were not in the results of the subquery. This was accomplished using the NOT IN operator.

Running this final query, I obtained a list of 1,519 tracks that are not selling, including track ID seven, which confirmed that the query effectively identified unsold tracks. This result meets the request from WSDA Music Management, and I am ready to provide them with the data they need for further analysis.