Final SQL Project - BIT

/** We're going to work with a dataset called chinook, which represents a digital media store and includes tables for artists, albums, media tracks, invoices, and customers. This is the final SQL project from Break Into Tech.  This project was interesting and stretched my skills into a real world application**/

/** 1. Show Customers (their full names, customer ID, and country) who are not in the US. (Hint: != or <> can be used to say "is not equal to").
Show only the Customers from Brazil.**/

SELECT  FirstName, LastName, CustomerId, Country
FROM chinook.customers 
WHERE Country !="USA";

/** 2. Find the Invoices of customers who are from Brazil. The resulting table should show the customer's full name, Invoice ID, Date of the invoice, and billing country.
Show the Employees who are Sales Agents.**/

SELECT  FirstName, LastName, Country
FROM chinook.customers 
WHERE Country ="Brazil";

SELECT cust.FirstName, cust.LastName, inv.InvoiceId, inv.BillingCountry, inv.InvoiceDate
FROM chinook.invoices as inv 
LEFT JOIN chinook.customers as cust 
on inv.CustomerId = cust.CustomerId 
WHERE inv.BillingCountry = "Brazil";

SELECT FirstName, LastName, Title
FROM chinook.employees
WHERE title = "Sales Support Agent";

/** 3. Find a unique/distinct list of billing countries from the Invoice table.**/

SELECT DISTINCT BillingCountry
FROM chinook.invoices;

/** 4. Provide a query that shows the invoices associated with each sales agent. The resulting table should include the Sales Agent's full name.
Show the Invoice Total, Customer name, Country, and Sales Agent name for all invoices and customers.**/

SELECT emp.LastName, emp.Firstname, cust.FirstName, cust.LastName, cust.Country, inv.total
From chinook.employees emp
JOIN chinook.Customers cust ON cust.SupportRepId = emp.EmployeeId
JOIN chinook.Invoices Inv ON Inv.CustomerId = cust.CustomerId;

/** 5. How many Invoices were there in 2009?**/

SELECT COUNT (*)
FROM chinook.invoices
WHERE InvoiceDate BETWEEN '2009-01-01' AND '2009-12-18';

/** 6. What are the total sales for 2009?**/

SELECT SUM(Total)
FROM chinook.Invoices
WHERE InvoiceDate BETWEEN '2009-01-01' AND '2009-12-31';

/** 7. Write a query that includes the purchased track name with each invoice line item.**/

SELECT t.Name, i.InvoiceLineId
FROM chinook.Invoice_items i 
JOIN chinook.Tracks t ON i.TrackId = t.TrackId;

/** 8. Write a query that includes the purchased track name AND artist name with each invoice line item.**/

SELECT t.Name, t.Composer, i.InvoiceLineId
FROM chinook.Invoice_items i 
JOIN chinook.Tracks t ON i.TrackId = t.TrackId;

/** 9. Provide a query that shows all the Tracks, and include the Album name, Media type, and Genre.**/

SELECT t.Name AS 'Track Name', a.Title AS 'Album Title', m.Name AS 'Media Type', g.Name AS 'Genre'
FROM chinook.tracks t
JOIN chinook.Albums a on a.AlbumId = t.AlbumId
JOIN chinook.Media_Types m on m.MediaTypeId = t.MediaTypeId
JOIN chinook.Genres g on g.GenreId = t.GenreId;

/**  10. Show the total sales made by each sales agent.*/

SELECT emp.FirstName, emp.LastName,
ROUND(SUM(Inv.Total), 2) as 'Total Sales' 
FROM chinook.Employees emp

JOIN chinook.Customers cust 
ON cust.SupportRepId = emp.EmployeeId

JOIN chinook.Invoices Inv 
ON Inv.CustomerId = cust.CustomerId

WHERE emp.Title = 'Sales Support Agent' 
GROUP BY emp.FirstName;

/** 11. Which sales agent made the most in sales in 2009?**/

SELECT emp.FirstName, emp.LastName,
ROUND(SUM(Inv.Total), 2) as 'Total Sales' 
FROM chinook.Employees emp

JOIN chinook.Customers cust 
ON cust.SupportRepId = emp.EmployeeId

JOIN chinook.Invoices Inv 
ON Inv.CustomerId = cust.CustomerId

WHERE emp.Title = 'Sales Support Agent' 
AND Inv.InvoiceDate LIKE '2009%' 
GROUP BY emp.FirstName
ORDER BY (round(sum(Inv.Total), 2))  DESC 
LIMIT 1;