Final SQL Project

#1. Show customers who are not in the USA.
SELECT FirstName, LastName, CustomerId, Country
FROM chinook.customers
WHERE Country <> 'USA';

#2. Show only customers from Brazil.
SELECT FirstName, LastName, CustomerId, Country
FROM chinook.customers
WHERE Country = 'Brazil';

#3. Show invoices of customers who are from Brazil. Show full name of customer, InvoiceId, date of invoice, and billing country.
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';

#4. Show the employees who are sales agents.
SELECT FirstName, LastName, Title
FROM chinook.employees
WHERE Title = 'Sales Support Agent';

#5. Find a unique/distinct list of billing countries from the Invoice table.
SELECT DISTINCT BillingCountry
FROM chinook.invoices;

#6. Provide a query that shows the invoices associated with each sales agent. Include full name of Sales Agent.
SELECT emp.FirstName, emp.LastName, inv.InvoiceId
FROM chinook.employees emp
JOIN chinook.customers cust ON cust.SupportRepId = emp.EmployeeId
JOIN chinook.invoices Inv ON Inv.CustomerId = cust.CustomerId;

#7. Show the Invoice Total, Customer Name, Country, and Sales Agent name for all invoices and customers.
SELECT inv.Total, cust.FirstName, cust.LastName, cust.Country, emp.FirstName, emp.LastName
FROM chinook.employees emp
JOIN chinook.customers cust ON cust.SupportRepId = emp.EmployeeId
JOIN chinook.invoices Inv ON Inv.CustomerId - cust.CustomerId;

#8. How many invoices were there in 2009.
SELECT COUNT(InvoiceId)
FROM chinook.invoices
WHERE InvoiceDate BETWEEN '2009-01-01' AND '2009-12-01';

#9. What are the total sales for 2009.
SELECT SUM(Total)
FROM chinook.invoices
WHERE InvoiceDate BETWEEN '2009-01-01' AND '2009-12-01';

#10. Write a query that includes the purchased track name with each invoice line Id.
SELECT t.Name, t.TrackId, i.InvoiceLineId
FROM chinook.invoice_items i
JOIN chinook.tracks t ON i.TrackId = t.TrackId;

#11. Write a query that includes the purchased track name AND artist name with each invoice line Id.
SELECT ar.name AS Artist, t.Name AS Track, i.InvoiceLineId
FROM chinook.Invoice_items i
LEFT JOIN chinook.tracks t 
ON i.TrackID=t.TrackID
INNER JOIN chinook.albums a
ON a.AlbumID=t.AlbumID
LEFT JOIN chinook.artists ar
ON ar.ArtistID=a.ArtistID;

#12. Provide a query that shows all the Tracks AND include the Album name, Media type, and Genre.
SELECT t.Name AS 'Track Name', t.GenreId AS 'Genre', t.MediaTypeId AS 'Media Type', a.Title AS 'Album Title'
FROM chinook.tracks t
JOIN chinook.albums a ON t.AlbumId = a.AlbumId
JOIN chinook.media_types m ON m.MediaTypeId = t.MediaTypeId
JOIN chinook.genres g ON g.GenreId = t.GenreId;

#13. 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;

#14.Which Sales Agent made the most dollars 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;