LAB 7

Practicing SQL Server Queries – Advanced Level

I. Prerequisites
- Database Engine: SQL Server 2016 or later.
- See https://youtu.be/hntVaBUnG44?feature=shared for macOS installation.
- Microsoft SQL Server Management Studio (SSMS) on Windows or Azure Data
Studio (ADS) on macOS.
- SQL file in Lab 7 folder.
- Reference Book: Beginning SQL Server for Developers - Fourth Edition.
II. Content
1. Rerun the db.sql, then data.sql and music.sql queries.
- For this task, refer to the Book on pg. 241.
2. Write the queries and return the result.
- For this task, refer to Chapter 11 of the book.
III. Lab Task
- Given the database, perform these tasks by writing your own SQL queries:
o Provide a query showing the invoices of customers from Brazil. The result table
should show the customer's full name, Invoice ID, Date of the invoice, and billing
country.
SELECT

c.FirstName + ' ' + c.LastName AS FullName,

i.InvoiceId,

i.InvoiceDate,

i.BillingCountry

FROM

Customer c

JOIN

Invoice i ON c.CustomerId = i.CustomerId

WHERE

c.Country = 'Brazil';
 o Provide a query showing only the Employees who are Sales Agents.
SELECT

FirstName + ' ' + LastName AS FullName

FROM

Employee

WHERE

Title = 'Sales Support Agent';

o Provide a query showing a unique list of billing countries from the Invoice table.
SELECT

DISTINCT BillingCountry

FROM

Invoice;
 o Provide a query showing the invoices of customers who are from Brazil.
SELECT

c.FirstName + ' ' + c.LastName AS FullName,

i.InvoiceId,

i.InvoiceDate,

i.BillingCountry

FROM

Customer c

JOIN

Invoice i ON c.CustomerId = i.CustomerId

WHERE

c.Country = 'Brazil';
 o Provide a query that shows the invoices associated with each sales agent. The
resultant table should include the Sales Agent's full name.
SELECT

e.FirstName + ' ' + e.LastName AS SalesAgent,

 i.InvoiceId,

i.InvoiceDate,

i.Total

FROM

Invoice i

JOIN

Customer c ON i.CustomerId = c.CustomerId

JOIN

Employee e ON c.SupportRepId = e.EmployeeId;

 o Provide a query that shows the Invoice Total, Customer name, Country, and Sale
Agent name for all invoices and customers.
SELECT
i.Total,
c.FirstName + ' ' + c.LastName AS CustomerName,
c.Country,
e.FirstName + ' ' + e.LastName AS SalesAgent
FROM
Invoice i
JOIN
Customer c ON i.CustomerId = c.CustomerId
JOIN
Employee e ON c.SupportRepId = e.EmployeeId;
 o How many Invoices were there in 2009 and 2011? What are the respective total
sales for each of those years?
SELECT
YEAR(InvoiceDate) AS Year,
COUNT(*) AS InvoiceCount,
SUM(Total) AS TotalSales
FROM
 Invoice
WHERE
YEAR(InvoiceDate) IN (2009, 2011)
GROUP BY
YEAR(InvoiceDate);

o Looking at the InvoiceLine table, provide a query that counts the number of line
items for Invoice ID 37.
SELECT
COUNT(*) AS LineItemCount
FROM
InvoiceLine
WHERE
InvoiceId = 37;

o Looking at the InvoiceLine table, provide a query that counts the number of line
items for each Invoice.
SELECT
InvoiceId,
COUNT(*) AS LineItemCount
FROM
InvoiceLine
GROUP BY
InvoiceId;

o Provide a query that includes the track name with each invoice line item.
SELECT
il.InvoiceLineId,
il.InvoiceId,
t.Name AS TrackName
FROM
 InvoiceLine il
JOIN
Track t ON il.TrackId = t.TrackId;

o Provide a query that includes the purchased track and artist names with each
invoice line item.
SELECT
il.InvoiceLineId,
il.InvoiceId,
 t.Name AS TrackName,
ar.Name AS ArtistName
FROM
InvoiceLine il
JOIN
Track t ON il.TrackId = t.TrackId
JOIN
Album al ON t.AlbumId = al.AlbumId
JOIN
Artist ar ON al.ArtistId = ar.ArtistId;
 o Provide a query that shows the # of invoices per country.
SELECT
BillingCountry,
COUNT(*) AS InvoiceCount
FROM
Invoice
GROUP BY
BillingCountry;
 o Provide a query that shows the total number of tracks in each playlist. The Playlist
name should be included in the resultant table.
SELECT
p.Name AS PlaylistName,
COUNT(*) AS TrackCount
FROM
Playlist p
JOIN
PlaylistTrack pt ON p.PlaylistId = pt.PlaylistId
GROUP BY
p.Name;
 o Provide a query that shows all the Tracks but displays no IDs. The resultant table
should include the Album name, Media type, and Genre.
SELECT
t.Name AS TrackName,
al.Title AS AlbumName,
mt.Name AS MediaType,
g.Name AS Genre
FROM
Track t
LEFT JOIN
Album al ON t.AlbumId = al.AlbumId
LEFT JOIN
MediaType mt ON t.MediaTypeId = mt.MediaTypeId
LEFT JOIN
Genre g ON t.GenreId = g.GenreId;
 o Provide a query that shows all Invoices but includes the # of invoice line items.
SELECT
i.InvoiceId,
i.InvoiceDate,
i.Total,
COUNT(il.InvoiceLineId) AS LineItemCount
FROM
Invoice i
LEFT JOIN
InvoiceLine il ON i.InvoiceId = il.InvoiceId
GROUP BY
i.InvoiceId, i.InvoiceDate, i.Total;
 o Provide a query that shows the total sales made by each sales agent.
SELECT
e.FirstName + ' ' + e.LastName AS SalesAgent,
SUM(i.Total) AS TotalSales
FROM
Invoice i
JOIN
Customer c ON i.CustomerId = c.CustomerId
JOIN
Employee e ON c.SupportRepId = e.EmployeeId
GROUP BY
e.FirstName, e.LastName;

o Which sales agent made the most in sales in 2009?

SELECT TOP 1
e.FirstName + ' ' + e.LastName AS SalesAgent,
SUM(i.Total) AS TotalSales
FROM
Invoice i
JOIN
Customer c ON i.CustomerId = c.CustomerId
JOIN
Employee e ON c.SupportRepId = e.EmployeeId
WHERE
YEAR(i.InvoiceDate) = 2009
GROUP BY
e.FirstName, e.LastName
ORDER BY
TotalSales DESC;
 o Which sales agent made the most in sales in 2010?
SELECT TOP 1
e.FirstName + ' ' + e.LastName AS SalesAgent,
SUM(i.Total) AS TotalSales
FROM
Invoice i
JOIN
Customer c ON i.CustomerId = c.CustomerId
JOIN
Employee e ON c.SupportRepId = e.EmployeeId
WHERE
YEAR(i.InvoiceDate) = 2010
GROUP BY
e.FirstName, e.LastName
ORDER BY
TotalSales DESC;

o Which sales agent made the most in sales overall?

SELECT TOP 1
e.FirstName + ' ' + e.LastName AS SalesAgent,
SUM(i.Total) AS TotalSales
FROM
Invoice i
JOIN
Customer c ON i.CustomerId = c.CustomerId
JOIN
Employee e ON c.SupportRepId = e.EmployeeId
GROUP BY
e.FirstName, e.LastName
ORDER BY
TotalSales DESC;

o Provide a query that shows the # of customers assigned to each sales agent.
SELECT
e.FirstName + ' ' + e.LastName AS SalesAgent,
COUNT(c.CustomerId) AS CustomerCount
FROM
Customer c
JOIN
Employee e ON c.SupportRepId = e.EmployeeId
GROUP BY
e.FirstName, e.LastName;

o Provide a query that shows the total sales per country. Which country's customers
spent the most?
SELECT
 c.Country,
SUM(i.Total) AS TotalSales
FROM
Invoice i
JOIN
Customer c ON i.CustomerId = c.CustomerId
GROUP BY
c.Country
ORDER BY
TotalSales DESC;

o Provide a query that shows the most purchased track of 2013.

SELECT TOP 1
t.Name AS TrackName,
COUNT(il.InvoiceLineId) AS PurchaseCount
FROM
InvoiceLine il
JOIN
Invoice i ON il.InvoiceId = i.InvoiceId
JOIN
Track t ON il.TrackId = t.TrackId
WHERE
YEAR(i.InvoiceDate) = 2013
GROUP BY
t.Name
ORDER BY
PurchaseCount DESC;

o Provide a query that shows the top 5 most purchased tracks overall.
SELECT TOP 5
t.Name AS TrackName,
COUNT(il.InvoiceLineId) AS PurchaseCount
FROM
InvoiceLine il
JOIN
Track t ON il.TrackId = t.TrackId
GROUP BY
t.Name
ORDER BY
PurchaseCount DESC;
 o Provide a query that shows the top 3 best-selling artists.
SELECT TOP 3
ar.Name AS ArtistName,
COUNT(il.InvoiceLineId) AS PurchaseCount
FROM
InvoiceLine il
JOIN
Track t ON il.TrackId = t.TrackId
JOIN
Album al ON t.AlbumId = al.AlbumId
JOIN
Artist ar ON al.ArtistId = ar.ArtistId
GROUP BY
ar.Name
ORDER BY
PurchaseCount DESC;

o Provide a query that shows the most purchased Media Type.

SELECT TOP 1
 mt.Name AS MediaType,
COUNT(il.InvoiceLineId) AS PurchaseCount
FROM
InvoiceLine il
JOIN
Track t ON il.TrackId = t.TrackId
JOIN
MediaType mt ON t.MediaTypeId = mt.MediaTypeId
GROUP BY
mt.Name
ORDER BY
PurchaseCount DESC;