Overview
Business questions answered with SQL against a classic sales database of customers, orders, products, suppliers, employees and shippers. I also designed an entity-relationship diagram for a Starbucks customer survey.
Key results
10business questions answered with SQL
4 tablesjoined in one query to rank suppliers by 1997 orders
5related tables designed in the Starbucks survey ER diagram
The question
Which customers, suppliers, shippers and employees drive the business, and how should survey data be structured so it can be queried?
What I did
- Filtered and counted records with WHERE, LIKE and OR conditions
- Joined up to four tables to count orders by country, supplier, shipper and city
- Used subqueries to find the youngest employee and employees with no orders
- Designed a normalized schema for survey data: Survey, General Information, Visitation, Orders and Ratings tables
What I found
Joins and subqueries turned raw tables into quick answers, like which shipper handled the most orders (United Package, 74) and which employee had never taken one. The ER diagram exercise showed how good table design makes those questions easy to ask later.
The Code
The actual sql I wrote for this project. Scroll inside the window to read it all.
-- 1. Customers based in Canada
SELECT COUNT(CustomerName)
FROM Customers WHERE Country= "Canada";
-- Result: 3
-- 2. Customers across North America (Mexico, USA, Canada)
SELECT COUNT(CustomerName)
FROM Customers WHERE Country= "Mexico" or Country= "USA" or Country= "Canada";
-- Result: 21
-- 3. Youngest employee (subquery on max birth date)
SELECT FirstName, LastName, BirthDate
FROM (Employees) WHERE (BirthDate) = (select max((BirthDate))
FROM Employees);
-- Result: Anne Dodsworth 1969-07-02
-- 4. Products with 'Queso' in the name
SELECT COUNT(ProductName)
FROM (Products) WHERE ProductName LIKE '%Queso%';
-- Result: 2
-- 5. Products over $30 from supplier 7
SELECT COUNT(ProductName)
FROM (Products) WHERE Price> 30 and SupplierID= 7;
-- Result: 3
-- 6. Orders by customer country in 1996 (join + group by)
SELECT Customers.Country, COUNT(Customers.Country)
FROM Orders JOIN Customers
ON Orders.CustomerID = Customers.CustomerID WHERE strftime('%Y', Orders.OrderDate) == "1996"
GROUP BY Country;
-- Result: USA 24
-- 7. Orders per supplier in 1997 (four-table join)
SELECT Suppliers.SupplierName, Count(Suppliers.SupplierName)
FROM Orders JOIN OrderDetails
ON Orders.OrderID = OrderDetails.OrderID JOIN Products
ON Products.ProductID = OrderDetails.ProductID
JOIN Suppliers ON Suppliers.SupplierID = Products.SupplierID WHERE strftime('%Y', Orders.OrderDate) == "1997"
GROUP BY SupplierName;
-- Result: Plutzer LebensmittelgroBmärkte AG 10
-- 8. Orders handled by each shipper
SELECT Shippers.ShipperName, COUNT(Shippers.ShipperName)
FROM (Orders) JOIN Shippers
ON Orders.ShipperID = Shippers.ShipperID
GROUP BY ShipperName;
-- Result: United Package 74
-- 9. Orders by customer city
SELECT Customers.City, COUNT(Customers.City)
FROM (Orders) JOIN Customers
ON Orders.CustomerID = Customers.CustomerID
GROUP BY City;
-- Result: Graz
-- 10. Employees who have never taken an order (NOT IN subquery)
SELECT FirstName, LastName
FROM Employees
WHERE Employees.EmployeeID NOT IN (SELECT Orders.EmployeeID FROM Orders);
-- Result: Adam West