Skip to content

Instantly share code, notes, and snippets.

@coderbyheart
Last active October 22, 2022 10:33
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save coderbyheart/77379f1e99455525a1093fb241c14481 to your computer and use it in GitHub Desktop.
Save coderbyheart/77379f1e99455525a1093fb241c14481 to your computer and use it in GitHub Desktop.
Chinook Database Excercises

Fragen Sie auf der Chinook Datenbank die folgenden Daten ab: (Download unter https://www.sqlitetutorial.net/sqlite-sample-database/)

  1. Welche Musikgenres sind in der Datenbank hinterlegt?
  2. Alle IDs, Namen und Geburtsdaten der Mitarbeiter alphabetisch nach Nachname, Vorname sortiert.
  3. Die Titel des Albums „Master Of Puppets“.
  4. Alle Kunden, die Ihre E-Mail-Adresse bei gmail.com haben
  5. Die ID, der vollständige Name und E-Mail-Adresse des Kunden mit den meisten Rechnungen
    1. Erweiterung: mit dem meisten Umsatz
  1. Provide a query showing Customers (just their full names, customer ID and country) who are not in the US.
  2. Provide a query only showing the Customers from Brazil.
  3. Provide a query showing the Invoices of customers who are from Brazil. The resultant table should show the customer's full name, Invoice ID, Date of the invoice and billing country.
  4. Provide a query showing only the Employees who are Sales Agents.
  5. Provide a query showing a unique/distinct list of billing countries from the Invoice table.
  6. Provide a query that shows the invoices associated with each sales agent. The resultant table should include the Sales Agent's full name.
  7. Provide a query that shows the Invoice Total, Customer name, Country and Sale Agent name for all invoices and customers.
  8. How many Invoices were there in 2009 and 2011?
  9. What are the respective total sales for each of those years?
  10. Looking at the InvoiceLine table, provide a query that COUNTs the number of line items for Invoice ID 37.
  11. Looking at the InvoiceLine table, provide a query that COUNTs the number of line items for each Invoice. HINT: GROUP BY
  12. Provide a query that includes the purchased track name with each invoice line item.
  13. Provide a query that includes the purchased track name AND artist name with each invoice line item.
  14. Provide a query that shows the # of invoices per country. HINT: GROUP BY
  15. Provide a query that shows the total number of tracks in each playlist. The Playlist name should be include on the resulant table.
  16. Provide a query that shows all the Tracks, but displays no IDs. The result should include the Album name, Media type and Genre.
  17. Provide a query that shows all Invoices but includes the # of invoice line items.
  18. Provide a query that shows total sales made by each sales agent.
  19. Which sales agent made the most in sales in 2009?
  20. Which sales agent made the most in sales over all? (Hint: Use the MAX function on a subquery.)
  21. Provide a query that shows the count of customers assigned to each sales agent.
  22. Provide a query that shows the total sales per country.
  23. Which country's customers spent the most?
  24. Provide a query that shows the most purchased track of 2013.
  25. Provide a query that shows the top 5 most purchased tracks over all.
  26. Provide a query that shows the top 3 best selling artists.
  27. Provide a query that shows the most purchased Media Type.# sqlite-assignment-chinook
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment