Chinook project

##In this SQL, I am querying a database with multiple tables to quantify statistics about 
customers, employees info and sales, and billing/invoice info.  

#1. Show customers who are not in the US.
SELECT FirstName, LastName, CustomerId, Country 
FROM chinook.customers 
WHERE Country != "USA";

#2. Show only customers from Brazil. 
SELECT * FROM chinook.customers 
WHERE Country = "Brazil" ;

#3. Find the Invoices of customers who are from 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";

#4. Show employees who are Sales Agents.
SELECT * FROM chinook.Employees
WHERE Title = "Sales Support Agent";

#5. Find a distinct list of billing countires from the Invoice table.
SELECT DISTINCT BillingCountry 
FROM chinook.Invoices;

#6. Provide a query that shows  the Invoices associated with each sales agent. Include agents full name.
SELECT emp.LastName, emp.Firstname, 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's name, Country, and Sales Agent namefor 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;

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

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

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

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

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

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