Schema: craftbrewery | 8 questions | 100 points total
| Q | Pts | Techniques | Required tables | Required fields | Clauses |
|---|---|---|---|---|---|
| 1 | 8 | select, where, order_by | craftbrewery.beers | beers.beerName, beers.abv, beers.ibu | WHERE, ORDER BY |
| 2 | 11 | join, order_by | craftbrewery.beers, craftbrewery.styles | beers.beerName, styles.styleName, styles.description | JOIN, ORDER BY |
| 3 | 11 | left_join, aggregate, group_by | craftbrewery.accounts, craftbrewery.orders | accounts.accountName, accounts.accountType, orders.orderId | JOIN, GROUP BY |
| 4 | 12 | join, group_by, having, aggregate | craftbrewery.styles, craftbrewery.beers | styles.styleName, beers.beerId | JOIN, GROUP BY |
| 5 | 12 | join, aggregate, group_by, order_by | craftbrewery.territories, craftbrewery.accounts, craftbrewery.orders, craftbrewery.orderLines | territories.territoryName, territories.salesRep, orderLines.lineTotal | JOIN, GROUP BY, ORDER BY |
| 6 | 15 | subquery, in_clause | craftbrewery.batches, craftbrewery.beerTags | batches.batchCode, batches.brewDate, batches.volumeLitres, batches.status | WHERE |
| 7 | 16 | case_when, aggregate, group_by, join | craftbrewery.beers, craftbrewery.taproomSales | beers.beerName, taproomSales.servingType, taproomSales.saleAmount | JOIN, GROUP BY |
| 8 | 15 | subquery, aggregate, group_by, having, join | craftbrewery.accounts, craftbrewery.orders, craftbrewery.orderLines | accounts.accountName, accounts.city, accounts.accountType, orderLines.unitPrice | JOIN, GROUP 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 beers.beerName, beers.abv, beers.ibu FROM craftbrewery.beers WHERE beers.isActive = 1 ORDER BY beers.abv DESC;SELECT beers.beerName, styles.styleName, styles.description FROM craftbrewery.beers JOIN craftbrewery.styles ON beers.styleId = styles.styleId ORDER BY styles.styleName ASC, beers.beerName ASC;SELECT accounts.accountName, accounts.accountType, COUNT(orders.orderId) AS totalOrders FROM craftbrewery.accounts LEFT JOIN craftbrewery.orders ON accounts.accountId = orders.accountId GROUP BY accounts.accountId, accounts.accountName, accounts.accountType ORDER BY totalOrders DESC;SELECT styles.styleName, COUNT(beers.beerId) AS activeBeerCount FROM craftbrewery.styles JOIN craftbrewery.beers ON styles.styleId = beers.styleId WHERE beers.isActive = 1 GROUP BY styles.styleId, styles.styleName HAVING COUNT(beers.beerId) > 5 ORDER BY activeBeerCount DESC;SELECT territories.territoryName, territories.salesRep, SUM(orderLines.lineTotal) AS totalRevenue FROM craftbrewery.territories JOIN craftbrewery.accounts ON territories.territoryId = accounts.territoryId JOIN craftbrewery.orders ON accounts.accountId = orders.accountId JOIN craftbrewery.orderLines ON orders.orderId = orderLines.orderId WHERE orders.status = 'Delivered' GROUP BY territories.territoryId, territories.territoryName, territories.salesRep ORDER BY totalRevenue DESC;SELECT batches.batchCode, batches.brewDate, batches.volumeLitres, batches.status FROM craftbrewery.batches WHERE batches.beerId IN (SELECT beerTags.beerId FROM craftbrewery.beerTags WHERE beerTags.tagName = 'Hoppy') ORDER BY batches.brewDate ASC;SELECT beers.beerName, SUM(CASE WHEN taproomSales.servingType IN ('Flight', 'Half Pint') THEN taproomSales.saleAmount ELSE 0 END) AS smallTotal, SUM(CASE WHEN taproomSales.servingType = 'Pint' THEN taproomSales.saleAmount ELSE 0 END) AS standardTotal, SUM(CASE WHEN taproomSales.servingType IN ('Growler Fill', 'Crowler') THEN taproomSales.saleAmount ELSE 0 END) AS largeTotal FROM craftbrewery.beers JOIN craftbrewery.taproomSales ON beers.beerId = taproomSales.beerId GROUP BY beers.beerId, beers.beerName ORDER BY beers.beerName ASC;SELECT accounts.accountName, accounts.city, accounts.accountType, ROUND(AVG(orderLines.unitPrice), 2) AS avgUnitPrice FROM craftbrewery.accounts JOIN craftbrewery.orders ON accounts.accountId = orders.accountId JOIN craftbrewery.orderLines ON orders.orderId = orderLines.orderId GROUP BY accounts.accountId, accounts.accountName, accounts.city, accounts.accountType HAVING AVG(orderLines.unitPrice) > (SELECT AVG(orderLines.unitPrice) FROM craftbrewery.orderLines) ORDER BY avgUnitPrice DESC;