Schema: chinook | 8 questions | 100 points total
| Q | Pts | Techniques | Required tables | Required fields | Clauses |
|---|---|---|---|---|---|
| 1 | 5 | select, where, order_by | chinook.Customer | Customer.FirstName, Customer.LastName, Customer.Email | WHERE, ORDER BY |
| 2 | 5 | select, where, order_by | chinook.Track | Track.Name, Track.UnitPrice | WHERE, ORDER BY |
| 3 | 10 | join, order_by | chinook.Track, chinook.Genre, chinook.MediaType | Track.Name, Genre.Name, MediaType.Name | JOIN, ORDER BY |
| 4 | 10 | left_join, aggregate, group_by | chinook.Artist, chinook.Album | Artist.Name, Album.AlbumId | JOIN, GROUP BY |
| 5 | 15 | join, aggregate, group_by, having | chinook.Genre, chinook.Track | Genre.Name, Track.TrackId | JOIN, GROUP BY |
| 6 | 15 | join, aggregate, group_by, order_by | chinook.Employee, chinook.Customer, chinook.Invoice | Employee.FirstName, Employee.LastName, Employee.Title, Invoice.Total | JOIN, GROUP BY, ORDER BY |
| 7 | 20 | subquery, in_clause, join | chinook.Customer, chinook.Invoice | Customer.FirstName, Customer.LastName, Customer.Email | WHERE |
| 8 | 20 | join, aggregate, group_by, case_when, order_by | chinook.Invoice | Invoice.BillingCountry, Invoice.InvoiceId, Invoice.Total | 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 Customer.FirstName, Customer.LastName, Customer.Email FROM chinook.Customer WHERE Customer.Country = 'USA' ORDER BY Customer.LastName ASC, Customer.FirstName ASC;SELECT Track.Name, Track.UnitPrice FROM chinook.Track WHERE Track.UnitPrice > 0.99 ORDER BY Track.UnitPrice DESC, Track.Name ASC;SELECT Track.Name, Genre.Name, MediaType.Name FROM chinook.Track JOIN chinook.Genre ON Track.GenreId = Genre.GenreId JOIN chinook.MediaType ON Track.MediaTypeId = MediaType.MediaTypeId ORDER BY Genre.Name ASC, Track.Name ASC;SELECT Artist.Name, COUNT(Album.AlbumId) AS AlbumCount FROM chinook.Artist LEFT JOIN chinook.Album ON Artist.ArtistId = Album.ArtistId GROUP BY Artist.ArtistId, Artist.Name ORDER BY AlbumCount DESC, Artist.Name ASC;SELECT Genre.Name, COUNT(Track.TrackId) AS TrackCount FROM chinook.Genre JOIN chinook.Track ON Genre.GenreId = Track.GenreId GROUP BY Genre.GenreId, Genre.Name HAVING COUNT(Track.TrackId) > 50 ORDER BY TrackCount DESC;SELECT Employee.FirstName, Employee.LastName, Employee.Title, SUM(Invoice.Total) AS TotalRevenue FROM chinook.Employee JOIN chinook.Customer ON Employee.EmployeeId = Customer.SupportRepId JOIN chinook.Invoice ON Customer.CustomerId = Invoice.CustomerId WHERE Employee.Title = 'Sales Support Agent' GROUP BY Employee.EmployeeId, Employee.FirstName, Employee.LastName, Employee.Title ORDER BY TotalRevenue DESC;SELECT Customer.FirstName, Customer.LastName, Customer.Email FROM chinook.Customer WHERE Customer.CustomerId NOT IN (SELECT Invoice.CustomerId FROM chinook.Invoice) ORDER BY Customer.LastName ASC, Customer.FirstName ASC;SELECT Invoice.BillingCountry, COUNT(Invoice.InvoiceId) AS InvoiceCount, ROUND(SUM(Invoice.Total), 2) AS TotalRevenue, CASE WHEN SUM(Invoice.Total) > 100 THEN 'High' WHEN SUM(Invoice.Total) >= 50 THEN 'Medium' ELSE 'Low' END AS RevenueTier FROM chinook.Invoice GROUP BY Invoice.BillingCountry ORDER BY TotalRevenue DESC;