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)
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)
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)
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)
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.
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.
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.
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.