Last Updated on
Sql Project 1

Database: http://chinookdatabase.codeplex.com/wikipage?title=Chinook_Schema
GitHub: URL

Introduction

In this project, I use Structured Query Language (SQL) to extract and analyze data stored in Chinook Database that holds information about a music store. In this project, I used SQL queries to create the visualization data images for assisting the Chinook team with understanding of their customers from the highest order volume country of total invoice and money of each country spent on the album to the order volume by Genre and many more.

Sql Project 2
SELECT
  Customer.Country AS Country,
  Total(Invoice.Total) AS "Total Sales"
FROM Invoice
INNER JOIN Customer
  ON (BillingCountry IS NOT NULL AND Customer.CustomerId = Invoice.CustomerId)
GROUP BY Country
ORDER BY "Total Sales" DESC
LIMIT 5;
Sql Project 3
SELECT
	Genre.Name AS Genre,
	SUM(CASE WHEN Invoice.BillingCountry = 'USA' THEN 1 ELSE 0 END) AS 'USA',
	SUM(CASE WHEN Invoice.BillingCountry = 'Canada' THEN 1 ELSE 0 END) AS 'Canada',
	SUM(CASE WHEN Invoice.BillingCountry = 'France' THEN 1 ELSE 0 END) AS 'France',
	SUM(CASE WHEN Invoice.BillingCountry = 'Brazil' THEN 1 ELSE 0 END) AS 'Brazil',
	SUM(CASE WHEN Invoice.BillingCountry = 'Germany' THEN 1 ELSE 0 END) AS 'Germany'
FROM InvoiceLine
INNER JOIN Track
  ON InvoiceLine.TrackId = Track.TrackId
INNER JOIN MediaType
	ON Track.MediaTypeId = MediaType.MediaTypeId
INNER JOIN Genre
	ON Track.GenreId = Genre.GenreId
INNER JOIN Invoice
	ON Invoice.InvoiceId = InvoiceLine.InvoiceId
INNER JOIN Customer
	ON Invoice.CustomerId = Customer.CustomerId
GROUP BY Genre ORDER BY Genre;
Sql Project 4
SELECT
  Customer.Country AS BillingCountry,
  AVG(Invoice.Total) AS AverageSpent,
  max(Invoice.Total) AS HighestSpent
FROM Invoice
INNER JOIN Customer
  ON (BillingCountry IS NOT NULL AND Customer.CustomerId = Invoice.CustomerId)
GROUP BY BillingCountry	
ORDER BY AverageSpent;
Sql Project 5
SELECT
	Invoice.BillingCountry AS Country,
	SUM(CASE WHEN MediaType.MediaTypeId = '1' THEN 1 ELSE 0 END) AS 'MPEG audio file',
	SUM(CASE WHEN MediaType.MediaTypeId = '2' THEN 1 ELSE 0 END) AS 'Protected AAC audio file',
	SUM(CASE WHEN MediaType.MediaTypeId = '3' THEN 1 ELSE 0 END) AS 'Protected MPEG-4 video file',
	SUM(CASE WHEN MediaType.MediaTypeId = '4' THEN 1 ELSE 0 END) AS 'Purchased AAC audio file',
	SUM(CASE WHEN MediaType.MediaTypeId = '5' THEN 1 ELSE 0 END) AS 'AAC audio file',
	COUNT(Country) AS 'Total'

FROM InvoiceLine
INNER JOIN Track
	ON InvoiceLine.TrackId = Track.TrackId
INNER JOIN MediaType
	ON Track.MediaTypeId = MediaType.MediaTypeId
INNER JOIN Genre
	ON Track.GenreId = Genre.GenreId
INNER JOIN Invoice
	ON Invoice.InvoiceId = InvoiceLine.InvoiceId
INNER JOIN Customer
	ON Invoice.CustomerId = Customer.CustomerId
GROUP BY Country ORDER BY "Total" DESC
LIMIT 5;

Spread Your Love By Sharing It On:
Write With ❤️ By Jorcus