As part of a music store database analysis project, I utilized SQL queries to extract valuable insights from a relational database.
- Employee Management: The database includes employee information such as employee ID, name, title, contact details, and hierarchy.
- Customer Analysis: The customer table stores customer details, including their ID, name, company, contact information, and support representative ID.
- Invoice Tracking: The database allows tracking invoices, including invoice ID, customer ID, invoice date, billing address, and total amount.
- Track and Genre Information: Tracks are categorized by genre, and the database provides details about each track's name, album ID, media type ID, composer, duration, and price.
- Playlist Creation: Playlists can be created and managed, associating tracks with specific playlists.
- Artist and Album Details: The database maintains information about artists, including their ID and name, and albums, including album ID, title, and associated artist ID.
The following data analysis tasks were performed on the music store database:
- Employee Count: Determined the total number of employees in the organization.
- Senior Most Employee: Identified the senior most employee based on job title and hierarchy.
- Total Sales Amount: Calculated the total sales amount from invoices.
- Top Invoice Countries: Identified countries with the highest number of invoices.
- Top Invoice Amounts: Determined the top values of total invoices.
- Customer Distribution: Analyzed customer distribution by country.
- Best Customer: Identified the customer who spent the most money.
- Popular Music Genre: Determined the most popular music genre based on track counts.
- Rock Music Listeners: Listed customers who enjoy rock music, along with their contact details.
- Top Rock Bands: Identified the top 10 rock bands based on the number of tracks.
- Longest Tracks: Listed tracks with song lengths longer than the average song length.
- Customer Spending on Artists: Calculated the amount spent by each customer on artists.
- Popular Genre by Country: Identified the most popular genre for each country based on purchases.
In conclusion, the analysis of the music store database revealed several key findings:
- The highest level of employee hierarchy consists of only one employee.
- The total number of employees in the organization is just nine.
- The majority of customers belong to the United States of America.
- Prague is the city with the highest sum of invoices, totaling 273.24.
- Rock emerged as the most popular music genre among customers.
- AC/DC stands out as the band that has written the most rock music in the database.
- Utilized SQL queries to analyze a music store database, extracting valuable insights on employee hierarchy, customer behavior, sales trends, and popular music genres.
- Leveraged data analysis skills to identify the best customer, calculate total sales, determine top rock bands, and analyze customer spending on artists, showcasing proficiency in SQL and database management.