Invoices by Year (chinook database)

Your main task in this lab is to craft SQL queries to extract specific data from the Chinook database. You should be able to understand the database schema, apply various SQL techniques, and come up with the required solution.

Database Schema

Here's a brief rundown of the tables and columns you will be working with:

• "Artist": Contains "ArtistId" and "Name" columns.
• "Album": Important columns include "AlbumId", "Title", and "ArtistId".
• "Track": Some key columns are "TrackId", "Name", "AlbumId", and "InvoiceDate".
• "Invoice": The main columns to note are "InvoiceId", and "InvoiceDate".

Instructions

1. Stick to the SQLite syntax as we are working with the SQLite version of the Chinook database.
2. Ensure your queries are optimized for performance.
3. Always use double quotes for table and column names for clarity, like "TableName" or "ColumnName".
4. Properly format and structure your SQL for readability.
5. Always include an "ORDER BY" clause where specified to ensure consistent and correct output.

Challenge

Objective: Your task is to count the number of invoices generated each year from the Invoice table.

1. Expected Output:

   • Columns:
     • Year: Extracted from the InvoiceDate column representing the year the invoices were generated. (Hint: Use string functions to extract the year part).
     • InvoiceCount: Represents the total number of invoices generated in that specific year.

2. Order: Ensure your results are sorted by the Year column in ascending order.

3. SQL Components to Use:

   • substr() function to extract the year from the InvoiceDate column.
   • GROUP BY clause to group results by year.
   • COUNT() function to count the invoices per year.
   • ORDER BY clause to sort the results by year.

Evaluation:

• Ensure your query returns the correct counts for each year.
• While the primary requirement is the SQLite syntax, try to keep your queries optimized.
• Your solution should be clear and easily understandable, with proper SQL formatting.