Northwind Database SQL Queries
1. Product Names, Supplier Names, and Unit Prices
SELECT
[Link],
[Link],
[Link]
FROM
Products p
JOIN
Suppliers s ON [Link] = [Link];
2. Customers Who Placed Orders in 2023
SELECT
DISTINCT [Link],
[Link]
FROM
Customers c
JOIN
Orders o ON [Link] = [Link]
WHERE
YEAR([Link]) = 2023;
3. Suppliers Providing Products in Each Category
SELECT
[Link],
[Link]
FROM
Categories c
JOIN
Products p ON [Link] = [Link]
JOIN
Suppliers s ON [Link] = [Link]
GROUP BY
[Link], [Link];
4. Products Never Ordered
SELECT
[Link]
FROM
Products p
LEFT JOIN
OrderDetails od ON [Link] = [Link]
WHERE
[Link] IS NULL;
5. Customers with Purchases from >3 Categories and Avg Order Value > $500
SELECT
[Link]
FROM
Customers c
JOIN
Orders o ON [Link] = [Link]
JOIN
OrderDetails od ON [Link] = [Link]
JOIN
Products p ON [Link] = [Link]
GROUP BY
[Link]
HAVING
COUNT(DISTINCT [Link]) > 3
AND AVG([Link] * [Link]) > 500;
6. Number of Products Sold in Each Category
SELECT
[Link],
SUM([Link]) AS TotalSold
FROM
Categories c
JOIN
Products p ON [Link] = [Link]
JOIN
OrderDetails od ON [Link] = [Link]
GROUP BY
[Link];
7. Customers Who Have Not Placed Orders
SELECT
[Link]
FROM
Customers c
LEFT JOIN
Orders o ON [Link] = [Link]
WHERE
[Link] IS NULL;
For customers who have placed orders and their details
SELECT
[Link],
[Link],
[Link],
[Link]
FROM
Customers c
JOIN
Orders o ON [Link] = [Link];
8. Orders Shipped After Required Date
SELECT
[Link],
DATEDIFF([Link], [Link]) AS DaysLate
FROM
Orders o
WHERE
[Link] > [Link];
9. Suppliers with Most Products Sold
SELECT
[Link],
SUM([Link]) AS TotalSold
FROM
Suppliers s
JOIN
Products p ON [Link] = [Link]
JOIN
OrderDetails od ON [Link] = [Link]
GROUP BY
[Link]
ORDER BY
TotalSold DESC;
10. Products Sold by Each Supplier and Suppliers Not Sold Any Products
SELECT
[Link],
COUNT([Link]) AS ProductsSold
FROM
Suppliers s
LEFT JOIN
Products p ON [Link] = [Link]
LEFT JOIN
OrderDetails od ON [Link] = [Link]
GROUP BY
[Link];
11. Customers Who Purchased Products from More than One Category
SELECT
[Link]
FROM
Customers c
JOIN
Orders o ON [Link] = [Link]
JOIN
OrderDetails od ON [Link] = [Link]
JOIN
Products p ON [Link] = [Link]
GROUP BY
[Link]
HAVING
COUNT(DISTINCT [Link]) > 1;
12. Employees with >5 Orders but No 'Tofu'
SELECT
[Link]
FROM
Employees e
JOIN
Orders o ON [Link] = [Link]
LEFT JOIN
OrderDetails od ON [Link] = [Link]
LEFT JOIN
Products p ON [Link] = [Link] AND [Link] LIKE '%Tofu%'
GROUP BY
[Link]
HAVING
COUNT([Link]) > 5
AND SUM(CASE WHEN [Link] IS NOT NULL THEN 1 ELSE 0 END) = 0;
13. Customers Ordered >5 Different Products in 2023
SELECT
[Link]
FROM
Customers c
JOIN
Orders o ON [Link] = [Link]
JOIN
OrderDetails od ON [Link] = [Link]
WHERE
YEAR([Link]) = 2023
GROUP BY
[Link]
HAVING
COUNT(DISTINCT [Link]) > 5;
14. Employees Processing Orders with Products from >3 Categories
SELECT
[Link],
SUM([Link] * [Link]) AS TotalRevenue
FROM
Employees e
JOIN
Orders o ON [Link] = [Link]
JOIN
OrderDetails od ON [Link] = [Link]
JOIN
Products p ON [Link] = [Link]
GROUP BY
[Link]
HAVING
COUNT(DISTINCT [Link]) > 3;