SQL Queries for the BikeStores Database Analysis

The collection of queries is designed to perform various analytical, filtering, and reporting tasks:

Sales and Order Analysis:

Determine the

first and last order dates for the highest-volume customer.

Calculate the

net value for orders, filtering for high-value transactions (over $20,000).

Generate a detailed order summary including the

customer name, store, total quantity, and total net value for each sale.

Find the

cheapest and most expensive products purchased by each customer.

Product and Inventory Management:

Identify products where the

total quantity ordered exceeds 100.

Determine products that are

out of stock (quantity = 0) or have never been ordered.

Calculate the

average list price grouped by brand for a specific model year (2018).

Retrieve all products and their associated order details, including those

never sold (LEFT JOIN).

Filtering and Specific Data Retrieval:

Find products based on specific

brand IDs (1 or 9) and a price range ($$$199.99 to $$$499.99).

Select staff members based on both

store ID (1) and manager ID (2).

Search for customers whose names and zip codes match specific patterns (e.g., first name ends in 't' and zip code starts with '11').

Count products with a specific name prefix ('Trek%') within a defined price range.