Chinook Analysis

# For this SQL, I am using the Chinook database which contains 11 tables. I queried the database to find the relations between tables and learn more about the database. 

#1. Show Customers with their full names, customer ID, and country that is not in the US. 
SELECT Customerid, FirstName, LastName, country
FROM chinook.customers
Where country != "USA";

#2. Show only the Customers from Canada.
SELECT *
FROM chinook.customers
Where country = "Canada";

#3. Find the Invoices of customers who are from Canada. The resulting table should show the customer's full name, Invoice ID, Date of the invoice, and billing country.
SELECT invoices.invoiceid, invoices.invoicedate, invoices.billingcountry, 
customers.firstname,customers.lastname
FROM chinook.invoices invoices

LEFT JOIN chinook.customers customers
ON invoices.Customerid = customers.Customerid
WHERE invoices.billingcountry = "Canada"

#4. Show the Employees who are Sales Agents.
SELECT *
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. The resulting table should include the Sales Agent's full name.
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. 7.	Show the Invoice Total, Customer name, Country, and Sales Agent name for all invoices and customers.
SELECT emp.firstname as salesagentfirst, emp.lastname as salesagentlast, 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 ID.
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 ID.
SELECT t.name AS Track, art.name AS Artist, i.invoicelineid
FROM chinook.invoice_items i

LEFT JOIN chinook.tracks t 
ON t.trackid = i.trackid

INNER JOIN chinook.albums alb 
ON alb.albumid = t.albumid 

LEFT JOIN chinook.artists art
ON art.artistid = alb.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', a.title as 'Album Name', 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 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;


#15.	What were the total sales made in the first 6 months of 2010?
SELECT ROUND(SUM(total),2)
FROM chinook.invoices 
WHERE invoicedate BETWEEN '2010-01-01' AND '2010-6-30';


#16. 	Write a query that shows track name and playlists. 
SELECT t.name as 'Track Name', play.name as 'Playlist'
FROM chinook.tracks t

JOIN chinook.playlist_track pt 
ON pt.trackid = t.trackid

JOIN chinook.playlists play 
ON play.playlistid = pt.playlistid
GROUP BY t.name


#17. Who was the most recent hired employee, with their name, title, and hire date. 
SELECT firstname, lastname, title, MAX(hiredate) AS 'Hire Date'
FROM chinook.employees;