SQL Server interview questions and answers for freshers – Set-2

Please use the same ShopDB database from here.

1. Find the total number of products in each category.
SELECT Category, COUNT(*) AS TotalProducts
FROM Product
GROUP BY Category;

2. List the top 3 products with the highest total charges assigned.
SELECT TOP 3 p.ProductID, p.ProductName, SUM(c.ChargeAmount) AS TotalCharges
FROM Product p
JOIN Charge c ON p.ProductID = c.ProductID
GROUP BY p.ProductID, p.ProductName
ORDER BY TotalCharges DESC;

3. Calculate the average charge amount per product.
SELECT p.ProductID, p.ProductName, AVG(c.ChargeAmount) AS AverageChargeAmount
FROM Product p
JOIN Charge c ON p.ProductID = c.ProductID
GROUP BY p.ProductID, p.ProductName;

4. Find the products that have charges assigned but have not been invoiced.
SELECT DISTINCT p.ProductID, p.ProductName
FROM Product p
JOIN Charge c ON p.ProductID = c.ProductID
LEFT JOIN Invoice i ON c.InvoiceID = i.InvoiceID
WHERE i.InvoiceID IS NULL;

5. Retrieve the invoice(s) with the most products assigned to it.
SELECT i.InvoiceID, COUNT(DISTINCT c.ProductID) AS TotalProducts
FROM Invoice i
JOIN Charge c ON i.InvoiceID = c.InvoiceID
GROUP BY i.InvoiceID
ORDER BY TotalProducts DESC
FETCH FIRST 1 ROWS ONLY;

6. Find the products that have been charged more than the average charge amount of all products.
WITH AverageCharge AS (
SELECT AVG(ChargeAmount) AS AvgCharge
FROM Charge
)
SELECT p.ProductID, p.ProductName, SUM(c.ChargeAmount) AS TotalCharges
FROM Product p
JOIN Charge c ON p.ProductID = c.ProductID
GROUP BY p.ProductID, p.ProductName
HAVING SUM(c.ChargeAmount) > (SELECT AvgCharge FROM AverageCharge);

7. Calculate the cumulative charge amount for each product.
SELECT p.ProductID, p.ProductName, SUM(c.ChargeAmount) OVER (PARTITION BY p.ProductID ORDER BY c.ChargeDate) AS CumulativeCharges
FROM Product p
JOIN Charge c ON p.ProductID = c.ProductID;

8. List the invoices along with the number of products and total charge amount for each invoice.
SELECT i.InvoiceID, COUNT(DISTINCT c.ProductID) AS TotalProducts, SUM(c.ChargeAmount) AS TotalChargeAmount
FROM Invoice i
JOIN Charge c ON i.InvoiceID = c.InvoiceID
GROUP BY i.InvoiceID;

9. Find the product that generated the highest revenue in a specific month (e.g., May 2024).
SELECT TOP 1 p.ProductID, p.ProductName, SUM(c.ChargeAmount) AS TotalRevenue
FROM Product p
JOIN Charge c ON p.ProductID = c.ProductID
JOIN Invoice i ON c.InvoiceID = i.InvoiceID
WHERE MONTH(i.InvoiceDate) = 5 AND YEAR(i.InvoiceDate) = 2024
GROUP BY p.ProductID, p.ProductName
ORDER BY TotalRevenue DESC;

10. List the customers who have been invoiced more than once.
SELECT CustomerID, COUNT(*) AS InvoiceCount
FROM Invoice
GROUP BY CustomerID
HAVING COUNT(*) > 1;

11. Retrieve the details of the most recent charge assignment for each product.
SELECT p.ProductID, p.ProductName, c.ChargeAmount, c.ChargeDate
FROM Product p
JOIN Charge c ON p.ProductID = c.ProductID
WHERE c.ChargeDate = (SELECT MAX(ChargeDate) FROM Chargeassignment WHERE ProductID = p.ProductID);

12. Calculate the monthly revenue for the current year.
SELECT MONTH(i.InvoiceDate) AS Month, SUM(c.ChargeAmount) AS MonthlyRevenue
FROM Invoice i
JOIN Charge c ON i.InvoiceID = c.InvoiceID
WHERE YEAR(i.InvoiceDate) = YEAR(GETDATE())
GROUP BY MONTH(i.InvoiceDate);

13. List the products that have never been invoiced.
SELECT p.ProductID, p.ProductName
FROM Product p
LEFT JOIN Charge c ON p.ProductID = c.ProductID
LEFT JOIN Invoice i ON c.InvoiceID = i.InvoiceID
WHERE i.InvoiceID IS NULL;

14. Retrieve the total charges for each product, sorted by the product with the highest charge first.
SELECT p.ProductID, p.ProductName, SUM(c.ChargeAmount) AS TotalCharges
FROM Product p
JOIN Charge c ON p.ProductID = c.ProductID
GROUP BY p.ProductID, p.ProductName
ORDER BY TotalCharges DESC;

15. Find the average number of products per invoice.
SELECT AVG(ProductCount) AS AverageProductsPerInvoice
FROM (
SELECT i.InvoiceID, COUNT(DISTINCT c.ProductID) AS ProductCount
FROM Invoice i
JOIN Charge c ON i.InvoiceID = c.InvoiceID
GROUP BY i.InvoiceID
) AS InvoiceProductCounts;

16. Identify the product with the most charge assignments.
SELECT TOP 1 p.ProductID, p.ProductName, COUNT(c.ChargeID) AS ChargeCount
FROM Product p
JOIN Charge c ON p.ProductID = c.ProductID
GROUP BY p.ProductID, p.ProductName
ORDER BY ChargeCount DESC;

17. Find the invoices that include charges from multiple products.
SELECT i.InvoiceID
FROM Invoice i
JOIN Charge c ON i.InvoiceID = c.InvoiceID
GROUP BY i.InvoiceID
HAVING COUNT(DISTINCT c.ProductID) > 1;

18. Calculate the total revenue generated by each customer.
SELECT i.CustomerID, SUM(c.ChargeAmount) AS TotalRevenue
FROM Invoice i
JOIN Chargea c ON i.InvoiceID = c.InvoiceID
GROUP BY i.CustomerID;

19. List all products and their respective total charges, even if there are no charges assigned.
SELECT p.ProductID, p.ProductName, COALESCE(SUM(c.ChargeAmount), 0) AS TotalCharges
FROM Product p
LEFT JOIN Charge c ON p.ProductID = c.ProductID
GROUP BY p.ProductID, p.ProductName;

20. Find the total number of invoices issued each year.
SELECT YEAR(InvoiceDate) AS Year, COUNT(*) AS TotalInvoices
FROM Invoice
GROUP BY YEAR(InvoiceDate);

21. Retrieve the products with charges assigned in the last 7 days.
SELECT DISTINCT p.ProductID, p.ProductName
FROM Product p
JOIN Charge c ON p.ProductID = c.ProductID
WHERE c.ChargeDate >= DATEADD(day, -7, GETDATE());

22. Identify the invoice with the highest number of distinct products.
SELECT TOP 1 i.InvoiceID, COUNT(DISTINCT c.ProductID) AS ProductCount
FROM Invoice i
JOIN Charge c ON i.InvoiceID = c.InvoiceID
GROUP BY i.InvoiceID
ORDER BY ProductCount DESC;

23. List the invoices and their total charge amount for each month of the current year.
SELECT MONTH(i.InvoiceDate) AS Month, i.InvoiceID, SUM(c.ChargeAmount) AS TotalChargeAmount
FROM Invoice i
JOIN Charge c ON i.InvoiceID = c.InvoiceID
WHERE YEAR(i.InvoiceDate) = YEAR(GETDATE())
GROUP BY MONTH(i.InvoiceDate), i.InvoiceID
ORDER BY Month, i.InvoiceID;

24. Find the products with charges assigned by more than one invoice.
SELECT p.ProductID, p.ProductName, COUNT(DISTINCT c.InvoiceID) AS InvoiceCount
FROM Product p
JOIN Chargea c ON p.ProductID = c.ProductID
GROUP BY p.ProductID, p.ProductName
HAVING COUNT(DISTINCT c.InvoiceID) > 1;

25. Retrieve the customer with the highest total invoice amount.
SELECT TOP 1 i.CustomerID, SUM(c.ChargeAmount) AS TotalInvoiceAmount
FROM Invoice i
JOIN Charge c ON i.InvoiceID = c.InvoiceID
GROUP BY i.CustomerID
ORDER BY TotalInvoiceAmount DESC;

26. List the invoices where the total charge amount exceeds the average total charge amount of all invoices.
WITH AverageInvoice AS (
SELECT AVG(TotalChargeAmount) AS AvgTotalChargeAmount
FROM (
SELECT SUM(c.ChargeAmount) AS TotalChargeAmount
FROM Invoice i
JOIN Charge c ON i.InvoiceID = c.InvoiceID
GROUP BY i.InvoiceID
) AS InvoiceCharges
)
SELECT i.InvoiceID, SUM(c.ChargeAmount) AS TotalChargeAmount
FROM Invoice i
JOIN Charge c ON i.InvoiceID = c.InvoiceID
GROUP BY i.InvoiceID
HAVING SUM(c.ChargeAmount) > (SELECT AvgTotalChargeAmount FROM AverageInvoice);

27. Calculate the median charge amount for each product.
WITH ChargeRanks AS (
SELECT p.ProductID, p.ProductName, c.ChargeAmount,
ROW_NUMBER() OVER (PARTITION BY p.ProductID ORDER BY c.ChargeAmount) AS RowAsc,
ROW_NUMBER() OVER (PARTITION BY p.ProductID ORDER BY c.ChargeAmount DESC) AS RowDesc
FROM Product p
JOIN Charge c ON p.ProductID = c.ProductID
)
SELECT ProductID, ProductName, AVG(ChargeAmount) AS MedianChargeAmount
FROM ChargeRanks
WHERE RowAsc = RowDesc OR RowAsc + 1 = RowDesc
GROUP BY ProductID, ProductName;

28. Identify the customer who has been invoiced for the highest number of distinct products.
SELECT TOP 1 i.CustomerID, COUNT(DISTINCT c.ProductID) AS ProductCount
FROM Invoice i
JOIN Charge c ON i.InvoiceID = c.InvoiceID
GROUP BY i.CustomerID
ORDER BY ProductCount DESC;

29. Find the total charge amount assigned to products in each category.
SELECT p.Category, SUM(c.ChargeAmount) AS TotalCharges
FROM Product p
JOIN Charge c ON p.ProductID = c.ProductID
GROUP BY p.Category;

30. List the products along with the total charges assigned, average charge amount, and the number of charges assigned.
SELECT p.ProductID, p.ProductName,
SUM(c.ChargeAmount) AS TotalCharges,
AVG(c.ChargeAmount) AS AverageChargeAmount,
COUNT(c.ChargeID) AS ChargeCount
FROM Product p
JOIN Charge c ON p.ProductID = c.ProductID
GROUP BY p.ProductID, p.ProductName;