Filtering, joins and aggregation across a bicycle retail and repair chain, generated end to end from a business brief.
Answer each of the 8 questions below using a single SQL SELECT statement. Every table reference must be schema-qualified using the 'bikeshop' prefix (e.g., bikeshop.customers), and every column must be table-qualified (e.g., customers.firstName) — do not use aliases or bare column names. Do not use table aliases at any point in your queries. Compile all 8 queries into a single .sql file, with each query preceded by a comment indicating its question number (e.g., -- Question 1). Submit your .sql file to the course portal before the deadline.
The BikeShop network operates multiple retail and service locations across the Midwest and beyond, tracking everything from customer bike registrations to detailed repair order histories. Management wants to better understand shop performance, customer value, and inventory trends. As a junior data analyst, you have been tasked with writing SQL queries to answer key business questions using the bikeShop relational database.
Pedal Works runs a small chain of neighbourhood bicycle shops. Each store sells new and refurbished bikes and runs a service counter where customers drop bikes off for repair. Technicians log the services they perform and the parts they consume on every job.
1. Retrieve the first name, last name, and email of all customers who live in the city of 'Green Bay'. Order the results by last name ascending. (8 pts)
SELECT customers.firstName, customers.lastName, customers.email FROM bikeshop.customers WHERE customers.city = 'Green Bay' ORDER BY customers.lastName ASC;2. List the bike brand, model, bike type, and purchase price for all bikes of type 'Mountain', ordered by purchase price from highest to lowest. (8 pts)
SELECT bikes.brand, bikes.model, bikes.bikeType, bikes.purchasePrice FROM bikeshop.bikes WHERE bikes.bikeType = 'Mountain' ORDER BY bikes.purchasePrice DESC;3. Retrieve the part name, part number, category, and unit cost for all parts whose part name contains the word 'bundle' (case-insensitive match is acceptable). Order results by unit cost ascending. (8 pts)
SELECT parts.partName, parts.partNumber, parts.category, parts.unitCost FROM bikeshop.parts WHERE parts.partName LIKE '%bundle%' ORDER BY parts.unitCost ASC;4. List the employee first name, last name, role, and hourly rate for all employees whose role is 'Technician'. Order the results by hourly rate descending. (8 pts)
SELECT employees.firstName, employees.lastName, employees.role, employees.hourlyRate FROM bikeshop.employees WHERE employees.role = 'Technician' ORDER BY employees.hourlyRate DESC;5. List the repair order ID, drop-off date, status, and the first and last name of the employee who handled each repair order for all repair orders currently in 'In Progress' status. Order results by drop-off date ascending. (12 pts)
Hint: Join repairOrders to employees using the foreign key relationship.
SELECT repairOrders.repairOrderId, repairOrders.dropOffDate, repairOrders.status, employees.firstName, employees.lastName FROM bikeshop.repairOrders JOIN bikeshop.employees ON repairOrders.employeeId = employees.employeeId WHERE repairOrders.status = 'In Progress' ORDER BY repairOrders.dropOffDate ASC;6. For each store, show the store name, city, and the total number of repair orders that have been assigned to that store. Include all stores, even those with zero repair orders. Order results by total repair orders descending. (12 pts)
Hint: Use a LEFT JOIN so that stores with no repair orders still appear. GROUP BY on the store identifier.
SELECT stores.storeName, stores.city, COUNT(repairOrders.repairOrderId) AS totalRepairOrders FROM bikeshop.stores LEFT JOIN bikeshop.repairOrders ON stores.storeId = repairOrders.storeId GROUP BY stores.storeId, stores.storeName, stores.city ORDER BY totalRepairOrders DESC;7. Find each bike brand along with the total number of bikes registered and the average purchase price for that brand. Only include brands that have more than 30 bikes registered in the system. Order results by average purchase price descending. (12 pts)
Hint: Use HAVING to filter groups after aggregation.
SELECT bikes.brand, COUNT(bikes.bikeId) AS totalBikes, AVG(bikes.purchasePrice) AS avgPurchasePrice FROM bikeshop.bikes GROUP BY bikes.brand HAVING COUNT(bikes.bikeId) > 30 ORDER BY avgPurchasePrice DESC;8. For each customer, show their full name (first and last), their city, the total number of repair orders across all their bikes, and the total line total revenue generated from repair items linked to those repair orders. Only include customers who have generated more than $500 in total line total revenue. Order the results by total revenue descending. (32 pts)
Hint: Chain joins from customers → bikes → repairOrders → repairItems. Use COUNT(DISTINCT ...) for repair orders to avoid inflation from multiple line items.
SELECT customers.firstName, customers.lastName, customers.city, COUNT(DISTINCT repairOrders.repairOrderId) AS totalRepairOrders, SUM(repairItems.lineTotal) AS totalRevenue FROM bikeshop.customers JOIN bikeshop.bikes ON customers.customerId = bikes.customerId JOIN bikeshop.repairOrders ON bikes.bikeId = repairOrders.bikeId JOIN bikeshop.repairItems ON repairOrders.repairOrderId = repairItems.repairOrderId GROUP BY customers.customerId, customers.firstName, customers.lastName, customers.city HAVING SUM(repairItems.lineTotal) > 500 ORDER BY totalRevenue DESC;