Final SQL Project

# 11 tables used for SQL queries

# 01 - albums
CREATE TABLE albums (
AlbumId INTEGER PRIMARY KEY AUTOINCREMENT
NOT NULL,
Title NVARCHAR (160) NOT NULL,
ArtistId INTEGER NOT NULL,
FOREIGN KEY (
ArtistId
)
REFERENCES artists (ArtistId) ON DELETE NO ACTION
ON UPDATE NO ACTION
);

# 02 - artists
CREATE TABLE artists (
ArtistId INTEGER PRIMARY KEY AUTOINCREMENT
NOT NULL,
Name NVARCHAR (120)
);

# 03 - customers
CREATE TABLE customers (
CustomerId INTEGER PRIMARY KEY AUTOINCREMENT
NOT NULL,
FirstName NVARCHAR (40) NOT NULL,
LastName NVARCHAR (20) NOT NULL,
Company NVARCHAR (80),
Address NVARCHAR (70),
City NVARCHAR (40),
State NVARCHAR (40),
Country NVARCHAR (40),
PostalCode NVARCHAR (10),
Phone NVARCHAR (24),
Fax NVARCHAR (24),
Email NVARCHAR (60) NOT NULL,
SupportRepId INTEGER,
FOREIGN KEY (
SupportRepId
)
REFERENCES employees (EmployeeId) ON DELETE NO ACTION
ON UPDATE NO ACTION
);

# 04 - employees
CREATE TABLE employees (
EmployeeId INTEGER PRIMARY KEY AUTOINCREMENT
NOT NULL,
LastName NVARCHAR (20) NOT NULL,
FirstName NVARCHAR (20) NOT NULL,
Title NVARCHAR (30),
ReportsTo INTEGER,
BirthDate DATETIME,
HireDate DATETIME,
Address NVARCHAR (70),
City NVARCHAR (40),
State NVARCHAR (40),
Country NVARCHAR (40),
PostalCode NVARCHAR (10),
Phone NVARCHAR (24),
Fax NVARCHAR (24),
Email NVARCHAR (60),
FOREIGN KEY (
ReportsTo
)
REFERENCES employees (EmployeeId) ON DELETE NO ACTION
ON UPDATE NO ACTION
);

# 05 - genres
CREATE TABLE genres (
GenreId INTEGER PRIMARY KEY AUTOINCREMENT
NOT NULL,
Name NVARCHAR (120)
);

# 06 - invoice_items
CREATE TABLE invoice_items (
InvoiceLineId INTEGER PRIMARY KEY AUTOINCREMENT
NOT NULL,
InvoiceId INTEGER NOT NULL,
TrackId INTEGER NOT NULL,
UnitPrice NUMERIC (10, 2) NOT NULL,
Quantity INTEGER NOT NULL,
FOREIGN KEY (
InvoiceId
)
REFERENCES invoices (InvoiceId) ON DELETE NO ACTION
ON UPDATE NO ACTION,
FOREIGN KEY (
TrackId
)
REFERENCES tracks (TrackId) ON DELETE NO ACTION
ON UPDATE NO ACTION
);

# 07 - invoices
CREATE TABLE invoices (
InvoiceId INTEGER PRIMARY KEY AUTOINCREMENT
NOT NULL,
CustomerId INTEGER NOT NULL,
InvoiceDate DATETIME NOT NULL,
BillingAddress NVARCHAR (70),
BillingCity NVARCHAR (40),
BillingState NVARCHAR (40),
BillingCountry NVARCHAR (40),
BillingPostalCode NVARCHAR (10),
Total NUMERIC (10, 2) NOT NULL,
FOREIGN KEY (
CustomerId
)
REFERENCES customers (CustomerId) ON DELETE NO ACTION
ON UPDATE NO ACTION
);

# 08 - media_types
CREATE TABLE media_types (
MediaTypeId INTEGER PRIMARY KEY AUTOINCREMENT
NOT NULL,
Name NVARCHAR (120)
);

# 09 - playlist_track
CREATE TABLE playlist_track (
PlaylistId INTEGER NOT NULL,
TrackId INTEGER NOT NULL,
CONSTRAINT PK_PlaylistTrack PRIMARY KEY (
PlaylistId,
TrackId
),
FOREIGN KEY (
PlaylistId
)
REFERENCES playlists (PlaylistId) ON DELETE NO ACTION
ON UPDATE NO ACTION,
FOREIGN KEY (
TrackId
)
REFERENCES tracks (TrackId) ON DELETE NO ACTION
ON UPDATE NO ACTION
);

# 10 - playlists
CREATE TABLE playlists (
PlaylistId INTEGER PRIMARY KEY AUTOINCREMENT
NOT NULL,
Name NVARCHAR (120)
);

# 11 - tracks
CREATE TABLE tracks (
TrackId INTEGER PRIMARY KEY AUTOINCREMENT
NOT NULL,
Name NVARCHAR (200) NOT NULL,
AlbumId INTEGER,
MediaTypeId INTEGER NOT NULL,
GenreId INTEGER,
Composer NVARCHAR (220),
Milliseconds INTEGER NOT NULL,
Bytes INTEGER,
UnitPrice NUMERIC (10, 2) NOT NULL,
FOREIGN KEY (
AlbumId
)
REFERENCES albums (AlbumId) ON DELETE NO ACTION
ON UPDATE NO ACTION,
FOREIGN KEY (
GenreId
)
REFERENCES genres (GenreId) ON DELETE NO ACTION
ON UPDATE NO ACTION,
FOREIGN KEY (
MediaTypeId
)
REFERENCES media_types (MediaTypeId) ON DELETE NO ACTION
ON UPDATE NO ACTION
);

# SQL QUERIES

# 1 - List all the tracks from the album "Killers"
SELECT chinook.tracks.Name,
       chinook.albums.Title
  FROM chinook.tracks
       JOIN
       chinook.albums ON chinook.tracks.AlbumId = chinook.albums.AlbumId
 WHERE chinook.albums.Title = 'Killers';


# 2 - Show all genres available in the store
SELECT DISTINCT Name
  FROM chinook.genres;

# 3 - Find customers who live in California
SELECT FirstName,
       LastName,
       City,
       State
  FROM chinook.customers
 WHERE State = 'CA';

# 4 - List employees, along with the name of the employee they report to
SELECT emp.FirstName AS EmployeeFirstName,
       emp.LastName AS EmployeeLastName,
       mgr.FirstName AS ManagerFirstName,
       mgr.LastName AS ManagerLastName
  FROM chinook.employees AS emp
       LEFT JOIN
       chinook.employees AS mgr ON emp.ReportsTo = mgr.EmployeeId;

# 5 - What is the most expensive track to purchase?
SELECT Name,
       UnitPrice
  FROM chinook.tracks
 ORDER BY UnitPrice DESC
 LIMIT 1;

# 6 - How many customers are in each country?
SELECT Country,
       COUNT( * ) AS CustomerCount
  FROM chinook.customers
 GROUP BY Country
 ORDER BY CustomerCount DESC;

# 7 - Which genre has generated the most revenue?
SELECT 
    genres.Name AS Genre, 
    SUM(invoice_items.UnitPrice * invoice_items.Quantity) AS TotalRevenue
FROM chinook.genres
JOIN chinook.tracks ON genres.GenreId = tracks.GenreId
JOIN chinook.invoice_items ON tracks.TrackId = invoice_items.TrackId
GROUP BY genres.GenreId 
ORDER BY TotalRevenue DESC;

# 8 - List all customers and the playlists they've created
SELECT
    customers.FirstName, 
    customers.LastName, 
    playlists.Name AS PlaylistName 
FROM chinook.customers
JOIN chinook.playlists ON customers.CustomerId = playlists.PlaylistId;

# 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 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],
       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.Genre

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