project sql

-- Final SQL Project: Example Queries on the Chinook Database

-- 1. Customers not located in the USA
SELECT FirstName, LastName, CustomerId, Country 
FROM chinook.customers 
WHERE Country != 'USA';

-- 2. All customers from Brazil
SELECT * FROM chinook.customers 
WHERE Country = 'Brazil';

-- 3. Brazilian customers with invoice details
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. List of Sales Support Agents
SELECT * FROM chinook.Employees
WHERE Title = 'Sales Support Agent';

-- 5. All unique billing countries from invoices
SELECT DISTINCT BillingCountry 
FROM chinook.Invoices;

-- 6. Employee names with their associated invoice IDs
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. Employee and customer details with invoice totals
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. Number of invoices from 2009
SELECT COUNT(*) 
FROM chinook.Invoices
WHERE InvoiceDate BETWEEN '2009-01-01' AND '2009-12-31';

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

-- 10. Track names with their invoice line IDs
SELECT t.Name, i.InvoiceLineId
FROM chinook.Invoice_items i
JOIN chinook.Tracks t ON i.TrackId = t.TrackId;

-- 11. Artist, track name, and invoice line ID details
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. Track details including album, 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. Total sales per Sales Support 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. Top-selling Sales Support Agent 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;