<repo_name>NSS-Day-Cohort-42/chinook-skratz17<file_sep>/queries/invoice_totals.sql
/*
Provide a query that shows the Invoice Total, Customer name, Country and Sale Agent name for all invoices and customers.
*/

SELECT i.Total, c.FirstName as CustomerFirstName, c.LastName as CustomerLastName, i.BillingCountry, e.FirstName as EmployeeFirstName, e.LastName as EmployeeLastName
FROM Invoice i 
JOIN Customer c
ON i.CustomerId = c.CustomerId
JOIN Employee e
ON c.SupportRepId = e.EmployeeId;<file_sep>/queries/total_sales_2011.sql
/*
What are the respective total sales for each of those years?
*/
SELECT ROUND(SUM(Total), 2) as YearSales
FROM Invoice
WHERE InvoiceDate >= '2011-01-01' AND InvoiceDate <= '2011-12-31';<file_sep>/queries/total_sales_2009.sql
/*
What are the respective total sales for each of those years?
*/
SELECT ROUND(SUM(Total), 2) as YearSales
FROM Invoice
WHERE InvoiceDate >= '2009-01-01' AND InvoiceDate <= '2009-12-31';<file_sep>/queries/sales_agent_customer_count.sql
/*
Provide a query that shows the count of customers assigned to each sales agent.
*/
SELECT e.EmployeeId, e.FirstName || ' ' || e.LastName as FullName, COUNT(*) as CustomerCount
FROM Employee e
JOIN Customer c
ON c.SupportRepId = e.EmployeeId
GROUP BY e.EmployeeId;<file_sep>/queries/country_invoices.sql
/*
Provide a query that shows the # of invoices per country.
*/
SELECT InvoiceId, BillingCountry, COUNT(*) As CountryCount
FROM Invoice
GROUP BY BillingCountry;<file_sep>/queries/top_5_tracks.sql
/*
Provide a query that shows the top 5 most purchased tracks over all.
*/
SELECT t.Name, SUM(il.Quantity) as PurchaseCount
FROM Track t
JOIN InvoiceLine il
ON il.TrackId = t.TrackId
GROUP BY t.TrackId
ORDER BY PurchaseCount DESC
LIMIT 5;<file_sep>/queries/line_item_track.sql
/*
Provide a query that includes the purchased track name with each invoice line item.
*/
SELECT il.*, t.Name
FROM InvoiceLine il
JOIN Track t 
ON il.TrackId = t.TrackId;
<file_sep>/queries/playlists_track_count.sql
/*
Provide a query that shows the total number of tracks in each playlist. The Playlist name should be include on the resulant table.
*/

SELECT p.PlaylistId, p.Name, Count(*)
FROM Playlist p
JOIN PlaylistTrack pt
ON pt.PlaylistId = p.PlaylistId
GROUP BY p.PlaylistId;<file_sep>/queries/top_agent.sql
/*
Which sales agent made the most in sales over all?
*/
SELECT FullName, MAX(TotalSales)
FROM (
  SELECT e.FirstName || ' ' || e.LastName as FullName, ROUND(SUM(i.Total), 2) as TotalSales
  FROM Employee e
  JOIN Customer c
  ON c.SupportRepId = e.EmployeeId
  JOIN Invoice i
  ON i.CustomerId = c.CustomerId
  GROUP BY e.EmployeeId
);<file_sep>/queries/sales_per_country.sql
/*
Provide a query that shows the total sales per country.
*/
SELECT BillingCountry, ROUND(SUM(Total), 2) as TotalSales
FROM Invoice
GROUP BY BillingCountry;<file_sep>/queries/top_media_type.sql
/*
Provide a query that shows the most purchased Media Type.
*/
SELECT MediaTypeName, MAX(MediaTypeCount) as QuantitySold
FROM (
  SELECT m.Name as MediaTypeName, SUM(il.Quantity) as MediaTypeCount
  FROM MediaType m
  JOIN Track t
  ON t.MediaTypeId = m.MediaTypeId
  JOIN InvoiceLine il
  ON il.TrackId = t.TrackId
  GROUP BY m.MediaTypeId
);<file_sep>/queries/total_invoices_2011.sql
/*
How many Invoices were there in 2009 and 2011?
*/
SELECT * FROM Invoice
WHERE InvoiceDate >= '2011-01-01' AND InvoiceDate <= '2011-12-31';<file_sep>/queries/tracks_no_id.sql
/*
Provide a query that shows all the Tracks, but displays no IDs. The result should include the Album name, Media type and Genre.
*/
SELECT t.Name as TrackName, a.Title as AlbumName, m.Name as MediaTypeName, g.Name as GenreName
FROM Track t
JOIN Album a
ON t.AlbumId = a.AlbumId
JOIN MediaType m
ON t.MediaTypeId = m.MediaTypeId
JOIN Genre g
ON t.GenreId = g.GenreId;<file_sep>/queries/top_2009_agent.sql
/*
Provide a query that shows total sales made by each sales agent.
*/
SELECT e.EmployeeId, e.FirstName || ' ' || e.LastName as EmployeeFullName, SUM(i.Total) as TotalSales
FROM Employee e
JOIN Customer c
ON c.SupportRepId = e.EmployeeId
JOIN Invoice i
ON i.CustomerId = c.CustomerId
WHERE Title = 'Sales Support Agent'
AND i.InvoiceDate >= '2009-01-01' AND i.InvoiceDate <= '2009-12-31'
GROUP BY e.EmployeeId
ORDER BY TotalSales DESC
LIMIT 1;

/*
Provide a query that shows total sales made by each sales agent (using max + subquery).
*/
SELECT EmployeeId, EmployeeFullName, MAX(TotalSales)
FROM (
  SELECT e.EmployeeId, e.FirstName || ' ' || e.LastName as EmployeeFullName, SUM(i.Total) as TotalSales
  FROM Employee e
  JOIN Customer c
  ON c.SupportRepId = e.EmployeeId
  JOIN Invoice i
  ON i.CustomerId = c.CustomerId
  WHERE Title = 'Sales Support Agent'
  AND i.InvoiceDate >= '2009-01-01' AND i.InvoiceDate <= '2009-12-31'
  GROUP BY e.EmployeeId
);<file_sep>/queries/invoice_37_line_item_count.sql
/*
Looking at the InvoiceLine table, provide a query that COUNTs the number of line items for Invoice ID 37.
*/
SELECT COUNT(*) as Invoice37LineItems
FROM Invoice i
JOIN InvoiceLine il
ON il.InvoiceId = i.InvoiceId
WHERE i.InvoiceId = 37;<file_sep>/README.md
# Chinook

SQL questions.

# ERD

<img src="erd.png"><file_sep>/queries/invoices_line_item_count.sql
/*
Provide a query that shows all Invoices but includes the # of invoice line items.
*/
SELECT i.*, COUNT(*) as LineItemCount
FROM Invoice i
JOIN InvoiceLine il
ON il.InvoiceId = i.InvoiceId
GROUP BY i.InvoiceId;<file_sep>/queries/top_2013_track.sql
/*
Provide a query that shows the most purchased track of 2013.
*/
SELECT Name, MAX(PurchaseCount)
FROM (
  SELECT t.Name, SUM(il.Quantity) as PurchaseCount
  FROM Track t
  JOIN InvoiceLine il
  ON il.TrackId = t.TrackId
  JOIN Invoice i
  ON il.InvoiceId = i.InvoiceId
  WHERE i.InvoiceDate >= '2013-01-01' AND i.invoiceDate <= '2013-12-31'
  GROUP BY t.TrackId
);<file_sep>/queries/total_invoices_2009.sql
/*
How many Invoices were there in 2009 and 2011?
*/
SELECT * FROM Invoice
WHERE InvoiceDate >= '2009-01-01' AND InvoiceDate <= '2009-12-31';<file_sep>/queries/top_3_artists.sql
/*
Provide a query that shows the top 3 best selling artists.
*/
SELECT a.Name, SUM(il.Quantity) as PurchaseCount
FROM Artist a
JOIN Album al
ON al.ArtistId = a.ArtistId
JOIN Track t
ON t.AlbumId = al.AlbumId
JOIN InvoiceLine il
ON il.TrackId = t.TrackId
GROUP BY a.ArtistId
ORDER BY PurchaseCount DESC
LIMIT 3;<file_sep>/queries/line_items_per_invoice.sql
/*
Looking at the InvoiceLine table, provide a query that COUNTs the number of line items for each Invoice.
*/
SELECT InvoiceId, COUNT(*) as LineItemsCount
FROM InvoiceLine
GROUP BY InvoiceLine.InvoiceId;<file_sep>/queries/line_item_track_artist.sql
/*
Provide a query that includes the purchased track name AND artist name with each invoice line item.
*/
SELECT il.InvoiceLineId, t.Name as TrackName, ar.Name as ArtistName
FROM InvoiceLine il
JOIN Track t 
ON il.TrackId = t.TrackId
JOIN Album al
ON t.AlbumId = al.AlbumId
JOIN Artist ar
ON al.ArtistId = ar.ArtistId;<file_sep>/queries/top_country.sql
/*
Which country's customers spent the most?
*/
SELECT BillingCountry, MAX(TotalSales)
FROM (
  SELECT BillingCountry, ROUND(SUM(Total), 2) as TotalSales
  FROM Invoice
  GROUP BY BillingCountry
);<file_sep>/queries/sales_agent_invoices.sql
/*
Provide a query that shows the invoices associated with each sales agent. The resultant table should include the Sales Agent's full name.
*/

SELECT e.FirstName, e.LastName, i.InvoiceId
FROM Employee e   
JOIN Customer c
ON e.EmployeeId = c.SupportRepId

JOIN Invoice i   
ON i.CustomerId = c.CustomerId;<file_sep>/queries/sales_agent_total_sales.sql
/*
Provide a query that shows total sales made by each sales agent.
*/
SELECT e.EmployeeId, e.FirstName || ' ' || e.LastName as EmployeeFullName, ROUND(SUM(i.Total), 2) as TotalSales
FROM Employee e
JOIN Customer c
ON c.SupportRepId = e.EmployeeId
JOIN Invoice i
ON i.CustomerId = c.CustomerId
WHERE Title = 'Sales Support Agent'
GROUP BY e.EmployeeId;