SQL Project

--For this project, I downloaded a dataset called chinook to SQLiteStudio
--The following SQL was written to answer specific questions about the content on chinook:

/* 1. Show Customers (their full names, customer ID, and country) who are not in the US.*/
SELECT FirstName || ' ' || LastName AS FullName,
CustomerId, Country
FROM Chinook.Customers
WHERE Country <> 'USA';

/* 2. Show only the Customers from Brazil.*/
SELECT *
FROM chinook.Customers
WHERE Country = "Brazil";

/* 3. Find the Invoices of customers who are from Brazil. The resulting table should show the customers full name, Invoice ID, Date of the invoice, and billing country.*/
SELECT cust.FirstName, cust.LastName,
inv.InvoiceId, inv.InvoiceDate, inv.BillingCountry
FROM chinook.Invoices inv
JOIN chinook.Customers cust
ON inv.CustomerID=cust.CustomerID
WHERE inv.BillingCountry = "Brazil";

*/ 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 Agents full name.*/
SELECT emp.FirstName, emp.LastName, inv.Invoiceid
FROM chinook.Employees emp
JOIN chinook.Customers cust
ON emp.EmployeeID=cust.SupportRepID
JOIN chinook.Invoices inv
ON cust.CustomerID=inv.CustomerID;


/* 7. Show the Invoice Total, Customer name, Country, and Sales Agent name for all invoices and customers.*/
SELECT cust.FirstName, cust.LastName, cust.Country,
emp.FirstName, emp.Lastname, 
inv.Total
FROM chinook.Employees emp
JOIN chinook.Customers cust
ON emp.EmployeeID=cust.SupportRepID
JOIN chinook.Invoices inv
ON cust.CustomerID=inv.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 ROUND(sum(total), 2) AS "Total Sales"
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 t.TrackId=i.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.artists ar
LEFT JOIN chinook.Albums a
ON ar.ArtistID=a.ArtistID
INNER JOIN chinook.Tracks t
ON a.AlbumID=t.AlbumID
LEFT JOIN chinook.Invoice_Items i
ON t.TrackID=i.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 t.AlbumID=a.AlbumID

JOIN chinook.Media_Types m
ON t.MediaTypeID=m.MediaTypeID

JOIN chinook.Genres g
ON t.GenreID=g.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;