Schema: bikeshop | 8 questions | 100 points total
| Q | Pts | Techniques | Required tables | Required fields | Clauses |
|---|---|---|---|---|---|
| 1 | 8 | select, where | bikeshop.customers | customers.firstName, customers.lastName, customers.email | WHERE |
| 2 | 8 | select, where, order_by | bikeshop.bikes | bikes.brand, bikes.model, bikes.bikeType, bikes.purchasePrice | WHERE, ORDER BY |
| 3 | 8 | select, where, like | bikeshop.parts | parts.partName, parts.partNumber, parts.category, parts.unitCost | WHERE |
| 4 | 8 | select, where | bikeshop.employees | employees.firstName, employees.lastName, employees.role, employees.hourlyRate | WHERE |
| 5 | 12 | join, where, order_by | bikeshop.repairOrders, bikeshop.employees | repairOrders.repairOrderId, repairOrders.dropOffDate, repairOrders.status, employees.firstName, employees.lastName | JOIN, WHERE, ORDER BY |
| 6 | 12 | join, group_by, aggregate | bikeshop.stores, bikeshop.repairOrders | stores.storeName, stores.city, repairOrders.repairOrderId | JOIN, GROUP BY |
| 7 | 12 | join, group_by, aggregate, having | bikeshop.bikes | bikes.brand, bikes.bikeId, bikes.purchasePrice | GROUP BY |
| 8 | 32 | join, group_by, aggregate, having, order_by | bikeshop.customers, bikeshop.bikes, bikeshop.repairOrders, bikeshop.repairItems | customers.firstName, customers.lastName, customers.city, repairOrders.repairOrderId, repairItems.lineTotal | JOIN, GROUP BY, ORDER BY |
Each question starts at full credit. The deductions below are applied against that question's point value and cannot take it below zero.
| Issue | Deduction |
|---|---|
| Required table missing from the query | -3 per table |
| Required field missing from the SELECT list | -2 per field |
| Required WHERE condition missing or using the wrong operator | -3 per condition |
| WHERE clause omitted entirely when one is required | -5 |
| JOIN omitted when the question requires one | -5 |
| GROUP BY omitted when the question requires one | -4 |
| ORDER BY omitted when one is required | -4 |
| ORDER BY on the wrong field | -2 per field |
| ORDER BY in the wrong direction | -1 per field |
For instructor and TA use. A student answer that differs from the reference but returns the correct result set earns full credit.
SELECT customers.firstName, customers.lastName, customers.email FROM bikeshop.customers WHERE customers.city = 'Green Bay' ORDER BY customers.lastName ASC;SELECT bikes.brand, bikes.model, bikes.bikeType, bikes.purchasePrice FROM bikeshop.bikes WHERE bikes.bikeType = 'Mountain' ORDER BY bikes.purchasePrice DESC;SELECT parts.partName, parts.partNumber, parts.category, parts.unitCost FROM bikeshop.parts WHERE parts.partName LIKE '%bundle%' ORDER BY parts.unitCost ASC;SELECT employees.firstName, employees.lastName, employees.role, employees.hourlyRate FROM bikeshop.employees WHERE employees.role = 'Technician' ORDER BY employees.hourlyRate DESC;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;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;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;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;